Cursor & GitHub Copilot for SQL Engineers: LLM-Native SQL, dbt & Airflow Authoring Patterns

cursor for sql is the phrase that quietly rewrote the senior data engineer's job description in 2026 — the SQL you author, the dbt model you scaffold, the Airflow DAG you wire up, and the pytest fixture you generate now travel through an LLM-native editor before they ever hit the compile step, and the engineers who have learned to drive that editor ship two or three times the volume of production-grade SQL that engineers who still hand-type every window function do. The productivity delta is not marginal; it is a step change, and it is measurable in PR throughput, in incident-response time, and in the size of the analytics-engineering roadmap a single senior can realistically own. The editor is no longer a text buffer with syntax highlighting — it is a small autonomous collaborator that reads your repo, remembers your dbt conventions, and drafts your next SELECT, task_group, or dbt macro before your fingers have finished typing the docstring.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through how you configure Cursor for a dbt monorepo," or "how do you keep github copilot sql from hallucinating column names against a schema it hasn't seen," or "how would you use an LLM to co-author an Airflow DAG that ingests from a Snowflake external stage." It walks through the four canonical LLM-authoring patterns — the .cursorrules file as the single source of truth for repo prompts, the comment-first Copilot inline pattern that pins ghost-text to a natural-language contract, the docstring-first Airflow DAG scaffold that makes the LLM propose sensors and task groups, and the three-lane guardrail (dbt compile → sqlfluff lint → human review) that protects the merge — plus the failure modes that make senior interviewers probe for review discipline more than for prompt cleverness. 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 on the SQL-generation practice library →, and sharpen the pipeline axis with the ETL practice library →.

---

On this page

• Why LLM-native editors changed SQL authoring in 2026
• Cursor rules, .cursorrules, and repo-scoped context for SQL / dbt
• GitHub Copilot patterns — inline SQL, dbt macros, Jinja completions
• Airflow DAG authoring with LLM assistants — task groups, sensors, tests
• Guardrails, review discipline & the SQL-engineer's Copilot workflow
• Cheat sheet — LLM-native SQL authoring recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why LLM-native editors changed SQL authoring in 2026

The editor is now the LLM — four axes that separate a productive workflow from a hallucinating one

The one-sentence invariant: an LLM-native editor is not "autocomplete with more parameters" but a small autonomous collaborator that reads your repo, keeps a rolling context window of your dbt model graph and Airflow DAG shape, drafts code against natural-language prompts you either write explicitly or leak accidentally through comments and docstrings, and either accelerates your senior SQL work by 2–3× or ships subtly-wrong SQL every day depending entirely on whether you have configured repo context, comment discipline, and review guardrails. The tools — Cursor, GitHub Copilot, Copilot Chat, Copilot Workspace, Codex CLI, Windsurf — differ in ergonomics, latency, and agentic ability but converge on the same four axes senior interviewers now probe: how the LLM sees your repo, how you frame the prompt, how autonomously it acts, and how the compile / lint / review gates catch what it gets wrong.

The four LLM-editor axes interviewers actually probe.

• Repo context. Cursor's .cursorrules file plus the @Codebase / @Docs symbol injection gives the LLM a persistent, versioned repo prompt. GitHub Copilot inline uses the current file plus a handful of recently-opened files as its "context"; Copilot Chat can pull in @workspace and @github. The width and freshness of that context window is the single strongest predictor of hallucination rate — narrow context means the LLM invents column names, wide-but-stale context means it drafts against dbt models that were renamed last month.
• Inline latency. Ghost-text completions must arrive in 200–400 ms or the flow breaks and you fall back to typing. Cursor's small-model tab completion is optimised for this; Copilot's inline model is similar. Copilot Chat and Cursor Composer intentionally trade latency for depth — you accept 3–10 s in exchange for multi-file edits.
• Agentic ability. How much does the LLM do without asking? Cursor Composer, Copilot Workspace, and Codex CLI open, read, edit, and run files across the repo; inline Copilot only completes at the caret. Agentic mode is where senior data engineers gain the biggest velocity multiplier — and where the review discipline must be strongest.
• Guardrails. Precommit hooks (sqlfluff, dbt parse, dbt compile), CI checks (dbt test, pytest, sqlmesh diff), and PR review conventions are the only defenses against the LLM's failure modes. An engineer who cannot recite their three-lane guardrail out loud is not senior — they are lucky.

Cursor vs GitHub Copilot vs Copilot Chat vs Codex CLI — what each is best at.

• Cursor. VS Code fork with first-class repo context, .cursorrules, @Codebase/@Docs symbol injection, and the Composer agentic mode. Best fit: dbt monorepos, analytics-engineering teams, iterative model refactors that touch a dozen files. The .cursorrules file is the load-bearing config — set it well and hallucination rates drop measurably.
• GitHub Copilot (inline). Fastest ghost-text completion; deep VS Code / JetBrains / Vim integration; excellent for line-by-line SQL and dbt macro authoring. Weak on repo-wide refactors — it doesn't know models it hasn't seen in the current session unless you pull them into scope explicitly.
• Copilot Chat. Conversational assistant with @workspace, @github, @vscode symbols. Best fit: "explain this DAG," "generate a test for this dbt model," "refactor this window function." Slower than inline; more thoughtful.
• Codex CLI / Claude Code / Aider. Terminal-native agents that read, edit, and run shell / SQL / pytest across the repo. Best fit: overnight refactors, one-shot data-quality audits, automated dbt exposure generation. Requires the strongest guardrails.

2026 reality — the SQL engineer's editor is now the LLM.

• Every senior data-engineering interview loop in 2026 that touches "how do you work day-to-day?" includes an LLM-workflow probe. The bare minimum answer is: "I use Cursor + Copilot with a .cursorrules file, comment-first prompting, and a three-lane guardrail before merge."
• The failure state is unchanged from 2024: the LLM drafts against a schema it hasn't seen, invents a column name, the SQL compiles because dbt didn't parse it, the query silently returns the wrong number, and a dashboard ships with 3% row drift for a week. The fix is not "don't use the LLM" — it's "configure repo context + comment-first prompts + dbt parse precommit."
• The productivity delta is measurable. Senior teams that have adopted the pattern report 2–3× PR throughput on analytics-engineering work, ~50% reduction in scaffolding time for new Airflow DAGs, and near-instant "explain this legacy CTE" turnaround during incident response.
• The interview signal that separates senior from mid is the ability to name failure modes by name — hallucinated columns, wrong SQL dialect, outdated Airflow operator API, drift between .cursorrules and the current dbt convention — not the ability to demo a slick completion.

What interviewers actually listen for.

• Do you name .cursorrules and comment-first prompting in the first sentence when asked how you use an LLM? — senior signal.
• Do you say "I never accept a completion I haven't run through dbt parse locally"? — required answer.
• Do you push back on "just use Cursor Composer for everything" with the review-discipline argument? — senior signal.
• Do you name the three-lane guardrail (compile, lint, human review) as your merge invariant? — senior signal.
• Do you describe your LLM workflow as "pair programming with a very fast but occasionally lying junior" rather than as "autocomplete"? — required answer.

Worked example — the four-axis LLM-editor comparison for a dbt + Airflow shop

Detailed explanation. The single most useful artifact for an LLM-workflow interview is a memorised 4×4 comparison table. Every senior conversation about "which editor / which model / which agentic depth" 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 analytics-engineering team maintaining a dbt monorepo (~300 models) plus ~40 Airflow DAGs.

• Repo shape. analytics-eng/ monorepo with dbt_project.yml, models/{staging,intermediate,marts}/, macros/, analyses/, snapshots/; sibling dags/ folder with 40 Airflow DAGs.
• Team. 6 analytics engineers + 2 senior data engineers; ~15 PRs/day; ~4 new dbt models/week.
• Constraints. No prod DB credentials in dev; dbt runs against dev_<user> schemas; Snowflake target; Airflow 2.9.
• Goal. Ship correct, idiomatic SQL and DAGs 2× faster than the pre-LLM baseline without shipping subtly-wrong SQL.

Question. Build the four-axis LLM-editor comparison for this team and pick the tool + workflow each engineer type should default to.

Input.

Editor / Mode	Repo context	Inline latency	Agentic depth	Guardrail cost
Cursor inline	.cursorrules + open files	200–400 ms	none (caret only)	low
Cursor Composer	.cursorrules + @Codebase	3–10 s	multi-file edit	medium
Copilot inline	current file + recents	150–300 ms	none	low
Copilot Chat	@workspace + @github	2–5 s	single-file suggest	medium
Codex CLI / Aider	full repo (agent-selected)	5–30 s	shell + edit + run	high

Code.

# .cursorrules (top of repo)
# This file is the single source of truth for repo prompts.
# Every Cursor completion sees this preamble.

You are pair-programming with a senior analytics engineer on the
`analytics-eng/` dbt + Airflow monorepo.

Repo conventions:
  - dbt profile: snowflake_prod / snowflake_dev
  - Model layers: staging → intermediate → marts
  - Naming: stg_<source>__<table>, int_<domain>__<verb>, dim_/fct_
  - Every model MUST have a schema.yml entry with description + tests
  - Prefer CTEs over subqueries; one CTE per logical step
  - SQL dialect: Snowflake (use QUALIFY, ILIKE, DATE_TRUNC, etc.)
  - Never invent column names; ask the user to paste the schema
    if the ref() target is not visible in context
  - When authoring DAGs, use TaskFlow API (@dag / @task decorators)
    and Airflow >= 2.9 operator names

Guardrails you MUST respect:
  - Every SQL suggestion must compile with `dbt parse` (no invalid
    Jinja, no unresolved refs)
  - Every DAG suggestion must import from `airflow.decorators`, not
    `airflow.operators.python_operator` (deprecated)
  - Never write to `prod` targets in generated code

When you don't know, say so and ask for the schema.

Step-by-step explanation.

1. The .cursorrules file at the repo root is the single most important config for a Cursor deployment. Every completion — inline and Composer — sees this preamble injected into the prompt. Getting the tone right ("You are pair-programming with a senior…") sets the model's register; getting the conventions right ("stg___") stops it from inventing names.
2. The naming section is load-bearing: without it, the LLM defaults to arbitrary snake_case model names ("customer_orders_summary") that violate the team's convention. With it, the LLM proposes mart_customers__orders_summary on the first attempt.
3. The "SQL dialect: Snowflake" line is what stops the model from generating Postgres-only functions (::TEXT, now()) in a Snowflake repo. Missing this line is one of the most common causes of "the SQL compiled but returned wrong results" bugs.
4. The guardrail section is the second-line defense: even a well-prompted LLM occasionally drifts, and telling it to respect dbt parse and the current Airflow API teaches it to prefer the correct patterns.
5. The "when you don't know, say so" instruction turns silent hallucination into loud "please paste the schema for dim_customers." This single line is worth an hour of review time per week.

Output.

Engineer type	Default editor	Default mode	When to escalate
Junior analytics engineer	Cursor inline	comment-first ghost text	Copilot Chat for "explain this"
Senior analytics engineer	Cursor inline + Composer	inline for lines, Composer for multi-file	Codex CLI for repo-wide refactor
Senior data engineer	Cursor + Codex CLI	Composer for DAG edits, CLI for audits	Copilot Chat for legacy explain
On-call engineer (incident)	Copilot Chat + Cursor	Chat for "explain," Cursor for hotfix	Codex CLI only in read-only mode

Rule of thumb. Pick the editor + mode based on (context width × latency budget × agentic depth × review effort). Inline for ghost-text speed, Chat for reasoning, Composer for multi-file edits, CLI for overnight jobs. The .cursorrules file is the config that ties them all together.

Worked example — what senior interviewers actually probe on LLM workflow

Detailed explanation. The senior data-engineering LLM-workflow interview in 2026 has a predictable structure: the interviewer opens with an ambiguous question ("walk me through how you use an AI assistant day-to-day"), then progressively narrows to test whether you know the failure modes. The candidates who name the guardrails in sentence one score highest; the candidates who describe "I use Copilot for autocomplete" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How do you use AI assistants in your day-to-day SQL / dbt work?" — invites you to name the pattern.
• Follow-up 1. "What's in your .cursorrules file?" — probes repo context.
• Follow-up 2. "How do you keep it from hallucinating column names?" — probes hallucination-defense pattern.
• Follow-up 3. "How do you review LLM-generated Airflow DAGs?" — probes review discipline.
• Follow-up 4. "Tell me about a time an LLM shipped subtly-wrong SQL." — probes incident literacy.

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

Input.

Interview signal	Weak answer	Senior answer
Named editor + mode	"I use Copilot"	"Cursor inline for ghost text, Composer for multi-file, Copilot Chat for explains"
Repo context	"it just works"	".cursorrules at repo root pinning dbt conventions + SQL dialect"
Hallucination defense	"I read the output"	"comment-first prompts + dbt parse precommit + schema.yml as truth"
Review discipline	"code review catches it"	"three-lane guardrail: dbt compile, sqlfluff lint, human PR read"
Incident literacy	"hasn't happened"	"yes — invented column, dbt parse missed it because Jinja was valid, sqlmesh diff caught it in CI"

Code.


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

Minute 1 — name the workflow up front
  "I use Cursor as my primary editor with a versioned `.cursorrules`
   file at the repo root, GitHub Copilot for inline ghost text when
   Cursor's small model lags, and Copilot Chat + Codex CLI for
   multi-file refactors and audits. Comment-first prompting is the
   single most important discipline."

Minute 2 — repo context and `.cursorrules`
  "The `.cursorrules` file pins the SQL dialect (Snowflake), the
   dbt naming conventions (stg_/int_/dim_/fct_), the model-layer
   hierarchy, and the guardrail: `dbt parse` must pass before any
   suggestion is accepted. Every completion sees this preamble.
   This alone cuts hallucination rate by ~4×."

Minute 3 — comment-first prompting
  "Before I write SQL, I write a comment describing what the SQL
   returns: `-- returns customers with 3+ orders in last 90d,
   grouped by tier`. Then I let the ghost text complete. The
   comment is a contract; if the completion drifts from it, I
   discard it. Same discipline for dbt models: docstring first,
   SQL second."

Minute 4 — guardrails before merge
  "Three lanes. Compile lane: `dbt parse` + `dbt compile` in
   precommit. Lint lane: `sqlfluff` + `sqlmesh diff` to catch
   semantic drift. Review lane: a human reads every line and runs
   at least one EXPLAIN. Nothing ships without all three."

Minute 5 — incident story
  "Last quarter Copilot completed a JOIN against a column named
   `customer_key` that didn't exist — the schema had `customer_id`.
   The Jinja compiled fine, so `dbt parse` passed. `sqlmesh diff`
   caught it because the affected model's row count changed by
   0.3%, flagged as a warning. Fix: added `dbt run --select ... 
   --defer` to the precommit for changed models. Root cause: the
   context window was stale."

Step-by-step explanation.

1. Minute 1 is the framing minute. Naming the editor and the discipline immediately — "Cursor + .cursorrules + comment-first" — signals you have a system, not a habit. Weak candidates say "I use Copilot" and stop; senior candidates open with the workflow.
2. Minute 2 addresses the repo-context axis. Naming the .cursorrules file, its contents (dialect, naming, layer hierarchy), and its effect on hallucination rate ("~4×") shows you've measured what the config does.
3. Minute 3 addresses prompt hygiene. The comment-first pattern is the single most transferable discipline; naming it explicitly shows you understand that the LLM's output quality is a function of the input's clarity.
4. Minute 4 covers guardrails. Naming three specific tools (dbt parse, sqlfluff, human review) beats naming "CI" as a black box. The "nothing ships without all three" language shows the discipline is non-negotiable.
5. Minute 5 is the incident-literacy probe. Every senior data engineer has an LLM-related incident story; telling it well — root cause, detection, fix — is more valuable than pretending it never happened.

Output.

Grading criterion	Weak score	Senior score
Names editor + mode	rare	mandatory
Names .cursorrules	rare	required
Names comment-first	rare	senior signal
Names 3-lane guardrail	rare	senior signal
Tells incident story	rare	senior signal

Rule of thumb. The senior LLM-workflow answer is a 5-minute monologue that covers repo context, prompt hygiene, guardrails, and a concrete incident without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the tool" decision tree

Detailed explanation. Given a new task, 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 task and you can walk the tree out loud. Walk through the tree with three canonical tasks: adding a column to a dbt staging model, scaffolding a new Airflow DAG, and doing a repo-wide refactor of a deprecated macro.

• Q1. Does the task touch one file or many? → one = inline; many = Composer / CLI.
• Q2. Is the change local and mechanical, or does it need reasoning? → local = inline ghost text; reasoning = Chat / Composer.
• Q3. Do you need to run shell / SQL / pytest as part of the task? → yes = Codex CLI / Aider; no = editor-only.
• Q4. Is the output going to a PR immediately? → yes = full 3-lane guardrail; no (scratchpad) = lint only.

Question. Walk the decision tree for the three tasks and record the tool + mode each ends up with.

Input.

Task	Q1 (files)	Q2 (reasoning)	Q3 (shell/SQL)	Q4 (PR)
Add column to stg_orders	one	mechanical	no	yes
Scaffold new S3 → Snowflake DAG	2–3	reasoning	yes (pytest)	yes
Refactor deprecated dbt macro across 40 models	40+	reasoning	yes	yes

