Gowtham Potureddi

Posted on Jul 31

dbt Copilot, Snowflake Cortex Analyst & Databricks AI/BI Genie: LLM Assistants for Analytics Teams

#python #sql #interview #dataengineering

llm assistants for analytics are the pick-one UX decision that decides whether your analytics engineers spend the next quarter authoring boilerplate models by hand or shipping curated semantic layers that a business user can talk to in English — and it is the single tooling decision senior data engineers get wrong most often because "just add ChatGPT" is not a strategy. Every warehouse vendor now bundles an LLM assistant into the console, every transformation vendor bundles one into the IDE, and every self-serve BI vendor now bundles one into the dashboard editor. The engineering trade-off does not live in "should we have an LLM assistant" — every stack with more than one analyst needs some form of natural-language front door — but in which assistant you deploy to which persona and what grounding source you feed it.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through the three vendor LLM assistants and their grounding stories," or "our CFO wants to ask questions in English — where do we put the semantic layer?", or "explain how you'd stop text-to-sql from hallucinating a join across two tables that share a column name but not a foreign key." It walks through the three canonical vendor assistants — dbt copilot (project-graph-grounded authoring inside dbt Cloud), snowflake cortex analyst (YAML-semantic-model-grounded natural language to sql REST API), and databricks ai/bi genie (Unity-Catalog-scoped natural-language BI over sample values and column comments) — the "four axes" interviewers actually probe (grounding source, governance boundary, latency budget, human review), the canonical setup for each, and the stacked pattern where dbt cloud ai authors the semantic layer upstream and Cortex Analyst / Genie serve it downstream. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse the SQL-generation practice library →, and sharpen the analytics axis with the data-analysis practice library →.

On this page

Why LLM assistants are the new UX layer for analytics teams in 2026
dbt Copilot — model-authoring, docs, and semantic-layer completions
Snowflake Cortex Analyst — governed text-to-SQL over your semantic model
Databricks AI/BI Genie — natural-language BI over Unity Catalog
Picking / stacking the assistants — decision matrix + interview signals
Cheat sheet — LLM assistant recipes
Frequently asked questions
Practice on PipeCode

1. Why LLM assistants are the new UX layer for analytics teams in 2026

Four assistants, four wildly different grounding stories — the pick that binds your semantic layer for years

The one-sentence invariant: an llm data assistant for analytics is a picking exercise between authoring-time completions grounded on your dbt project graph, serving-time text-to-SQL grounded on a hand-authored YAML semantic model, natural-language BI grounded on Unity Catalog comments and sample values, or an ungrounded generic chatbot that hallucinates joins on ambiguous column names — and each assistant trades grounding fidelity against governance boundary, target persona, and latency budget in a way that surfaces as either a productivity boost or a compliance incident depending on how you deploy it. The assistant you pick in month one becomes the grounding contract you fight to maintain in year three, because every downstream question, every trained business user, and every executive dashboard hard-codes assumptions about which metric definition is "correct" and which synonyms map to which columns.

The four axes interviewers actually probe.

Grounding source. dbt Copilot grounds on the compiled manifest.json and catalog.json — every ref(), every source, every existing macro is context. Cortex Analyst grounds on a hand-authored YAML semantic layer file that declares tables, dimensions, measures, time-dimensions, filters, and synonyms. Genie grounds on Unity Catalog table comments, column comments, sample values, and curated example queries. A generic LLM (raw ChatGPT / Claude API) grounds on whatever the user pastes into the prompt — usually nothing. Interviewers open with this question because grounding is what separates a useful assistant from a hallucinating one.
Governance boundary. Cortex Analyst executes the generated SQL with the caller's Snowflake role — row-level policies and column masks bind automatically. Genie executes with Unity Catalog governance — same story. dbt Copilot only drafts SQL; a human commits and dbt Cloud runs it under the project's warehouse credentials. Generic LLMs have no governance boundary — they'll suggest queries against tables the user has no grant on, and it's on you to enforce access at the warehouse.
Target persona. dbt Copilot is aimed at analytics engineers — the people writing models. Cortex Analyst is aimed at analysts and developers — anyone writing SQL against the semantic model or embedding the REST API in an internal app. Genie is aimed at business users — sales ops, marketing analysts, finance partners who never learned SQL. Deploying the wrong tool to the wrong persona is the fastest way to erode trust: a business user getting a hallucinated Copilot completion assumes it's correct; an analyst getting a Genie chat window feels handcuffed.
Latency + human review. dbt Copilot completions are inline, ~500 ms; a human always reviews before commit. Cortex Analyst REST calls are 2-5 seconds and return the generated SQL for programmatic review before execution (or you can auto-execute — your call). Genie is 3-8 seconds per turn, and end users see the SQL below the chart so they can flag surprises. Generic LLMs vary wildly; usually no built-in review loop.

The 2026 reality — every vendor ships an assistant, but only three ship one with a real grounding story.

dbt Copilot is the default authoring assistant for any team using dbt Cloud IDE in 2026. It reads your compiled project graph on every completion, so ref('dim_customers') autocompletes and generated SQL references real columns from your catalog.json. Prior to Copilot, the equivalent was "GitHub Copilot in your IDE guessing." Ground-truth grounding is what separates a project-aware assistant from a hallucinating one.
Snowflake Cortex Analyst is the default text-to-SQL layer for teams whose analytics warehouse is Snowflake. It's a REST API on top of Cortex; you author a semantic_model.yaml describing your tables, measures, and dimensions; you POST a natural-language question; it returns generated SQL, an interpretation, and optionally a chart spec. The Snowflake role executing the query is the caller's role — governance is inherited.
Databricks AI/BI Genie is the default natural-language BI experience inside Databricks. Genie spaces are scoped to a specific set of Unity Catalog tables; business users chat with the space; Genie generates SQL grounded on the tables' comments, sample values, and any curated example queries you've supplied. AI/BI Dashboards embed Genie as a "ask a follow-up" pane.
Generic LLM wrappers — Cursor / VS Code + raw Claude / GPT-4o — are alive and well for one-off analysis, exploration, and coding help, but they lack the grounding contract that vendor assistants supply. They belong in the engineer's toolbox, not in the business user's dashboard.

What interviewers listen for.

Do you name all three vendor assistants without prompting? — senior signal.
Do you say "grounding" in the first sentence when the interviewer asks about hallucination? — required answer.
Do you push back on "we'll just wrap an LLM" with the governance question — "whose role does the SQL run under?" — senior signal.
Do you name the semantic layer as the thing every assistant needs to be grounded on, not as "a dbt feature"? — senior signal.
Do you describe LLM assistants as "UX layers over a grounding source" rather than as vague "AI features"? — required answer.

Worked example — the four-axis comparison table

Detailed explanation. The single most useful artifact for an LLM-assistant interview is a memorised 4×4 comparison table. Every senior discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for a hypothetical B2B SaaS analytics team migrating from raw SQL to LLM-assisted workflows across three personas.

Team shape. 3 analytics engineers (author dbt models), 6 analysts (write SQL, build dashboards), ~50 business users (marketing, sales ops, finance) who never write SQL.
Warehouse. Snowflake — primary; a Databricks lakehouse for ML feature engineering.
Transformation. dbt Cloud managed project against the Snowflake warehouse.
Governance. SOC2 Type II; row-level policies on customer data; column masks on PII.

Question. Build the four-assistant comparison for this team and assign the right assistant to each persona.

Input.

Assistant	Grounding source	Persona	Governance	Latency
dbt Copilot	manifest.json + catalog.json	analytics engineer	project-scoped; human commits	~500 ms inline
Cortex Analyst	YAML semantic model	analyst / dev	caller's Snowflake role	2-5 s per REST call
AI/BI Genie	Unity Catalog + example queries	business user	Unity Catalog policies	3-8 s per turn
Generic LLM	none (whatever prompt says)	engineer (ad hoc)	none	1-10 s

Code.

# Semantic layer YAML fragment — the grounding contract for Cortex Analyst
name: revenue_model
description: |
  Grounded semantic model over fct_orders + dim_customers for the
  finance and sales-ops teams. Every measure is documented; every
  synonym is enumerated; every FK relationship is declared.

tables:
  - name: fct_orders
    description: One row per order; grain is order_id.
    base_table:
      database: PROD_ANALYTICS
      schema:   MART
      table:    FCT_ORDERS
    dimensions:
      - name: order_date
        expr: order_date
        data_type: DATE
        synonyms: [date, day, order day]
      - name: region
        expr: region
        data_type: TEXT
        synonyms: [geo, territory, sales region]
    measures:
      - name: revenue
        expr: SUM(total_cents) / 100.0
        data_type: NUMBER
        description: Gross revenue in USD (dollars, not cents).
        synonyms: [sales, gross revenue, total sales]
    time_dimensions:
      - name: order_date
        expr: order_date
        data_type: DATE
        synonyms: [date, day, order day]

Step-by-step explanation.

