semantic-layer-audit

Semantic Layer Audit

Why this matters: AI agents can only use data they know about. This skill maintains a living data catalog so you never lose track of available datasets, APIs, or credentials.

Prerequisites

• GCP Project with BigQuery enabled
• gcloud CLI authenticated (gcloud auth login)
• bq CLI (comes with gcloud)
• Environment variable: GCP_PROJECT_ID set to your project

# Set your project
export GCP_PROJECT_ID="your-project-id"

# Verify access
gcloud config set project $GCP_PROJECT_ID
bq ls --project_id=$GCP_PROJECT_ID

Quick Start

# Run full infrastructure audit
python3 scripts/audit_infrastructure.py > audit-results.json

# Review and update your semantic layer doc
# (see templates/SEMANTIC-LAYER-TEMPLATE.md)

When to Run

Trigger	Action
"What data do we have?"	Full audit
"Audit data sources"	Full audit
After creating views	Document with full schema + usage guidance
After new integrations	Infrastructure scan
Weekly maintenance	Cron: catch schema changes
Before complex analysis	Verify sources exist

---

Documentation Standards

The Problem

Most data catalogs are just lists of table names. This leads to:

• Agents querying wrong tables
• Missing context about when to use what
• No guidance on data source transitions
• Confusion when numbers don't match between sources

The Solution: Documentation Levels

Level 1: Table Documentation (Minimum)

Every table needs these fields:

Field	Required	Example
Name	✅	orders
Description	✅	"All completed orders"
Key Fields	✅	order_id, created_at, total
Granularity	✅	Per-order
Source	✅	"Synced from Shopify API"

Level 2: View Documentation (Full Detail)

Views require MORE documentation because users need to know when to use them vs raw tables:

#### `daily_sales_summary` (VIEW)

**Purpose:** Pre-aggregated daily sales metrics. Faster than aggregating raw orders.

**When to use:**
- Daily/weekly/monthly revenue trends
- High-level reporting dashboards
- Quick "how did yesterday go?" questions

**When NOT to use:**
- Need individual order details → use `orders`
- Need customer-level data → use `orders` joined with `customers`

| Column | Type | Description |
|--------|------|-------------|
| `date` | DATE | Sales date |
| `total_revenue` | FLOAT64 | Sum of order totals (dollars) |
| `order_count` | INT64 | Number of orders |
| `avg_order_value` | FLOAT64 | Revenue / orders |

**Example:**
\`\`\`sql
-- Last 30 days revenue trend
SELECT date, total_revenue, order_count
FROM `project.dataset.daily_sales_summary`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
ORDER BY date
\`\`\`