Code.


# Decision-tree helper (illustrative)
def pick_llm_workflow(files_touched: int,
                       needs_reasoning: bool,
                       needs_shell: bool,
                       going_to_pr: bool) -> dict:
    """Return the LLM tool + mode + guardrail for a task."""
    workflow = {}

    if files_touched == 1 and not needs_reasoning:
        workflow["tool"] = "Cursor inline / Copilot inline"
        workflow["mode"] = "comment-first ghost text"
    elif files_touched <= 5 and needs_reasoning:
        workflow["tool"] = "Cursor Composer / Copilot Chat"
        workflow["mode"] = "conversational, single-turn"
    elif files_touched > 5 or needs_shell:
        workflow["tool"] = "Codex CLI / Aider / Cursor Composer"
        workflow["mode"] = "agentic, multi-file, shell-capable"
    else:
        workflow["tool"] = "Cursor inline"
        workflow["mode"] = "ghost text"

    if going_to_pr:
        workflow["guardrail"] = ["dbt parse", "sqlfluff", "human review"]
    else:
        workflow["guardrail"] = ["sqlfluff"]

    return workflow


# Walk the three tasks
print(pick_llm_workflow(1,  False, False, True))
# → {'tool': 'Cursor inline / Copilot inline', 'mode': 'comment-first ghost text',
#    'guardrail': ['dbt parse', 'sqlfluff', 'human review']}

print(pick_llm_workflow(3,  True,  True,  True))
# → {'tool': 'Cursor Composer / Copilot Chat', 'mode': 'conversational, single-turn',
#    'guardrail': ['dbt parse', 'sqlfluff', 'human review']}

print(pick_llm_workflow(40, True,  True,  True))
# → {'tool': 'Codex CLI / Aider / Cursor Composer', 'mode': 'agentic, multi-file, shell-capable',
#    'guardrail': ['dbt parse', 'sqlfluff', 'human review']}

Step-by-step explanation.

1. Task 1 — add a column to a staging model. Q1 = one, Q2 = mechanical → inline ghost text is enough. The three-lane guardrail runs on the PR because Q4 = yes.
2. Task 2 — scaffold a new S3 → Snowflake DAG with a pytest fixture. Q1 = 2–3, Q2 = reasoning (which operator? which sensor? what retry policy?), Q3 = yes (pytest run needed) → Cursor Composer or Copilot Chat, with the human running pytest locally before push.
3. Task 3 — refactor a deprecated dbt macro across 40 models. Q1 = 40+, Q3 = yes (need dbt parse + dbt run --select) → Codex CLI / Aider / Composer. The agentic mode reads and edits all 40 files; the human reviews the diff and runs the compile step.
4. Q4 (PR vs scratchpad) determines the guardrail intensity. PR-bound work gets the full 3-lane; scratchpad exploration gets sqlfluff only. Never skip the compile step for PR-bound work — this is the most common source of "the LLM's SQL passed CI but broke prod."
5. The tree scales: any additional constraint (secrets, cross-repo edits, data-quality tests) adds another guardrail lane, never subtracts one. The rule is that the guardrail depth is a function of the risk, not the tool.

Output.

Task	Tool	Mode	Guardrail
Add column to stg_orders	Cursor inline	comment-first	3-lane
Scaffold new DAG	Cursor Composer / Chat	conversational	3-lane
Refactor macro × 40 models	Codex CLI / Aider	agentic	3-lane

Rule of thumb. The four-question decision tree fits on a sticky note. Practice walking it end-to-end so any interviewer can hand you a task and get a tool + mode + guardrail combo in under 60 seconds.

Senior interview question on LLM-editor selection

A senior interviewer often opens with: "You inherit an analytics-engineering team of six that hand-types every dbt model and Airflow DAG. Adoption of Cursor + Copilot has been informal — some engineers use it constantly, some not at all, and PR quality is uneven. Walk me through the 30-day plan to standardise the LLM workflow, the .cursorrules file you'd ship on day one, the guardrails you'd add to CI, and the metrics you'd track to prove the workflow works."

Solution Using a versioned .cursorrules, a three-lane guardrail, and PR-throughput / defect-rate metrics

# .cursorrules — day one; versioned in the repo root
You are pair-programming with a senior analytics engineer on the
`analytics-eng/` dbt + Airflow monorepo. Snowflake target,
Airflow 2.9 TaskFlow API, dbt 1.8.

Repo conventions:
  - Model layers: staging (stg_) → intermediate (int_) → marts (dim_/fct_/mart_)
  - Every model has schema.yml with description + tests
  - Prefer CTEs; one CTE per logical step; ORDER BY only at the end
  - SQL dialect: Snowflake (QUALIFY / ILIKE / DATE_TRUNC / :: casting)
  - dbt macros live in macros/; import from dbt_utils and dbt_expectations
  - DAGs use @dag / @task decorators; sensors from airflow.sensors.*

Never do:
  - Never invent a column name — ask for schema.yml if unclear
  - Never use `airflow.operators.python_operator` (deprecated)
  - Never write to `prod` targets in generated code
  - Never suggest a completion that fails `dbt parse`

When you don't know, say so and ask.

# .pre-commit-config.yaml — three-lane guardrail
repos:
  # Lane 1 — compile
  - repo: local
    hooks:
      - id: dbt-parse
        name: dbt parse
        entry: dbt parse --profiles-dir profiles
        language: system
        pass_filenames: false
      - id: dbt-compile-changed
        name: dbt compile (changed)
        entry: bash -c 'dbt compile --select state:modified --defer --state ./manifest'
        language: system
        pass_filenames: false

  # Lane 2 — lint
  - repo: https://github.com/sqlfluff/sqlfluff
    rev: 3.2.0
    hooks:
      - id: sqlfluff-lint
        args: [--dialect, snowflake]
      - id: sqlfluff-fix
        args: [--dialect, snowflake, --force]

  # Lane 3 — model-level tests (CI, not precommit — too slow)
  # runs in GitHub Actions: dbt build --select state:modified+

# metrics.py — track the workflow impact over 30 days
import pandas as pd

METRICS = pd.DataFrame({
    "metric": [
        "PRs merged per engineer per week",
        "mean PR lifetime (open → merged)",
        "dbt models added per week",
        "post-merge defects per 100 PRs",
        "sqlfluff violations per PR",
        "`.cursorrules` violations per PR",
    ],
    "baseline_wk0": [4.2, "2.1 d", 1.5, 8, 12, "N/A"],
    "target_wk4":   [8.0, "0.9 d", 3.5, 6, 4,  "< 1"],
})

# Alert if defect rate rises above baseline for two consecutive weeks
# (LLM-driven regressions typically show up here first)

Step-by-step trace.

Step	Before (informal adoption)	After (standardised workflow)
.cursorrules	absent	versioned; ~40 lines at repo root
Comment-first prompting	ad-hoc	required by convention
Precommit compile	none	dbt parse + dbt compile --select state:modified
Precommit lint	inconsistent	sqlfluff with snowflake dialect
PR review discipline	uneven	template requires "ran EXPLAIN?" checkbox
Metrics	none	PR throughput, mean lifetime, defect rate tracked weekly
Onboarding	tribal knowledge	30-min recorded pair session per engineer

After the 30-day rollout, PR throughput doubles (~4/wk → ~8/wk per engineer), mean PR lifetime drops from ~2 days to under a day, and the defect rate holds steady or falls (the guardrails catch what the increased volume would otherwise ship). The two engineers who were skeptical of the LLM workflow become the loudest advocates once the .cursorrules file removes the "it hallucinated my column name" objection.

Output:

Metric	Baseline (week 0)	After 30 days (target)
PRs merged / engineer / week	4.2	8.0
Mean PR lifetime	2.1 d	0.9 d
dbt models added / week	1.5	3.5
Post-merge defects per 100 PRs	8	6
sqlfluff violations per PR	12	4
.cursorrules violations per PR	N/A	< 1

Why this works — concept by concept:

• .cursorrules as source of truth — versioning the LLM's repo prompt in the repo itself makes it a first-class deliverable, reviewable in PRs, evolvable with the codebase. This is the single most impactful config change.
• Comment-first prompting — the natural-language contract you write before the SQL is what pins ghost text to intent. Skipping it forces the LLM to guess from surrounding context, which is where most hallucinations enter.
• Three-lane precommit — dbt parse catches unresolved refs; dbt compile catches invalid Jinja; sqlfluff catches style drift. Running all three locally before push turns "the LLM shipped bad SQL to CI" into "the LLM's suggestion never made it out of the editor."
• Deferred compile — dbt compile --defer --state ./manifest compiles only the models whose upstream state has actually changed, keeping the precommit fast even in a 300-model repo.
• Cost — one .cursorrules file (~40 lines), one .pre-commit-config.yaml (~30 lines), a 30-min onboarding session per engineer, and weekly metric tracking. The payback is 2× PR throughput and stable defect rate. Compared to hand-typing everything, this is O(1) config for O(N) engineer-hours saved per week.

SQL
Topic — sql
SQL problems for LLM-assisted authoring practice

Practice →


SQL-Generation Topic — sql-generation SQL-generation drills for prompt hygiene

Practice →


---

2. Cursor rules, .cursorrules, and repo-scoped context for SQL / dbt

The .cursorrules file is the single artifact that decides whether Cursor writes idiomatic repo-SQL or generic textbook SQL

The mental model in one line: cursor rules for sql are a versioned, repo-scoped preamble that every Cursor completion (inline and Composer) sees before it drafts a single character — a well-crafted .cursorrules pins the SQL dialect, the dbt naming convention, the model-layer hierarchy, the do-nots, and the guardrail expectations, and moves Cursor from "generic SQL assistant" to "member of the analytics-engineering team who remembers the conventions from month one." The single most measured lever in a Cursor deployment is the quality of this file; teams that neglect it complain about hallucination rates, teams that maintain it treat Cursor as a productivity multiplier.



The four axes of a good .cursorrules file.

• Voice + role. Open with "You are pair-programming with a senior analytics engineer on the <repo> monorepo." This sets the LLM's register — it stops writing tutorial-grade SQL and starts writing the kind of terse, CTE-heavy SQL a senior would ship. Skip this line and every completion reads like a Stack Overflow answer.
• Conventions. Explicit rules for naming (stg_, int_, dim_, fct_), model layers (staging → intermediate → marts), style (CTE per step, ORDER BY at the end, QUALIFY over subqueries for window filters), and SQL dialect (Snowflake / BigQuery / Postgres). Every convention you omit is one the LLM will guess.
• Do-nots. Explicit anti-patterns: "never use airflow.operators.python_operator (deprecated)," "never invent a column name — ask for schema.yml," "never write to prod targets." Do-nots are more effective than do's for constraining generation.
• Guardrail expectations. "Every SQL suggestion must compile with dbt parse" — telling the LLM what the reviewer will check makes it self-check before proposing. This is prompt-engineering leverage.

The context window — what Cursor actually sees at completion time.

• .cursorrules. Always injected. Bounded (~1–2k tokens is comfortable; over ~5k crowds out useful file context).
• Currently open file. Full text (up to a per-model window budget).
• Recently opened files. Truncated; more recent = more weight.
• @Codebase symbol. In Cursor Chat / Composer, @Codebase performs a vector search over the repo and injects the top-N matched files. Cost: a few seconds of retrieval latency; benefit: the LLM now sees the actual schema.yml for the model you're editing.
• @Docs symbol. Injects external docs the user has linked (dbt docs, Airflow docs, Snowflake syntax). Use for API-drift-prone tasks (Airflow operator names, dbt macro signatures).

Failure modes senior engineers pre-empt.

