Curate a Semantic Layer That Makes Text-to-SQL Accurate

Tier: Core · Workflow area: Text-to-SQL & AI-ready serving

Uber's Finch runs on curated single-table marts plus a semantic layer precisely to cut WHERE-clause and join errors; Genie spaces and Cortex Analyst docs say the same thing in vendor language. This playbook is the data engineer's real lever: curated marts, column and value aliases, and certified metric definitions that turn "which of these five revenue columns?" from a coin-flip into a lookup.

When to reach for this

• •
  A text-to-SQL or BI-assistant rollout is planned or underperforming — accuracy complaints trace to wrong tables, wrong filters, or wrong metric definitions.
• •
  The warehouse has multiple plausible homes for the same concept (three revenue columns, four order tables).
• •
  You're standing up Genie spaces / Cortex Analyst and want them to work on day one rather than after months of complaints.

This is prerequisite work for the text-to-SQL build playbook — do it first, or at latest in parallel.

The workflow

1. 1.

   Mine the real question distribution. Collect 50–100 actual analyst questions (BI tickets, Slack threads, dashboard titles). Cluster them by subject. This tells you which marts to curate and becomes your eval set later — curation without a question distribution is decoration.

2. 2.

   Build (or bless) single-table marts per question cluster. Wide, denormalized, one row per business grain, pre-joined and pre-filtered: mart_orders_daily, mart_customer_summary. LLMs fail most on joins and grain; a single-table target removes both failure modes for the 80% of questions that fit. Keep the mart count small — every additional table is another wrong-choice opportunity.

3. 3.

   Write column descriptions for a reader with no context. In dbt YAML (or the platform's metadata store), every column gets: meaning, unit, grain, and the trap ("revenue_inr: order revenue in INR excluding GST and refunds; for gross revenue see revenue_gross_inr"). Descriptions are retrieved into prompts verbatim — write them as prompt text, because they are.

   Copy

   models:
     - name: mart_orders_daily
       description: One row per order per day. Preferred source for all order/revenue questions.
       columns:
         - name: order_status
           description: >
             Lifecycle state. Allowed values: placed, paid, shipped, delivered,
             cancelled, refunded. "Completed orders" means status = 'delivered'.
   

4. 4.

   Add value aliases for the vocabulary gap. Users say "UPI", the column says payment_method = 'unified_payments'; users say "Bangalore", the data says 'Bengaluru'. Maintain an alias map (natural term → column + value) in the metadata store or a dedicated seed table, and make sure the text-to-SQL retrieval layer indexes it. WHERE-clause errors are overwhelmingly vocabulary errors.

5. 5.

   Certify metric definitions in one place. Every metric that matters gets a single, executable definition — dbt semantic layer metric, or a certified SQL snippet in the metadata store: name, formula, filters, grain, owner. "Active user", "net revenue", and "churn" must have exactly one answer each; a model choosing between three ad-hoc definitions is guaranteed to disagree with someone's dashboard.

6. 6.

   Wire the curation into the assistant's context. For a homegrown pipeline: schema cards are generated from these descriptions, and the alias map plus metric definitions are indexed for retrieval. For Genie: curate the space to the marts only, and paste the metric definitions and aliases into its instructions. For Cortex Analyst: encode marts, dimensions, measures, and synonyms in the semantic model YAML. Same curation, three delivery formats.

7. 7.

   Prove the uplift with a before/after eval. Run the same 30-question set (from step 1) against the assistant before and after curation, scoring execution accuracy — did the query run and return the known-correct answer? A typical result worth writing up: wrong-table errors nearly eliminated, WHERE-clause errors halved. This number is how you justify curation time to management.

Verify before you trust it

• Before/after execution accuracyon the fixed eval set, with error categories (wrong table, wrong filter, wrong metric) counted separately.
• Coverage checkevery question cluster from step 1 maps to a curated mart; every high-traffic metric has exactly one certified definition.
• Freshness wiringschema-card regeneration runs in CI on dbt changes — a curated layer that drifts from the warehouse is worse than none, because it teaches the model stale truths.
• Spot-check retrievalfor 10 sample questions, confirm the retrieved schema cards and aliases are actually the relevant ones.

Pitfalls

• Curating tables instead of questions. Beautifully documented tables nobody queries don't move accuracy; the question distribution decides what deserves curation.
• Descriptions written for humans with context. "Standard revenue field" is useless in a prompt; state the unit, the exclusions, and when to prefer another column.
• Letting definitions multiply. If the semantic layer holds two "active user" definitions, you've shipped the ambiguity you were hired to remove.
• One-time curation. Without CI hooks, the semantic layer decays with every migration and the assistant's accuracy quietly follows.
• Boiling the warehouse. Curate one domain to high quality and prove uplift; a shallow pass over everything helps nothing.

Tools

• dbt (semantic layer + YAML docs)column descriptions, certified metrics, and CI-refreshed schema cards; dbt Core keeps the path free.
• Databricks Geniecurated spaces consume exactly this work; Free Edition suffices for practice.
• Snowflake Cortex Analystsemantic model YAML with synonyms and measures; trial credits cover a lab.

Client-safe variant

Semantic-layer curation is unusually client-safe as AI work goes — it's metadata engineering, and most of it (marts, dbt docs, metric definitions) involves no LLM at all. The care points: column descriptions and metric definitions can encode confidential business logic (margin formulas, churn definitions), so the metadata store and any assistant indexing it must stay within the client's approved boundary, and you should confirm whether their BI-assistant vendor's terms allow schema metadata in prompts. Deliver the curation as versioned dbt code plus a metrics-governance doc naming an owner per metric, and leave the before/after eval harness behind — it's how the client's team measures whether future curation keeps paying off.