The team's three personas each need a different UX. Analytics engineers live inside dbt Cloud IDE — dbt Copilot is where their productivity gain lives, and the grounding source is already the artifact they maintain (the dbt project). No extra work.
Analysts live in Snowsight (Snowflake's UI) and internal apps that call the warehouse programmatically. They need natural language to sql that respects the metric definitions the analytics engineers curated. Cortex Analyst is grounded on a YAML semantic model — someone has to author that YAML, but it's a bounded artifact scoped to one metric domain (revenue, funnel, retention) at a time.
Business users live in dashboards. They want to ask "why did revenue drop in the west region last week?" and get an answer with a chart. Genie spaces are scoped to specific Unity Catalog tables and grounded on table + column comments and curated example queries. The important word is scoped: a Genie space over the marketing tables cannot join to finance tables, which prevents cross-domain hallucinations.
The generic LLM row is the strawman. Every candidate proposes "let's just wrap Claude" at some point. Naming the grounding gap ("Claude doesn't know your column names, your metric definitions, or your row-level policies") is the senior response.
The most common architectural mistake is deploying one assistant to all three personas. A business user getting a raw Cortex Analyst text-to-SQL prompt without a chart wrapper feels lost; an analytics engineer inside a Genie chat window feels handcuffed. Match the assistant to the persona.

Output.

Persona	Recommended assistant	Grounding source	Why
Analytics engineer	dbt Copilot	manifest.json + catalog.json	authoring layer; drafts models + docs
Analyst	Cortex Analyst REST	semantic_model.yaml	governed text-to-SQL for internal apps
Business user	Genie space	Unity Catalog + example queries	chat + chart; scoped to marketing tables
Engineer (ad hoc)	generic LLM (Claude / Cursor)	prompt only	exploration; never in production

Rule of thumb. Never pick an LLM assistant based on "which one has the most demos." Pick it based on (grounding × persona × governance × latency) — the four axes. Draw the assignment table on a whiteboard first; the vendor falls out of the constraints.

Worked example — what interviewers actually probe

Detailed explanation. The senior data / analytics engineering interview on LLM assistants has a predictable structure: the interviewer opens with an ambiguous question ("how would you let our business users query the warehouse in English?"), then progressively narrows to test whether you know the axes. Candidates who name the grounding source in sentence one score highest; candidates who describe "a Slack bot with ChatGPT" score lowest. Walk through the interview grading rubric.

Ambiguous opener. "How would you let a non-technical exec ask our warehouse questions?" — invites you to name a vendor + grounding source.
Follow-up 1. "How do you stop it from hallucinating joins?" — probes grounding axis.
Follow-up 2. "How do you enforce row-level access?" — probes governance axis.
Follow-up 3. "What's the latency budget?" — probes latency + persona axis.
Follow-up 4. "How would you evaluate whether the assistant is answering correctly?" — probes review + eval-harness axis.

Question. Draft a 5-minute senior LLM-assistant answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Vendor + grounding named	"we'd use ChatGPT"	"Cortex Analyst grounded on a YAML semantic model"
Hallucination story	"we'd fine-tune it"	"grounded on declared measures + synonyms; no free-form joins"
Governance	"we'd add auth"	"runs under caller's Snowflake role; RLS and column masks inherited"
Latency budget	"should be fast"	"2-5 s per REST call; async for long queries"
Evaluation	"we'd try it"	"golden-question set; ANSWER-vs-EXPECTED SQL diff; semantic equivalence"

Code.

Senior LLM-assistant answer template (5 minutes)
================================================

Minute 1 — name the assistant + grounding source up front
  "For business-user natural-language BI on our Snowflake stack,
   I'd deploy Snowflake Cortex Analyst, grounded on a hand-authored
   YAML semantic model that declares our tables, measures, dimensions,
   time-dimensions, and synonyms. For the dashboard experience I'd
   wrap it in an internal app that shows the generated SQL and the
   interpretation alongside the answer."

Minute 2 — hallucination containment
  "The YAML semantic model is the grounding contract. The LLM cannot
   generate a measure that isn't declared, can't join tables that
   don't have a declared FK, can't reference columns that aren't in
   the model. Synonyms map user-facing language ('sales', 'revenue',
   'gross') to the same underlying measure. This is qualitatively
   different from wrapping a generic LLM — the grounding source
   bounds the hypothesis space."

Minute 3 — governance
  "Cortex Analyst returns generated SQL, which is then executed via
   Snowflake with the caller's role. Row-access policies, column
   masks, and warehouse grants all bind automatically. The LLM never
   sees the row values — it only sees the schema described by the
   YAML. This is critical for SOC2 and HIPAA workloads."

Minute 4 — latency + human review
  "REST latency is 2-5 s per turn. For interactive chart panes we
   render a spinner and stream the interpretation; for programmatic
   use we return the generated SQL for a rules-based safety check
   before we execute it. Every response ships the SQL below the
   chart so power users can spot-check."

Minute 5 — evaluation harness
  "We maintain a golden-question suite — 100-500 questions per
   semantic model, each with an expected SQL query. On every model
   change we run the suite: generate SQL, semantically diff against
   expected (canonicalise, ignore alias order, compare join graph +
   filter set + measure), report exact-match rate + semantic-match
   rate. A regression on either metric blocks the merge."

Step-by-step explanation.

Minute 1 is the crucial framing. Naming the assistant and the grounding source immediately — "Cortex Analyst grounded on a YAML semantic model" — signals you know what makes an assistant useful. Weak candidates name a tool without a grounding story ("we'd use ChatGPT / Claude / a copilot").
Minute 2 addresses hallucination before the interviewer asks. The naive "we'll fine-tune it" answer is a red flag — fine-tuning does not stop hallucination on a schema the model has never seen. The senior answer is "the grounding source is the safety rail; the LLM can only generate against what's declared."
Minute 3 is the governance probe. Cortex Analyst (and Genie) executing under the caller's role is the killer feature vs a generic LLM app that has its own service account. Naming row-access policies and column masks unprompted is senior signal.
Minute 4 is the latency + human-review argument. Every generated response ships the SQL for human spot-check; this is the "trust but verify" contract. Answering "our users don't need to see the SQL" is a red flag — they always need to see it, even if 90% of them never look.
Minute 5 is the evaluation-harness answer. The single biggest professional-signal marker is "we have a golden-question suite and we run it on every model change." Candidates who name the eval harness get graded senior; candidates who don't get graded mid.

Output.

Grading criterion	Weak score	Senior score
Names vendor + grounding in minute 1	rare	mandatory
Names hallucination containment	rare	required
Names governance mechanism	occasional	mandatory
Names latency budget + review UX	rare	senior signal
Names evaluation harness	rare	senior signal

Rule of thumb. The senior LLM-assistant answer is a 5-minute monologue that covers all four axes without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the assistant" decision tree

Detailed explanation. Given a new analytics workload, the senior architect runs a 4-question decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk the tree out loud. Walk through the tree with three canonical scenarios: authoring a new dbt project, embedding text-to-SQL in an internal analyst tool, and giving a marketing team a chat-with-your-data dashboard.

Q1. Is the user an analytics engineer authoring transformations? → yes = dbt Copilot; no = go to Q2.
Q2. Is the warehouse Snowflake and the user an analyst or developer needing SQL? → yes = Cortex Analyst REST API; no = go to Q3.
Q3. Is the warehouse Databricks and the user a business user wanting a chart? → yes = AI/BI Genie space; no = go to Q4.
Q4. Does the user need one-off exploration outside a governed workload? → yes = generic LLM in Cursor / VS Code; no = tell them "no LLM yet — build the grounding first."
Q5 (parallel branch). Is the semantic layer defined? → no = author it first (dbt semantic models + a YAML export for Cortex Analyst + Unity Catalog comments for Genie).

Question. Walk the decision tree for the three scenarios and record the assistant each ends up with.

Input.

Scenario	Q1 (author?)	Q2 (Snowflake analyst?)	Q3 (Databricks BI?)	Q5 (semantic layer?)
Author new dbt project	yes	—	—	in progress
Analyst internal tool on Snowflake	no	yes	no	yes (revenue_model.yaml)
Marketing chat dashboard on Databricks	no	no	yes	yes (comments + examples)

Code.

# Decision-tree helper (illustrative)
def pick_llm_assistant(is_authoring: bool,
                        is_snowflake_analyst: bool,
                        is_databricks_business_user: bool,
                        semantic_layer_defined: bool,
                        is_exploration: bool) -> list[str]:
    """Return the primary LLM assistant recommendation."""
    picks = []

    if is_authoring:
        picks.append("dbt Copilot")

    if is_snowflake_analyst:
        if not semantic_layer_defined:
            raise ValueError("author revenue_model.yaml before enabling Cortex Analyst")
        picks.append("Snowflake Cortex Analyst")

    if is_databricks_business_user:
        if not semantic_layer_defined:
            raise ValueError("populate UC comments + example queries before enabling Genie")
        picks.append("Databricks AI/BI Genie")

    if is_exploration and not picks:
        picks.append("generic LLM (Cursor / Claude)")

    if not picks:
        raise ValueError("no assistant matches; refine the persona question")

    return picks


# Walk the three scenarios
print(pick_llm_assistant(True,  False, False, True,  False))
# → ['dbt Copilot']

print(pick_llm_assistant(False, True,  False, True,  False))
# → ['Snowflake Cortex Analyst']

print(pick_llm_assistant(False, False, True,  True,  False))
# → ['Databricks AI/BI Genie']

Step-by-step explanation.

Scenario 1 — an analytics engineer authoring a new dbt project. Q1 short-circuits at "yes" → dbt Copilot. This is the modern default for the authoring persona. Copilot grounds on whatever exists in the project graph; on a greenfield project the completions become richer as the project fills out.
Scenario 2 — an analyst embedding SQL into an internal margin-analysis app on Snowflake. Q2 = yes → Cortex Analyst REST API. The Q5 guard is critical: without a semantic_layer YAML, Cortex Analyst has nothing to ground on and its output collapses to whatever the LLM guesses from table names. Author the YAML before enabling the assistant.
Scenario 3 — a marketing team wanting a chart-with-conversation experience on their Databricks lakehouse. Q3 = yes → Genie space. Same Q5 guard: without populated Unity Catalog comments and a handful of curated example queries, the space returns brittle answers. The setup is "populate comments → curate examples → enable Genie".
The parallel Q5 branch (semantic layer) is orthogonal to the primary pick. Every serving-layer assistant (Cortex Analyst, Genie) requires a defined semantic layer to ground on. Refusing to enable the assistant until the semantic layer is ready is senior signal — it prevents shipping a hallucinating tool.
If none of Q1-Q4 pass, the honest answer is "the request isn't specific enough — refine the persona." Deploying an assistant to "everyone" is the fastest way to burn goodwill; scope it to a persona and a use case first.

Output.

Scenario	Primary assistant	Prereq
Author dbt project	dbt Copilot	dbt Cloud IDE enabled
Analyst app on Snowflake	Cortex Analyst REST	revenue_model.yaml authored
Marketing chat on Databricks	AI/BI Genie space	Unity Catalog comments + example queries

Rule of thumb. The five-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get a vendor name in under 60 seconds. And always name the semantic-layer prerequisite for the serving-layer assistants.

Senior interview question on LLM assistant selection

A senior interviewer often opens with: "You inherit a Snowflake + dbt analytics stack with 40 dashboards, 6 analysts, and a leadership team that wants to 'ask questions in English.' The previous team shipped a raw ChatGPT slack bot; it hallucinates joins and leaks PII. Walk me through the LLM-assistant strategy you'd deploy, the grounding sources you'd author, and the evaluation harness you'd ship on day one."

Solution Using a stacked assistant deployment — dbt Copilot for authors, Cortex Analyst for analysts, and a golden-question eval harness

# 1. Semantic model YAML — the grounding contract for Cortex Analyst
#    File: semantic_models/revenue_model.yaml
name: revenue_model
description: |
  Governed revenue semantic model for the finance + sales-ops teams.
  Grounded on fct_orders (grain: order_id) joined to dim_customers,
  dim_products, and the date spine. Every measure is declared; every
  synonym is enumerated. Cortex Analyst is not allowed to generate
  a measure that isn't in this file.

tables:
  - name: fct_orders
    description: One row per order; grain is order_id.
    base_table:
      database: PROD_ANALYTICS
      schema:   MART
      table:    FCT_ORDERS
    primary_key:
      columns: [order_id]

    dimensions:
      - name: order_date
        expr: order_date
        data_type: DATE
        synonyms: [date, day, order day, transaction date]
      - name: region
        expr: region
        data_type: TEXT
        synonyms: [geo, territory, sales region]
      - name: customer_segment
        expr: customer_segment
        data_type: TEXT
        synonyms: [segment, tier, customer tier]

    time_dimensions:
      - name: order_date
        expr: order_date
        data_type: DATE

    measures:
      - name: revenue
        expr: SUM(total_cents) / 100.0
        data_type: NUMBER
        description: Gross revenue in USD.
        synonyms: [sales, gross revenue, total sales, top line]
      - name: order_count
        expr: COUNT(*)
        data_type: NUMBER
        description: Number of orders.
        synonyms: [orders, transactions, num orders]
      - name: unique_customers
        expr: COUNT(DISTINCT customer_id)
        data_type: NUMBER
        description: Distinct customers with at least one order.
        synonyms: [customers, buyers, unique buyers]

verified_queries:
  - name: revenue_by_region_last_quarter
    question: What was our revenue by region last quarter?
    sql: |
      SELECT region, SUM(total_cents)/100.0 AS revenue
      FROM   PROD_ANALYTICS.MART.FCT_ORDERS
      WHERE  order_date BETWEEN DATE_TRUNC('quarter', DATEADD('quarter', -1, CURRENT_DATE))
                            AND LAST_DAY(DATEADD('quarter', -1, CURRENT_DATE))
      GROUP  BY region
      ORDER  BY revenue DESC;

# 2. Golden-question evaluation harness (runs in CI on every YAML change)
#    File: eval/run_golden_questions.py
import json
import requests
from pathlib import Path
from sql_metadata import Parser   # canonicalises SQL for semantic diff

CORTEX_URL   = "https://<account>.snowflakecomputing.com/api/v2/cortex/analyst/message"
SEMANTIC_YAML = "@PROD_ANALYTICS.SEMANTIC_MODELS/revenue_model.yaml"

def canonicalise(sql: str) -> dict:
    """Reduce SQL to (tables, join_graph, filter_set, measure_set, group_by)."""
    p = Parser(sql)
    return {
        "tables":     sorted(p.tables),
        "columns":    sorted(p.columns),
        "with_names": sorted(p.with_names or []),
    }

def score_semantic_match(expected: str, actual: str) -> float:
    e = canonicalise(expected)
    a = canonicalise(actual)
    hits = sum(1 for k in e if e[k] == a.get(k))
    return hits / len(e)

def run_golden_question(q: dict, token: str) -> dict:
    resp = requests.post(
        CORTEX_URL,
        json={
            "messages": [{"role": "user", "content": [{"type": "text", "text": q["question"]}]}],
            "semantic_model_file": SEMANTIC_YAML,
        },
        headers={"Authorization": f"Bearer {token}"},
        timeout=30,
    )
    resp.raise_for_status()
    payload = resp.json()
    sql_returned = next(
        (c["statement"] for m in payload["message"]["content"]
         for c in [m] if c.get("type") == "sql"),
        None,
    )
    return {
        "question":       q["question"],
        "expected_sql":   q["expected_sql"],
        "actual_sql":     sql_returned,
        "semantic_match": score_semantic_match(q["expected_sql"], sql_returned or ""),
    }

def main():
    questions = json.loads(Path("eval/golden_questions.json").read_text())
    token     = Path("/run/secrets/snowflake_pat").read_text().strip()
    results   = [run_golden_question(q, token) for q in questions]

    n        = len(results)
    exact    = sum(1 for r in results if r["semantic_match"] == 1.0)
    partial  = sum(r["semantic_match"] for r in results)
    print(f"exact-match: {exact}/{n}  ({exact/n:.1%})")
    print(f"semantic-avg: {partial/n:.2f}")

    if exact / n < 0.90 or partial / n < 0.95:
        raise SystemExit("golden-question regression; blocking merge")

if __name__ == "__main__":
    main()

// 3. Sample golden-question file
[
  {
    "question": "What was our revenue by region last quarter?",
    "expected_sql": "SELECT region, SUM(total_cents)/100.0 AS revenue FROM PROD_ANALYTICS.MART.FCT_ORDERS WHERE order_date BETWEEN DATE_TRUNC('quarter', DATEADD('quarter', -1, CURRENT_DATE)) AND LAST_DAY(DATEADD('quarter', -1, CURRENT_DATE)) GROUP BY region ORDER BY revenue DESC"
  },
  {
    "question": "How many unique customers placed orders in the enterprise segment last month?",
    "expected_sql": "SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM PROD_ANALYTICS.MART.FCT_ORDERS WHERE customer_segment = 'enterprise' AND order_date >= DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE)) AND order_date < DATE_TRUNC('month', CURRENT_DATE)"
  }
]

Step-by-step trace.

Step	Before (raw ChatGPT bot)	After (stacked assistants + eval)
Grounding	none (prompt only)	dbt project graph + revenue_model.yaml + UC comments
Author persona	not addressed	dbt Copilot inline in dbt Cloud IDE
Analyst persona	ChatGPT bot	Cortex Analyst REST wrapped in analyst tool
Business-user persona	ChatGPT bot	(future) Genie space over marketing tables
Governance	bot has service account	Cortex Analyst executes as caller
Hallucination containment	none	YAML declares measures + synonyms
Evaluation	none	golden-question suite in CI
Reg-block on regression	none	exact-match < 90% blocks merge

After the migration, the analytics engineers get inline dbt Copilot completions grounded on the compiled project; the analyst app calls Cortex Analyst with revenue_model.yaml as the grounding source and every generated SQL runs under the caller's Snowflake role; the golden-question harness runs in CI on every YAML change and blocks merges that break more than 10% of the questions. The raw ChatGPT slack bot is retired.

Output:

Metric	Before	After
Hallucinated joins in generated SQL	~40% of responses	< 5%
PII leak risk	service account had wide grants	caller role only
Governance boundary	none	RLS + column masks inherited
Semantic-match on golden questions	not measured	95%+
Time-to-first-generated-SQL	8 s	3-5 s (Cortex) / 500 ms (Copilot)

Why this works — concept by concept:

Stacked assistants — dbt Copilot at the authoring layer, Cortex Analyst at the serving layer, Genie (future) at the business-user layer. Each assistant grounds on the artifact its persona already produces or reads; no assistant is asked to do the work of another.
YAML semantic model — the declarative grounding contract. Measures are defined once, synonyms are enumerated, joins are declared. Cortex Analyst cannot generate a measure that isn't in the YAML; the hypothesis space is bounded by design.
Caller-role execution — Cortex Analyst returns the generated SQL, then Snowflake executes it under the caller's role. Row-access policies, column masks, warehouse grants all bind automatically. The LLM never touches row values.
Verified queries block — the verified_queries section of the YAML acts as few-shot examples for the model and as documentation for humans. Cortex Analyst is more likely to produce a correct answer for a question that closely matches a verified query.
Golden-question harness — the deterministic regression check. Every YAML change runs the suite; semantic-diff against expected SQL; block merges below the threshold. This is what turns an LLM assistant from a demo into a shippable product. Cost: O(1) API call per golden question per CI run; typical suites run in under 60 seconds.

SQL
Topic — sql
SQL problems on semantic-layer and text-to-SQL patterns

Practice →

SQL Generation Topic — sql-generation SQL-generation and natural-language-to-SQL problems

Practice →

2. dbt Copilot — model-authoring, docs, and semantic-layer completions

`dbt copilot` grounds on the project graph — the authoring assistant that turns manifest.json into inline completions

The mental model in one line: dbt copilot is the LLM assistant embedded in the dbt Cloud IDE that grounds every completion, refactor, and generation on the compiled dbt project (manifest.json, catalog.json, existing macros, upstream refs) — it drafts model SQL, YAML docs, tests, and semantic-layer metric definitions with knowledge of your actual column names and lineage, and its productivity gain is entirely proportional to how well-modelled your existing project already is. Every analytics engineer using dbt Cloud in 2026 has Copilot available; the difference between teams getting value and teams generating hallucinated ref() calls is whether the project's manifest is fresh and the seeds / sources are complete.

The four axes for dbt Copilot.

Grounding. Compiled project graph — manifest.json (every ref(), every source(), every macro, every test) plus catalog.json (columns, data types, row counts). Copilot re-reads the manifest on every completion; a stale manifest = stale completions. This is the strongest grounding of any authoring assistant on the market.
Persona. Analytics engineers writing dbt models, tests, YAML docs, and semantic-model metrics. Not aimed at business users; not aimed at analysts writing ad-hoc SQL. If your team doesn't author dbt models, Copilot is not the assistant for you.
Governance. Project-scoped, not warehouse-scoped. Copilot only drafts SQL — a human commits and dbt Cloud runs it under the project's warehouse credentials. Copilot itself has no runtime governance boundary; the safety comes from the human review + CI pipeline (dbt test, sqlfluff, code review).
Latency + review. Inline, ~500 ms per completion. Every suggestion is reviewed by the analytics engineer at author time; there is no auto-commit path. This "always reviewed" contract is what makes Copilot safe for authoring workloads even without a runtime governance layer.

What Copilot actually generates — the four common patterns.

Model SQL from a natural-language prompt. "Build an incremental fct_orders from stg_orders and stg_order_lines, grain order_id, with a is_backfill flag." Copilot reads the referenced staging models' columns, drafts a SELECT with the right joins, adds the incremental config, and inserts a Jinja is_incremental() block.
YAML docs and tests. "Document fct_orders and add tests for the primary key + not-null on customer_id." Copilot reads the model's columns from the catalog, drafts a YAML block with descriptions and adds unique + not_null tests.
Refactors. "Refactor this CTE into a separate staging model." Copilot extracts the CTE, creates a new staging file, wires the ref(), and rewrites the calling model.
Semantic-layer metrics. "Define a total_revenue metric on fct_orders." Copilot drafts a semantic_models.yml metric block with the measure expression, dimension bindings, and description.

The manifest freshness problem — the failure mode Copilot inherits from dbt.

The mechanism. Copilot reads the last-compiled manifest.json. If you haven't compiled since adding a new source or column, Copilot doesn't know about it — completions reference the old graph.
The fix. Compile on save, or trust the dbt Cloud IDE's live compile (enabled by default). If completions look wrong, the first debugging step is "did the manifest recompile?"
The subtle case. A colleague pushes a new source table; you pull; you haven't compiled. Copilot completions for a model that references the new source will silently miss it. The fix is a dbt compile before serious authoring.

Common interview probes on dbt Copilot.

"What does dbt Copilot ground on?" — required answer: compiled manifest.json + catalog.json.
"How do you stop it from hallucinating column names?" — required answer: keep the catalog fresh (dbt docs generate after schema changes) and compile before authoring.
"When would you pick dbt Copilot over GitHub Copilot?" — required answer: when the productivity comes from project-graph awareness (refs, sources, columns) that a generic code assistant doesn't have.
"What about governance?" — required answer: Copilot is authoring-only; runtime governance is handled by the dbt Cloud project's warehouse credentials + code review.

Worked example — prompting dbt Copilot to draft an `fct_orders` incremental model

Detailed explanation. The canonical dbt Copilot flow: you open a new model file models/marts/fct_orders.sql, describe what you want in a comment or a chat prompt, and Copilot drafts a full incremental model that references your existing staging models. Walk through the flow and what Copilot pulls from the project graph.

Preconditions. stg_orders.sql and stg_order_lines.sql exist and are compiled; both have documented columns in models/staging/schema.yml.
The prompt. "Build an incremental fct_orders joining stg_orders and stg_order_lines by order_id, grain order_id, materialised as incremental with unique_key=order_id and on_schema_change=fail."
Copilot's context. Reads manifest.json to find stg_orders and stg_order_lines; reads catalog.json to know their columns; reads existing macros to reuse is_incremental() boilerplate.

Question. Show the natural-language prompt, the drafted model, and the YAML block Copilot generates alongside.

Input.

Artifact	Value
Prompt	"incremental fct_orders joining stg_orders + stg_order_lines, grain order_id"
Referenced models	`stg_orders`, `stg_order_lines` (already compiled)
Target materialisation	incremental, unique_key=order_id
YAML expected	fct_orders block with description + PK test

Code.

-- models/marts/fct_orders.sql
-- Prompt: incremental fct_orders joining stg_orders + stg_order_lines,
--         grain order_id, materialised as incremental with unique_key.

{{ config(
    materialized = 'incremental',
    unique_key   = 'order_id',
    on_schema_change = 'fail'
) }}

with orders as (
    select *
    from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where updated_at > (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
    {% endif %}
),

order_lines as (
    select
        order_id,
        sum(quantity)              as total_quantity,
        sum(quantity * unit_price) as total_cents
    from {{ ref('stg_order_lines') }}
    group by order_id
)

select
    o.order_id,
    o.customer_id,
    o.status,
    o.region,
    o.customer_segment,
    o.order_date,
    ol.total_quantity,
    ol.total_cents,
    o.created_at,
    o.updated_at
from orders o
left join order_lines ol
  on o.order_id = ol.order_id

# models/marts/schema.yml (drafted alongside the model)
version: 2

models:
  - name: fct_orders
    description: |
      Fact table at the grain of one row per order, joined to summed
      order-line totals. Materialised as incremental keyed on
      updated_at from stg_orders. Failing on schema change (add columns
      via explicit migrations, not silent evolution).

    columns:
      - name: order_id
        description: Primary key.
        tests:
          - unique
          - not_null

      - name: customer_id
        description: Foreign key to dim_customers.
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

      - name: total_cents
        description: Sum of quantity * unit_price across all order lines.

      - name: order_date
        description: Business-effective order date (from stg_orders).

Step-by-step explanation.

Copilot reads the prompt (either as a chat message or as a leading SQL comment). It parses the intent: "incremental fact table, grain order_id, joining two staging models."
It queries the project graph via manifest.json to locate stg_orders and stg_order_lines. It reads catalog.json to enumerate their columns — order_id, customer_id, status, region, customer_segment, order_date, created_at, updated_at on stg_orders; order_id, quantity, unit_price on stg_order_lines.
Copilot drafts the model with the correct ref() calls (not raw table names), the {{ config }} block matching the prompt's unique_key = order_id, and the is_incremental() guard around the updated_at filter. Because it knows the columns, it aggregates stg_order_lines correctly (sum(quantity), sum(quantity * unit_price)) and joins on order_id.
Copilot also drafts a schema.yml entry with per-column descriptions and tests — unique + not_null on order_id (the primary key from the prompt), not_null + relationships on customer_id (inferred from column naming + upstream dim_customers in the graph). This is the "generate docs while generating code" superpower that generic Copilots don't have.
Copilot returns drafts — the analytics engineer reviews, adjusts column casing, edits descriptions, and commits. There is no auto-commit. The productivity gain is "80% of the boilerplate, without the schema hallucination."

Output.

Artifact	Copilot output	Human review changes
Model SQL	correct refs, incremental config, join	usually 0-2 line tweaks (formatting, order)
schema.yml	descriptions + PK/FK tests	expand descriptions to add business context
dbt compile	passes on first try (~90% of cases)	fix column-case mismatches when they occur
dbt test	PK + not-null tests pass	add domain-specific tests (status in list, revenue > 0)

Rule of thumb. For any dbt Copilot workflow, keep the manifest fresh (compile on save), keep the catalog fresh (dbt docs generate after schema changes), and treat Copilot output as a first draft — always review before commit. The productivity gain is boilerplate elimination, not blind trust.

Worked example — drafting semantic-layer metrics from a natural-language spec

Detailed explanation. dbt's semantic layer (semantic_models.yml) declares metrics, measures, and dimensions that downstream tools (Cube, MetricFlow, Cortex Analyst via YAML export) consume. Authoring these YAML blocks by hand is tedious; Copilot excels at drafting them from the underlying fact model. Walk through prompting Copilot to define a total_revenue metric on fct_orders.

The upstream fact. fct_orders with columns including total_cents, order_date, region, customer_segment.
The prompt. "Define a semantic model on fct_orders with a total_revenue metric (sum of total_cents / 100), a order_count metric, dimensions region and customer_segment, and a time-dimension on order_date."
The output. A semantic_models.yml block with entities, dimensions, measures, and metrics.

Question. Show the Copilot output and the down-line consumption in Cortex Analyst / MetricFlow.

Input.

Element	Value
Fact model	fct_orders
Metric 1	total_revenue = sum(total_cents) / 100.0
Metric 2	order_count = count(order_id)
Dimensions	region, customer_segment
Time dimension	order_date

Code.

# models/marts/semantic_models.yml (drafted by Copilot)
semantic_models:
  - name: sm_orders
    description: |
      Semantic model over fct_orders exposing revenue and order-count
      metrics, sliced by region and customer_segment, timestamped on
      order_date. Consumed by dbt semantic layer and exported for
      Snowflake Cortex Analyst.

    model: ref('fct_orders')

    entities:
      - name: order
        type: primary
        expr: order_id

      - name: customer
        type: foreign
        expr: customer_id

    dimensions:
      - name: region
        type: categorical
        expr: region

      - name: customer_segment
        type: categorical
        expr: customer_segment

      - name: order_date
        type: time
        type_params:
          time_granularity: day
        expr: order_date

    measures:
      - name: total_cents
        agg: sum
        expr: total_cents

      - name: order_count
        agg: count
        expr: order_id

metrics:
  - name: total_revenue
    description: Gross revenue in USD (sum of total_cents divided by 100).
    type: derived
    label: Total Revenue
    type_params:
      expr: total_cents / 100.0
      metrics:
        - name: total_cents

  - name: order_count
    description: Number of orders.
    type: simple
    label: Orders
    type_params:
      measure: order_count

# Consuming the metric via the dbt semantic-layer JDBC endpoint
import pandas as pd
from dbt_metricflow import MetricFlowClient   # illustrative

mf = MetricFlowClient(profile="prod")
df = mf.query(
    metrics=["total_revenue", "order_count"],
    group_by=["region", "order_date__month"],
    where="{{ Dimension('order_date') }} >= '2026-01-01'",
    order_by=["order_date__month", "region"],
    limit=1000,
).to_pandas()

print(df.head())
#           order_date__month  region  total_revenue  order_count
# 0        2026-01-01           EAST      1_240_450         3_412
# 1        2026-01-01           WEST      1_082_710         2_998

Step-by-step explanation.

Copilot reads the prompt and understands the target: a semantic model with entities, dimensions, measures, and metrics. It queries the project graph to find fct_orders and enumerate its columns via catalog.json.
It drafts the entities block: order as the primary entity (order_id is the grain), customer as a foreign entity (linking to dim_customers). Getting these entity types right is the piece a generic Copilot cannot do — it requires knowledge of the referenced model's PK.
It drafts the dimensions block with the right types (categorical for text, time for order_date with time_granularity: day) and expr bindings pointing to the columns.
It drafts the measures block — the raw aggregations sum(total_cents) and count(order_id) — and then the metrics block that composes measures into user-facing metric definitions with labels and descriptions. total_revenue is a derived metric that divides total_cents by 100; order_count is a simple metric that wraps the measure directly.
The consuming code (via MetricFlow JDBC) calls total_revenue by name without needing to know the underlying arithmetic. This is the semantic-layer contract: downstream tools query metrics; the layer resolves them to SQL against the fact table.

Output.

Layer	Artifact	Consumer
Fact model	fct_orders	dbt semantic model
Semantic model	sm_orders (dimensions + measures)	metrics
Metric	total_revenue (derived)	MetricFlow / Cortex Analyst
Query	mf.query(metrics=..., group_by=...)	pandas DataFrame

Rule of thumb. Author semantic-layer metrics with Copilot's help; review the entity types (primary / foreign / natural) carefully — they're the piece Copilot occasionally gets wrong when the fact-table PK is ambiguous. Once authored, downstream consumption is metric-by-name; the arithmetic lives in one place.

Worked example — the manifest-freshness failure mode

Detailed explanation. A team has dbt Copilot enabled. An analytics engineer pulls a colleague's branch that added a new source (raw.stripe_payouts). Without recompiling, they ask Copilot to build a stg_stripe_payouts model referencing the new source. Copilot has no knowledge of the new source in its manifest — it hallucinates a ref() to a similarly-named model that doesn't exist. Walk through the diagnosis and the fix.

Symptom. Copilot's suggested select * from {{ ref('stripe_payouts') }} fails with "Compilation Error — model 'stripe_payouts' not found".
Root cause. The pulled branch added sources.yml for raw.stripe_payouts, but the local manifest.json is from before the pull — Copilot doesn't know about the new source.
Fix. Run dbt compile; Copilot re-reads the manifest and now knows the source. The correct completion is select * from {{ source('raw', 'stripe_payouts') }}.

Question. Codify the "always compile after pulling" workflow into the team's CI + local hooks.

Input.

Component	Enforcement
Local dev	pre-commit hook: `dbt compile` on staged .sql / .yml changes
CI	GitHub Action: `dbt compile` on every PR
IDE	dbt Cloud IDE live-compile: enabled by default
Runbook	"if Copilot hallucinates, run `dbt compile` first"

Code.

# .pre-commit-config.yaml — local enforcement
repos:
  - repo: local
    hooks:
      - id: dbt-compile
        name: dbt compile
        entry: bash -c 'cd dbt_project && dbt compile --quiet'
        language: system
        pass_filenames: false
        files: '^dbt_project/(models|macros|seeds|snapshots|analyses)/.*\.(sql|yml)$'
        stages: [commit]

# .github/workflows/dbt-ci.yml — CI enforcement
name: dbt-ci
on: [pull_request]

jobs:
  compile:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: '3.12'
      - run: pip install dbt-snowflake==1.9.0
      - run: cd dbt_project && dbt deps
      - run: cd dbt_project && dbt compile --profiles-dir ci_profiles
      - name: Fail if manifest is stale relative to source
        run: |
          # If a model references a source that isn't in the manifest,
          # `dbt compile` raises. This is our stale-manifest tripwire.
          echo "compile OK; manifest is fresh."

# Team runbook (one-liner in CONTRIBUTING.md)
# "If dbt Copilot suggests a ref() that fails, run: dbt compile"

Step-by-step explanation.

The root cause is that dbt Copilot's grounding artifact — manifest.json — was stale relative to the source of truth (the .yml files on disk). Copilot cannot re-read what hasn't been compiled; the failure surfaces as hallucinated model names or missing columns.
The pre-commit hook forces dbt compile on any change to .sql / .yml files under models/, macros/, seeds/, snapshots/, analyses/. This ensures the manifest is fresh on every commit; Copilot never sees a stale graph in the middle of authoring.
The CI enforcement is the belt-and-braces: even if a developer skips the pre-commit hook, PR-level compilation catches the drift before merge. This also validates that new sources are complete (columns declared, types set) — an incomplete source in the YAML makes the manifest technically fresh but functionally useless to Copilot.
The dbt Cloud IDE's live-compile toggle (enabled by default) mostly obviates the local hook — the IDE recompiles on every save. The pre-commit hook still matters for developers who work outside the Cloud IDE (VS Code, Cursor).
The runbook line — "if Copilot hallucinates, run dbt compile first" — is the debug-first-step every team member should know. Nine times out of ten, hallucinated completions are stale-manifest completions.

Output.

Manifest state	Copilot behaviour	Fix
Fresh	grounded completions	none
Stale (missed source)	hallucinated `ref()`	`dbt compile`
Stale (missed columns)	wrong column names	`dbt docs generate` (catalog)
Missing (never compiled)	generic SQL completions	`dbt compile` first

Rule of thumb. Treat manifest freshness as the #1 dbt Copilot quality-of-life lever. A pre-commit hook + CI compile + Cloud IDE live-compile is the belt-and-braces setup that keeps completions grounded. When Copilot surprises you, run dbt compile before you start debugging the prompt.

Senior interview question on dbt Copilot

A senior interviewer might ask: "Your team just onboarded dbt Copilot in dbt Cloud. After two weeks, analytics engineers report that half the completions reference columns that don't exist and half the semantic-model drafts miss the primary-key entity. Design the workflow, the CI checks, and the code-review rubric that fixes both problems and makes Copilot productive."

Solution Using freshness hooks + a Copilot code-review rubric + a semantic-model-first authoring pattern

# 1. dbt project config — enforce full compile + catalog on every dev iteration
# dbt_project.yml
name: prod_analytics
version: '1.0.0'
config-version: 2

profile: prod

require-dbt-version: '>=1.9.0'

models:
  +on_schema_change: fail    # catch schema drift on incremental models
  +persist_docs:
    relation: true
    columns:  true            # persist YAML descriptions to the warehouse

vars:
  # Turn on strict-mode compile warnings
  strict: true

# .pre-commit-config.yaml — enforce compile + docs generate
repos:
  - repo: local
    hooks:
      - id: dbt-compile
        name: dbt compile
        entry: bash -c 'cd dbt_project && dbt compile --quiet'
        language: system
        pass_filenames: false
        files: '^dbt_project/(models|macros|seeds|snapshots)/.*\.(sql|yml)$'
        stages: [commit]

      - id: dbt-docs
        name: dbt docs generate
        entry: bash -c 'cd dbt_project && dbt docs generate --quiet --no-compile'
        language: system
        pass_filenames: false
        files: '^dbt_project/(models|macros|seeds|snapshots)/.*\.(sql|yml)$'
        stages: [push]

<!-- 2. Copilot code-review rubric (CONTRIBUTING.md excerpt) -->
### Reviewing a PR that used dbt Copilot

Every PR authored with Copilot must be reviewed for the four grounding hazards:

1. **Ref hallucination.** Grep the diff for `ref('...')`; verify each referenced
   model exists in `manifest.json` at HEAD. Common failure: Copilot references a
   model on a colleague's unmerged branch.
2. **Column hallucination.** Grep for column names in the diff; verify each
   exists in the referenced model's YAML or in `catalog.json`. Common failure:
   Copilot invents plural-vs-singular column names (`orders` vs `order_id`).
3. **Test / doc mismatch.** If the PR added a `schema.yml` block, verify the
   `columns:` list matches the model's actual SELECT columns 1:1.
4. **Semantic-model entity types.** If the PR added or edited a
   `semantic_models.yml` block, verify: exactly one `type: primary` entity per
   model; the `expr:` on the primary entity matches the model's grain
   (unique_key).

Any of the four issues => request changes; do not merge.

# 3. Automated Copilot-drift checker (runs in CI)
# scripts/check_copilot_drift.py
import json
import sys
from pathlib import Path

MANIFEST = Path("dbt_project/target/manifest.json")
CATALOG  = Path("dbt_project/target/catalog.json")

def known_models(m: dict) -> set[str]:
    return {n.split(".")[-1] for n in m["nodes"] if n.startswith("model.")}

def known_columns(c: dict, model: str) -> set[str]:
    for node in c["nodes"].values():
        if node["metadata"]["name"] == model:
            return set(node["columns"].keys())
    return set()

def main():
    manifest = json.loads(MANIFEST.read_text())
    catalog  = json.loads(CATALOG.read_text())
    models   = known_models(manifest)

    drift = []
    for sql_file in Path("dbt_project/models").rglob("*.sql"):
        text = sql_file.read_text()
        import re
        for m in re.finditer(r"ref\(['\"]([^'\"]+)['\"]\)", text):
            ref_model = m.group(1)
            if ref_model not in models:
                drift.append((sql_file, "unknown ref", ref_model))

    if drift:
        for f, kind, val in drift:
            print(f"{f}: {kind} — {val}", file=sys.stderr)
        sys.exit(1)

    print("no Copilot drift detected")

if __name__ == "__main__":
    main()

Step-by-step trace.

Step	Change	Effect on Copilot
pre-commit `dbt compile`	manifest fresh on every commit	grounded refs
pre-push `dbt docs generate`	catalog fresh on every push	grounded columns
Copilot review rubric	4-check human review	catches hallucinated refs + columns
CI drift checker	script fails PR on unknown ref	last-line defense
dbt Cloud IDE live compile	manifest refreshes on save	inline grounded completions
`on_schema_change: fail`	incremental models refuse silent drift	catches column deletions
`persist_docs: columns: true`	YAML descriptions land in warehouse	Genie / Cortex reuse them

After the rollout, Copilot completions reference real models 99%+ of the time, semantic-model entity types are correct on first draft 85%+ of the time (with the remaining 15% caught by the code-review rubric), and the CI drift checker acts as the belt-and-braces safety net for anyone who bypasses the local hooks.

Output:

Metric	Before	After
Hallucinated `ref()` rate	~50% of PRs	< 1%
Wrong-column rate	~30% of PRs	< 5%
Semantic-model entity errors	~40%	~15% (caught in review)
Copilot productivity gain	net-negative (review cost)	+30% author velocity
Downstream tool grounding (Genie/Cortex)	broken	consistent

Why this works — concept by concept:

Manifest freshness — Copilot's grounding source is the compiled artifact, not the on-disk YAML. A pre-commit dbt compile guarantees the manifest is fresh; a stale manifest is the root cause of most Copilot hallucinations.
Catalog freshness — column-level grounding lives in catalog.json, which requires dbt docs generate (an extra step). Column hallucinations drop dramatically when catalog is regenerated on every schema change.
Copilot review rubric — the four checks (ref, column, doc, entity) codify the "trust but verify" contract. A human always reviews Copilot output; the rubric ensures the review is systematic, not ad hoc.
CI drift checker — the automated last-line defense. Even if pre-commit hooks are skipped and review misses a hallucinated ref, the CI script fails the PR before merge.
Cost — pre-commit dbt compile adds ~5-30 seconds per commit; CI drift check adds ~2 seconds; total dev-loop overhead ~<1 minute per PR. Compared to shipping hallucinated models to production, this cost is negligible. Net O(project size) per compile, O(files changed) per drift check.

ETL
Topic — etl
ETL and dbt-modelling problems for analytics engineers

Practice →

SQL Generation Topic — sql-generation SQL-generation problems with grounded prompts

Practice →

3. Snowflake Cortex Analyst — governed text-to-SQL over your semantic model

`snowflake cortex analyst` is a REST-API text-to-SQL layer grounded on a YAML semantic model — governance runs through the caller's role

The mental model in one line: snowflake cortex analyst is a REST API on Snowflake Cortex that accepts a natural-language question plus a reference to a YAML semantic layer file, returns generated SQL, an interpretation string, and optionally a chart spec — the LLM is bounded by the tables, measures, dimensions, and synonyms declared in the YAML, and every generated SQL is executed by Snowflake under the caller's role, so row-access policies and column masks bind automatically without any extra plumbing. This is qualitatively different from any generic text-to-SQL wrapper: the grounding is declarative and the governance is inherited.

The four axes for Cortex Analyst.

Grounding. A YAML file — the semantic_model.yaml — declares tables, dimensions, measures, time_dimensions, filters, entities, relationships, and verified_queries (few-shot exemplars). The LLM cannot generate a measure that isn't declared; it cannot join tables without a declared relationship; it cannot reference columns that aren't in the YAML. This bounds the hypothesis space to what the semantic-layer author has approved.
Persona. Analysts and internal-app developers. Cortex Analyst is a REST API, not a UI — you consume it from a Streamlit app, a Slack bot, an internal analyst tool, or Snowsight's Cortex Analyst panel. Not aimed at business users directly (they get the wrapping app's UI, not the raw REST).
Governance. The generated SQL is not executed by Cortex — the caller executes it under their own Snowflake role. Every row-access policy, column mask, warehouse grant, and network policy applies exactly as if the analyst had written the SQL by hand. Cortex sees the schema (via YAML) but never the row values.
Latency + review. 2-5 seconds per turn (REST call). Every response ships the generated SQL for programmatic review before execution — you can auto-execute, or you can apply a rules-based safety check first (e.g., disallow DELETE, cap LIMIT, require WHERE on partitioned columns).

The YAML semantic model — the grounding contract in detail.

Tables. Each table block declares a base table (database.schema.name), a description, a primary key, and lists of dimensions, measures, and time-dimensions. Descriptions are prompt context; they matter.
Dimensions. Categorical columns the LLM can GROUP BY or WHERE on. Each dimension declares an expr (usually just the column name, but can be a SQL expression like INITCAP(region)) and a list of synonyms for user-facing language.
Measures. Numeric aggregations. expr: SUM(total_cents) / 100.0 becomes a callable measure named revenue. Every measure has a description and synonyms.
Time-dimensions. Special case of dimensions — used for period filters ("last quarter", "year to date") and time-grain groupings. Declare them explicitly.
Verified queries. A section listing (question, sql) pairs that act as few-shot exemplars for the LLM. If a user question closely matches a verified question, the LLM is much more likely to produce the exact expected SQL. Curate 10-50 per semantic model.
Relationships. For multi-table models, declare foreign-key relationships so the LLM can join safely. Without a declared relationship, Cortex Analyst refuses to join.

The REST API — request + response shape.

Endpoint. POST https://<account>.snowflakecomputing.com/api/v2/cortex/analyst/message
Request. { "messages": [{ "role": "user", "content": [{ "type": "text", "text": "revenue by region last quarter" }] }], "semantic_model_file": "@DB.SCHEMA.STAGE/revenue_model.yaml" }
Response. JSON with message.content array containing objects of type text (interpretation), sql (generated SQL statement + confidence), and optionally suggestion (follow-up question suggestions).
Auth. Snowflake PAT (Personal Access Token) or OAuth JWT. The token carries the user's role; the SQL is executed under that role when the caller runs it.

Common interview probes on Cortex Analyst.

"How is Cortex Analyst different from wrapping GPT-4o with a system prompt?" — required answer: grounded on declarative YAML semantic model; measures and joins are bounded; runs under caller's role.
"How do you keep the YAML in sync with the warehouse?" — required answer: generate from dbt semantic models (or a dbt-cortex exporter); CI-verify column existence via INFORMATION_SCHEMA.
"How do you evaluate accuracy?" — required answer: golden-question harness with semantic-diff scoring.
"What about PII?" — required answer: caller-role execution + column masks + never expose row values in the YAML.

Worked example — authoring `revenue_model.yaml` and calling the REST API

Detailed explanation. The canonical Cortex Analyst deployment: (1) author a revenue_model.yaml declaring tables + measures + dimensions + synonyms + verified queries, (2) upload it to a Snowflake stage, (3) POST natural-language questions to the REST endpoint, (4) execute the returned SQL under the caller's role, (5) render the result. Walk through every step.

Model scope. fct_orders + dim_customers joined by customer_id, with measures revenue and unique_customers and dimensions region, customer_segment, and a time-dimension on order_date.
Consumer. A Streamlit app for the finance team.
Auth. Snowflake PAT stored in Streamlit secrets.

Question. Author the YAML, show the REST call, and render the result in Streamlit.

Input.

Layer	Value
Fact	PROD_ANALYTICS.MART.FCT_ORDERS
Dim	PROD_ANALYTICS.MART.DIM_CUSTOMERS
Measures	revenue, unique_customers, order_count
Dimensions	region, customer_segment, industry
Time-dim	order_date
Verified queries	3 (revenue by region, unique customers by segment, monthly trend)

Code.

# semantic_models/revenue_model.yaml
name: revenue_model
description: |
  Governed revenue semantic model for the finance + sales-ops teams.
  Grounded on fct_orders joined to dim_customers by customer_id.

tables:
  - name: fct_orders
    description: One row per order; grain is order_id.
    base_table:
      database: PROD_ANALYTICS
      schema:   MART
      table:    FCT_ORDERS
    primary_key:
      columns: [order_id]

    dimensions:
      - name: region
        expr: region
        data_type: TEXT
        synonyms: [geo, territory, sales region]
      - name: customer_segment
        expr: customer_segment
        data_type: TEXT
        synonyms: [segment, tier, customer tier]

    time_dimensions:
      - name: order_date
        expr: order_date
        data_type: DATE
        synonyms: [date, day, order day]

    measures:
      - name: revenue
        expr: SUM(total_cents) / 100.0
        data_type: NUMBER
        description: Gross revenue in USD.
        synonyms: [sales, gross revenue, total sales, top line]
      - name: order_count
        expr: COUNT(*)
        data_type: NUMBER
        description: Number of orders.
        synonyms: [orders, transactions]
      - name: unique_customers
        expr: COUNT(DISTINCT customer_id)
        data_type: NUMBER
        description: Distinct customers with at least one order.
        synonyms: [customers, buyers, unique buyers]

  - name: dim_customers
    description: One row per customer.
    base_table:
      database: PROD_ANALYTICS
      schema:   MART
      table:    DIM_CUSTOMERS
    primary_key:
      columns: [customer_id]

    dimensions:
      - name: industry
        expr: industry
        data_type: TEXT
        synonyms: [vertical, sector]

relationships:
  - left_table:  fct_orders
    left_column: customer_id
    right_table: dim_customers
    right_column: customer_id
    join_type:   left_outer
    relationship_type: many_to_one

verified_queries:
  - name: revenue_by_region_last_quarter
    question: What was our revenue by region last quarter?
    sql: |
      SELECT region, SUM(total_cents)/100.0 AS revenue
      FROM   PROD_ANALYTICS.MART.FCT_ORDERS
      WHERE  order_date BETWEEN DATE_TRUNC('quarter', DATEADD('quarter', -1, CURRENT_DATE))
                            AND LAST_DAY(DATEADD('quarter', -1, CURRENT_DATE))
      GROUP  BY region
      ORDER  BY revenue DESC

  - name: unique_customers_by_industry
    question: How many unique customers do we have by industry?
    sql: |
      SELECT c.industry, COUNT(DISTINCT o.customer_id) AS unique_customers
      FROM   PROD_ANALYTICS.MART.FCT_ORDERS o
      LEFT   JOIN PROD_ANALYTICS.MART.DIM_CUSTOMERS c
             ON c.customer_id = o.customer_id
      GROUP  BY c.industry
      ORDER  BY unique_customers DESC

-- Upload the YAML to a stage
CREATE STAGE IF NOT EXISTS PROD_ANALYTICS.SEMANTIC_MODELS
  FILE_FORMAT = (TYPE = 'CSV');   -- format ignored; stage is for raw files

-- Then via SnowSQL or the web UI:
PUT file://semantic_models/revenue_model.yaml @PROD_ANALYTICS.SEMANTIC_MODELS
  AUTO_COMPRESS=FALSE OVERWRITE=TRUE;

# streamlit_app.py — analyst-facing UI
import json
import requests
import streamlit as st
import snowflake.connector

ACCOUNT     = "abcxyz-us-east-1"
CORTEX_URL  = f"https://{ACCOUNT}.snowflakecomputing.com/api/v2/cortex/analyst/message"
SEMANTIC    = "@PROD_ANALYTICS.SEMANTIC_MODELS/revenue_model.yaml"

def ask_cortex(question: str, pat: str) -> dict:
    resp = requests.post(
        CORTEX_URL,
        headers={
            "Authorization": f"Bearer {pat}",
            "Content-Type":  "application/json",
        },
        json={
            "messages": [
                {"role": "user",
                 "content": [{"type": "text", "text": question}]}
            ],
            "semantic_model_file": SEMANTIC,
        },
        timeout=30,
    )
    resp.raise_for_status()
    return resp.json()

def render(payload: dict, conn) -> None:
    for block in payload["message"]["content"]:
        if block["type"] == "text":
            st.write(block["text"])
        elif block["type"] == "sql":
            st.code(block["statement"], language="sql")
            if st.button("Run this query"):
                df = conn.cursor().execute(block["statement"]).fetch_pandas_all()
                st.dataframe(df)
        elif block["type"] == "suggestion":
            st.info(f"Follow-up: {block['suggestions'][0]}")

st.title("Revenue Analyst")
question = st.text_input("Ask a revenue question:")
if question:
    pat = st.secrets["snowflake_pat"]
    resp = ask_cortex(question, pat)
    conn = snowflake.connector.connect(
        account   = ACCOUNT,
        user      = st.secrets["snowflake_user"],
        authenticator = "oauth",
        token     = pat,
        role      = st.secrets["snowflake_role"],
        warehouse = "ANALYST_WH",
    )
    render(resp, conn)

Step-by-step explanation.

The YAML file is the grounding contract. tables block declares the two fact/dim tables with their primary keys; relationships block declares the FK from fct_orders.customer_id → dim_customers.customer_id so Cortex can safely generate joins. Without the relationship block, joins are refused.
Every measure lists synonyms — revenue has [sales, gross revenue, total sales, top line]. When a user asks "what was our top line last quarter?", the LLM maps "top line" → revenue via the synonym list, then generates SQL against the declared measure expression.
Verified queries are few-shot exemplars. They serve two purposes: (a) they anchor the LLM's output for questions matching the verified pattern, dramatically increasing exact-match rate; (b) they document the "canonical" way to answer common questions, so a human reviewer can spot-check unusual generations.
The Streamlit app POSTs the user's question to the Cortex Analyst REST endpoint with semantic_model_file pointing to the stage-hosted YAML. The response contains a message.content array with a text interpretation block ("Here's revenue by region for last quarter…"), a sql block with the generated statement, and optional suggestion blocks with follow-up prompts.
Critically, the SQL is not executed by Cortex — the app shows it, waits for the analyst to click "Run this query", then executes via the Snowflake connector under the analyst's own role. RLS, column masks, warehouse grants — all bind automatically. Cortex never sees a single row of data.

Output.

Question	Generated SQL	Executed under
"revenue by region last quarter"	SELECT region, SUM(total_cents)/100 ... GROUP BY region	analyst's role
"unique customers by industry"	JOIN dim_customers + GROUP BY industry	analyst's role
"top line by segment this year"	recognises "top line" = revenue; SELECT customer_segment, ...	analyst's role
"delete old orders"	REFUSED — DDL/DML outside the declared measures	N/A

Rule of thumb. For any Cortex Analyst deployment, invest time up front in synonyms (they're the difference between "grounded" and "user-frustrated") and verified queries (they're the difference between "10% exact-match" and "80% exact-match"). Store the YAML in a Snowflake stage under version control; treat schema changes as YAML PRs.

Worked example — the schema-drift failure mode

Detailed explanation. A team ships revenue_model.yaml referencing fct_orders.region. Two months later, an analytics engineer renames region to sales_region in dbt and re-materialises fct_orders. The YAML is out of date. Cortex Analyst still generates SQL referencing region; the SQL executes and returns "SQL compilation error: invalid identifier 'REGION'". Every analyst using the app sees the error. Walk through the diagnosis and the fix — plus the CI check that would have prevented it.

Symptom. All Cortex-generated SQL fails with "invalid identifier 'REGION'".
Root cause. The warehouse column was renamed but the YAML wasn't updated.
Fix. Update the YAML; re-upload; verify.
Prevention. CI check that queries INFORMATION_SCHEMA and verifies every YAML column exists in the referenced table.

Question. Ship the CI check that catches schema drift before it reaches production.

Input.

Component	Purpose
INFORMATION_SCHEMA query	source of truth for real columns
YAML parser	extract declared columns
Diff	flag columns in YAML but not warehouse
CI gate	fail PR on drift

Code.

# scripts/check_cortex_yaml_drift.py
import sys
import yaml
import snowflake.connector
from pathlib import Path

YAML_FILE = Path("semantic_models/revenue_model.yaml")

def load_yaml_columns(path: Path) -> dict[str, set[str]]:
    """Return {fully_qualified_table: {declared_columns}}."""
    doc = yaml.safe_load(path.read_text())
    out: dict[str, set[str]] = {}
    for t in doc.get("tables", []):
        bt = t["base_table"]
        fq = f"{bt['database']}.{bt['schema']}.{bt['table']}"
        cols = set()
        for section in ("dimensions", "measures", "time_dimensions"):
            for item in t.get(section, []):
                # measures may have SQL expressions; extract identifiers only for leaf columns
                expr = item.get("expr", "")
                if expr.isidentifier():
                    cols.add(expr.upper())
        out[fq] = cols
    return out

def load_warehouse_columns(conn, fq: str) -> set[str]:
    db, schema, table = fq.split(".")
    with conn.cursor() as cur:
        cur.execute(f"""
            SELECT column_name
            FROM   {db}.INFORMATION_SCHEMA.COLUMNS
            WHERE  table_schema = %s
              AND  table_name   = %s
        """, (schema, table))
        return {r[0].upper() for r in cur.fetchall()}

def main() -> None:
    conn = snowflake.connector.connect(
        account="abcxyz-us-east-1",
        user="ci_reader",
        authenticator="externalbrowser",   # or OAuth token
        role="ANALYTICS_READER",
        warehouse="CI_WH",
    )
    yaml_cols = load_yaml_columns(YAML_FILE)
    drift = []

    for fq, declared in yaml_cols.items():
        actual = load_warehouse_columns(conn, fq)
        missing = declared - actual
        if missing:
            drift.append((fq, missing))

    if drift:
        for fq, missing in drift:
            print(f"SCHEMA DRIFT: {fq} declares columns not in warehouse: {sorted(missing)}", file=sys.stderr)
        sys.exit(1)

    print("no schema drift; revenue_model.yaml is consistent with warehouse")

if __name__ == "__main__":
    main()

# .github/workflows/cortex-yaml-drift.yml
name: cortex-yaml-drift
on:
  pull_request:
    paths:
      - 'semantic_models/**/*.yaml'
      - 'dbt_project/models/**/*.sql'    # trigger on model changes too

jobs:
  drift-check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: {python-version: '3.12'}
      - run: pip install snowflake-connector-python[pandas] pyyaml
      - name: Configure Snowflake OAuth
        run: |
          echo "SNOWFLAKE_TOKEN=${{ secrets.SNOWFLAKE_CI_TOKEN }}" >> $GITHUB_ENV
      - run: python scripts/check_cortex_yaml_drift.py

Step-by-step explanation.

The root cause is that the YAML semantic model and the warehouse schema drifted independently. dbt owns the warehouse schema; the YAML owns the LLM grounding contract. Without a link, either can change without the other noticing.
The drift-check script parses the YAML, extracts every declared column (from dimensions, measures, time_dimensions), then queries Snowflake's INFORMATION_SCHEMA.COLUMNS for the referenced tables. Any column in the YAML but not in the warehouse = drift.
Note the expr.isidentifier() guard: measure expr values can be SQL expressions (e.g., SUM(total_cents) / 100.0); the drift check only validates simple column identifiers. A stricter check would parse the expressions with a SQL parser (sqlglot) to extract every referenced column, but simple-identifier coverage catches 80% of drift cases.
The CI job runs on any PR that touches semantic_models/*.yaml OR dbt_project/models/*.sql. This means a dbt model rename triggers the same drift check as a YAML edit, so drift is caught in whichever direction it originates.
On drift, the script exits 1 with a clear error message. The developer must either update the YAML or revert the dbt change; either way, the drift is blocked before merge. Over time, teams standardise on "dbt is the source of truth; YAML follows" or "YAML is the source of truth; dbt columns must not be renamed without a YAML PR." Either policy works; the drift check enforces whichever one the team picks.

Output.

Change	Warehouse	YAML	Drift check
Add `industry` column to dim_customers	present	present	pass
Rename `region` → `sales_region` in dbt	`sales_region`	`region`	fail
Drop unused `notes` column in dbt	absent	present	fail
Edit YAML to reference a typo column	present	typo	fail
Correct fix (dbt + YAML in one PR)	consistent	consistent	pass

Rule of thumb. For any Cortex Analyst deployment, ship the schema-drift check on day one. It's a 50-line Python script and one GitHub Action; the cost is trivial and the failure mode it prevents (silent hallucinated SQL that fails on execution) is a production outage.

Worked example — synonym curation for user-friendly text-to-SQL

Detailed explanation. A finance team's users ask questions like "what's our MRR by geo?" and "show me churned logos this quarter." The YAML declares revenue (a measure) and region (a dimension), but nothing about "MRR", "geo", "churned logos", or "logos." Cortex Analyst either fails to answer or hallucinates a SELECT against a table that doesn't exist. The fix is aggressive synonym curation: enumerate every business-user phrase and map it to the canonical measure or dimension. Walk through the curation workflow.

Symptom. ~30% of business-user questions get "I don't know how to answer that" or hallucinated SQL.
Root cause. User vocabulary ("MRR", "geo", "logos") doesn't match declared dimension/measure names.
Fix. Expand synonyms lists on every dimension and measure; add verified queries covering common business phrases.

Question. Extend revenue_model.yaml to cover the finance team's real vocabulary.

Input.

Business phrase	Canonical measure / dimension
"MRR"	derived measure: `SUM(monthly_recurring_revenue_cents) / 100`
"geo"	dimension: `region`
"logos"	dimension: `customer_id` (as COUNT DISTINCT)
"churned logos"	measure: unique customers with status='churned'
"net-new logos"	measure: unique customers with first order this period

Code.

# revenue_model.yaml — extended synonyms + new measures
tables:
  - name: fct_orders
    dimensions:
      - name: region
        expr: region
        data_type: TEXT
        synonyms:
          - geo
          - geography
          - territory
          - sales region
          - market
          - area
          - country

      - name: customer_id
        expr: customer_id
        data_type: NUMBER
        synonyms:
          - logo
          - logos
          - account
          - accounts
          - customer

    measures:
      - name: revenue
        expr: SUM(total_cents) / 100.0
        description: Gross revenue in USD.
        synonyms:
          - sales
          - gross revenue
          - total sales
          - top line
          - bookings
          - GMV

      - name: mrr
        expr: SUM(monthly_recurring_revenue_cents) / 100.0
        description: Monthly recurring revenue in USD.
        synonyms:
          - MRR
          - monthly recurring revenue
          - recurring revenue
          - subscription revenue

      - name: unique_logos
        expr: COUNT(DISTINCT customer_id)
        description: Distinct customers (unique logos) with at least one order.
        synonyms:
          - unique logos
          - unique customers
          - number of logos
          - customer count
          - accounts

      - name: churned_logos
        expr: COUNT(DISTINCT CASE WHEN status = 'churned' THEN customer_id END)
        description: Distinct customers with churned status in the period.
        synonyms:
          - churn
          - churned
          - churned logos
          - lost customers
          - attrition

verified_queries:
  - name: mrr_by_geo_this_quarter
    question: "What is our MRR by geo this quarter?"
    sql: |
      SELECT region AS geo,
             SUM(monthly_recurring_revenue_cents)/100.0 AS mrr
      FROM   PROD_ANALYTICS.MART.FCT_ORDERS
      WHERE  order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
        AND  order_date <  DATE_TRUNC('quarter', DATEADD('quarter', 1, CURRENT_DATE))
      GROUP  BY region
      ORDER  BY mrr DESC;

  - name: churned_logos_this_quarter
    question: "How many logos churned this quarter?"
    sql: |
      SELECT COUNT(DISTINCT customer_id) AS churned_logos
      FROM   PROD_ANALYTICS.MART.FCT_ORDERS
      WHERE  status = 'churned'
        AND  order_date >= DATE_TRUNC('quarter', CURRENT_DATE);

Step-by-step explanation.

Every dimension and measure gets an expanded synonyms list covering the business team's real vocabulary. "MRR", "GMV", "logos", "geo" are business-user language; the YAML translates them to canonical measure / dimension names. A missing synonym = a failed question; over-curating synonyms is cheap.
Where the business team has a phrase that doesn't map to an existing measure ("MRR"), add a new measure. Cortex Analyst can then answer MRR questions natively without any prompt gymnastics. Note the measure expression SUM(monthly_recurring_revenue_cents) / 100.0 — it assumes the underlying column exists on fct_orders; a drift check would catch a missing column.
Complex business concepts ("churned logos") become measures with CASE WHEN guards inside the aggregation. This encapsulates the business rule ("churned = status = 'churned'") in one place; users don't have to remember it and can't get it wrong.
Verified queries covering the top business phrases anchor the LLM's output. Every quarter, look at the last 200 questions users asked; the top 10-20 recurring phrases become verified queries. This creates a positive feedback loop — the more the tool is used, the better it gets at the common questions.
Synonym curation is never done. Every quarter, review Cortex Analyst's answer logs for questions that returned low-confidence SQL or errored. The vocabulary gaps become the next synonym-list additions.

Output.

User phrase	Before synonym	After synonym
"MRR by geo"	"I can't compute MRR"	correct SQL with `region AS geo`
"unique logos"	wrong (used `count(*)`)	`COUNT(DISTINCT customer_id)`
"churn this quarter"	hallucinated table	`SUM(... status='churned' ...)`
"bookings by market"	brittle	`revenue by region`
"top line"	rare hit	90%+ correct

Rule of thumb. Synonym curation is a continuous investment, not a one-time task. Budget 30 minutes a week per semantic model for the first quarter; drop to 30 minutes a month once the vocabulary stabilises. The payoff is user trust: every "I don't know how to answer that" erodes it; every correct answer builds it.

Senior interview question on Cortex Analyst

A senior interviewer might ask: "Design a production Cortex Analyst deployment for a finance team of 30 people asking natural-language revenue questions against a Snowflake warehouse. Include the YAML semantic model, the REST-API integration, governance (RLS + column masks), the drift check, the synonym curation loop, and the golden-question eval harness. Then show me the metric you'd track to know it's working."

Solution Using a governed YAML semantic model + RLS-inherited execution + drift + golden-question harness + a user-facing "confidence surface"

-- 1. Snowflake governance — row-access policy + column mask on the fact
CREATE OR REPLACE ROW ACCESS POLICY finance_region_rap
  AS (region VARCHAR) RETURNS BOOLEAN ->
    (
      -- Finance-team members see all regions; sales-region managers see only their own
      IS_ROLE_IN_SESSION('FINANCE_TEAM')
      OR region = CURRENT_ROLE()   -- role names match region codes for sales managers
    );

ALTER TABLE PROD_ANALYTICS.MART.FCT_ORDERS
  ADD ROW ACCESS POLICY finance_region_rap ON (region);

CREATE OR REPLACE MASKING POLICY customer_email_mask AS
  (val STRING) RETURNS STRING ->
    CASE
      WHEN IS_ROLE_IN_SESSION('FINANCE_TEAM','SUPPORT_TEAM') THEN val
      ELSE REGEXP_REPLACE(val, '(.).*(@.*)', '\\1***\\2')
    END;

ALTER TABLE PROD_ANALYTICS.MART.DIM_CUSTOMERS
  MODIFY COLUMN email SET MASKING POLICY customer_email_mask;

# 2. Analyst app — Cortex Analyst REST + caller-role execute + confidence surface
import json
import re
import requests
import snowflake.connector

ACCOUNT     = "abcxyz-us-east-1"
CORTEX_URL  = f"https://{ACCOUNT}.snowflakecomputing.com/api/v2/cortex/analyst/message"
SEMANTIC    = "@PROD_ANALYTICS.SEMANTIC_MODELS/revenue_model.yaml"

SAFETY_DENYLIST = re.compile(r"\b(DROP|DELETE|TRUNCATE|ALTER|GRANT|REVOKE|CREATE)\b", re.I)

class UnsafeSQLError(Exception):
    ...

def ask_cortex(question: str, pat: str, history: list[dict] | None = None) -> dict:
    resp = requests.post(
        CORTEX_URL,
        headers={"Authorization": f"Bearer {pat}", "Content-Type": "application/json"},
        json={
            "messages": (history or []) + [
                {"role": "user", "content": [{"type": "text", "text": question}]}
            ],
            "semantic_model_file": SEMANTIC,
        },
        timeout=30,
    )
    resp.raise_for_status()
    return resp.json()

def extract_sql(payload: dict) -> tuple[str, float]:
    for block in payload["message"]["content"]:
        if block["type"] == "sql":
            return block["statement"], float(block.get("confidence", 0.0))
    raise ValueError("no SQL block in response")

def safe_execute(conn, sql: str, confidence: float) -> "pd.DataFrame":
    if SAFETY_DENYLIST.search(sql):
        raise UnsafeSQLError("generated SQL contains DDL/DML; refusing to execute")
    if confidence < 0.6:
        raise UnsafeSQLError(f"confidence too low ({confidence:.2f}); require human review")
    return conn.cursor().execute(sql).fetch_pandas_all()

def render(question: str, payload: dict, conn) -> None:
    sql, confidence = extract_sql(payload)
    print(f"[Cortex] confidence={confidence:.2f}")
    print(sql)
    try:
        df = safe_execute(conn, sql, confidence)
        print(df.head())
    except UnsafeSQLError as e:
        print(f"NOT EXECUTED: {e}")

# 3. Golden-question harness (from section 1, extended)
# runs in CI on every YAML change; reports exact-match + semantic-match + confidence

# 4. Ops metric — confidence distribution + refusal rate over time
# Log every response: (question, confidence, executed?, error?, user_flag?)
# Dashboards:
#   - Avg confidence by week (target > 0.75)
#   - Refusal rate (target < 5%)
#   - User "wrong answer" flag rate (target < 3%)
#   - Golden-question semantic-match rate (target > 95%)

Step-by-step trace.

Layer	Component	Purpose
Warehouse	RLS + column masks on fct_orders + dim_customers	governance boundary
Stage	`@PROD_ANALYTICS.SEMANTIC_MODELS/revenue_model.yaml`	grounding contract
Analyst app	Cortex REST + PAT + caller role	ask + interpret
Safety layer	DDL/DML denylist + confidence gate	pre-execute guard
Eval harness	golden questions + semantic-diff	CI regression gate
Ops dashboard	confidence + refusal + user-flag rates	production monitoring
Sync check	schema-drift script	prevent silent breakage

After the rollout, finance-team members ask questions in English and see the generated SQL alongside the chart; the caller-role execution inherits RLS (so a west-region sales manager only sees west-region rows) and column masks (so support-team users see redacted emails); the safety gate refuses DDL/DML even in the unlikely event Cortex hallucinates one; and the ops dashboard tracks confidence + refusal + user-flag rates so drift is visible before it becomes a production incident.

Output:

Metric	Target	Actual (after 30 days)
Avg confidence per response	> 0.75	0.82
Refusal rate (safety gate)	< 5%	1.2%
User "wrong answer" flag rate	< 3%	2.1%
Golden-question semantic-match	> 95%	96.4%
RLS enforcement (unauthorised rows in results)	0	0
Column-mask enforcement	100%	100%

Why this works — concept by concept:

YAML semantic model — the declarative grounding contract. Measures, dimensions, synonyms, and verified queries are the boundary of what Cortex Analyst can generate. Hallucination reduction is not a fine-tuning problem; it's a grounding problem.
Caller-role execution — the generated SQL runs under the analyst's own Snowflake role. RLS, column masks, warehouse grants, network policies all bind automatically. The LLM never touches row values; the safety boundary is the same as if the analyst had written the SQL by hand.
Safety denylist + confidence gate — the pre-execute guardrail. DDL/DML gets refused even if Cortex hallucinates one; low-confidence responses require human review. This is the "belt-and-braces" defense against the small failure rate the grounding alone can't catch.
Confidence surface in UI — showing users the confidence score alongside the SQL builds trust. Analysts learn to trust > 0.8 responses and to spot-check < 0.6 responses. This creates a shared vocabulary between the tool and the user.
Cost — Cortex Analyst per-message costs are Snowflake-priced (Cortex credits); a typical 30-user finance-team deployment consumes ~100-300 credits/month. The engineering cost is a one-time YAML authoring investment (~1-2 weeks) plus continuous synonym curation (~30 min/week). The eliminated cost is analyst time hand-writing SQL for repetitive questions. Net payback typically < 2 months.

SQL
Topic — sql
SQL problems on governed text-to-SQL and semantic models

Practice →

API Integration Topic — api-integration API-integration problems on REST-served LLM assistants

Practice →

4. Databricks AI/BI Genie — natural-language BI over Unity Catalog

`databricks ai/bi genie` grounds on Unity Catalog metadata + curated example queries — the business-user chat surface over the lakehouse

The mental model in one line: databricks ai/bi genie is the natural-language BI surface inside Databricks that scopes each conversation to a "Genie space" (a curated bundle of Unity Catalog tables + column comments + sample values + example queries), lets end users chat with the space and get charts back, and enforces every existing Unity Catalog governance policy (row-level filters, column masks, grants) automatically — its answer quality is directly proportional to how completely you've populated the metadata and how many curated example queries the space ships with. Genie is the business-user counterpart to Cortex Analyst; the grounding source is Unity Catalog metadata rather than a hand-authored YAML, and the persona is analyst-adjacent business users rather than SQL-writing developers.

The four axes for AI/BI Genie.

Grounding. Unity Catalog table comments + column comments + sample values + curated example queries + optional "AI/BI Instructions" (free-text guidance the space author writes). No YAML semantic model in the Cortex-Analyst sense — Genie leans on the metadata already in Unity Catalog + hand-written examples.
Persona. Business users — marketing analysts, sales-ops managers, finance partners — who never learned SQL. Genie ships an in-workspace chat pane and is embedded in AI/BI Dashboards as a "ask a follow-up" surface.
Governance. Unity Catalog RLS (ROW FILTER), column masks, grants, and audit logs all bind automatically because Genie executes the generated SQL via a Databricks SQL warehouse under the caller's identity.
Latency + review. 3-8 seconds per turn. Every response shows the chart and the generated SQL side by side; users can toggle to see the SQL and flag "wrong answer" if the chart looks off. Genie ships a first-class "explain / regenerate" loop.

Setting up a Genie space — the four-step recipe.

Step 1: pick the scope. A Genie space is scoped to a small set of Unity Catalog tables (typically 3-15). Cross-domain spaces (marketing + finance + product) tend to be brittle; single-domain spaces (marketing only) tend to be sharp.
Step 2: populate metadata. Every table gets a COMMENT; every column gets a COMMENT. Sparse comments = brittle grounding. Sample values (via sample_values or by letting Genie profile the table) help the LLM understand distributions and units.
Step 3: curate example queries. 10-50 example (question, SQL) pairs per space. These act as few-shot exemplars. Curate iteratively: watch what users ask, add the top misses as examples.
Step 4: write AI/BI instructions. A free-text prompt scoped to the space — "always show revenue in millions", "when asked about churn, use the status = 'churned' convention", "date filters default to the last quarter unless specified."

Common interview probes on Genie.

"How is Genie different from Cortex Analyst?" — Genie grounds on UC metadata + examples (no YAML); persona is business users not analysts; UI is chat + chart, not REST.
"How do you stop it from leaking PII?" — column masks on sensitive columns; caller-identity execution; scope the space to tables the users are already granted on.
"How do you evaluate a Genie space?" — golden questions per space; user-flag rate; sample-value coverage.
"When would you pick Genie over embedding a dashboard?" — when the question space is unbounded (users ask novel questions); dashboards win when the question space is fixed.

Worked example — setting up a Genie space for a marketing analytics team

Detailed explanation. The canonical Genie deployment: pick 4 marketing tables (campaigns, leads, conversions, spend), populate comments and sample values, curate 15 example queries, write a page of AI/BI Instructions, and hand the space to the marketing team. Walk through the SQL / Databricks CLI commands that ship the space.

Tables. marketing.campaigns, marketing.leads, marketing.conversions, marketing.spend.
Persona. Marketing ops manager + 8 marketing analysts.
Governance. Marketing team has SELECT on the marketing catalog; column mask on leads.email.

Question. Ship the Genie space: comments, sample-value curation, example queries, instructions.

Input.

Layer	Purpose
Table + column comments	grounding metadata
Example queries	few-shot exemplars
AI/BI Instructions	space-scoped prompt
Column mask	PII protection

Code.

-- 1. Ensure every table + column has a comment (grounding metadata)
COMMENT ON TABLE marketing.campaigns IS
  'One row per marketing campaign. Grain: campaign_id. Includes launch/end dates, channel, and total budget.';

COMMENT ON COLUMN marketing.campaigns.campaign_id  IS 'Primary key; unique per campaign.';
COMMENT ON COLUMN marketing.campaigns.channel      IS 'Marketing channel; one of: email, social, paid_search, display, referral.';
COMMENT ON COLUMN marketing.campaigns.budget_cents IS 'Total campaign budget in cents. Divide by 100 for USD.';
COMMENT ON COLUMN marketing.campaigns.launch_date  IS 'Business-effective launch date.';

COMMENT ON TABLE marketing.conversions IS
  'One row per conversion event. Grain: conversion_id. Links campaign_id → lead_id → revenue.';

-- 2. Column mask on PII
CREATE OR REPLACE FUNCTION marketing.email_mask(v STRING) RETURNS STRING
  RETURN CASE
    WHEN is_account_group_member('marketing-pii-approved') THEN v
    ELSE regexp_replace(v, '(.).*(@.*)', '$1***$2')
  END;

ALTER TABLE marketing.leads
  ALTER COLUMN email SET MASK marketing.email_mask;

-- 3. Row filter — sales-region managers see only their region
CREATE OR REPLACE FUNCTION marketing.region_row_filter(region STRING) RETURNS BOOLEAN
  RETURN is_account_group_member('marketing-global')
      OR region = current_user_group_region();

ALTER TABLE marketing.leads
  SET ROW FILTER marketing.region_row_filter ON (region);

# 4. Provision the Genie space via the Databricks SDK
from databricks.sdk import WorkspaceClient
from databricks.sdk.service.ai import GenieSpace, GenieInstruction, GenieExample

w = WorkspaceClient()

space = w.genie.create_space(GenieSpace(
    name        = "marketing-analytics",
    description = "Chat with marketing.campaigns / leads / conversions / spend.",
    table_identifiers = [
        "marketing.campaigns",
        "marketing.leads",
        "marketing.conversions",
        "marketing.spend",
    ],
    warehouse_id = "abc123-shared-analytics-wh",
    instructions = GenieInstruction(text="""
        - Revenue is `SUM(conversions.revenue_cents) / 100.0` in USD.
        - Cost per lead = SUM(spend.cost_cents) / COUNT(DISTINCT leads.lead_id) / 100.
        - When users say "campaign performance", show conversions and cost side-by-side.
        - Default time range is the last quarter unless the user specifies.
        - Channel values are exactly: email, social, paid_search, display, referral.
    """),
    example_queries = [
        GenieExample(
            question = "What was our best-performing campaign last quarter?",
            sql      = """
                SELECT c.campaign_id, c.channel, SUM(v.revenue_cents)/100.0 AS revenue
                FROM   marketing.campaigns    c
                JOIN   marketing.conversions  v USING (campaign_id)
                WHERE  v.event_date >= date_trunc('QUARTER', date_sub(current_date(), 90))
                GROUP  BY c.campaign_id, c.channel
                ORDER  BY revenue DESC
                LIMIT  10
            """,
        ),
        GenieExample(
            question = "Cost per lead by channel this month",
            sql      = """
                SELECT c.channel,
                       SUM(s.cost_cents) / COUNT(DISTINCT l.lead_id) / 100.0 AS cost_per_lead
                FROM   marketing.campaigns c
                JOIN   marketing.spend     s USING (campaign_id)
                JOIN   marketing.leads     l USING (campaign_id)
                WHERE  s.spend_date >= date_trunc('MONTH', current_date())
                GROUP  BY c.channel
                ORDER  BY cost_per_lead ASC
            """,
        ),
        # ... 13 more curated examples
    ],
))
print(f"Genie space created: {space.id}")

Step-by-step explanation.

Every table and every column gets a COMMENT. This is Genie's primary grounding source; sparse comments produce brittle answers. The comment on channel enumerates the valid values (email, social, paid_search, display, referral), so Genie knows not to invent channels users might reference.
Column masks and row filters are Unity Catalog features that bind automatically to any query — including Genie's generated SQL. The email-mask function redacts email addresses for anyone not in marketing-pii-approved; the row filter restricts non-global users to their own region. Genie's caller-identity execution means these policies inherit for free.
The Genie space is provisioned via the Databricks SDK with (a) the four table identifiers, (b) a shared warehouse for execution, (c) free-text AI/BI Instructions that give the LLM space-scoped guidance (business rules, definitions, defaults), and (d) 15 curated example queries.
AI/BI Instructions do the work that verified queries do in Cortex Analyst — they encode business rules and defaults that would otherwise require the user to specify every time. "Revenue is SUM(conversions.revenue_cents) / 100.0 in USD" prevents the LLM from picking a different revenue definition.
Example queries are the highest-leverage grounding artifact after column comments. Every business phrase you want the space to handle well ("best-performing campaign", "cost per lead", "conversion rate by channel") should have a curated example. Curate iteratively: watch the top user questions in production, add the top misses as examples.

Output.

Genie space asset	Count	Purpose
Scoped tables	4	grounding scope
Column comments	~40 (all columns)	grounding metadata
Example queries	15 (curated)	few-shot exemplars
AI/BI instructions	1 page	space-scoped prompt
Row filters	1 (region)	governance
Column masks	1 (email)	PII protection

Rule of thumb. Every Genie space needs (a) complete column comments, (b) at least 10 curated example queries, and (c) a page of AI/BI Instructions. Skimping on any of the three produces brittle answers. Budget 1-2 days to author a space; treat it as a living artifact that improves with usage.

Worked example — the sparse-comments failure mode + iterative curation

Detailed explanation. A team ships a Genie space with only table-level comments (no column comments) and 3 example queries. Users ask questions like "what's the CTR by channel?"; Genie hallucinates a ctr column that doesn't exist. The fix is a curation sprint: comment every column with its meaning and unit, add 15 example queries covering the top user phrases, and iterate weekly.

Symptom. ~35% of user questions return either "I don't know" or wrong-column errors.
Root cause. Column meanings are inferable from column names alone; Genie hallucinates plausible column names.
Fix. Populate every column comment; add example queries covering the top misses.

Question. Ship the "comment audit" script that grades a Genie space's grounding completeness.

Input.

Metric	Target
Comment coverage	100% of columns
Example query count	≥ 10 per space
Ambiguous column names	zero (or explicitly commented)

Code.

# scripts/audit_genie_grounding.py
from databricks.sdk import WorkspaceClient

TABLES = [
    "marketing.campaigns",
    "marketing.leads",
    "marketing.conversions",
    "marketing.spend",
]

w = WorkspaceClient()

def audit_columns(fq: str) -> tuple[int, int, list[str]]:
    parts = fq.split(".")
    schema = ".".join(parts[:-1])
    table  = parts[-1]
    rows = w.sql.execute_statement(
        warehouse_id="abc123-shared-analytics-wh",
        statement=f"""
            SELECT column_name, comment
            FROM   {schema}.INFORMATION_SCHEMA.COLUMNS
            WHERE  table_name = '{table}'
        """,
    ).result.data_array
    total = len(rows)
    commented = sum(1 for r in rows if r[1])
    missing   = [r[0] for r in rows if not r[1]]
    return total, commented, missing

def main() -> None:
    print(f"{'table':<40}{'covered':>10}{'total':>8}{'coverage':>12}")
    for t in TABLES:
        total, commented, missing = audit_columns(t)
        pct = commented / total if total else 0
        flag = "" if pct == 1.0 else "  (missing: " + ", ".join(missing) + ")"
        print(f"{t:<40}{commented:>10}{total:>8}{pct:>11.1%}{flag}")

if __name__ == "__main__":
    main()

Step-by-step explanation.

The audit script queries INFORMATION_SCHEMA.COLUMNS for every table in every Genie space; any column without a comment is flagged. This runs weekly in CI or on-demand; the output tells the space author exactly which columns need attention.
The fix is per-column COMMENT ON COLUMN DDL. Include: (a) the column's meaning in one sentence, (b) the unit if numeric ("cents", "USD", "seconds"), (c) the valid values if categorical, (d) any known gotchas ("nullable; NULL means unknown, not zero").
Sample values are the second-order grounding source. Genie can auto-profile tables to learn value distributions, but explicit sample values in the comment ('Channel; one of: email, social, paid_search') are more reliable than profiling.
Curation sprints on example queries: every week, pull the top 20 low-confidence questions from the Genie audit log; convert the ones that should have worked into example queries; deploy. Genie answer quality improves visibly week over week under this loop.
Track a "grounding completeness score" per space: (comment coverage) × (example query count / target) × (AI/BI Instructions presence). Below 0.7 = brittle; 0.7-0.9 = usable; > 0.9 = production. Publish the score alongside space usage metrics.

Output.

State	Comment coverage	Example queries	Answer quality
Bootstrap	30%	3	brittle (~35% wrong)
Comment sprint	100%	3	improved (~15% wrong)
Example curation	100%	15	usable (~5% wrong)
Weekly iteration	100%	30+	production (~2% wrong)

Rule of thumb. Genie space quality is a curation game, not a modelling game. Ship the audit script; publish the grounding-completeness score; run weekly curation sprints. Six weeks of curation moves a brittle space to a production-grade one.

Senior interview question on Genie

A senior interviewer might ask: "You're launching Genie to a marketing team on Databricks. Design the space setup, the governance, the eval harness, and the on-call story when a business user reports 'the numbers are wrong.'"

Solution Using scoped Genie space + UC governance + weekly curation sprint + a "wrong-answer" triage runbook

-- 1. Governance — RLS + column mask (as before)
-- 2. Comments on every column (audit script enforces)
-- 3. Genie space created via SDK with 15 example queries + AI/BI instructions

# 4. Wrong-answer triage runbook (encoded as a script)
# scripts/triage_wrong_answer.py
import json
from datetime import datetime

def triage(question: str, generated_sql: str, correct_sql: str, user: str) -> dict:
    """Categorise a user-flagged wrong answer and produce a fix ticket."""
    diagnosis = {}

    # (a) grounding gap
    if any(tok in generated_sql.upper() and tok not in correct_sql.upper()
           for tok in ["FROM MARKETING.", "JOIN MARKETING."]):
        diagnosis["kind"] = "grounding-gap"
        diagnosis["fix"]  = "add column/table comment; re-audit"

    # (b) missing example query
    elif len(correct_sql) > 100 and "GROUP BY" in correct_sql:
        diagnosis["kind"] = "missing-example"
        diagnosis["fix"]  = f"add example query: ({question!r}, {correct_sql!r})"

    # (c) business rule missing from AI/BI Instructions
    elif "revenue_cents" in generated_sql and "/100" not in generated_sql:
        diagnosis["kind"] = "instruction-gap"
        diagnosis["fix"]  = "add rule to AI/BI Instructions: 'divide revenue_cents by 100'"

    # (d) LLM regression (rare — file a Genie support ticket)
    else:
        diagnosis["kind"] = "llm-regression"
        diagnosis["fix"]  = "escalate to Databricks support with the pair"

    return {
        "question":      question,
        "user":          user,
        "diagnosis":     diagnosis,
        "flagged_at":    datetime.utcnow().isoformat(),
        "generated_sql": generated_sql,
        "correct_sql":   correct_sql,
    }

Step-by-step trace.

Layer	Component	Purpose
Unity Catalog	column comments + RLS + masks	grounding + governance
Genie space	4 tables + 15 examples + instructions	conversation scope
Weekly sprint	audit + example curation	continuous grounding improvement
Wrong-answer triage	script categorises the failure mode	fast fix routing
Ops dashboard	user-flag rate + comment coverage	production monitoring

After the deployment, marketing analysts ask questions in English, see charts + generated SQL, flag surprises with one click; the weekly curation sprint absorbs the flags into either new comments, new example queries, or new AI/BI Instructions; the wrong-answer triage script routes each flag to the correct fix in under 60 seconds. Space quality improves visibly for the first 6 weeks, then stabilises.

Output:

Metric	Week 1	Week 6
User "wrong answer" flag rate	~15%	~2%
Comment coverage	40%	100%
Example queries	5	30
Avg time to fix a flag	3 days	2 hours
Marketing-team confidence	low	high

Why this works — concept by concept:

Unity-Catalog-grounded — no YAML to author separately; the grounding source is the same metadata that powers governance, lineage, and search. One source of truth reduces drift.
Scoped Genie space — a 4-table space produces sharper answers than a 40-table space. Scope forces you to think about persona and use case; sprawl invites hallucination.
Curation loop — weekly example-query additions and comment refinements move space quality from brittle to production in ~6 weeks. Space quality is a function of continuous investment, not one-time setup.
Wrong-answer triage — the runbook categorises each flag into one of four failure modes (grounding gap, missing example, instruction gap, LLM regression) so the fix is targeted, not shotgun.
Cost — space quality is bounded by curation time (~2 hours/week per space post-bootstrap) and SQL warehouse compute per query. Compared to building custom analyst-only dashboards for every question, Genie's marginal cost per novel question is ~$0.01 in warehouse credits. Net O(1) per question; O(space size × week) for curation.

Data Analysis
Topic — data-analysis
Data-analysis problems on natural-language BI patterns

Practice →

Data Validation Topic — data-validation Data-validation problems on generated-SQL correctness

Practice →

5. Picking / stacking the assistants — decision matrix + interview signals

`llm assistants for analytics` should be stacked, not picked — dbt Copilot upstream authors what Cortex Analyst / Genie serve downstream

The mental model in one line: the mature 2026 answer to "which LLM assistant should we deploy" is stack them: dbt Copilot at the authoring layer builds and documents the models, dbt semantic models declare the metrics, Cortex Analyst serves the SQL-writing persona from the same semantic layer via YAML export, and Genie serves the business-user persona from the same warehouse via Unity-Catalog metadata — each assistant grounds on an artifact the upstream layer already produces, so the total investment scales as O(models) rather than O(assistants × personas). Picking one assistant only works for teams with one persona; the moment you have authors, analysts, and business users, you need the stack.

The four axes for the stack decision.

Grounding compatibility. dbt Copilot grounds on the compiled project; dbt semantic models export to Cortex Analyst YAML; Unity Catalog comments (which dbt can persist_docs) feed Genie. The upstream layer feeds the downstream layer, so authoring effort compounds.
Persona coverage. Authors → analysts → business users. Each persona has a canonical assistant. Skipping a persona = a coverage gap; the skipped users either fall back to hand-written SQL or an ungoverned side-channel.
Governance uniformity. All three serving assistants (Cortex Analyst, Genie, dbt semantic layer) execute under caller identity, so warehouse-level RLS + column masks bind uniformly. Authoring assistants (dbt Copilot) have no runtime governance; safety comes from CI + review.
Evaluation harness. One golden-question suite per semantic model, run in CI on every change, catches regressions across whichever assistant consumes the semantic layer. One test suite, many assistants.

The stack recipe.

Layer 1: authoring. dbt Cloud + dbt Copilot. Models + tests + docs + semantic models authored inline. Manifest + catalog kept fresh via pre-commit + CI.
Layer 2: semantic layer. dbt semantic models declare metrics once. Exporter (dbt-cortex, custom script) transforms them into Cortex Analyst YAML. persist_docs writes descriptions into the warehouse for Genie.
Layer 3: serving. Cortex Analyst for the analyst / developer persona (REST API, YAML-grounded). Genie for the business-user persona (chat + chart, UC-grounded). Both execute under caller identity.
Layer 4: evaluation. Golden-question suite per semantic model. CI runs it on every YAML / model change; alerts on regression.

Common interview probes on stacking.

"Do you pick one assistant or stack them?" — required answer: stack; each persona has a canonical assistant.
"What's the single source of truth for the semantic layer?" — required answer: dbt semantic models; export to Cortex YAML, persist_docs to Unity Catalog.
"How do you keep them consistent?" — CI export + drift check + golden-question harness.
"What if you're on Snowflake but not Databricks?" — dbt Copilot + Cortex Analyst covers authors + analysts + business users (via a Streamlit / internal-app wrapper on Cortex).

Worked example — the stacked-assistant reference architecture

Detailed explanation. The reference architecture: dbt Cloud authors semantic models; a CI job exports them to Cortex Analyst YAML + persists comments to Unity Catalog; Cortex Analyst serves the analyst REST API; Genie serves business-user chat; a shared golden-question suite runs on every semantic-model change and gates deploys.

Repo layout. dbt_project/ (models + semantic models), semantic_models/ (exported YAML), eval/ (golden questions), .github/workflows/ (CI).
CI pipeline. On PR: compile dbt → export semantic models to Cortex YAML → upload YAML to Snowflake stage → run golden-question harness → gate merge.
Governance. RLS + column masks on warehouse tables; Cortex Analyst + Genie inherit automatically.

Question. Show the CI pipeline that keeps the stack consistent.

Input.

Stage	Purpose
dbt compile	manifest freshness
Semantic-model export	dbt → Cortex YAML
YAML upload	to Snowflake stage
UC comment sync	`persist_docs` for Genie
Golden-question run	regression gate

Code.

# .github/workflows/stacked-assistants.yml
name: stacked-assistants
on:
  pull_request:
    paths:
      - 'dbt_project/**'
      - 'semantic_models/**'
      - 'eval/**'

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: {python-version: '3.12'}
      - run: pip install dbt-snowflake==1.9.0 pyyaml requests snowflake-connector-python

      # Layer 1 — compile dbt (Copilot grounding freshness)
      - run: cd dbt_project && dbt deps && dbt compile --profiles-dir ci_profiles

      # Layer 2 — export semantic models to Cortex YAML
      - run: python scripts/export_dbt_to_cortex.py \
                 --manifest dbt_project/target/manifest.json \
                 --out semantic_models/revenue_model.yaml

      # Layer 2b — persist docs (writes UC comments for Genie)
      - run: cd dbt_project && dbt docs generate
      - run: cd dbt_project && dbt run-operation persist_all_docs

      # Layer 3 — upload YAML to Snowflake stage
      - run: python scripts/upload_yaml_to_stage.py semantic_models/revenue_model.yaml

      # Layer 4 — golden-question regression gate
      - run: python eval/run_golden_questions.py

      # Layer 4b — schema-drift check
      - run: python scripts/check_cortex_yaml_drift.py

      # Layer 4c — Copilot drift check
      - run: python scripts/check_copilot_drift.py

# scripts/export_dbt_to_cortex.py — one exporter, one semantic layer, three assistants
import json
import yaml
import argparse
from pathlib import Path

def dbt_metric_to_cortex_measure(metric: dict) -> dict:
    """Translate a dbt metric into a Cortex Analyst measure block."""
    return {
        "name":        metric["name"],
        "expr":        metric.get("type_params", {}).get("expr") or metric["name"],
        "data_type":   "NUMBER",
        "description": metric.get("description", ""),
        "synonyms":    metric.get("meta", {}).get("synonyms", []),
    }

def dbt_dimension_to_cortex(dim: dict) -> dict:
    return {
        "name":        dim["name"],
        "expr":        dim.get("expr", dim["name"]),
        "data_type":   dim.get("data_type", "TEXT").upper(),
        "synonyms":    dim.get("meta", {}).get("synonyms", []),
    }

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--manifest", required=True)
    ap.add_argument("--out",      required=True)
    args = ap.parse_args()

    manifest = json.loads(Path(args.manifest).read_text())
    sms      = [n for n in manifest["semantic_models"].values()]
    metrics  = list(manifest["metrics"].values())

    tables = []
    for sm in sms:
        model_name = sm["node_relation"]["alias"]
        tables.append({
            "name": model_name,
            "description": sm.get("description", ""),
            "base_table": {
                "database": sm["node_relation"]["database"],
                "schema":   sm["node_relation"]["schema"],
                "table":    sm["node_relation"]["alias"],
            },
            "dimensions": [dbt_dimension_to_cortex(d)
                           for d in sm.get("dimensions", []) if d.get("type") == "categorical"],
            "time_dimensions": [dbt_dimension_to_cortex(d)
                                for d in sm.get("dimensions", []) if d.get("type") == "time"],
            "measures": [dbt_metric_to_cortex_measure(m) for m in metrics],
        })

    cortex_yaml = {
        "name":        "revenue_model",
        "description": "Auto-exported from dbt semantic models.",
        "tables":      tables,
    }

    Path(args.out).write_text(yaml.safe_dump(cortex_yaml, sort_keys=False))
    print(f"exported {len(tables)} tables to {args.out}")

if __name__ == "__main__":
    main()

Step-by-step explanation.

The CI job runs on any PR touching dbt models, semantic models, or the eval harness. Each layer of the stack has its own step; a failure at any layer blocks the merge, so drift never reaches production.
dbt compile refreshes the manifest — the grounding source for dbt Copilot. This is also the source-of-truth artifact for the exporter script.
The exporter translates dbt semantic models into Cortex Analyst YAML. Metrics become measures; categorical dimensions stay as dimensions; time-typed dimensions become time-dimensions. The synonym list travels from dbt meta.synonyms to Cortex synonyms.
persist_docs writes dbt column descriptions into the warehouse as native COMMENT ON COLUMN statements. This is what feeds Genie's grounding — one authoring workflow (dbt) produces grounding metadata for both Cortex (YAML) and Genie (warehouse comments).
The golden-question harness (from section 1) runs against the deployed YAML and gates the merge. Schema-drift check + Copilot-drift check are additional safety rails. Merged PRs cannot break any assistant in the stack.

Output.

Layer	Artifact produced	Consumed by
dbt models	fct_, dim_ tables	warehouse queries
dbt semantic models	sm_orders (metrics + dims)	exporter
exporter	revenue_model.yaml	Cortex Analyst
persist_docs	UC column comments	Genie
golden-question harness	regression score	CI gate

Rule of thumb. Author the semantic layer once, in dbt; export it to every consuming assistant (Cortex YAML, UC comments); run one golden-question suite that catches regressions across the stack. The upfront authoring investment pays off across every persona.

Senior interview question on stacking

A senior interviewer might ask: "Design the LLM-assistant strategy for a company on Snowflake + dbt + Databricks (they have both warehouses). Cover the authoring persona, analyst persona, and business-user persona; explain how the semantic layer stays consistent across three assistants and two warehouses; specify the eval harness that gates every change."

Solution Using a dbt-first semantic layer, exporters to Cortex + UC, and a unified golden-question harness

# 1. dbt semantic models — the single source of truth
# (as in section 2's worked example)

# 2. Two exporters — one per warehouse
# scripts/export_dbt_to_cortex.py   (as above; produces Cortex YAML)
# scripts/export_dbt_to_uc.py       (produces UC COMMENT ON COLUMN DDL)

# 3. Unified golden-question harness — runs against BOTH assistants
# eval/run_golden_questions.py extended with --backend cortex|genie
import sys
from cortex_client import ask_cortex
from genie_client  import ask_genie

def run(backend: str, questions: list[dict]) -> None:
    for q in questions:
        if backend == "cortex":
            actual = ask_cortex(q["question"])
        elif backend == "genie":
            actual = ask_genie(q["question"], space_id=q["genie_space_id"])
        score = semantic_match(q["expected_sql"], actual)
        print(f"[{backend}] {q['question'][:40]!r} -> {score:.2f}")

Step-by-step trace.

Layer	Artifact	Assistant fed
dbt semantic models	metrics + dims	authoring source
Cortex exporter	revenue_model.yaml	Snowflake Cortex Analyst
UC exporter	COMMENT ON DDL	Databricks Genie
Golden questions	shared suite (backend switch)	both regressions caught
CI	5-stage pipeline	gates every merge

After the rollout, one dbt PR simultaneously updates dbt Copilot's grounding (via manifest recompile), Cortex Analyst's grounding (via YAML export), and Genie's grounding (via UC comment persist). The shared golden-question harness catches regressions on either backend. The company runs three assistants in production but maintains one semantic layer — the authoring investment scales linearly, not quadratically.

Output:

Metric	Single-assistant deployment	Stacked deployment
Semantic layer sources	1-3 (fragmented)	1 (dbt)
Authoring cost per new metric	3 (one per assistant)	1 (dbt only)
CI regression coverage	per-assistant	unified
Persona coverage	1	3
Governance uniformity	ad-hoc	inherited (caller role)
Time-to-add a new metric	1 week	1 day

Why this works — concept by concept:

Stack, don't pick — assistants complement, they don't compete. Authoring assistants (dbt Copilot) sit above serving assistants (Cortex, Genie); serving assistants sit above governance (warehouse RLS). Skipping a layer creates a coverage gap.
One semantic layer, many exporters — dbt semantic models are the source of truth. Cortex YAML and UC comments are derived artifacts, regenerated by CI on every model change. Drift is impossible when the derivation is automated.
Persona-matched serving — Cortex Analyst for analysts (REST + SQL surfaced), Genie for business users (chat + chart). Each persona gets the UX that fits; neither is asked to use the other's UX.
Unified golden-question harness — one suite, two backends. Both assistants are held to the same accuracy bar; a regression on either fails CI. Evaluation cost scales as O(questions), not O(questions × assistants).
Cost — one-time exporter development (~1 sprint), continuous dbt authoring (weeks), zero incremental per-assistant maintenance. Compared to maintaining three independent semantic layers, this is a 3× reduction in authoring cost and a 3× reduction in drift risk. Net O(dbt project size) authoring; O(1) per-assistant marginal cost.

Design
Topic — design
Design problems on multi-assistant analytics stacks

Practice →

SQL Generation
Topic — sql-generation
SQL-generation problems on stacked semantic-layer patterns

Practice →

Cheat sheet — LLM assistant recipes

Which assistant when. dbt Copilot is the 2026 default for the authoring persona whenever your team uses dbt Cloud (or a dbt-Copilot-compatible plugin). Cortex Analyst is the default text-to-SQL layer for analyst-persona serving on Snowflake, grounded on a hand-authored YAML semantic model. Databricks AI/BI Genie is the default business-user chat surface on Databricks, grounded on Unity Catalog comments + curated example queries. Generic LLM wrappers (Cursor / Claude / GPT-4o) belong in the engineer's ad-hoc toolbox, not in a governed production workload.
dbt Copilot enablement checklist. Enable Copilot in dbt Cloud project settings; enforce dbt compile on pre-commit for anyone authoring outside dbt Cloud IDE; enforce dbt docs generate after any column-shape change; set persist_docs: {relation: true, columns: true} in dbt_project.yml so YAML descriptions land in the warehouse for downstream Genie / Cortex consumption; run a CI drift-check script that fails PRs referencing unknown ref() targets; adopt a Copilot-code-review rubric (ref hallucination, column hallucination, doc mismatch, semantic-model entity types).
Cortex Analyst semantic-model YAML template. name, description, tables[] (each with base_table.{database,schema,table}, primary_key.columns, dimensions[], time_dimensions[], measures[] with expr + synonyms + description), relationships[] (declared FKs; joins refused without them), verified_queries[] (10-50 few-shot exemplars). Upload to a Snowflake stage; reference via @DB.SCHEMA.STAGE/model.yaml in the REST call.
Cortex Analyst REST call. POST https://<account>.snowflakecomputing.com/api/v2/cortex/analyst/message with {"messages": [{"role":"user","content":[{"type":"text","text":"<question>"}]}], "semantic_model_file": "@..."}; Bearer PAT. Response message.content has text interpretation blocks, sql blocks (statement + confidence), and optional suggestion blocks. Execute the SQL under the caller's role — never Cortex's — so RLS + column masks bind.
Databricks Genie space setup steps. (1) Scope the space to 3-15 Unity Catalog tables in one domain; (2) ensure every column has a COMMENT (audit script enforces); (3) apply column masks and row filters on sensitive columns; (4) curate 10+ example queries per space; (5) write a page of AI/BI Instructions covering business rules, defaults, and unit conventions; (6) provision via Databricks SDK (w.genie.create_space(...)) or the workspace UI; (7) ship the wrong-answer triage runbook so flags convert to fixes in hours, not weeks.
Golden-question evaluation harness skeleton. eval/golden_questions.json — 100-500 (question, expected_sql) pairs per semantic model. eval/run_golden_questions.py — POSTs each question to the assistant, extracts generated SQL, canonicalises via sql_metadata / sqlglot, computes semantic-match score. CI gate: block merge on exact-match < 90% OR semantic-match < 95%. Track drift week-over-week on a dashboard.
Schema-drift check. For Cortex: parse the YAML, extract declared columns per table, query Snowflake INFORMATION_SCHEMA.COLUMNS, fail on any YAML column not in the warehouse. For dbt Copilot: parse ref() calls, verify each target exists in manifest.json. For Genie: audit INFORMATION_SCHEMA.COLUMNS for comment coverage (target 100%). Run all three in CI on every PR touching models or YAML.
Governance uniformity contract. All three serving assistants (Cortex Analyst REST, Genie chat, dbt semantic layer) execute the generated SQL under the caller's warehouse role. Row-access policies, column masks, warehouse grants, and audit logs bind automatically. Authoring assistants (dbt Copilot) have no runtime governance; the safety comes from human review + CI. This is the key argument against generic LLM wrappers — they run under a service account with wide grants, so PII leaks are structural, not incidental.
Synonym / vocabulary curation loop. For Cortex Analyst: expand synonyms on every dimension and measure to cover business-user phrasing ("MRR", "GMV", "logos", "geo"); add verified queries for the top misses. For Genie: refine table + column comments, add example queries, tweak AI/BI Instructions. Run a weekly 30-minute curation sprint per semantic model / Genie space; the vocabulary stabilises after 6-8 weeks then requires only monthly refreshes.
Wrong-answer triage runbook. When a user flags a wrong answer, categorise it in under 60 seconds: (a) grounding gap → add column comment + re-audit; (b) missing example → add verified query / Genie example; (c) instruction gap → add rule to AI/BI Instructions; (d) LLM regression → escalate to vendor. Log every flag; publish weekly flag-rate + fix-time dashboards; treat > 5% flag rate as a P2 incident.
Stacking pattern. dbt Copilot (authoring) → dbt semantic models (single source of truth) → exporters (dbt → Cortex YAML; dbt persist_docs → UC comments) → Cortex Analyst (analyst persona) + Genie (business-user persona) → unified golden-question harness (regression gate). One semantic layer, many consuming assistants; O(models) authoring cost, O(1) per-assistant marginal cost.
Confidence surface + safety gate. For every generated SQL, surface the model's confidence score to the user (Cortex returns it in the sql block; Genie surfaces it in the UI). Pre-execute safety gate: refuse DDL/DML via a regex denylist; refuse confidence < 0.6 without human review; cap LIMIT on unbounded queries; require WHERE on partitioned columns for cost control. This is the belt-and-braces defense between grounded generation and warehouse execution.
Cost management. Cortex Analyst credits ~$0.01-0.05 per message depending on model + question complexity; Genie warehouse compute scales with SQL execution cost (typically $0.001-0.01 per turn on a serverless warehouse). Budget alerts + per-user rate limits are non-optional; unbounded usage can spike credit consumption. dbt Copilot is priced as a dbt Cloud seat add-on; per-user cost is fixed and cheap relative to authoring productivity gain.
Migration cost between assistants. Adding Cortex Analyst on top of an existing dbt project: ~1 sprint per semantic model (YAML authoring + REST integration + golden questions). Adding Genie on top of a Databricks project with dense UC comments: ~2 days per space (scope + examples + instructions). Migrating off a generic-LLM wrapper to a grounded assistant: ~2-4 weeks (semantic-layer authoring + integration switch + user retraining). The stacked deployment is worth the upfront cost by month 2.

Frequently asked questions

What is an LLM assistant for analytics in one sentence?

An llm data assistant for analytics is a natural-language interface — inline completion in an IDE, REST API, or embedded chat pane — that grounds an LLM's generation on a declarative artifact (dbt project graph, YAML semantic model, Unity Catalog metadata) so the assistant can produce SQL, docs, semantic-layer metrics, or charts that reference the team's real tables, columns, and metrics without hallucinating. The three canonical vendor assistants — dbt copilot (project-graph-grounded authoring in dbt Cloud), snowflake cortex analyst (YAML-semantic-model-grounded text-to-SQL REST API), and databricks ai/bi genie (Unity-Catalog-grounded chat-with-your-data over the lakehouse) — differ in grounding source, target persona, governance boundary, and latency, and the choice binds every downstream question, dashboard, and trained business user for years. Every senior analytics-engineering interview probes LLM assistants because they're the load-bearing UX pattern for the modern warehouse + semantic-layer stack.

dbt Copilot vs Snowflake Cortex Analyst — when do I pick each?

Pick dbt Copilot when the user is an analytics engineer authoring dbt models, tests, YAML docs, or semantic-layer metrics — Copilot is embedded in the dbt Cloud IDE, grounds on the compiled manifest.json + catalog.json, and drafts SQL / YAML / semantic-model blocks that reference your real refs, sources, and columns. Pick Snowflake Cortex Analyst when the user is an analyst or app developer on Snowflake who needs natural language to sql grounded on a declarative semantic model — it's a REST API that consumes a semantic_model.yaml you author, returns generated SQL + confidence + interpretation, and executes under the caller's Snowflake role so RLS and column masks bind automatically. The mature answer is both: dbt Copilot at the authoring layer, Cortex Analyst at the serving layer, with dbt semantic models as the single source of truth exported to Cortex YAML via CI. Never treat the two as substitutes; they serve different personas at different points in the analytics workflow.

What is a semantic layer and why does it matter for LLM assistants?

A semantic layer is a declarative artifact that names your business measures (revenue, mrr, churn) and dimensions (region, customer_segment, channel) once, ties each to its underlying SQL expression against warehouse tables, enumerates synonyms for user-facing vocabulary, declares foreign-key relationships between tables, and (for the mature implementations) ships verified example queries as few-shot exemplars. It matters for LLM assistants because the semantic layer is the grounding contract — the LLM can only generate what's declared, so hallucination is bounded by design rather than fought after the fact. Cortex Analyst grounds on a YAML semantic model; Genie grounds on Unity Catalog metadata + example queries (a lightweight semantic layer); dbt Copilot grounds on semantic_models.yml blocks in the project. Without a semantic layer, every assistant collapses to "guess from table names", which is the failure mode users experience as "the numbers are wrong."

Can I use these assistants without exposing raw table access to end users?

Yes — and you should. The three vendor assistants all execute their generated SQL under the caller's warehouse identity, not a shared service account, so every row-access policy, column mask, warehouse grant, and audit log applies exactly as if the user had written the SQL by hand. Cortex Analyst returns the generated SQL to your application; your application runs it via the Snowflake connector as the authenticated user. Genie runs it via a Databricks SQL warehouse under the caller's Unity Catalog identity. dbt Copilot only drafts SQL; a human commits and dbt Cloud runs it under the project credentials with normal RBAC + code review. The pattern to avoid is a generic LLM wrapper with its own service account — that pattern leaks PII structurally, because the LLM has grants the end user doesn't. Grounded vendor assistants + caller-identity execution + column masks + row-level filters is the correct 2026 answer for regulated data.

How do I evaluate an LLM analytics assistant?

Ship a golden-question harness on day one: 100-500 (question, expected_sql) pairs per semantic model, maintained in a eval/golden_questions.json file under version control. On every semantic-model change (dbt PR, YAML edit, new Genie example), run the harness in CI: POST each question to the assistant, extract the generated SQL, canonicalise it (via sqlglot or sql_metadata — extract tables, join graph, filter set, measure set, GROUP BY), compute a semantic-match score against expected, and gate the merge on exact_match_rate < 90% OR semantic_match_rate < 95%. Track weekly: avg confidence, refusal rate (safety gate), user-flag rate (wrong-answer button clicks), and golden-question regression rate. Publish the dashboards. The professional signal is "we run the golden-question suite in CI and block merges below threshold" — candidates who name this pattern get graded senior; candidates who don't get graded mid.

Are these assistants safe for regulated data?

Yes, with the same discipline any production analytics workload requires. The safety story rests on three legs: (1) grounded generation bounds the hypothesis space — the LLM cannot generate SQL against tables or measures not declared in the YAML / manifest / UC metadata; (2) caller-identity execution means every row-access policy, column mask, and grant on the underlying warehouse tables binds automatically to the generated SQL; (3) safety gates in the application layer refuse DDL/DML, low-confidence responses, and unbounded queries before execution. For HIPAA, PCI-DSS, SOC2, and GDPR workloads: put column masks on every PII column, define row-access policies for tenant / region isolation, scope Genie spaces / Cortex semantic models to the minimum tables required for the use case, log every request + response for audit, and run the golden-question harness on every change. The pattern to reject is a generic LLM wrapper with a shared service account — it fails the caller-identity leg by construction and cannot be retrofitted safely. Grounded vendor assistants + warehouse governance + application-layer safety gates is the auditable 2026 architecture.

Practice on PipeCode

Drill the SQL practice library → for the semantic-layer, text-to-SQL, and grounded-generation problems senior interviewers love.
Rehearse the SQL-generation practice library → for natural-language-to-SQL, semantic-diff scoring, and golden-question harness patterns.
Sharpen the analytics axis with the data-analysis practice library → for business-user question decomposition and metric-definition scenarios.
Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the three-vendor decision matrix against real graded inputs.

Lock in LLM-assistant muscle memory

Docs explain features. PipeCode drills explain the decision — when dbt Copilot's manifest goes stale, when Cortex Analyst's YAML drifts from the warehouse, when Genie's answer quality is limited by column-comment coverage, when a golden-question harness earns its place in CI. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior analytics and data engineers actually face when picking, stacking, and evaluating LLM assistants.

Practice SQL-generation problems →
Practice data-analysis problems →

1. Why LLM assistants are the new UX layer for analytics teams in 2026

Four assistants, four wildly different grounding stories — the pick that binds your semantic layer for years

Worked example — the four-axis comparison table

Worked example — what interviewers actually probe

Worked example — the "pick the assistant" decision tree

Senior interview question on LLM assistant selection

Solution Using a stacked assistant deployment — dbt Copilot for authors, Cortex Analyst for analysts, and a golden-question eval harness

2. dbt Copilot — model-authoring, docs, and semantic-layer completions

dbt copilot grounds on the project graph — the authoring assistant that turns manifest.json into inline completions

Worked example — prompting dbt Copilot to draft an fct_orders incremental model

Worked example — drafting semantic-layer metrics from a natural-language spec

Worked example — the manifest-freshness failure mode

Senior interview question on dbt Copilot

Solution Using freshness hooks + a Copilot code-review rubric + a semantic-model-first authoring pattern

3. Snowflake Cortex Analyst — governed text-to-SQL over your semantic model

snowflake cortex analyst is a REST-API text-to-SQL layer grounded on a YAML semantic model — governance runs through the caller's role

Worked example — authoring revenue_model.yaml and calling the REST API

Worked example — the schema-drift failure mode

Worked example — synonym curation for user-friendly text-to-SQL

Senior interview question on Cortex Analyst

Solution Using a governed YAML semantic model + RLS-inherited execution + drift + golden-question harness + a user-facing "confidence surface"

4. Databricks AI/BI Genie — natural-language BI over Unity Catalog

databricks ai/bi genie grounds on Unity Catalog metadata + curated example queries — the business-user chat surface over the lakehouse

Worked example — setting up a Genie space for a marketing analytics team

Worked example — the sparse-comments failure mode + iterative curation

Senior interview question on Genie

Solution Using scoped Genie space + UC governance + weekly curation sprint + a "wrong-answer" triage runbook

5. Picking / stacking the assistants — decision matrix + interview signals

llm assistants for analytics should be stacked, not picked — dbt Copilot upstream authors what Cortex Analyst / Genie serve downstream

Worked example — the stacked-assistant reference architecture

Senior interview question on stacking

Solution Using a dbt-first semantic layer, exporters to Cortex + UC, and a unified golden-question harness

Cheat sheet — LLM assistant recipes

Frequently asked questions

What is an LLM assistant for analytics in one sentence?

dbt Copilot vs Snowflake Cortex Analyst — when do I pick each?

What is a semantic layer and why does it matter for LLM assistants?

Can I use these assistants without exposing raw table access to end users?

How do I evaluate an LLM analytics assistant?

Are these assistants safe for regulated data?

Practice on PipeCode

Lock in LLM-assistant muscle memory

`dbt copilot` grounds on the project graph — the authoring assistant that turns manifest.json into inline completions

Worked example — prompting dbt Copilot to draft an `fct_orders` incremental model

`snowflake cortex analyst` is a REST-API text-to-SQL layer grounded on a YAML semantic model — governance runs through the caller's role

Worked example — authoring `revenue_model.yaml` and calling the REST API

`databricks ai/bi genie` grounds on Unity Catalog metadata + curated example queries — the business-user chat surface over the lakehouse

`llm assistants for analytics` should be stacked, not picked — dbt Copilot upstream authors what Cortex Analyst / Genie serve downstream