• Stale rules. The team renames dim_customer → dim_customers; the .cursorrules still references the old name; every completion drifts. Fix: PR-review the .cursorrules file whenever a convention changes; treat it as part of the schema.
• Over-prompting. A 500-line .cursorrules file eats the context window and leaves no room for the actual file the user is editing. Fix: keep it under ~1500 tokens; put verbose examples in docs/ and let @Docs pull them.
• Invalidated context. The user opens 15 files, the context window fills with the earliest, and the current-file completion has stale scope. Fix: close unused tabs; use Cursor: Restart Cursor to reset context if suggestions drift.
• Silent drift. The LLM starts suggesting completions that violate .cursorrules and no one notices because the completions still compile. Fix: add a per-PR sqlfluff rule for repo-idiom (sqlfluff doesn't do this natively; roll a custom dbt-checkpoint or pre-commit-dbt rule that greps for prohibited patterns).

Common interview probes on .cursorrules.

• "What's the first thing you'd put in a .cursorrules file?" — the voice + role line ("You are pair-programming with a senior…").
• "How long should a .cursorrules file be?" — 1–2 kilobytes; under ~1500 tokens; anything longer crowds out context.
• "How do you keep the LLM from inventing column names?" — comment-first prompt + @Codebase or @Docs injection of schema.yml + the do-not rule.
• "How often do you update .cursorrules?" — every time a repo convention changes; reviewed in PRs like any other config.

Worked example — a production-grade .cursorrules for a dbt + Snowflake repo

Detailed explanation. Build a .cursorrules file for a real analytics-engineering repo: dbt 1.8, Snowflake, ~300 models across staging/intermediate/marts, dbt_utils + dbt_expectations installed, a macros/ folder with 20 custom macros, Airflow 2.9 for orchestration. Every clause has a reason; walk through them one by one.

• Model shape. stg → int → mart layers; every model has a schema.yml entry.
• SQL style. CTE-first, QUALIFY over subqueries, snake_case, ORDER BY only when needed.
• dbt idioms. ref() over hardcoded schema, source() for raw tables, dbt_utils.pivot over hand-rolled CASE.
• Airflow idioms. TaskFlow API, @dag/@task decorators, sensors from airflow.sensors.*, retries on network operators.

Question. Write a production-grade .cursorrules for this repo, staying under 1500 tokens, and explain each section's purpose.

Input.

Section	Purpose	Token budget
Role + voice	set the model's register	~50
SQL dialect + repo shape	ground the completions	~200
Naming conventions	pin identifier shape	~200
Style rules	pin structural patterns	~200
dbt + Airflow idioms	pin idiomatic API usage	~300
Do-nots	explicit anti-patterns	~200
Guardrail expectations	teach the LLM to self-check	~150

Code.


# .cursorrules
You are pair-programming with a senior analytics engineer on the
`analytics-eng/` monorepo. This repo runs on Snowflake, dbt 1.8,
and Airflow 2.9. You write terse, review-ready SQL and Python
that respects the following conventions.

## Repo shape
- `dbt_project/` — dbt project root
  - `models/staging/` — one folder per source system; models named `stg_<source>__<table>.sql`
  - `models/intermediate/` — models named `int_<domain>__<verb>.sql`
  - `models/marts/` — models named `dim_<entity>.sql` (dimensions) or `fct_<event>.sql` (facts)
  - `macros/` — custom macros, one per file; snake_case
  - `snapshots/` — dbt snapshots for slowly-changing dimensions
- `dags/` — Airflow DAGs, one file per DAG; DAG id matches filename
- `tests/` — pytest for DAGs

## SQL style
- SQL dialect: Snowflake. Use QUALIFY, ILIKE, DATE_TRUNC, :: casting, TRY_CAST.
- Prefer CTEs over subqueries; one CTE per logical step.
- Name CTEs after their purpose (`customers_with_orders`, `latest_status_per_order`).
- ORDER BY only in the final SELECT and only when required by downstream.
- Never SELECT * in a mart; always list columns explicitly.
- Use `{{ ref('...') }}` for dbt models and `{{ source('...') }}` for raw tables.
- For pivots, prefer `dbt_utils.pivot`; for testing, prefer `dbt_expectations`.

## dbt idioms
- Every model has a `schema.yml` entry with `description`, `columns:`, and at least one `unique` or `not_null` test on the primary key.
- Use `{{ config(materialized='incremental', unique_key='id', on_schema_change='sync_all_columns') }}` for incremental models.
- Never hardcode a schema name in SQL; always route through `{{ ref() }}` / `{{ source() }}`.
- Macros go in `macros/`; document with a `{% docs %}` block.

## Airflow idioms
- Use TaskFlow API: `@dag`, `@task`, `@task_group`.
- Sensors: `S3KeySensor`, `SnowflakeSensor`, `TimeDeltaSensor` — never poll in a `PythonOperator`.
- Every operator that touches the network gets `retries=3, retry_delay=timedelta(minutes=5)`.
- Never import from `airflow.operators.python_operator` (deprecated in 2.8; removed in 3.0).
- Every DAG has a docstring at the top: purpose, schedule, owner.

## Do-nots
- Never invent a column name. If the ref() target is not in context, ask for the schema.yml or pause and request the schema.
- Never write to a `prod_` target in generated code.
- Never suggest a completion that would fail `dbt parse`.
- Never use `now()` in Snowflake — use `current_timestamp()`.
- Never omit `ORDER BY` inside a window function that needs deterministic ordering.

## Guardrail expectations
Every completion you propose will be checked by:
  1. `dbt parse` (unresolved refs fail here)
  2. `dbt compile --select state:modified` (invalid Jinja fails here)
  3. `sqlfluff lint --dialect snowflake` (style fails here)
  4. Human PR read (semantic drift fails here)
If you're not confident a suggestion passes all four, ask the user for clarification instead of guessing.

Step-by-step explanation.

1. The role line ("pair-programming with a senior analytics engineer") sets the register. Without it, Cursor writes tutorial-grade SQL with unnecessary comments and beginner idioms. With it, the completions are terse and CTE-heavy.
2. The repo shape section tells Cursor where files live. This is what stops it from generating a new dbt model in dbt_project/dbt_project/models/ (a common mistake when the LLM guesses the folder structure).
3. The SQL style section pins structural choices: CTE-first, QUALIFY over subqueries, no SELECT * in marts. Every rule here is one the LLM would otherwise guess randomly.
4. The dbt idioms section is the highest-leverage: it teaches the LLM the exact dbt patterns your team uses (incremental config, schema.yml convention, dbt_utils.pivot). Skipping this section gets you generic dbt that doesn't fit.
5. The do-nots section is more effective than the do's because "never invent a column name" is a specific defense against the single most-cited hallucination failure mode. Naming failure modes by name in the rules file trains the LLM to avoid them.
6. The guardrail expectations section closes the loop: the LLM knows what tools will check its output and can self-check against those tools. This is what turns "propose anything" into "propose only what will pass CI."

Output.

Section	Before rules (hallucination rate)	After rules
Column name invention	~1/10 completions	~1/50 completions
Wrong SQL dialect	~1/8 completions	~1/100 completions
Non-idiomatic dbt	~1/4 completions	~1/25 completions
Deprecated Airflow API	~1/6 completions	~1/50 completions
Missing schema.yml entry	~1/3 new models	~1/15 new models

Rule of thumb. Write the .cursorrules file in the same voice you'd use in a good CONTRIBUTING.md. Sections in order: role, repo shape, SQL style, dbt idioms, Airflow idioms, do-nots, guardrails. Keep it under ~1500 tokens. Review it in PRs. Rewrite it when a convention changes.

Worked example — using @Codebase and @Docs for context-window control

Detailed explanation. Even a perfect .cursorrules file cannot make the LLM know what columns dim_customers has. For that, Cursor exposes two symbols: @Codebase (vector-search the repo and inject matching files) and @Docs (inject external docs the user has linked). Using them well is what turns Cursor Chat / Composer from "generic assistant" into "assistant with your schema in memory." Walk through a canonical workflow.

• Task. Write an incremental dbt model int_orders__enriched.sql that joins stg_orders with dim_customers on customer_id.
• Problem. Cursor Composer doesn't know the columns on either table.
• Solution. @Codebase stg_orders.sql schema.yml + @Codebase dim_customers.sql schema.yml injects both models' definitions and their schema.yml entries.

Question. Draft the Cursor Composer prompt for this task, using @Codebase to pull in the two upstream models and their schemas.

Input.

Symbol	Injects	Cost
@Codebase <term>	vector-searched files matching term	2–5 s retrieval
@Codebase <exact-file>	that file's full contents	0.5 s
@Docs <linked-doc>	user-added docs (dbt, Airflow, Snowflake)	1–3 s
@Files	manually-selected file list	~0 s
@Web	live web search (Cursor Chat only)	3–10 s

Code.


# Cursor Composer prompt
Author `models/intermediate/int_orders__enriched.sql`.

Context:
  @Codebase stg_orders schema.yml
  @Codebase dim_customers schema.yml
  @Docs dbt-incremental-materialization

Requirements:
  - Incremental materialization on Snowflake, unique_key='id'
  - Join stg_orders (o) with dim_customers (c) on o.customer_id = c.customer_id
  - Add computed columns: customer_lifetime_value (SUM of o.total_cents per customer),
    order_recency_days (DATE_DIFF from o.order_date to current_date)
  - Filter to orders in the last 730 days
  - Add schema.yml entry with tests: unique on id, not_null on id, relationships
    to dim_customers on customer_id
  - Follow the repo's stg_/int_/mart_ naming; QUALIFY over subqueries where possible

Step-by-step explanation.

1. The prompt opens with the task — one sentence describing what file to author and what it does. Cursor Composer works best when the task is scoped concretely.
2. The Context: block uses three symbol injections. @Codebase stg_orders schema.yml performs a vector search and pulls in stg_orders.sql and schema.yml; the LLM now sees the actual columns. @Codebase dim_customers schema.yml does the same for the dimension. @Docs dbt-incremental-materialization pulls in the dbt docs page for incremental materialization.
3. The requirements section pins the SQL shape: incremental config, join keys, computed columns, filters, tests. Each requirement is a specific instruction; skip any of them and the LLM guesses.
4. The final line ("follow the repo's naming") is a hint that the .cursorrules file will also inject. Redundant reminders in the prompt help when the context window is deep.
5. The output is a first-draft .sql file plus a proposed schema.yml entry, both of which the engineer reviews, runs dbt parse on, and either commits or iterates.

Output.

Prompt element	Without @Codebase	With @Codebase
Column-name accuracy	~60% (guessed from stem names)	~99% (read from schema.yml)
Join-key accuracy	~70%	~99%
dbt config correctness	~80% (docs in preamble)	~99% (docs re-injected)
First-draft compilable rate	~40%	~90%
Reviewer iterations	3–4	1

Rule of thumb. For any Cursor Composer task that touches upstream models, always inject @Codebase <model> schema.yml for each upstream. For API-drift-prone tasks (Airflow operators, dbt macro signatures), always inject @Docs. The two-second retrieval cost pays back in first-draft accuracy.

Worked example — context-window management for a 300-model monorepo

Detailed explanation. In a 300-model dbt monorepo, the context window fills quickly. If the user has 15 files open in tabs, Cursor prioritises the current file plus recent tabs; older tabs get truncated or dropped. This means the LLM's "memory" of the codebase is much smaller than the codebase itself. Walk through the discipline that keeps the context window tight.

• Symptom. Cursor suggests a completion that references dim_customer (old name) instead of dim_customers (new name), even though the .cursorrules mentions the new name.
• Cause. An old tab with dim_customer references is still open; it's competing with the .cursorrules for context.
• Fix. Close unused tabs; use Cursor: Reindex Codebase after schema changes; use @Files to explicitly scope context.

Question. Design a context-window hygiene routine for a 300-model repo and quantify the impact on suggestion quality.

Input.

Practice	Cost	Benefit
Close unused tabs	~10 s	keep current-file context weighted
Reindex after schema change	~30 s	fresh codebase vector index
Use @Files for scoped context	~0 s	explicit control, no drift
Reset context weekly	~5 s	flush stale conversation history
Update .cursorrules on rename	~1 min per rename	prevent old name leakage

Code.


# context-window-hygiene.sh — a weekly reset routine for Cursor

# 1. Close all files in Cursor (Cmd-K Cmd-W)
# 2. Reindex codebase (Cmd-Shift-P → "Cursor: Reindex Codebase")
# 3. Verify .cursorrules is current
cat .cursorrules | wc -c    # target < 6000 chars (~ 1500 tokens)

# 4. Verify docs/ folder is current (Cursor @Docs pulls from here)
ls -la docs/ | head -20

# 5. Grep for any stale model references in .cursorrules or docs/
grep -rn "dim_customer\b" .cursorrules docs/ || echo "clean"

# 6. Optional: purge the Cursor cache for a full reset
#    (macOS)  rm -rf ~/Library/Application\ Support/Cursor/User/globalStorage
#    (linux)  rm -rf ~/.config/Cursor/User/globalStorage

# .cursor/settings.json — repo-scoped Cursor settings
{
  "cursor.chat.context.mode": "auto",
  "cursor.chat.context.autoInclude": [
    ".cursorrules",
    "dbt_project.yml",
    "profiles.yml"
  ],
  "cursor.chat.context.maxTokens": 8000,
  "cursor.codebase.indexingEnabled": true,
  "cursor.codebase.embedModel": "large"
}

Step-by-step explanation.

1. The core discipline is that the context window is a scarce resource. Every open tab, every conversation-history turn, every retrieved file eats into the budget. The default Cursor context is ~8k–16k tokens for chat, less for inline. Above the ceiling, the earliest content gets truncated silently.
2. Closing unused tabs is the cheapest and highest-leverage practice. The LLM weights recent tabs; if a stale tab is open, it can leak old names into completions. Cmd-K Cmd-W (close all) is a fine end-of-session habit.
3. Reindexing after schema changes rebuilds Cursor's codebase vector index. Without it, @Codebase returns stale search results — you'll get the old model names for a day or two after a rename.
4. The .cursor/settings.json file (project-scoped) lets you pin what's always in context. autoInclude: [".cursorrules", "dbt_project.yml", "profiles.yml"] guarantees these three files are always visible to Cursor, regardless of what's open.
5. The grep -rn "old_name\b" .cursorrules docs/ check catches the "renamed a model but forgot to update the rules" case. Adding this to a weekly hygiene routine turns silent context drift into a caught defect.

Output.

Symptom	Without hygiene	With hygiene
Stale-name completions per week	5–10	0–1
Cursor Composer first-draft accuracy	~70%	~90%
Time spent debugging "why did it suggest that?"	~2 h/week	~0 h/week
Context-window overflow errors	occasional	rare
Weekly hygiene cost	0 min (skipped)	~5 min

Rule of thumb. Treat the Cursor context window as a scarce resource. Close unused tabs, reindex after schema changes, pin .cursorrules + dbt_project.yml + profiles.yml via autoInclude, and grep for stale names weekly. Five minutes of hygiene prevents ten hours of "why did the LLM suggest that?" debugging.

Senior interview question on Cursor rules

A senior interviewer might ask: "Your analytics team just adopted Cursor. Some engineers love it, some complain it hallucinates constantly. You've been asked to standardise the setup. Walk me through the .cursorrules file you'd ship, the repo-context-injection patterns you'd teach the team, and how you'd measure the impact over 30 days."

Solution Using a versioned .cursorrules, @Codebase/@Docs injection patterns, and a hallucination-rate metric

# .cursorrules — production draft, ~1300 tokens
You are pair-programming with a senior analytics engineer on the
`analytics-eng/` monorepo. Snowflake 8.x, dbt 1.8, Airflow 2.9.
Terse, review-ready SQL and Python; respect these conventions.

## Repo shape
- dbt_project/ — models/{staging,intermediate,marts}, macros/, snapshots/, seeds/
- dags/ — one Airflow DAG per file; DAG id matches filename
- tests/ — pytest for DAGs
- profiles.yml — snowflake_prod / snowflake_dev

## Naming
- Staging: stg_<source>__<table>.sql
- Intermediate: int_<domain>__<verb>.sql
- Marts: dim_<entity>.sql | fct_<event>.sql | mart_<aggregate>.sql
- Every model has a schema.yml entry with `description`, `columns:`, and PK tests.

## SQL style
- Snowflake dialect (QUALIFY, ILIKE, DATE_TRUNC, ::casting, TRY_CAST).
- CTE-first; one CTE per logical step; named after purpose.
- ORDER BY only in the final SELECT and only when needed downstream.
- Never SELECT * in a mart.
- Prefer `{{ ref() }}` and `{{ source() }}` over hardcoded schema.
- Prefer `dbt_utils.pivot` over hand-rolled CASE for pivots.

## dbt idioms
- Incremental: `{{ config(materialized='incremental', unique_key='id', on_schema_change='sync_all_columns') }}`
- Snapshots: strategy='timestamp' with `updated_at` column.
- Macros: one per file, snake_case, documented with `{% docs %}` block.

## Airflow idioms
- TaskFlow API: `@dag`, `@task`, `@task_group`.
- Sensors: `S3KeySensor`, `SnowflakeSensor` — never poll in `PythonOperator`.
- Network operators: `retries=3, retry_delay=timedelta(minutes=5)`.
- Never `airflow.operators.python_operator` (deprecated).
- Every DAG has a top-of-file docstring: purpose, schedule, owner.

## Do-nots
- Never invent a column name. Ask for the schema.yml if unclear.
- Never write to `prod_` targets in generated code.
- Never suggest a completion that would fail `dbt parse`.
- Never use `now()` on Snowflake — use `current_timestamp()`.
- Never omit `ORDER BY` inside a window function that needs deterministic ordering.

## Guardrails you must respect
Every completion is checked by:
  1. `dbt parse` (unresolved refs fail)
  2. `dbt compile --select state:modified` (invalid Jinja fails)
  3. `sqlfluff lint --dialect snowflake` (style fails)
  4. Human PR read (semantic drift fails)

If a suggestion might fail any of these, ask for clarification instead.

# hallucination_rate.py — weekly metric
# Run: python hallucination_rate.py --pr-window 7d

import subprocess, re, sys
from collections import defaultdict

# 1. List PRs merged in the last 7 days
prs = subprocess.check_output(
    ["gh", "pr", "list", "--state=merged",
     "--search", "merged:>=2026-07-23", "--json", "number,files"]
).decode()

hallucination_flags = defaultdict(int)
total_prs = 0

# 2. For each PR, check the diff against `.cursorrules` do-nots
DO_NOTS = [
    r"\bfrom airflow\.operators\.python_operator\b",
    r"\bnow\s*\(\s*\)",                    # Snowflake: use current_timestamp()
    r"SELECT\s+\*.*FROM\s+\{\{\s*ref\(",   # SELECT * in a mart
]

import json
for pr in json.loads(prs):
    total_prs += 1
    diff = subprocess.check_output(
        ["gh", "pr", "diff", str(pr["number"])]
    ).decode()
    for pat in DO_NOTS:
        if re.search(pat, diff, re.I):
            hallucination_flags[pat] += 1

# 3. Report
print(f"PRs scanned: {total_prs}")
for pat, cnt in hallucination_flags.items():
    print(f"  {cnt:3d} PRs violated: {pat}")
print(f"Overall hallucination rate: {sum(hallucination_flags.values()) / total_prs:.1%}")

Step-by-step trace.

Step	Value	Reasoning
.cursorrules size	~1300 tokens	under the 1500-token ceiling; leaves room for file context
Sections	role → shape → naming → style → dbt → Airflow → do-nots → guardrails	mirrors what a CONTRIBUTING.md would say
@Codebase usage	mandatory for Composer tasks touching upstream models	injects schema.yml so LLM sees real columns
@Docs usage	mandatory for Airflow / dbt API tasks	prevents deprecated-API suggestions
Hygiene routine	weekly reindex + tab cleanup	keeps context fresh
Metric	hallucination rate per merged PR	weekly grep against do-not patterns

After the 30-day rollout, hallucination-flag rate (do-not-pattern violations in merged PRs) drops from ~15% baseline to ~2%. The two engineers who complained about Cursor "constantly making things up" become the loudest advocates once the .cursorrules file removes the "it invented a column name" objection. PR throughput doubles; defect rate holds steady.

Output:

Metric	Baseline (week 0)	After 30 days
PRs / engineer / week	4.2	8.0
Do-not violations / merged PR	15%	2%
dbt parse failures / PR	8%	1%
Deprecated Airflow API sightings	5/wk	0/wk
Engineer NPS on Cursor	4/10	8/10

Why this works — concept by concept:

• Versioned .cursorrules — putting the LLM's repo prompt in the repo makes it a first-class deliverable that evolves with the codebase and is reviewed in PRs. The alternative — every engineer keeping their own personal preamble — reintroduces the variance the rules file exists to eliminate.
• Comment-first prompting — the natural-language contract you write before the SQL is what pins ghost text to intent. Without it, the LLM guesses from surrounding code, which is where most hallucinations enter.
• @Codebase schema injection — vector-search injection of schema.yml for the upstream models the current model references is what stops column-name hallucination in Composer tasks. Two-second retrieval cost; near-eliminates the failure mode.
• Do-not clauses — telling the LLM what not to do is more effective than telling it what to do. "Never use airflow.operators.python_operator" is more effective than "prefer TaskFlow API" because the negation is unambiguous.
• Cost — one .cursorrules file (~1300 tokens), a weekly 5-minute hygiene routine, and a weekly hallucination-rate metric script. The payback is 2× PR throughput and a ~7× drop in do-not violations. Compared to letting each engineer figure out their own prompt discipline, this is O(1) config for O(N × week) engineer-hours saved.

SQL
Topic — sql-generation
SQL-generation problems for prompt / context practice

Practice →


Design Topic — design Design problems on repo-scoped tooling

Practice →


---

3. GitHub Copilot patterns — inline SQL, dbt macros, Jinja completions

Comment-first prompting is the single lever that turns Copilot's ghost text from lucky to reliable

The mental model in one line: github copilot sql is at its best when you write a comment describing what the SQL should return, let Copilot draft the ghost text, review it against the comment, and either accept or iterate — the comment is a contract between you and the model, and treating it as a contract (not a hint) is what separates a productive Copilot workflow from a lottery of hopeful autocompletes. Every senior data engineer who ships Copilot-authored SQL day-in day-out uses the same three-line pattern: write the intent comment, wait for the ghost text, verify against the comment before accepting.



The four modes of GitHub Copilot, and when to use each.

• Inline ghost text. Sub-300-ms completions at the caret. Best for line-by-line SQL, small edits, filling in SELECT column lists, completing dbt Jinja blocks. Comment-first prompt is the discipline.
• Copilot Chat. Conversational; @workspace, @github, @vscode symbols. Best for "explain this query," "generate a test for this dbt model," "why does this DAG fail on the sensor step?"
• Copilot Workspace. Multi-file suggestions bundled as PRs. Best for "add pagination to all three list endpoints" or "migrate this dbt project from schema.yml v1 to v2." Longer latency; multi-file scope.
• Copilot CLI. gh copilot suggest / gh copilot explain in the terminal. Best for shell one-liners, git recipes, "how do I run dbt with defer?" from the command line.

The comment-first pattern — the load-bearing Copilot discipline.

• What it is. Before writing SQL, write a comment describing what the SQL should return: -- returns customers with 3+ orders in the last 90 days, grouped by tier. Then hit Enter and let the ghost text fill.
• Why it works. Copilot's model heavily weights nearby comments as prompt intent. A specific comment produces specific SQL; a vague comment produces vague SQL.
• How to write good comments. Name the output shape (columns + grain), the filter conditions, the aggregation, and the ordering. "returns customers with 3+ orders in the last 90 days, grouped by tier, ordered by total_spend desc" is a five-clause contract.
• How to iterate. If the ghost text drifts, edit the comment to be more specific — never start typing SQL and expect the LLM to catch up. The comment leads; the SQL follows.

Failure modes senior engineers pre-empt.

• Hallucinated columns. Copilot invents customer_key when the schema has customer_id. Fix: pull the source table into an open tab; add a -- source: dim_customers has (customer_id, tier, signup_date) comment above the completion; or use Cursor's @Codebase if you're in Cursor.
• Wrong SQL dialect. Copilot writes Postgres ::TEXT in a Snowflake file; writes CURRENT_TIMESTAMP in a MySQL file. Fix: add a -- Snowflake header comment at the top of the file; put the dialect in .cursorrules (Cursor) or the workspace instructions (Copilot Chat).
• Non-idiomatic dbt. Copilot suggests hardcoded schema references instead of {{ ref() }}. Fix: keep an example ref()-based query at the top of the file as an in-context template.
• Deprecated API. Copilot suggests airflow.operators.python_operator (deprecated). Fix: pin the Airflow version in a header comment; use Cursor's .cursorrules do-not clause.

Common interview probes on Copilot.

• "How do you keep Copilot from hallucinating column names?" — comment-first prompt + open the source table's schema.yml in a sibling tab.
• "How do you make Copilot suggest the right SQL dialect?" — file-header dialect comment + .cursorrules in Cursor.
• "How do you review Copilot-generated dbt models?" — dbt parse precommit + human read against the schema.yml.
• "When would you prefer Cursor Composer over Copilot inline?" — multi-file edits, repo-context-heavy tasks (e.g. renaming a model across 40 references).

Worked example — authoring a window-function query with Copilot inline

Detailed explanation. The canonical Copilot inline task: rank customers by lifetime spend, keep the top-N per tier, return their most-recent order. The senior data engineer's move is comment-first: write a five-clause comment, hit Enter, let the ghost text fill, verify, accept or iterate.

• Task. Rank customers by lifetime spend within tier; keep top 100 per tier; return their most recent order.
• Discipline. Comment describes the exact output shape; ghost text drafts the SQL; human verifies against the comment.
• Dialect. Snowflake — file header states so.

Question. Author the query using Copilot inline with a comment-first prompt, and show the ghost-text output.

Input.

Component	Value
Dialect	Snowflake
Source table	stg_orders(customer_id, order_date, total_cents, status)
Dimension	dim_customers(customer_id, tier)
Output	top 100 customers per tier by lifetime spend, with most-recent order
Ordering	tier ASC, rank ASC

Code.


-- Snowflake
-- returns top 100 customers per tier by lifetime spend,
--   with their most recent order (order_date, total_cents).
-- Grain: one row per customer.
-- Filters: status IN ('paid', 'shipped') only.

WITH customer_lifetime AS (
    SELECT o.customer_id,
           c.tier,
           SUM(o.total_cents)  AS lifetime_cents,
           MAX(o.order_date)   AS most_recent_order_date
    FROM   {{ ref('stg_orders') }} o
    JOIN   {{ ref('dim_customers') }} c USING (customer_id)
    WHERE  o.status IN ('paid', 'shipped')
    GROUP  BY o.customer_id, c.tier
),
most_recent_row AS (
    SELECT o.customer_id,
           o.order_date       AS most_recent_order_date,
           o.total_cents      AS most_recent_order_cents
    FROM   {{ ref('stg_orders') }} o
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY o.customer_id
        ORDER BY     o.order_date DESC
    ) = 1
),
ranked AS (
    SELECT cl.customer_id,
           cl.tier,
           cl.lifetime_cents,
           mr.most_recent_order_date,
           mr.most_recent_order_cents,
           ROW_NUMBER() OVER (
               PARTITION BY cl.tier
               ORDER BY     cl.lifetime_cents DESC
           ) AS tier_rank
    FROM   customer_lifetime cl
    JOIN   most_recent_row   mr USING (customer_id)
)
SELECT customer_id,
       tier,
       lifetime_cents,
       most_recent_order_date,
       most_recent_order_cents,
       tier_rank
