A semantic layer — measures, dimensions, joins, and metric definitions sitting on top of your marts — is the accuracy backbone for both BI self-serve and text-to-SQL. It is also tedious to author: most of it is a mechanical restatement of structure that already exists in your mart SQL.
Tier: Core · Workflow area: Docs, lineage & governance
This playbook has an LLM draft the semantic model from your marts, then pins correctness the only way that counts: reproducing known-good report numbers.
Don't start here if the marts themselves are unstable; a semantic layer over churning models multiplies the churn.
Collect the ground truth first. Before any generation, assemble 5–10 known-good numbers from trusted reports: "Q1 net revenue = 4,213,882", "active customers March = 18,440", with the exact filters used. These are your acceptance tests; without them, review of the generated layer is vibes.
Feed marts + conventions, ask for the draft. With dbt Copilot this is a built-in generation flow; with a repo-scale assistant, prompt explicitly:
Draft a semantic model (MetricFlow YAML) over these marts:
fct_orders, fct_refunds, dim_customers, dim_products (SQL and schema.yml attached).
Requirements:
- Entities/joins: derive from the actual keys in the SQL, not from column-name
similarity. State the join cardinality you inferred for each.
- Dimensions: every categorical and date column that appears in a GROUP BY
anywhere in the attached dashboard SQL (also attached).
- Measures: sum/count/count_distinct as appropriate; name them with our convention
(net_ vs gross_ prefixes; _usd suffix on monetary).
- Metrics: define net_revenue, refund_rate, active_customers. For each, state IN A
COMMENT the business rule you assumed (e.g. what counts as "active") so a human
can confirm or correct it.
- Do not invent columns. If a metric needs a column that doesn't exist, say so.
Expected output: valid semantic-layer YAML where every assumed business rule is surfaced as a comment — the comments are the review surface.
Review the assumptions, not the syntax. The YAML will parse; that's not the risk. The risk is refund_rate defined as refunds/orders when finance defines it as refund_amount/gross_revenue. Walk the assumption comments with the metric owner and correct definitions before any validation run — this is the human pass that cannot be skipped.
Validate against the known-good numbers. Compile and run each metric through the semantic layer for the exact period/filters of your ground-truth set:
# dbt Semantic Layer example
dbt sl query --metrics net_revenue --group-by metric_time__quarter
Compare to the trusted report values. Exact match, or explainable difference (timezone, late-arriving data, a dashboard that was itself wrong) — chase every discrepancy to a written root cause. A semantic layer that ships with unexplained deltas will be distrusted forever.
Lock it with CI. Encode the ground-truth checks as tests (a scheduled query comparing semantic-layer output to pinned values, or dbt tests on metric outputs) so future edits to marts or metric YAML re-verify automatically.
Point consumers at it. Migrate one dashboard and, if relevant, your text-to-SQL system to query the semantic layer instead of raw marts. The Uber-Finch-style lesson generalizes: text-to-SQL accuracy comes mostly from the semantic layer underneath, not the model on top.
Generated semantic models are validated by reproducing known-good report numbers, never by reading the YAML. Collect the ground-truth numbers before you generate, and chase every delta to a written root cause.
SELECT count(*) FROM fct_orders o LEFT JOIN dim_customers c USING (customer_id) WHERE c.customer_id IS NULL returns what the stated cardinality implies).For client work, the ground-truth numbers are the sensitive artifact — real revenue figures should not appear in prompts to external APIs. Generate the semantic model from structure only (SQL + schema), run all numeric validation inside the client's environment, and if an external assistant is prohibited entirely, use the client's in-platform option (dbt Copilot, Genie) or a self-hosted model. Deliver the validation matrix (metric × ground-truth source × match status × sign-off owner) as part of the engagement artifacts — it doubles as UAT evidence and makes the semantic layer auditable after you roll off.