FROM   ranked
WHERE  tier_rank <= 100
ORDER  BY tier ASC, tier_rank ASC

Step-by-step explanation.

1. The five-line comment header is the prompt contract. It names the output (top 100 customers per tier), the grain (one row per customer), and the filter (status IN paid/shipped). Copilot's ghost text draft was ~95% of this final; the engineer accepted with two small tweaks.
2. The customer_lifetime CTE aggregates spend per customer per tier. Copilot suggested this shape immediately from the "lifetime spend" phrasing in the comment.
3. The most_recent_row CTE uses QUALIFY ROW_NUMBER() = 1 — the Snowflake idiom for "keep the most recent row per key." Copilot suggested QUALIFY because the file-header comment stated the dialect. Without that comment, Copilot defaults to a subquery / LEFT JOIN pattern.
4. The ranked CTE joins the two prior CTEs and computes tier_rank. Copilot correctly used PARTITION BY tier + ORDER BY lifetime_cents DESC — a standard senior pattern.
5. The final SELECT filters tier_rank <= 100 and orders by tier then rank. The ORDER BY at the end matches the .cursorrules "ORDER BY only in final SELECT" rule.

Output.

customer_id	tier	lifetime_cents	most_recent_order_date	most_recent_order_cents	tier_rank
4021	Platinum	1,240,000	2026-07-28	42,000	1
8801	Platinum	980,000	2026-07-30	33,000	2
2312	Gold	540,000	2026-07-24	28,000	1
7793	Silver	98,000	2026-07-15	5,500	1

Rule of thumb. Write the comment first with five clauses (output columns, grain, filter, aggregation, ordering). Wait for the ghost text. Verify against the comment. Accept or iterate on the comment — never on the SQL. This is the single most transferable Copilot discipline.

Worked example — dbt macro completion with Jinja + dbt_utils

Detailed explanation. Copilot is uneven on Jinja by default because Jinja is a small fraction of most training data. Anchoring it with a well-formed opening — {% macro ... %} on the first line, dbt_utils import in a comment above — makes it produce correct Jinja + SQL macros most of the time. Walk through authoring a dbt_utils.pivot-based macro.

• Task. A macro that pivots a wide order_events table into a per-event-type column set (event_type_paid, event_type_shipped, etc.).
• Prompt technique. Docstring block + macro signature + dbt_utils.pivot import — Copilot fills the body.

Question. Author the pivot macro using Copilot inline; show the docstring + signature that anchors the completion.

Input.

Component	Value
Macro name	pivot_order_events
Input table	{{ ref('stg_order_events') }}
Pivot column	event_type
Aggregate	count of events per type
Group by	order_id

Code.


{#- pivot_order_events.sql — Snowflake dbt macro -#}
{#-
Pivots stg_order_events (one row per event) into a wide row per order,
with one count-column per event_type.

Args:
    model — the dbt ref() to pivot (usually {{ ref('stg_order_events') }})
    group_by — the column to group by (usually 'order_id')

Returns:
    A SELECT statement that produces one row per group_by value,
    with columns: <group_by>, event_type_<type_1>, event_type_<type_2>, ...
-#}

{% macro pivot_order_events(model, group_by) %}
  {%- set event_types = dbt_utils.get_column_values(
        table=model,
        column='event_type',
        default=[]
      ) -%}

  SELECT
      {{ group_by }},
      {{ dbt_utils.pivot(
           column='event_type',
           values=event_types,
           agg='count',
           then_value=1,
           else_value=0,
           prefix='event_type_'
      ) }}
  FROM   {{ model }}
  GROUP  BY {{ group_by }}

{% endmacro %}

Step-by-step explanation.

1. The opening {#- ... -#} block is a docstring in Jinja. It tells Copilot what the macro does, what arguments it takes, and what shape it returns. Without it, Copilot's suggestions drift.
2. The {% macro pivot_order_events(model, group_by) %} signature is what the engineer typed; Copilot then filled the body. The macro name is descriptive, the args are named — both give Copilot enough context.
3. The dbt_utils.get_column_values(...) line dynamically pulls the distinct event types from the input model. Copilot suggested this pattern from the dbt_utils import comment; without the import hint, it would have generated a static list.
4. The dbt_utils.pivot(...) call is the actual pivot. then_value=1, else_value=0, agg='count' computes a per-type count. prefix='event_type_' gives clean column names.
5. The final GROUP BY {{ group_by }} closes the aggregation. Copilot correctly used the macro arg — a common failure mode is to hardcode order_id here; the docstring prevented it.

Output.

order_id	event_type_paid	event_type_shipped	event_type_returned
101	1	1	0
102	1	0	0
103	1	1	1
104	1	1	0

Rule of thumb. For Jinja / dbt macros, anchor Copilot with a full docstring + macro signature + relevant import hints in comments. The docstring is the prompt; the macro body follows.

Worked example — comment-first pattern for a complex CTE chain

Detailed explanation. A gnarly CTE chain — sessionise events, compute per-session revenue, attribute the session to a marketing channel — is the kind of task that Copilot handles poorly without a good comment header and handles very well with one. Walk through the discipline.

• Task. Sessionise page_events (30-min inactivity threshold), attribute the first-touch channel per session, join to orders to compute revenue per session.
• Prompt technique. Multi-clause comment describing each CTE by name.

Question. Author the query using a per-CTE comment header, and show the resulting SQL.

Input.

Component	Value
Events	stg_page_events(user_id, event_ts, url, utm_source)
Orders	stg_orders(customer_id, order_ts, total_cents)
Session threshold	30 minutes of inactivity
Attribution	first-touch (first utm_source in the session)

Code.


-- Snowflake
-- Pipeline:
--   1. sessions = sessionise page_events with 30-min inactivity threshold
--   2. session_attribution = first-touch utm_source per session
--   3. session_revenue = orders that happened during the session, summed
--   4. output = one row per session with attribution + revenue

WITH sessioned AS (
    SELECT user_id,
           event_ts,
           url,
           utm_source,
           SUM(CASE
                 WHEN DATEDIFF(minute, LAG(event_ts) OVER (
                        PARTITION BY user_id ORDER BY event_ts
                      ), event_ts) > 30
                 THEN 1 ELSE 0
               END) OVER (PARTITION BY user_id ORDER BY event_ts) AS session_idx
    FROM   {{ ref('stg_page_events') }}
),
sessions AS (
    SELECT user_id,
           session_idx,
           MIN(event_ts) AS session_start,
           MAX(event_ts) AS session_end
    FROM   sessioned
    GROUP  BY user_id, session_idx
),
session_attribution AS (
    SELECT s.user_id,
           s.session_idx,
           s.session_start,
           s.session_end,
           FIRST_VALUE(sd.utm_source) OVER (
               PARTITION BY s.user_id, s.session_idx
               ORDER BY     sd.event_ts
           ) AS first_touch_channel
    FROM   sessions  s
    JOIN   sessioned sd
      ON   sd.user_id = s.user_id
     AND   sd.session_idx = s.session_idx
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY s.user_id, s.session_idx
        ORDER BY     sd.event_ts
    ) = 1
),
session_revenue AS (
    SELECT sa.user_id,
           sa.session_idx,
           sa.first_touch_channel,
           SUM(o.total_cents) AS session_revenue_cents
    FROM   session_attribution sa
    LEFT JOIN {{ ref('stg_orders') }} o
      ON   o.customer_id = sa.user_id
     AND   o.order_ts BETWEEN sa.session_start AND sa.session_end
    GROUP  BY 1, 2, 3
)
SELECT * FROM session_revenue
ORDER BY session_revenue_cents DESC NULLS LAST

Step-by-step explanation.

1. The header comment lists four CTEs by name and one-line purpose. Copilot uses this as a scaffold — each CTE below matches a comment line. Without the header, the LLM would produce a single monolithic subquery.
2. sessioned computes a running session index via a windowed CASE that increments whenever the gap between consecutive events exceeds 30 minutes. Copilot suggested this pattern from the "sessionise" verb in the comment.
3. sessions aggregates events into session bounds (start/end timestamps). This is the standard session-collapse pattern.
4. session_attribution uses QUALIFY ROW_NUMBER() = 1 to pick the first event per session and pulls its utm_source. Copilot correctly used QUALIFY for the top-N-per-key idiom.
5. session_revenue joins to orders on the time window and sums revenue. The LEFT JOIN keeps sessions that generated no revenue.

Output.

user_id	session_idx	first_touch_channel	session_revenue_cents
4021	3	google_ads	42,000
8801	1	organic	33,000
2312	5	facebook_ads	28,000
7793	2	direct	NULL

Rule of thumb. For complex CTE chains, write a top-of-file comment listing every CTE by name and one-line purpose before letting Copilot draft. The comment is the outline; the SQL is the fill. This produces cleaner, more auditable SQL than any single-shot prompt.

Senior interview question on Copilot patterns

A senior interviewer might ask: "You're leading an analytics team on a Snowflake + dbt stack. GitHub Copilot has been available to the team for six months. Half the engineers use it well; half have shipped subtly-wrong SQL because Copilot invented column names. Walk me through the Copilot discipline you'd standardise — the comment-first pattern, the file-header conventions, the hallucination defenses — and how you'd measure the impact on shipped-defect rate."

Solution Using comment-first prompting, file-header dialect pins, and a per-PR hallucination-flag script

-- COPILOT WORKFLOW TEMPLATE (paste at top of new .sql files)
-- Dialect: Snowflake (dbt 1.8, snowflake_prod / snowflake_dev)
-- Source: {{ ref('stg_orders') }}, {{ ref('dim_customers') }}
-- Grain: one row per customer per tier
-- Filters: status IN ('paid','shipped'); order_date >= dateadd(day, -730, current_date)
-- Output: customer_id, tier, lifetime_cents, tier_rank

-- Step 1 — comment describes what the SQL returns
-- Step 2 — wait for Copilot ghost text
-- Step 3 — verify against the comment
-- Step 4 — accept, or edit the comment (never the SQL) to iterate

# copilot_hallucination_scan.py — precommit hook
# Scans staged .sql / .py files for known hallucination patterns.
# Runs on `git commit`; blocks commit if any pattern matches.

import subprocess, re, sys

STAGED = subprocess.check_output(
    ["git", "diff", "--cached", "--name-only"]
).decode().splitlines()

PATTERNS = {
    # Wrong-dialect timestamp on Snowflake
    r"\bnow\s*\(\s*\)": "wrong dialect (use current_timestamp() on Snowflake)",
    # Deprecated Airflow operator
    r"\bfrom airflow\.operators\.python_operator\b": "deprecated Airflow API",
    # Hardcoded schema reference in a dbt model
    r"\bFROM\s+[a-z_]+\.[a-z_]+\s+": "hardcoded schema — use {{ ref() }}",
    # SELECT * in a mart
    r"SELECT\s+\*\s+FROM\s+\{\{\s*ref\(": "SELECT * in a mart — list columns",
}

violations = []
for fn in STAGED:
    if not fn.endswith((".sql", ".py")):
        continue
    try:
        text = open(fn).read()
    except FileNotFoundError:
        continue
    for pat, msg in PATTERNS.items():
        for m in re.finditer(pat, text, re.I | re.M):
            line = text[:m.start()].count("\n") + 1
            violations.append(f"  {fn}:{line}  {msg}  → {m.group(0)!r}")

if violations:
    print("Copilot hallucination scan flagged the following:")
    print("\n".join(violations))
    print("\nEdit and re-commit, or run `git commit --no-verify` if intentional.")
    sys.exit(1)

# .pre-commit-config.yaml — add the scan
repos:
  - repo: local
    hooks:
      - id: copilot-hallucination-scan
        name: copilot hallucination scan
        entry: python scripts/copilot_hallucination_scan.py
        language: system
        pass_filenames: false
        stages: [commit]

Step-by-step trace.

Step	Value	Reasoning
File header	5-clause comment (dialect, source, grain, filters, output)	pins Copilot's prompt intent
Comment-first	mandatory	write comment first, SQL after
dbt parse	precommit	catches unresolved refs
sqlfluff lint	precommit	catches style drift
Hallucination-flag scan	precommit	catches wrong-dialect + deprecated API + hardcoded schema
Human PR review	required	verifies output against the comment contract
Weekly hallucination-rate metric	tracked	catches convention drift over time

After the 30-day rollout of the comment-first template + hallucination scan, the shipped-defect rate on Copilot-authored SQL drops from ~15% to ~2%. The two engineers who "shipped subtly-wrong SQL" become the loudest advocates of the template because the precommit scan now catches what code review used to miss.

Output:

Metric	Baseline (week 0)	After 30 days
PRs / engineer / week	3.5	7.0
Copilot-authored SQL rows	40%	65%
Shipped defects / 100 PRs	15	2
Wrong-dialect flags / week	4	0
Hardcoded-schema flags / week	6	0

Why this works — concept by concept:

• Comment-first prompting — the header comment is a contract between the engineer's intent and Copilot's draft. Writing it first anchors the ghost text; iterating on it (not on the SQL) keeps the discipline pure.
• File-header dialect pin — a single-line -- Snowflake at the top of every SQL file eliminates the wrong-dialect failure mode. This is the smallest possible fix for the highest-frequency hallucination.
• Hallucination-flag precommit scan — a lightweight regex-based scanner catches the top-4 do-not patterns (wrong dialect, deprecated Airflow API, hardcoded schema, SELECT * in mart) before commit. Blocks the commit; forces a fix.
• Human review against the comment contract — the PR reviewer's first check is "does the SQL do what the comment says?" This turns review from a "read the code" chore into a "verify the contract" audit, which is faster and higher-signal.
• Cost — one file-header template (paste-at-top-of-new-files), one 30-line hallucination scan, one PR-template checkbox. The payback is a ~7× drop in shipped-defect rate and 2× PR throughput. Compared to letting Copilot roam free, this is O(1) config for O(N × week) engineer-hours saved.

SQL
Topic — joins/sql
SQL joins and window-function problems for Copilot practice

Practice →


SQL Topic — window-functions/sql Window-function drills for Copilot ghost text

Practice →


---

4. Airflow DAG authoring with LLM assistants — task groups, sensors, tests

Docstring-first DAG scaffolding is what turns Copilot / Cursor from "generic Python assistant" into "senior data engineer who knows the TaskFlow API"

The mental model in one line: airflow dag copilot authoring works best when you write a top-of-file docstring stating the DAG's purpose, schedule, owner, retry policy, and task-list — the LLM then scaffolds the @dag and @task decorators, proposes sensors and task groups, and generates the pytest fixture — turning a 60-minute DAG-drafting task into a 10-minute review-and-tweak task while keeping the reviewer in charge of every architectural choice. Every senior data engineer who ships LLM-authored DAGs uses the same pattern: docstring first, decorators second, business logic third, tests fourth.



The four axes of a good LLM-authored DAG.

• Docstring shape. Every DAG has a top-of-file docstring: purpose, schedule (@daily, cron), owner, upstream dependencies, retry policy, SLAs. The docstring is the prompt for everything below it.
• TaskFlow API. @dag, @task, @task_group decorators from airflow.decorators. The LLM must never generate airflow.operators.python_operator (deprecated in 2.8, removed in 3.0). .cursorrules do-not clause; Copilot header pin.
• Sensors. Explicit sensor operators (S3KeySensor, SnowflakeSensor, TimeDeltaSensor) — never poll in a PythonOperator. The LLM proposes; the reviewer verifies against the upstream reality.
• Tests. Every DAG has a pytest fixture that (a) imports the DAG, (b) asserts task list, (c) asserts dependency graph, (d) mocks external operators. The LLM scaffolds all four; the reviewer verifies.

The docstring-first pattern — the load-bearing DAG-authoring discipline.

• What it is. Write a 5–8-line module docstring at the top of the file describing the DAG. Then let the LLM scaffold the DAG below.
• Why it works. The docstring is the prompt intent. A specific docstring produces a specific DAG; a vague docstring produces a vague DAG.
• How to write good docstrings. Name the purpose (what data flows where), the schedule (cron / preset), the owner (team + on-call), the upstream (what triggers this DAG), the retry policy (how many, how long between).
• How to iterate. If the LLM's DAG drifts, edit the docstring — never the code below. The docstring leads; the DAG follows.

Failure modes senior engineers pre-empt.

• Deprecated operator API. LLM suggests PythonOperator instead of @task, or S3ListOperator instead of S3ListOperator from the correct provider path. Fix: .cursorrules do-not clause pinning Airflow 2.9 TaskFlow API; file-header -- Airflow 2.9 comment for Copilot.
• Missing retries on network operators. LLM writes a SnowflakeOperator with no retries= kwarg. Fix: docstring names the retry policy; .cursorrules requires retries=3 on network operators.
• Sensor polling in PythonOperator. LLM writes while not s3.head_object(...) in a task. Fix: docstring names the sensor explicitly ("wait for s3://bucket/{{ ds }}/_SUCCESS with S3KeySensor").
• No pytest fixture. LLM ships a DAG without any test. Fix: PR template requires "pytest for DAG"; .cursorrules do-not clause "never ship a DAG without a pytest fixture."

Common interview probes on LLM-authored DAGs.

• "How do you keep the LLM from generating a deprecated Airflow API?" — .cursorrules do-not clause + file-header dialect pin + @Docs airflow-2.9 injection.
• "How do you review LLM-generated DAGs?" — pytest-first, then human read against the docstring contract.
• "How do you make the LLM propose the right sensor?" — name the sensor in the docstring; put the sensor's expected key format in a comment.
• "How do you test LLM-generated DAGs?" — pytest that imports the DAG, asserts task list, asserts dependency graph, mocks external operators.

Worked example — a Snowflake → S3 DAG with a sensor and a task group

Detailed explanation. The canonical LLM-assisted DAG task: ingest a daily partition from Snowflake into S3, gated by an upstream _SUCCESS marker sensor, with a task group for the extract → transform → load steps, then run a downstream dbt run --select step. Walk through the docstring-first scaffold.

• Purpose. Daily incremental extract from Snowflake to S3, then trigger downstream dbt.
• Sensor. S3KeySensor on s3://raw/orders/{{ ds }}/_SUCCESS.
• Task group. extract_load containing three tasks: extract_orders, write_to_s3, verify_s3_row_count.
• Downstream. dbt_run task using the DbtRunOperator from the community provider.

Question. Write the module docstring; let Cursor Composer scaffold the DAG; verify against the docstring.

Input.

Component	Value
DAG id	orders_ingest_daily
Schedule	@daily (00:15 UTC)
Sensor	S3KeySensor on _SUCCESS marker
Task group	extract_load
Downstream	dbt run --select fct_orders+

Code.


"""
DAG: orders_ingest_daily

Purpose:
    Daily incremental extract of orders from Snowflake to S3,
    then trigger downstream dbt run on fct_orders and its children.

Schedule:
    @daily (00:15 UTC).

Upstream:
    S3 marker at s3://raw/orders/{{ ds }}/_SUCCESS
    (published by the upstream ingest DAG).

Owner:
    data-platform-team; on-call: pagerduty analytics-oncall.

Retry policy:
    retries=3, retry_delay=timedelta(minutes=5), retry_exponential_backoff=True.

SLA:
    Complete within 90 minutes of scheduled start; page if breached.

Tasks:
    1. wait_for_marker  — S3KeySensor on the _SUCCESS marker.
    2. extract_load     — task_group:
         a. extract_orders     — Snowflake -> DataFrame.
         b. write_to_s3        — DataFrame -> parquet at s3://curated/orders/{{ ds }}/.
         c. verify_s3_row_count — sanity check: row count matches source.
    3. dbt_run          — dbt run --select fct_orders+.
"""
from __future__ import annotations

from datetime import datetime, timedelta

from airflow.decorators import dag, task, task_group
from airflow.sensors.filesystem import FileSensor
from airflow.providers.amazon.aws.sensors.s3 import S3KeySensor
from airflow.providers.snowflake.hooks.snowflake import SnowflakeHook
from airflow.providers.amazon.aws.hooks.s3 import S3Hook

DEFAULT_ARGS = {
    "owner": "data-platform-team",
    "retries": 3,
    "retry_delay": timedelta(minutes=5),
    "retry_exponential_backoff": True,
    "email_on_failure": False,
    "sla": timedelta(minutes=90),
}


@dag(
    dag_id="orders_ingest_daily",
    default_args=DEFAULT_ARGS,
    schedule="15 0 * * *",         # 00:15 UTC daily
    start_date=datetime(2026, 7, 1),
    catchup=False,
    tags=["orders", "ingest", "snowflake", "s3"],
    doc_md=__doc__,
)
def orders_ingest_daily():

    wait_for_marker = S3KeySensor(
        task_id="wait_for_marker",
        bucket_key="orders/{{ ds }}/_SUCCESS",
        bucket_name="raw",
        aws_conn_id="aws_default",
        poke_interval=60,
        timeout=60 * 60 * 2,        # 2 hours
        mode="reschedule",          # release worker slot while poking
    )

    @task_group(group_id="extract_load")
    def extract_load(execution_date: str):

        @task
        def extract_orders(execution_date: str) -> dict:
            """Read the ingested partition from raw.orders into a Snowflake stage."""
            hook = SnowflakeHook(snowflake_conn_id="snowflake_prod")
            sql = f"""
                SELECT COUNT(*) AS n
                FROM   raw.orders
                WHERE  order_date = '{execution_date}'
            """
            row = hook.get_first(sql)
            return {"n_source": int(row[0])}

        @task
        def write_to_s3(execution_date: str, extract_meta: dict) -> dict:
            """Unload the partition to S3 as parquet."""
            hook = SnowflakeHook(snowflake_conn_id="snowflake_prod")
            hook.run(f"""
                COPY INTO @s3_curated/orders/{execution_date}/
                FROM (SELECT * FROM raw.orders WHERE order_date = '{execution_date}')
                FILE_FORMAT = (TYPE = PARQUET)
                HEADER = TRUE
            """)
            return {"n_source": extract_meta["n_source"]}

        @task
        def verify_s3_row_count(execution_date: str, write_meta: dict) -> None:
            """Assert the parquet row count matches the source row count."""
            s3 = S3Hook(aws_conn_id="aws_default")
            keys = s3.list_keys(bucket_name="curated",
                                prefix=f"orders/{execution_date}/")
            n_written = sum(_parquet_row_count(s3, "curated", k) for k in keys)
            if n_written != write_meta["n_source"]:
                raise ValueError(
                    f"row count mismatch: source={write_meta['n_source']} "
                    f"written={n_written}"
                )

        meta = extract_orders(execution_date)
        write_meta = write_to_s3(execution_date, meta)
        verify_s3_row_count(execution_date, write_meta)

    @task
    def dbt_run(execution_date: str) -> None:
        """dbt run --select fct_orders+ --vars {{'run_date': execution_date}}."""
        import subprocess
        subprocess.check_call([
            "dbt", "run",
            "--select", "fct_orders+",
            "--vars", f"{{run_date: '{execution_date}'}}",
            "--profiles-dir", "/opt/dbt/profiles",
        ])

    wait_for_marker >> extract_load(execution_date="{{ ds }}") >> dbt_run(
        execution_date="{{ ds }}"
    )


def _parquet_row_count(s3, bucket: str, key: str) -> int:
    """Read parquet footer for the row count without downloading data."""
    import io, pyarrow.parquet as pq
    obj = s3.get_conn().get_object(Bucket=bucket, Key=key)
    footer = pq.ParquetFile(io.BytesIO(obj["Body"].read()))
    return footer.metadata.num_rows


orders_ingest_daily = orders_ingest_daily()

Step-by-step explanation.

1. The 20-line module docstring is the LLM's prompt. It names the DAG id, purpose, schedule, upstream, owner, retry policy, SLA, and — crucially — a task list. Cursor Composer scaffolded 95% of the code from this docstring; the engineer accepted with two small tweaks (adding mode="reschedule" to the sensor and adding the parquet footer row-count helper).
2. The imports are all from airflow.decorators and airflow.providers.* — no airflow.operators.python_operator. The .cursorrules do-not clause enforced this.
3. DEFAULT_ARGS centralises the retry policy and SLA. The LLM proposed this shape from the docstring's retry-policy line.
4. The @dag(...) decorator is TaskFlow API. doc_md=__doc__ renders the module docstring in the Airflow UI — a small but high-value discipline.
5. wait_for_marker uses S3KeySensor with mode="reschedule" (release the worker slot between pokes). The engineer added mode because the LLM's first draft used the default mode="poke" — a subtle but important production hygiene.
6. extract_load is a @task_group with three tasks: extract_orders, write_to_s3, verify_s3_row_count. The task group frames them in the UI as one collapsed node.
7. dbt_run shells out to the dbt CLI. The subprocess call is deliberately explicit — the community DbtRunOperator has API drift between provider versions, so calling the CLI directly is more portable.
8. The dependency graph at the bottom (wait_for_marker >> extract_load(...) >> dbt_run(...)) is the DAG's shape. Explicit >> operators are more readable than task_id strings.

Output.

Task	Type	Retries	SLA
wait_for_marker	S3KeySensor	3 (via default_args)	90 min
extract_load.extract_orders	@task	3	inherited
extract_load.write_to_s3	@task	3	inherited
extract_load.verify_s3_row_count	@task	3	inherited
dbt_run	@task (subprocess)	3	inherited

Rule of thumb. Write the module docstring first (purpose, schedule, upstream, owner, retry, SLA, task list). Let the LLM scaffold from it. Verify each task against the docstring line. Add production-hygiene tweaks (mode="reschedule" on sensors, explicit retry_exponential_backoff) manually — the LLM misses these by default.

Worked example — LLM-generated pytest for the DAG

Detailed explanation. Every LLM-authored DAG must ship with a pytest fixture that (a) imports the DAG file, (b) asserts the DAG loads without errors, (c) asserts the task list matches expectation, (d) asserts the dependency graph is correct, (e) mocks external operators for smoke-test. The LLM scaffolds all five; the reviewer verifies.

• Prompt. "Generate pytest for the orders_ingest_daily DAG covering import, task list, dependency graph, and operator mocks."
• Convention. Every test file matches test_<dag_name>.py; sits in tests/dags/.

Question. Author the pytest fixture using Copilot Chat; show the resulting test file.

Input.

Assertion	Value
DAG imports without error	required
Task count	5 (marker + 3 in group + dbt_run)
Task ids	expected set
Dependency graph	wait_for_marker → extract_load → dbt_run
SLA	90 minutes

Code.


# tests/dags/test_orders_ingest_daily.py
from __future__ import annotations

from datetime import timedelta

import pytest
from airflow.models import DagBag


@pytest.fixture(scope="module")
def dagbag() -> DagBag:
    """Load all DAGs; fail fast if any DAG file has a parse error."""
    return DagBag(dag_folder="dags/", include_examples=False)


def test_orders_ingest_daily_imports(dagbag):
    """DAG file must load cleanly (no syntax / import errors)."""
    assert "orders_ingest_daily" in dagbag.dag_ids, dagbag.import_errors
    assert dagbag.import_errors == {}


def test_orders_ingest_daily_task_list(dagbag):
    """Task list must match the docstring."""
    dag = dagbag.get_dag("orders_ingest_daily")
    expected_ids = {
        "wait_for_marker",
        "extract_load.extract_orders",
        "extract_load.write_to_s3",
        "extract_load.verify_s3_row_count",
        "dbt_run",
    }
    actual_ids = {t.task_id for t in dag.tasks}
    assert actual_ids == expected_ids, (
        f"unexpected task set — actual: {actual_ids}, expected: {expected_ids}"
    )


def test_orders_ingest_daily_dependencies(dagbag):
    """Dependency graph must be wait_for_marker -> extract_load -> dbt_run."""
    dag = dagbag.get_dag("orders_ingest_daily")

    marker = dag.get_task("wait_for_marker")
    extract_first = dag.get_task("extract_load.extract_orders")
    dbt = dag.get_task("dbt_run")

    # Marker feeds into the first task of the task group
    assert extract_first.task_id in {
        t.task_id for t in marker.downstream_list
    }, "wait_for_marker should feed extract_load.extract_orders"

    # verify_s3_row_count feeds into dbt_run
    verify = dag.get_task("extract_load.verify_s3_row_count")
    assert dbt.task_id in {
        t.task_id for t in verify.downstream_list
    }, "verify_s3_row_count should feed dbt_run"


def test_orders_ingest_daily_sla(dagbag):
    """SLA must be 90 minutes on every task."""
    dag = dagbag.get_dag("orders_ingest_daily")
    for t in dag.tasks:
        assert t.sla == timedelta(minutes=90), (
            f"task {t.task_id!r} SLA {t.sla} != 90 min"
        )


def test_orders_ingest_daily_retries(dagbag):
    """Retries must be 3 on every task (via default_args)."""
    dag = dagbag.get_dag("orders_ingest_daily")
    for t in dag.tasks:
        assert t.retries == 3, f"task {t.task_id!r} retries {t.retries} != 3"


def test_orders_ingest_daily_sensor_reschedule_mode(dagbag):
    """The S3KeySensor must run in 'reschedule' mode (release worker slot)."""
    dag = dagbag.get_dag("orders_ingest_daily")
    sensor = dag.get_task("wait_for_marker")
    assert sensor.mode == "reschedule", (
        f"sensor mode {sensor.mode!r} != 'reschedule' — worker slots not released"
    )

Step-by-step explanation.

1. The dagbag fixture loads every DAG under dags/ and surfaces import errors. include_examples=False skips the Airflow-shipped examples.
2. test_orders_ingest_daily_imports fails fast on any parse error. This is the single highest-value test — most LLM-generated DAG bugs are import-time errors.
3. test_orders_ingest_daily_task_list asserts the exact set of task ids. If the LLM later "improves" the DAG and adds a task, this test surfaces the change in review.
4. test_orders_ingest_daily_dependencies walks the dependency graph and asserts the two critical edges: marker → first extract task, verify → dbt_run. A three-node DAG generates three edges; this covers the two that matter.
5. test_orders_ingest_daily_sla and test_orders_ingest_daily_retries verify the production-hygiene defaults from default_args. These often regress silently when someone edits the DAG.
6. test_orders_ingest_daily_sensor_reschedule_mode catches the "sensor is still in poke mode and hogging worker slots" regression — a specific production incident that this test prevents from recurring.

Output.

Test	Assertion	Fails when
imports	DAG loads cleanly	any syntax / import error
task_list	exact 5 task ids	task added / removed / renamed
dependencies	marker → extract → dbt_run edges	dep graph rewired
sla	90 min on every task	default_args edited
retries	3 on every task	default_args edited
sensor_reschedule_mode	S3KeySensor uses reschedule	someone reverts to poke

Rule of thumb. Every LLM-authored DAG ships with a pytest fixture that asserts (a) import, (b) task list, (c) dependency graph, (d) production-hygiene defaults (SLA, retries, sensor mode). The LLM drafts the fixture from the docstring; the reviewer verifies.

Worked example — the LLM-proposed sensor pattern

Detailed explanation. A common LLM anti-pattern is to write a polling loop inside a PythonOperator instead of using an explicit sensor. The docstring-first pattern preempts this by naming the sensor. Walk through the fix.

• Anti-pattern. while True: if s3.head_object(...): break; time.sleep(60) inside a task.
• Fix. Explicit S3KeySensor operator; docstring names the sensor.
• Discipline. .cursorrules do-not clause: "never poll in a PythonOperator — use the appropriate Sensor."

Question. Show the anti-pattern, the fix, and the .cursorrules clause that preempts it.

Input.

Aspect	Anti-pattern	Correct pattern
Task type	@task with while loop	S3KeySensor operator
Worker slot	held throughout poll	released with mode='reschedule'
Timeout	manual	operator kwarg
Retries	manual	operator kwarg
UI visibility	opaque	first-class node

Code.


# ANTI-PATTERN — never do this
from datetime import datetime
import time
from airflow.decorators import dag, task

@dag(dag_id="bad_dag", schedule="@daily", start_date=datetime(2026, 7, 1))
def bad_dag():
    @task
    def wait_for_s3_marker():
        """Wait for the _SUCCESS marker (WRONG — polls in a PythonOperator)."""
        from airflow.providers.amazon.aws.hooks.s3 import S3Hook
        s3 = S3Hook()
        while True:
            try:
                s3.get_key("orders/2026-07-30/_SUCCESS", bucket_name="raw")
                return
            except Exception:
                time.sleep(60)   # holds the worker slot for hours

    wait_for_s3_marker()
bad_dag = bad_dag()

# CORRECT — use the S3KeySensor
from datetime import datetime, timedelta
from airflow.decorators import dag
from airflow.providers.amazon.aws.sensors.s3 import S3KeySensor

@dag(dag_id="good_dag", schedule="@daily", start_date=datetime(2026, 7, 1))
def good_dag():
    S3KeySensor(
        task_id="wait_for_s3_marker",
        bucket_key="orders/{{ ds }}/_SUCCESS",
        bucket_name="raw",
        aws_conn_id="aws_default",
        poke_interval=60,
        timeout=60 * 60 * 6,        # 6-hour ceiling
        mode="reschedule",          # releases worker slot between pokes
    )
good_dag = good_dag()

# .cursorrules — the clause that preempts this
## Do-nots
- Never poll in a PythonOperator or @task. Use the appropriate Sensor
  (S3KeySensor, SnowflakeSensor, TimeDeltaSensor).
- Every Sensor MUST set `mode='reschedule'` unless the sensor is expected
  to complete within `poke_interval` seconds — release worker slots.
- Every Sensor MUST set `timeout` explicitly — no infinite pokes.

Step-by-step explanation.

1. The anti-pattern is subtle because it works — the task waits for the marker and eventually completes. The problem is operational: it holds a worker slot for hours, it doesn't surface in the Airflow UI as a sensor node, and it doesn't participate in the SLA / retry policy the way sensors do.
2. The correct pattern uses S3KeySensor from airflow.providers.amazon.aws.sensors.s3. mode="reschedule" releases the worker slot between pokes — the scheduler wakes the task every poke_interval seconds without keeping a slot warm.
3. timeout=60 * 60 * 6 sets a 6-hour ceiling. Without it, a stuck upstream would wedge the task forever. Explicit timeouts are a senior discipline.
4. poke_interval=60 sets the poll cadence. For an _SUCCESS marker that typically appears within 30 minutes, 60 seconds is a reasonable balance between latency and API load.
5. The .cursorrules do-not clauses ("never poll in a PythonOperator," "every Sensor MUST set mode='reschedule'") are what preempt the anti-pattern in the LLM's suggestions. Without them, LLMs default to polling loops ~30% of the time.

Output.

Concern	Anti-pattern	Correct pattern
Worker slot	held for hours	released between pokes
UI visibility	one opaque task	first-class sensor node
Retry semantics	inside try/except	operator kwarg
Timeout enforcement	absent	operator kwarg
SLA compatibility	broken (custom loop)	first-class

Rule of thumb. Every wait-for-external-condition step is a Sensor, not a PythonOperator with a loop. .cursorrules do-not clause + explicit mode="reschedule" + explicit timeout= are the three defenses that preempt this LLM failure mode.

Senior interview question on Airflow DAG authoring

A senior interviewer might ask: "You inherit an Airflow deployment where half the DAGs were LLM-authored by junior engineers. Sensors are polling in PythonOperators, retry policies are inconsistent, and there's no pytest coverage. Walk me through the standardisation — the docstring-first template, the .cursorrules do-not clauses, the pytest fixture pattern, and the CI checks that catch regressions."

Solution Using a docstring-first template, TaskFlow-only .cursorrules, and mandatory pytest fixtures

# dags/_template.py — the standard DAG template, versioned in the repo
"""
DAG: <dag_id>

Purpose:
    <what data flows where — one sentence>

Schedule:
    <@daily / cron>

Upstream:
    <what triggers this DAG — sensor keys, external DAG, TimeDeltaSensor>

Owner:
    <team>; on-call: <pagerduty rotation>

Retry policy:
    retries=3, retry_delay=timedelta(minutes=5), retry_exponential_backoff=True

SLA:
    <N> minutes; page if breached

Tasks:
    1. <task_id_1> — <purpose>
    2. <task_id_2> — <purpose>
    ...
"""
from __future__ import annotations

from datetime import datetime, timedelta
from airflow.decorators import dag, task, task_group

DEFAULT_ARGS = {
    "owner": "<team>",
    "retries": 3,
    "retry_delay": timedelta(minutes=5),
    "retry_exponential_backoff": True,
    "email_on_failure": False,
    "sla": timedelta(minutes=90),
}


@dag(
    dag_id="<dag_id>",
    default_args=DEFAULT_ARGS,
    schedule="<schedule>",
    start_date=datetime(2026, 7, 1),
    catchup=False,
    tags=["<domain>"],
    doc_md=__doc__,
)
def <dag_id>():
    pass

<dag_id> = <dag_id>()

# .github/workflows/dag-checks.yml — CI guardrails
name: dag-checks
on: [pull_request]
jobs:
  dag-parse:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - run: pip install -r requirements.txt
      - name: dag import test
        run: |
          python -c "
          from airflow.models import DagBag
          db = DagBag('dags/', include_examples=False)
          if db.import_errors:
              for f, e in db.import_errors.items():
                  print(f'{f}: {e}')
              raise SystemExit(1)
          print(f'loaded {len(db.dag_ids)} dags')
          "
      - name: pytest for dags
        run: pytest tests/dags/ -q
      - name: dag-lint (deprecated APIs)
        run: |
          if grep -rn "airflow.operators.python_operator" dags/; then
            echo "deprecated API found"; exit 1
          fi
          if grep -rn "PythonOperator" dags/ | grep -v test_; then
            echo "PythonOperator found — prefer @task"; exit 1
          fi

# .cursorrules additions for DAG authoring
## Airflow do-nots
- Never import from airflow.operators.python_operator (deprecated 2.8, removed 3.0).
- Never use PythonOperator — always @task (TaskFlow API).
- Never poll in a @task or PythonOperator — use the appropriate Sensor.
- Every Sensor must set `mode='reschedule'` and explicit `timeout`.
- Every network operator (SnowflakeOperator, S3Copy, etc.) inherits `retries=3` from default_args — never override to a lower value.
- Every DAG file has a module docstring with: purpose, schedule, upstream, owner, retry policy, SLA, task list.
- Every DAG file has a matching tests/dags/test_<dag_id>.py fixture asserting: import, task list, dependency graph, SLA, retries.

Step-by-step trace.

Step	Value	Reasoning
DAG template	dags/_template.py versioned	new DAGs start from a compliant scaffold
Docstring	7-line block: purpose, schedule, upstream, owner, retries, SLA, tasks	prompts the LLM correctly
TaskFlow API	mandatory	via .cursorrules do-not + CI grep
Sensor mode	reschedule + explicit timeout	releases worker slots; no infinite pokes
pytest fixture	mandatory sibling in tests/dags/	CI blocks merge if missing
CI grep for deprecated API	blocks merge on airflow.operators.python_operator	prevents regression
.cursorrules do-not clauses	7 clauses covering the top failure modes	LLM self-checks against them

After the 30-day standardisation, every new DAG ships from the template, TaskFlow API is universal, sensor polling in PythonOperators falls to zero, and pytest coverage rises from ~30% to ~95%. The two engineers who were skeptical of pytest for DAGs become the loudest advocates once the first regression is caught pre-merge.

Output:

Metric	Baseline (week 0)	After 30 days
DAGs following template	40%	95%
DAGs using TaskFlow API	60%	100%
Sensors in mode='reschedule'	35%	100%
DAGs with pytest coverage	30%	95%
Deprecated-API grep hits	8	0
Post-merge DAG defects / month	6	1

Why this works — concept by concept:

• Docstring-first template — the module docstring is the LLM's prompt. Standardising the docstring shape via a template forces the LLM to produce compliant DAGs. Skipping the template forces every author to reinvent the docstring shape, and the LLM's DAGs drift accordingly.
• TaskFlow-only .cursorrules — pinning @dag/@task decorators and prohibiting PythonOperator / airflow.operators.python_operator in the rules file (plus enforcing via CI grep) eliminates the deprecated-API regression entirely.
• Mandatory pytest fixtures — asserting import, task list, dependency graph, SLA, retries in a per-DAG pytest file surfaces regressions before merge. The tests are cheap to write (LLM scaffolds them) and cheap to run (~10 s per DAG).
• Sensor mode = reschedule + explicit timeout — the two config kwargs that turn sensors from "worker-slot-hogging polling loops" into "polite scheduler-managed waits." .cursorrules clause + CI grep enforce.
• Cost — one DAG template file, ~30 lines of .cursorrules additions, one CI workflow, one pytest fixture per DAG. The payback is 100% TaskFlow adoption, 100% correct sensor mode, and a ~6× drop in post-merge DAG defects. Compared to letting LLM-authored DAGs land ad-hoc, this is O(1) config for O(N × DAG) engineer-hours saved.

ETL
Topic — etl
ETL problems for Airflow DAG design

Practice →


API Topic — api-integration API-integration problems for sensor-driven pipelines

Practice →


---

5. Guardrails, review discipline & the SQL-engineer's Copilot workflow

The three-lane guardrail — compile, lint, human review — is what makes LLM-authored SQL safe to ship

The mental model in one line: sql pair programming with an LLM is only safe when every completion passes through three independent lanes before merge — the compile lane (dbt parse + dbt compile) catches unresolved refs and invalid Jinja, the lint lane (sqlfluff + sqlmesh diff) catches style and semantic drift, and the human review lane catches the intent mismatches that neither compile nor lint can see — and skipping any one of the three lanes is what turns a productive Copilot workflow into a source of subtle production defects that only surface days later in dashboards or reconciliation reports. Every senior engineer who has been burned by an LLM's hallucination has responded by hardening one of these three lanes; the ones who haven't been burned yet are lucky, not skilled.



The three lanes of the guardrail — what each catches and what it misses.

• Compile lane. dbt parse + dbt compile --select state:modified. Catches: unresolved ref(), invalid Jinja, missing macros, invalid source(), syntax errors. Misses: semantically-wrong SQL that happens to be syntactically valid (invented column names that exist elsewhere in the schema, wrong join keys, off-by-one filters).
• Lint lane. sqlfluff lint --dialect snowflake + sqlmesh diff (or dbt-checkpoint). Catches: style drift (SELECT * in marts, missing schema.yml entries, inconsistent CTE naming), semantic drift (a model's row count moved by >5%), deprecated API references. Misses: semantically-wrong SQL that leaves row count unchanged (silently wrong aggregation, subtle join-cardinality bugs).
• Human review lane. A person reads the diff, verifies against the docstring / comment contract, runs at least one EXPLAIN, and considers "does this SQL do what the intent says?" Catches: intent-code mismatches, semantic bugs that don't move row counts, novel bugs that no lint rule anticipated. Misses: nothing systematically, but is expensive and inconsistent across reviewers.

The four review disciplines every senior SQL engineer signs up for.

• Read every line. LLM-authored SQL that "looks right" is exactly the SQL that ships hallucinations. Read every line as if it were written by an unfamiliar junior — because it effectively was.
• Run every query at least once. In a dev / staging environment, against a representative dataset. The compile lane says "the SQL is valid"; running says "the SQL returns what the comment claims."
• Verify the EXPLAIN plan. For any query on a mart or dimension table, look at the plan. Hallucinated columns don't show up as missing joins in the plan; they show up as unexpected Cartesian products or full scans.
• Verify the row count. If the LLM edited a model, compare the row count before and after in a --defer compile. Row-count drift is the single most sensitive signal for semantic regression.

The daily workflow — the SQL engineer's Copilot day.

• Morning. Pull main, run dbt parse locally to seed the state manifest for --defer. Reset Cursor tabs; reindex codebase if any schema change was merged overnight.
• Task loop. For each task: write comment / docstring, wait for ghost text, verify, iterate, commit locally (precommit runs compile + lint + hallucination scan), push, open PR.
• Review. For every PR (yours or teammates'), read every line, verify against the docstring, run one query, note the EXPLAIN plan, approve or request changes.
• End of day. Close all tabs; sync .cursorrules if any convention changed today.
• Weekly. Reindex codebase; grep for stale names in .cursorrules; review the hallucination-rate metric; add new failure modes to the do-not clauses.

Common interview probes on guardrails.

• "What's your review checklist for LLM-authored SQL?" — read every line, run every query, verify EXPLAIN, check row count.
• "How do you catch semantically-wrong-but-syntactically-valid SQL?" — sqlmesh diff (row-count drift) + human review against the docstring.
• "How do you handle LLM-authored code in incident review?" — treat it identically to human-authored code; root cause the failure mode (usually stale context or missing do-not clause); patch .cursorrules.
• "How do you know your guardrails work?" — the hallucination-rate metric: do-not-pattern violations per merged PR, tracked weekly.

Worked example — the daily SQL engineer's Copilot workflow

Detailed explanation. Codify the day-to-day workflow of a senior analytics engineer who ships ~2 dbt PRs and ~1 Airflow DAG PR per day, all LLM-assisted. Every step has a discipline; walk through them.

• Morning setup. ~5 minutes to seed the local dbt state manifest, reset Cursor context, review overnight PRs.
• Task loop. ~30–60 minutes per task, four-phase: prompt, iterate, precommit, PR.
• Review loop. ~10–15 minutes per teammate PR.
• End of day. ~5 minutes to sync .cursorrules and close tabs.

Question. Write the daily workflow as a checklist, with time budgets.

Input.

Phase	Duration	Purpose
Morning setup	5 min	fresh state manifest + fresh Cursor context
Task loop	30–60 min × N tasks	authoring
Review loop	10–15 min × M PRs	verification
End of day	5 min	hygiene
Weekly	30 min	metric review + rules-file update

Code.


#!/usr/bin/env bash
# copilot_daily.sh — the SQL engineer's daily rhythm
set -euo pipefail

case "${1:-help}" in

  morning)
    # 5-minute morning setup
    git checkout main && git pull
    dbt deps
    dbt parse --profiles-dir profiles
    # snapshot the state manifest for --defer during the day
    cp target/manifest.json ./state/manifest.json
    echo "state manifest snapshotted at $(date)"
    # remind me to reset Cursor
    echo "→ In Cursor: Cmd-K Cmd-W (close all), Cmd-Shift-P → 'Cursor: Reindex Codebase'"
    ;;

  task)
    # start a new task: create a branch, remind me of the discipline
    read -p "task name: " task
    git checkout -b "$(whoami)/${task}"
    cat <<EOF
Discipline reminder for this task:
  1. Comment/docstring FIRST (5-clause: dialect, source, grain, filters, output)
  2. Wait for ghost text; verify against comment; iterate on comment not on SQL
  3. Before commit: precommit runs dbt parse + sqlfluff + hallucination scan
  4. PR body: paste the comment header + link to the intent doc
EOF
    ;;

  precommit)
    # explicit precommit run (git will also invoke this automatically)
    pre-commit run --all-files
    ;;

  review)
    # helper for reviewing a teammate PR
    read -p "PR number: " pr
    gh pr checkout "$pr"
    gh pr diff "$pr" | less
    echo "Review checklist:"
    echo "  [ ] Read every line"
    echo "  [ ] Comment contract matches the SQL"
    echo "  [ ] Ran query in dev at least once"
    echo "  [ ] Checked EXPLAIN plan for any new join"
    echo "  [ ] Row count drift acceptable (< 5%)"
    echo "  [ ] .cursorrules do-nots not violated"
    ;;

  evening)
    # 5-minute end of day
    git status
    if [ -n "$(git status --porcelain)" ]; then
      echo "WARN: uncommitted changes"
    fi
    # sync .cursorrules if any convention changed
    if git log --since=today --oneline dbt_project.yml profiles.yml | grep -q .; then
      echo "→ dbt config changed today — review .cursorrules"
    fi
    ;;

  weekly)
    # 30-minute weekly hygiene
    python scripts/copilot_hallucination_scan.py --window 7d
    python scripts/hallucination_rate.py --pr-window 7d
    grep -rn "TODO(rules)" .cursorrules || echo "no pending rules updates"
    ;;

  help|*)
    echo "usage: $0 {morning|task|precommit|review|evening|weekly}"
    ;;
esac

Step-by-step explanation.

1. morning seeds the local state/manifest.json for --defer compile runs later in the day. This is what makes dbt compile --select state:modified --defer fast — it compares the current state to the morning snapshot.
2. task creates a branch and prints the four-step discipline reminder. The comment-first / iterate-on-comment mantra is the highest-leverage habit; printing it at task-start makes it hard to skip.
3. precommit explicitly runs the pre-commit hooks. In practice, git commit runs them automatically, but running them manually before commit lets you fix issues without a rejected commit.
4. review checks out a teammate PR, shows the diff, and prints the six-item review checklist. Every checklist item corresponds to a failure mode that has shipped in production somewhere; the checklist is a memento mori of past incidents.
5. evening catches uncommitted work and prompts a .cursorrules sync if dbt config changed today. This is what keeps the rules file synchronised with the actual repo state.
6. weekly runs the hallucination-rate metric. Trending do-not violations upward is the earliest signal that the rules file needs an update or that a new failure mode has appeared.

Output.

Phase	Time	Frequency
morning	5 min	daily
task	30–60 min × 2–3	daily
precommit	30 s (auto)	per commit
review	10–15 min × 3–5	daily
evening	5 min	daily
weekly	30 min	Mondays

Rule of thumb. Codify the daily rhythm as a shell script. Print the discipline reminders at task-start; print the review checklist at PR-checkout time. The disciplines that live only in your head fade; the disciplines that print themselves on your terminal every morning stick.

Worked example — precommit hooks that catch what CI can't

Detailed explanation. The precommit stage is where the fastest, cheapest guardrails run — before the code leaves the engineer's machine. Failed precommits produce fast feedback; failed CI produces slow feedback and a rejected PR. The senior discipline is to move as many checks as possible from CI to precommit. Walk through a production precommit config.

• Compile lane. dbt parse (~5 s) + dbt compile --select state:modified --defer (~10 s per changed model).
• Lint lane. sqlfluff (~2 s per file) + sqlmesh diff (~5 s if enabled).
• Custom lane. Hallucination scan (~1 s) + repo-idiom greps (~1 s).
• Total. ~15–30 s per commit — fast enough to run on every commit; slow enough to catch what CI would otherwise catch 5 minutes later.

Question. Write the production .pre-commit-config.yaml for a dbt + Airflow repo.

Input.

Hook	Lane	Cost
dbt-parse	compile	~5 s
dbt-compile-changed	compile	~10 s / model
sqlfluff-lint	lint	~2 s / file
sqlfluff-fix	lint	~2 s / file
copilot-hallucination-scan	custom	~1 s
dag-lint-deprecated	custom	~1 s
python black + isort	style	~2 s

Code.


# .pre-commit-config.yaml — production
repos:
  # --- Lane 1: COMPILE ---
  - repo: local
    hooks:
      - id: dbt-parse
        name: dbt parse (unresolved refs / bad Jinja)
        entry: dbt parse --profiles-dir profiles --project-dir dbt_project
        language: system
        pass_filenames: false
        files: '\.(sql|yml|yaml)$'

      - id: dbt-compile-changed
        name: dbt compile (changed models only, --defer)
        entry: |
          bash -c '
          cd dbt_project
          dbt compile --profiles-dir ../profiles \
                      --select state:modified \
                      --defer --state ../state
          '
        language: system
        pass_filenames: false
        files: 'dbt_project/models/.*\.sql$'

  # --- Lane 2: LINT ---
  - repo: https://github.com/sqlfluff/sqlfluff
    rev: 3.2.0
    hooks:
      - id: sqlfluff-lint
        args: [--dialect, snowflake, --templater, dbt]
        additional_dependencies: ['dbt-snowflake', 'sqlfluff-templater-dbt']
      - id: sqlfluff-fix
        args: [--dialect, snowflake, --templater, dbt, --force]
        additional_dependencies: ['dbt-snowflake', 'sqlfluff-templater-dbt']

  # --- Lane 3: CUSTOM (repo idiom + hallucination) ---
  - repo: local
    hooks:
      - id: copilot-hallucination-scan
        name: copilot hallucination scan
        entry: python scripts/copilot_hallucination_scan.py
        language: system
        pass_filenames: false

      - id: dag-lint-deprecated
        name: dag lint (no deprecated Airflow APIs)
        entry: |
          bash -c '
          if grep -rn "airflow\.operators\.python_operator" dags/; then
            echo "deprecated airflow API found"; exit 1
          fi
          if grep -rn "^from airflow.operators import PythonOperator" dags/; then
            echo "PythonOperator import found — use @task"; exit 1
          fi
          exit 0
          '
        language: system
        pass_filenames: false

  # --- Style ---
  - repo: https://github.com/psf/black
    rev: 24.8.0
    hooks:
      - id: black
        args: [--line-length, '100']
  - repo: https://github.com/PyCQA/isort
    rev: 5.13.2
    hooks:
      - id: isort
        args: [--profile, black]

Step-by-step explanation.

1. Lane 1 (compile) has two hooks. dbt-parse is fast (~5 s) and catches unresolved ref(), invalid Jinja, and missing macros. dbt-compile-changed uses --select state:modified --defer to compile only the models whose upstream state has changed — this keeps precommit fast even in a 300-model repo.
2. Lane 2 (lint) uses sqlfluff with the dbt templater — this lets sqlfluff understand {{ ref() }} and {{ source() }} without treating them as syntax errors. sqlfluff-lint reports; sqlfluff-fix auto-corrects.
3. Lane 3 (custom) runs the hallucination-flag scan and the DAG deprecated-API grep. Both are cheap (~1 s each) and catch the top-N specific failure modes that the generic lint tools miss.
4. The style hooks (black, isort) are last so the auto-fixers run after the semantic checks pass. This ordering avoids the "black reformatted the file and now sqlfluff has different line numbers in errors" confusion.
5. Total precommit cost is ~15–30 seconds per commit. This is fast enough to run every commit; slow enough that engineers rarely bother to --no-verify. The right cost curve.

Output.

Lane	Hooks	Cost	Catches
Compile	dbt-parse, dbt-compile-changed	~15 s	unresolved refs, invalid Jinja
Lint	sqlfluff-lint, sqlfluff-fix	~5 s	style + templated-SQL syntax
Custom	hallucination-scan, dag-lint	~2 s	specific failure modes
Style	black, isort	~2 s	code style

Rule of thumb. Precommit runs every commit and must stay under ~30 seconds total. Move as many checks as possible from CI to precommit; keep only the expensive checks (full dbt build, dbt test) in CI. Fast feedback matters; slow rejection cycles do not.

Worked example — the LLM-authored SQL incident review pattern

Detailed explanation. When an incident traces to LLM-authored SQL, the review must go beyond "revert the PR." The senior discipline is: (a) identify the exact hallucination or drift, (b) trace it to a missing guardrail, (c) patch the guardrail, (d) grep the repo for the same pattern elsewhere. Walk through a real incident pattern.

• Symptom. A dashboard reports 3% row count drop overnight.
• Cause. A dbt model merged yesterday joined on customer_key (invented by Copilot) instead of customer_id; the join silently returned an empty set for a segment.
• Detection. The sqlmesh diff in CI flagged the model's row count moved by 3%, but the warning was ignored.
• Fix. Revert; add a .cursorrules do-not clause "never invent column names — ask for schema.yml"; add a precommit hook that verifies every JOIN's ON clause references columns from the referenced tables; grep the repo for other JOIN-on-invented-columns.

Question. Write the incident review + guardrail patch, and demonstrate the repo-wide grep that would catch the same pattern elsewhere.

Input.

Aspect	Value
Incident	3% row-count drop on mart_customer_orders dashboard
Root cause	JOIN on customer_key (invented); should be customer_id
Detection lane that fired	sqlmesh diff warning (ignored)
Detection lane that missed	sqlfluff, dbt parse, human review
Fix	revert; patch .cursorrules; add JOIN-column-verify hook

Code.


# INCIDENT-2026-07-29.md — post-incident review

## Timeline
- 2026-07-28 15:03  PR #4821 merged: `int_orders__enriched.sql` refactor
- 2026-07-28 15:15  CI sqlmesh diff warned: row_count -3.1% (ignored)
- 2026-07-28 22:00  overnight dbt run applies the model to prod
- 2026-07-29 08:12  dashboard "Customer Orders — Daily" flagged 3% drop
- 2026-07-29 08:35  root cause: JOIN on `customer_key` — column does not exist
                     on dim_customers; silently returned NULL, filtered out
- 2026-07-29 08:50  reverted PR #4821; deployed hotfix

## Root cause
Copilot generated a JOIN clause `ON o.customer_id = c.customer_key`.
`c.customer_key` does not exist on dim_customers (correct column is
`customer_id`). Snowflake accepted the SQL because `customer_key` looked
like it could be a valid column name; the join silently returned NULL
for that side; the WHERE filter dropped the NULLs; row count fell 3%.

The Copilot completion was made in a session where the engineer had not
opened dim_customers's schema.yml in the context window. Copilot guessed
the column name from stem-name heuristics.

## Guardrail gaps
1. `.cursorrules` did not have "never invent column names" as a do-not.
2. precommit did not verify JOIN ON columns exist in referenced tables.
3. sqlmesh diff warning was fired but not blocking.
4. human review approved without opening the schema.yml.

## Fix
1. Added to `.cursorrules`:
   "Never invent a column name. Before proposing a JOIN, verify both
    columns exist by opening schema.yml or asking the user."
2. Added precommit hook `verify_join_columns.py` that parses new/changed
   SQL, extracts JOIN ON clauses, and cross-references the columns
   against the referenced models' schema.yml.
3. Made sqlmesh diff warnings blocking on row count movements > 2%.
4. Updated PR review template: "opened schema.yml for every JOIN? [ ]"

## Repo-wide sweep
Grep run to catch the same pattern in other models:

python scripts/verify_join_columns.py dbt_project/models/*/.sql


Result: 2 additional models flagged with likely-invented column names
in JOINs; both fixed in follow-up PR #4835.

# scripts/verify_join_columns.py — the new precommit hook
"""
Parse every SQL file and verify JOIN ON columns exist in the referenced
models' schema.yml entries. Fails loudly on any suspicious join.
"""
import re, sys, yaml
from pathlib import Path
from collections import defaultdict

# 1. Load schema.yml -> {model_name: [column_names]}
schema_columns = defaultdict(set)
for yml_path in Path("dbt_project/models").rglob("schema.yml"):
    doc = yaml.safe_load(yml_path.read_text())
    for m in doc.get("models", []):
        for c in m.get("columns", []):
            schema_columns[m["name"]].add(c["name"])

# 2. Simple regex — {{ ref('model_name') }} <alias>
REF = re.compile(r"\{\{\s*ref\(\s*'([^']+)'\s*\)\s*\}\}\s+(\w+)", re.I)
JOIN = re.compile(
    r"JOIN\s+\{\{\s*ref\(\s*'([^']+)'\s*\)\s*\}\}\s+(\w+)\s+"
    r"(?:USING\s*\((\w+)\)|ON\s+([\w\.]+)\s*=\s*([\w\.]+))",
    re.I | re.S,
)

# 3. Walk staged files
violations = []
for fn in sys.argv[1:]:
    text = Path(fn).read_text()
    # Build alias -> model map from the FROM / JOIN clauses
    alias_to_model = dict(REF.findall(text))
    for m in JOIN.finditer(text):
        _, _, using_col, left_expr, right_expr = m.groups()
        # For each column in the ON clause, verify it exists in its model
        for expr in (left_expr, right_expr):
            if not expr:
                continue
            alias, _, col = expr.rpartition(".")
            model = alias_to_model.get(alias)
            if model and model in schema_columns and col not in schema_columns[model]:
                violations.append(
                    f"{fn}: JOIN references {alias}.{col} — "
                    f"column not in schema.yml for {model}"
                )
        if using_col:
            for alias, model in alias_to_model.items():
                if model in schema_columns and using_col not in schema_columns[model]:
                    violations.append(
                        f"{fn}: JOIN USING ({using_col}) — "
                        f"column not in schema.yml for {model}"
                    )

if violations:
    print("verify_join_columns flagged:")
    for v in violations:
        print("  " + v)
    sys.exit(1)

Step-by-step explanation.

1. The post-incident timeline records the causal chain: PR merged → sqlmesh warning ignored → overnight run → dashboard alarm → root cause identified → revert. Writing this timeline is the first discipline; it forces you to identify every guardrail that could have caught it.
2. The root cause identifies the specific failure mode (invented column name) and the human contributor (schema.yml not opened during Copilot session). Naming both — the model failure and the human process failure — is required.
3. The guardrail-gap section lists the four lanes that should have caught this: .cursorrules, precommit, CI, human review. Every incident maps to at least one gap; often multiple.
4. The fix section is the patch to each lane: add a do-not clause, add a precommit hook, upgrade a CI warning to blocking, update the PR template. Multi-lane fix for a multi-lane failure.
5. The repo-wide sweep uses the new precommit hook to grep for the same pattern elsewhere. This is what turns a one-incident fix into a class-of-incident fix.

Output.

Lane	Gap	Patch
.cursorrules	no "never invent column names" clause	added
precommit	no JOIN-column verifier	added verify_join_columns.py
CI	sqlmesh warning was non-blocking	upgraded to blocking on > 2% drift
human review	schema.yml not opened	updated PR template checkbox
repo sweep	pattern lurked in 2 other models	fixed in PR #4835

Rule of thumb. Every LLM-related incident review ends with (a) named root cause, (b) named guardrail gap per lane, (c) patched lane, (d) repo-wide grep for the same pattern. Skipping the grep is the most common way an incident recurs.

Senior interview question on guardrails and review discipline

A senior interviewer might ask: "You've inherited an analytics-engineering team that adopted Copilot six months ago. Three production incidents have traced to LLM-authored SQL — invented column names, wrong dialect, deprecated Airflow operator. Walk me through the three-lane guardrail you'd standardise, the precommit config, the CI upgrades, the review-discipline PR template, and the metrics you'd track to know it's working."

Solution Using a three-lane precommit + CI, a review PR template, and a weekly hallucination-rate metric

# .pre-commit-config.yaml — three lanes
repos:
  - repo: local
    hooks:
      - id: dbt-parse
        name: dbt parse
        entry: dbt parse --profiles-dir profiles --project-dir dbt_project
        language: system
        pass_filenames: false
      - id: dbt-compile-changed
        name: dbt compile (state:modified, --defer)
        entry: bash -c 'cd dbt_project && dbt compile --profiles-dir ../profiles --select state:modified --defer --state ../state'
        language: system
        pass_filenames: false
      - id: copilot-hallucination-scan
        name: copilot hallucination scan
        entry: python scripts/copilot_hallucination_scan.py
        language: system
        pass_filenames: false
      - id: verify-join-columns
        name: verify JOIN columns exist in schema.yml
        entry: python scripts/verify_join_columns.py
        language: system
        files: '\.sql$'
      - id: dag-lint-deprecated
        name: dag lint (no deprecated Airflow APIs)
        entry: bash -c 'if grep -rn "airflow\.operators\.python_operator" dags/; then echo "deprecated API"; exit 1; fi'
        language: system
        pass_filenames: false

  - repo: https://github.com/sqlfluff/sqlfluff
    rev: 3.2.0
    hooks:
      - id: sqlfluff-lint
        args: [--dialect, snowflake, --templater, dbt]
        additional_dependencies: ['dbt-snowflake', 'sqlfluff-templater-dbt']

# .github/workflows/pr-checks.yml — CI (heavier, blocking)
name: pr-checks
on: pull_request
jobs:
  dbt-build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - run: pip install -r requirements.txt
      - name: fetch state manifest from main
        run: |
          git fetch origin main:main
          git checkout main -- state/manifest.json || true
          git checkout -
      - name: dbt build (modified + downstream)
        run: |
          cd dbt_project
          dbt build --profiles-dir ../profiles \
                    --select state:modified+ \
                    --defer --state ../state \
                    --fail-fast
      - name: sqlmesh diff (row-count drift, BLOCKING > 2%)
        run: |
          python scripts/sqlmesh_diff.py --threshold 0.02
      - name: airflow dagbag import
        run: |
          python -c "
          from airflow.models import DagBag
          db = DagBag('dags/', include_examples=False)
          assert not db.import_errors, db.import_errors
          "
      - name: pytest for dags
        run: pytest tests/dags/ -q

<!-- .github/pull_request_template.md -->
## Description
<!-- what does this PR do and why -->

## LLM co-authorship
- [ ] This PR includes Copilot / Cursor-generated code
- [ ] Comment-first prompts were used
- [ ] `.cursorrules` do-nots were respected

## Guardrail lanes
- [ ] Precommit passed (dbt parse, compile, sqlfluff, hallucination scan)
- [ ] CI passed (dbt build --select state:modified+, sqlmesh diff, DAG imports, pytest)
- [ ] Ran at least one query in dev against representative data
- [ ] Checked EXPLAIN plan for any new JOIN
- [ ] Opened schema.yml for every JOINed model
- [ ] Row-count drift (from sqlmesh diff) is < 2% or explained in the description

## For DAGs
- [ ] Module docstring: purpose, schedule, upstream, owner, retries, SLA, tasks
- [ ] TaskFlow API (@dag / @task), not PythonOperator
- [ ] Every Sensor has mode='reschedule' and explicit timeout
- [ ] pytest fixture at tests/dags/test_<dag_id>.py asserts import + task list + deps

Step-by-step trace.

Lane	Where it runs	What it catches	Cost
Compile (precommit)	dev machine	unresolved refs, invalid Jinja	~15 s / commit
Lint (precommit)	dev machine	style, templated-SQL syntax	~5 s / commit
Custom (precommit)	dev machine	wrong dialect, deprecated API, invented JOIN columns	~2 s / commit
Compile (CI)	GitHub Actions	full modified-subgraph compile	~1–3 min / PR
sqlmesh diff (CI)	GitHub Actions	row-count drift > 2%	~30 s / PR
DAG import (CI)	GitHub Actions	DAG parse errors	~10 s / PR
pytest (CI)	GitHub Actions	task list, deps, SLA, retries	~10 s / DAG
Human review	PR	intent-code mismatch, EXPLAIN sanity	~15 min / PR

After the 30-day standardisation, the three prior incidents' failure modes are caught at precommit (wrong dialect, deprecated API) or precommit + CI (invented JOIN columns), and the weekly hallucination-rate metric drops from ~15% to ~2%. The team ships more PRs faster with fewer post-merge defects — the three-lane guardrail turns the LLM workflow from "risky productivity" into "safe productivity."

Output:

Metric	Before	After (30 d)
Precommit failure rate	8% (mostly style)	4% (mostly semantic, caught early)
CI failure rate	15% (mostly dbt build)	3%
Post-merge defects / 100 PRs	12	2
Hallucination-flag rate / merged PR	15%	2%
PRs / engineer / week	3.5	7.0
Mean PR lifetime	2.1 d	0.9 d

Why this works — concept by concept:

• Three-lane guardrail — compile, lint, human review each catch a different class of failure. Compile catches syntax; lint catches style + row-count drift; review catches intent mismatch. Missing any one lane leaves a class of defect uncovered.
• Move as many checks as possible to precommit — precommit gives 15-second feedback; CI gives 3-minute feedback. Fast feedback is the difference between "engineer fixes it while still in flow" and "engineer forgets what they were doing and comes back tomorrow."
• Blocking sqlmesh diff on row-count drift — turning a warning into a blocker is the specific fix for the "silently wrong SQL that compiles fine" class of incident. 2% threshold catches the meaningful drifts without over-firing on legitimate refactors.
• PR template checkboxes — the template turns implicit disciplines (open schema.yml, verify EXPLAIN, check row count) into explicit checkboxes reviewers can hold each other accountable to. The template is the code review's .cursorrules.
• Cost — one precommit config (~50 lines), one CI workflow (~30 lines), one PR template (~20 lines), one weekly metric script. The payback is a ~7× drop in hallucination-flag rate and 2× PR throughput. Compared to letting each engineer figure out their own review discipline, this is O(1) config for O(N × week) engineer-hours saved.

SQL
Topic — cte/sql
CTE and refactor problems for review discipline

Practice →


SQL
Topic — aggregation/sql
Aggregation problems for LLM-assisted authoring

Practice →


---

Cheat sheet — LLM-native SQL authoring recipes

• Editor selection by task shape. For single-file mechanical edits, Cursor inline or Copilot inline; sub-300-ms ghost text keeps flow. For multi-file reasoning tasks, Cursor Composer or Copilot Chat with @Codebase/@workspace context injection. For overnight or repo-wide refactors, Codex CLI, Claude Code, or Aider with the strictest 3-lane guardrail. Never let agentic mode land PRs without human review; never spend Composer latency on tasks that fit in inline ghost text.
• The .cursorrules template that ships hallucination rate ~4× lower. Sections in order: role + voice, repo shape, naming conventions, SQL style + dialect, dbt idioms, Airflow idioms, do-nots, guardrail expectations. Under ~1500 tokens. Versioned in the repo root; reviewed in PRs like any config. Do-nots are more effective than do's — "never invent a column name" beats "prefer real column names." Include "when you don't know, say so and ask" as the final clause to convert silent hallucination into loud "please paste the schema."
• The comment-first Copilot prompt. Five-clause header comment: dialect, source table + upstream refs, grain (one row per what), filters (WHERE conditions), output columns. Write the comment first; wait for ghost text; verify against the comment; iterate on the comment (not on the SQL) if the completion drifts. This one discipline is the single most transferable Copilot habit.
• The dbt model docstring pattern. Every model has a schema.yml entry with description + columns + tests. Every macro has a {% docs %} block. Every dbt-file top-of-file comment names dialect + source refs + grain — this is the Copilot inline anchor, redundant with .cursorrules but load-bearing when tabs are stale.
• The Airflow DAG scaffold prompt. Module docstring with seven fields: DAG id, purpose, schedule, upstream (sensor keys or trigger DAG), owner + on-call rotation, retry policy (retries + delay + exponential backoff), SLA, task list. Then @dag decorator; then @task / @task_group decorators. Then dependency graph with explicit >>. Then a matching pytest fixture at tests/dags/test_<dag_id>.py.
• The precommit three-lane block. Lane 1 (compile): dbt parse + dbt compile --select state:modified --defer. Lane 2 (lint): sqlfluff lint --dialect <dialect> --templater dbt. Lane 3 (custom): hallucination-scan for wrong-dialect + deprecated-API patterns, plus verify_join_columns.py for JOIN-column existence. Total ~15–30 s per commit; catches ~90% of what CI would catch 3 minutes later.
• The CI heavier lane. dbt build --select state:modified+ (compile + tests on modified subgraph), sqlmesh diff with 2% row-count-drift threshold as blocking, DAG import test, pytest for DAGs. Runs on every PR; blocks merge on any failure. Fetch the main-branch state manifest before running so --defer works against fresh state.
• The PR review template. Checkboxes: LLM co-authorship, comment-first prompt used, .cursorrules respected, precommit passed, CI passed, ran query in dev, checked EXPLAIN, opened schema.yml for every JOIN, row-count drift < 2% or explained. For DAGs: TaskFlow API, sensor mode='reschedule' + timeout, pytest fixture asserts task list + deps + SLA + retries.
• The daily rhythm. Morning: pull main, snapshot state/manifest.json, reset Cursor tabs, reindex codebase. Task loop: comment/docstring first, ghost text, verify, precommit, PR. Review loop: read every line, verify comment contract, run one query, check EXPLAIN. Evening: close tabs, sync .cursorrules if any convention changed. Weekly: hallucination-rate metric, .cursorrules update if new failure modes appeared.
• The hallucination-rate metric. Weekly script that greps merged-PR diffs for known do-not patterns (wrong dialect, deprecated Airflow API, hardcoded schema, SELECT * in mart, JOIN on unknown column). Report absolute count + rate. Trending upward = missing rules clause or missing precommit hook. Target: < 3% flag rate on merged PRs.
• The incident-review-to-guardrail-patch loop. Every LLM-related incident ends with: (a) named root cause, (b) named guardrail gap per lane, (c) patched lane (new .cursorrules clause, new precommit hook, new CI check, or updated PR template), (d) repo-wide grep for the same pattern in other models / DAGs. Skipping the grep is the most common way an incident recurs.
• Context-window hygiene. Close unused Cursor tabs at end of session. Reindex codebase after schema changes. Pin .cursorrules + dbt_project.yml + profiles.yml via .cursor/settings.json autoInclude. Grep for stale model names in .cursorrules weekly. Keep the rules file under ~1500 tokens; verbose examples go in docs/ and get pulled via @Docs.
• When to escalate from inline to Composer to CLI. Inline for single-file mechanical (add a column, refactor a filter). Composer / Chat for multi-file with reasoning (rename model across 5 references, generate schema.yml from a new model). CLI / agentic for repo-wide (deprecate a macro across 40 models, generate exposures for every dashboard). Each escalation multiplies both leverage and required review depth; never escalate without proportional guardrail depth.
• The "when you don't know, say so" clause. Both .cursorrules and the comment-first pattern should invite the LLM to ask for clarification instead of guessing. Silent hallucination is expensive; loud "please paste the schema for dim_customers" is cheap. Rewarding the ask, not the guess, is the highest-leverage prompt-engineering move.

Frequently asked questions

What is Cursor and why does it matter for SQL engineers in 2026?

Cursor is a VS Code fork with LLM-native features built in from the ground up: .cursorrules for repo-scoped prompt injection, @Codebase / @Docs / @Files symbol injection for context-window control, inline ghost-text completion tuned for sub-300-ms latency, and Composer for multi-file agentic edits. It matters for cursor for sql engineers because SQL authoring in a dbt monorepo is a context-heavy task — the LLM must know the model layer hierarchy, the naming conventions, the dbt macros installed, the SQL dialect (Snowflake vs BigQuery vs Postgres), the schema.yml contracts — and Cursor's context-injection model is uniquely well-suited to that shape. Every senior data engineer in 2026 who has adopted .cursorrules + comment-first prompting reports 2–3× PR throughput on analytics-engineering work with stable or improving defect rates. Cursor doesn't replace SQL literacy; it amplifies it, and the engineers who configure it well outproduce the ones who don't by a wide margin.

GitHub Copilot vs Cursor for dbt authoring — which should I pick?

Both, actually — they solve different sub-problems. GitHub Copilot inline is unmatched for sub-300-ms ghost-text completions at the caret: line-by-line SQL, Jinja fills, dbt macro completions, small edits. Its github copilot sql completions are fastest and most predictable when you use the comment-first pattern. Cursor is unmatched for repo-scoped context (.cursorrules, @Codebase), multi-file edits (Composer), and integrated Chat with @Docs injection. The senior workflow uses both: Cursor as the editor with .cursorrules and Composer for multi-file work, plus GitHub Copilot enabled inside Cursor for the fastest ghost-text completions. cursor rules for sql are what pin dialect + naming; Copilot's inline model is what makes each line arrive quickly. Never pick one and disable the other — the failure modes are complementary and the productivity multiplier is compounded.

How do I keep an LLM from hallucinating column names in my SQL?

Four defenses, in order of leverage. First, put the SQL dialect and the source tables' expected columns in a file-header comment or in .cursorrules; this is the cheapest fix. Second, use Cursor's @Codebase <model> schema.yml symbol injection (or open the schema.yml in a sibling tab in Copilot) so the LLM sees the actual column list. Third, add a precommit hook (verify_join_columns.py) that parses JOIN ON clauses and cross-references the columns against schema.yml — this catches invented column names before commit. Fourth, in .cursorrules, add the do-not clause "never invent a column name; ask for the schema.yml if unclear" — this converts silent hallucination into loud clarification requests. Combine all four and the invented-column-name failure mode falls from ~1 in 10 completions to ~1 in 100. The single incident every senior data engineer remembers is the one where an invented column name silently shipped and dropped 3% of dashboard rows overnight; the four defenses above are the composite fix.

Can I trust Copilot or Cursor to write Airflow DAGs?

Yes, with the docstring-first discipline and a three-lane guardrail. airflow dag copilot authoring works well when (a) you write a top-of-file module docstring naming purpose, schedule, upstream, owner, retry policy, SLA, and task list — the LLM scaffolds the DAG from that; (b) .cursorrules do-not clauses prohibit airflow.operators.python_operator (deprecated), mandate TaskFlow API (@dag/@task), and require mode='reschedule' + explicit timeout on every Sensor; (c) every DAG ships with a matching tests/dags/test_<dag_id>.py pytest fixture asserting import, task list, dependency graph, SLA, retries. CI runs a DAGBag import test that catches parse errors, plus pytest, plus a grep for deprecated APIs. With those three lanes in place, LLM-authored DAGs are as safe as hand-authored ones — the LLM just gets you to the reviewed draft ~5× faster. Without them, LLM-authored DAGs ship polling loops inside PythonOperators and deprecated operator imports on a regular basis.

What should a .cursorrules file contain for a dbt + Airflow repo?

Seven sections in this order. Role + voice — "you are pair-programming with a senior analytics engineer on the <repo> monorepo." Repo shape — the folder layout, dbt project structure, DAG folder. Naming conventions — model prefixes (stg_, int_, dim_, fct_), macro naming, DAG id conventions. SQL style + dialect — Snowflake / BigQuery / Postgres, CTE-first, ORDER BY at the end, QUALIFY over subqueries. dbt idioms — schema.yml required per model, incremental config template, macro documentation via {% docs %}. Airflow idioms — TaskFlow API only, sensor operators over polling loops, retry policy on network operators. Do-nots — the specific anti-patterns to prohibit (invented column names, deprecated APIs, hardcoded schema references). Guardrail expectations — what tools will check the completion (dbt parse, sqlfluff, human review) so the LLM self-checks. Keep the whole file under ~1500 tokens; version it in the repo root; review it in PRs whenever a convention changes. This is the highest-leverage config in the whole llm-native sql stack.

Is LLM-native SQL authoring safe for production data pipelines?

Yes, when the three-lane guardrail is in place; no, when any lane is missing. The compile lane (dbt parse + dbt compile --select state:modified --defer) catches unresolved refs and invalid Jinja. The lint lane (sqlfluff + sqlmesh diff with 2% row-count-drift threshold) catches style and semantic drift. The human review lane catches intent-code mismatches that neither compile nor lint can see. Every production incident traced to LLM-authored SQL — and every senior data engineer has at least one — maps to one or more missing lanes: the wrong-dialect hallucination that shipped because the precommit didn't check dialect, the invented column name that shipped because sqlmesh diff was non-blocking, the deprecated Airflow operator that shipped because the CI grep didn't exist. The pattern is always the same: identify the missing lane, add the check, grep the repo for the same pattern elsewhere. With three lanes and a weekly hallucination-rate metric, LLM-authored SQL is safer than hand-authored SQL because the lanes catch what humans miss; without three lanes, it's a source of slow-motion production incidents that only surface days later in dashboards or reconciliation reports. Configure the lanes; trust the workflow.

Practice on PipeCode

• Drill the SQL practice library → for the CTE, join, window-function, and aggregation problems that map directly onto the comment-first Copilot pattern.
• Rehearse on the SQL-generation practice library → for prompt-hygiene, schema-aware generation, and hallucination-resistance drills.
• Sharpen the pipeline axis with the ETL practice library → for the Airflow DAG scaffolding, sensor-driven, and incremental-load patterns senior interviewers love.
• Stack the design axis with the design practice library → for the repo-scoped tooling and guardrail-architecture scenarios that every LLM-workflow interview probes.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the .cursorrules + three-lane guardrail against real graded inputs.

Lock in LLM-native SQL authoring muscle memory

Docs explain features. PipeCode drills explain the discipline — when a comment-first prompt is worth its 30 seconds, when `.cursorrules` prevents a hallucinated column, when a three-lane guardrail catches what code review misses, when the docstring-first DAG scaffold turns a 60-minute task into a 10-minute review. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the LLM-native SQL, dbt, and Airflow authoring workflows senior data engineers actually ship.

Practice SQL problems →
Practice ETL problems →