AI-Accelerated Pipeline Development

AI assistants write plausible pipeline code in seconds; the distance between plausible and production is context in, verification out. This module teaches both halves: feeding assistants the schema and metadata context that makes them generate your code instead of generic code, and the verification workflows that catch what "almost right" data code reliably gets wrong.

The scenario — one dbt model, two Mondays

Monday, without the discipline. Meena is a senior data engineer at a subscription-commerce company — dbt, Snowflake, Airflow, 240 models.

Monday, with it. Same ticket, 09:40 — but Meena spends twelve minutes assembling a context pack before generating anything.

Same assistant, same model. The difference was fifteen minutes of context and twenty minutes of verification replacing three hours of spreadsheet archaeology — and knowing which twenty minutes, because data code fails in predictable places.

The context pack — what you feed the assistant decides what you get back

Here is the difference in prompt terms. The one that produced Meena's bad Monday:

Write a dbt model for monthly subscription revenue by plan, net of refunds.

And the shape that produced her good one:

Build a dbt model `fct_subscription_revenue`.

## Warehouse context
<DDL for subscriptions, payments, refunds — real column names and types>
<5 sample rows per table>

## Project conventions
- staging models: stg_<source>__<entity>; marts read only from staging
- surrogate keys via dbt_utils.generate_surrogate_key
- style exemplar: <paste an existing fct model>

## Business rules
- Revenue month = IST calendar month (timestamps are stored UTC)
- refunds join to payments via charge_id, NOT payment_id
- Grain: one row per plan_code per month

After the model, list every assumption you made that this context does not settle.

The principle: assistants do not fail for lack of intelligence, they fail for lack of your metadata — and a model generated without schema context is a guess wearing SQL syntax. A context pack is DDL, sample rows, conventions, one exemplar, and explicit business rules; the closing "list your assumptions" line converts silent guesses into a reviewable list. Metadata-aware tools automate part of this assembly: dbt Copilot generates SQL, tests, and docs from your project's own metadata, and Databricks Genie Code operates with workspace context across notebooks and the SQL editor. For repo-level assistants — Claude Code, Cursor, GitHub Copilot — a checked-in CONTEXT.md per mart (sources, grain conventions, timezone policy, known traps) means every future prompt starts warm.

The loop: generate, interrogate, verify, commit

Meena's good Monday followed a repeatable four-step loop, and it is worth making explicit because the steps people skip are exactly the ones that hurt:

1. 1Generate from context. Model SQL, schema tests, and docs come from the same context pack in one pass — dbt Copilot does this from project metadata with a click; in Claude Code or Cursor you ask for all three explicitly. Generating tests separately from the model, later, from memory, is how tests end up testing nothing.
2. 2Interrogate before you run. Ask the assistant three questions about its own output: What is the grain of this model? Which business rule was I least sure about? What input would make this produce wrong numbers without erroring? The answers cost thirty seconds and routinely surface the assumption that becomes next week's incident.
3. 3Verify with fixtures, not eyeballs. Run the mechanical pass from the taxonomy later in this module — twice-run diff, null fixture, timezone boundary fixture, grain uniqueness. Reading generated SQL top to bottom feels like verification; it is proofreading, and quiet bugs survive proofreading.
4. 4Commit context with code. When verification catches something the context pack should have prevented — a trap, a convention, a business rule — the fix goes into the pack, not just the model. This is the compounding step: every caught bug makes the next generation better.

Where does the loop stop being worth it? A rough division of labor:

The gradient runs on blast radius and quietness of failure: the more silently a bug would corrupt data, the more the human owns.

Small generation wins: parsers and connectors

The quickest daily returns are the fiddly, low-stakes generation tasks. Gnarly ingestion parsing first — hand the assistant three real sample records and make it fight its own output:

Here are 3 sample lines from a fixed-width mainframe export:
<samples>
Field spec: positions 1-8 account_id, 9-16 posting_date (YYYYMMDD), 17-28
amount (implied 2 decimals, leading zeros, trailing '-' means negative).

1. Write a Python parser returning typed records.
2. Then write 10 adversarial test cases that would break a naive parser:
   short lines, non-ASCII, all-spaces amount, date 00000000, trailing-minus.
3. Show me which of your test cases your own parser fails.

Step 3 is the trick: the model is good at generating hostile inputs and honest about running its own code against them, which turns "generate a parser" into "generate a parser plus its falsification attempt." The same context-first pattern scaffolds API and CDC connectors: paste the relevant pages of the API docs into the prompt and require the boring production furniture — cursor-based pagination, rate-limit backoff with jitter, retry-with-dead-letter, and an idempotent upsert on the natural key. Assistants omit exactly these unless asked, because most public example code omits them too. Any frontier assistant handles this; Airbyte's AI connector builder covers the same ground when you want the output landing in an existing connector framework.

For CDC specifically, add two lines to every scaffold prompt:

• How are deletes represented?tombstones, soft-delete flags, or absent entirely; the answer changes the sink logic.
• What happens on replay?the connector will re-deliver, so the load must be an upsert, never an append.

Generated connector code that appends CDC events into a table works perfectly until the first redeploy, after which every metric downstream is double-counted — a bug you find at quarter close, not in review.

Repo-scale refactors — agentic editing with a safety net

The 900-line monolith model that joins eleven sources and computes four grains is where agentic assistants earn their subscription. The refactor workflow that works:

1. 1Write the target structure down firststaging → intermediate → marts, naming rules, one model per grain — in a file the agent reads. You are the architect; the agent is the hands.
2. 2Let the agent propose the split as a plan, not a diff. Review the plan: which CTEs become stg_ models, which become int_, what each model's grain is.
3. 3Execute model-by-model, compiling and testing after each extractionnot one 4,000-line diff at the end. Claude Code and Cursor both sustain this loop; dbt Copilot helps regenerate tests and docs for each extracted model.
4. 4Gate the merge on equivalence, not vibes: the refactored mart must produce identical output to the monolith. Run a data diff (Recce or a SELECT ... EXCEPT pair in both directions) between old and new relations.

The same loop applies style and convention enforcement across a repo — renaming to conventions, adding missing unique/not_null tests, standardizing incremental configs — which is exactly the tedious-but-mechanical work that used to be nobody's sprint priority. The rule that keeps it safe: an agentic refactor of working pipelines is only as trustworthy as the equivalence check behind it, so the diff gate is not optional ceremony, it is the merge criterion.

Translation work: pandas to PySpark, with proof

The prompt pattern for code translation is translation plus round-trip evidence:

Convert this pandas job to PySpark. Then generate a pytest that runs BOTH
versions on the same fixture DataFrame (include nulls, duplicate keys, and
an empty partition) and asserts the outputs are identical after sorting.
Flag any pandas behavior that has no exact Spark equivalent.

The flag request matters because the two libraries disagree in quiet ways:

• Row orderpandas preserves it, Spark does not.
• Null representationpandas NaN and Spark null behave differently in comparisons and groupbys.
• Joins on null keyspandas merge and a Spark join produce different rows.
• Integer columnssilently become floats in pandas when nulls appear.

A translation that compiles is not a translation that agrees — the round-trip test on a hostile fixture is what promotes it. Databricks Genie Code does this conversion in-workspace with catalog context; Claude Code or Copilot in the IDE work anywhere, fed the same fixture discipline.

The "almost right" taxonomy — where AI data code bites

Generated data code fails in a short, learnable list of places. This table is the verification pass Meena ran; it should become muscle memory:

Two of these deserve their own workflows. Incremental conversion (full refresh → incremental) is a cost win the assistant can draft — give it the model plus table stats (row counts, update patterns, how late data arrives) and ask for a strategy proposal: materialization (merge vs insert_overwrite), unique_key, and lookback window, each with a one-line justification. Then you validate with the only evidence that counts:

-- Parity: full-refresh build vs incremental build over the same period
(SELECT * FROM analytics.fct_orders_full_refresh WHERE order_date >= '2026-06-01'
 EXCEPT SELECT * FROM analytics.fct_orders_incremental WHERE order_date >= '2026-06-01')
UNION ALL
(SELECT * FROM analytics.fct_orders_incremental WHERE order_date >= '2026-06-01'
 EXCEPT SELECT * FROM analytics.fct_orders_full_refresh WHERE order_date >= '2026-06-01');
-- Zero rows in both directions, or the incremental strategy is wrong

Streaming scaffolding is the highest-stakes case, because correctness lives in semantics no compiler checks. Let the assistant scaffold the Kafka consumer or Structured Streaming job — it produces good boilerplate — but review against an explicit checklist you paste into the generation prompt itself:

• Delivery guaranteestated, and matched to the sink (idempotent writes or transactional output for effective exactly-once).
• Watermarkjustified against measured event lateness rather than defaulted.
• Late-data pathdefined: drop, side-output, or upsert.
• State expiryconfigured, so the job does not grow unboundedly.

Generated streaming code defaults to the happy path — a ten-second watermark because tutorials use ten seconds, foreachBatch writes that duplicate on retry — and each default looks fine in a demo where events arrive on time and nothing fails. The assistant drafts; the engineer owns semantics — nowhere is that division sharper than streaming.

AI code review for pipeline PRs

Generic AI review comments on style; DE review needs the taxonomy above encoded as a checklist the reviewer cannot skip. Check this into your repo (Claude Code picks it up via /review conventions; CodeRabbit and GitHub Copilot code review accept repo-level instructions):

Review this pipeline PR as a senior data engineer. For each changed model/job,
answer explicitly — "not applicable" is an answer, silence is not:
1. Idempotency: safe to run twice? Safe to backfill a partition?
2. Grain: stated? Is there a uniqueness test proving it?
3. Nulls: any filter/join where a null silently changes membership?
4. Timezones: every DATE_TRUNC/DATE() call — what timezone, and is it the
   business's or the server's?
5. Incremental: unique_key correct? Late data within lookback? Deletes handled?
6. Cost: full scans a partition filter would prevent? SELECT * into wide marts?
7. Contract: any column rename/retype/drop a downstream consumer sees?

Position AI review as the first reviewer, not the last: it runs on every PR within minutes, catches the mechanical 70%, and leaves human review for grain choices and business logic — the part that actually needs a human. Roughly half of engineering postings now name reviewing and debugging AI-generated code as a core skill; this checklist is that skill made concrete for data work.

Triaging the review output is a skill of its own, because AI reviewers fail in both directions.

• They over-flagstyle nitpicks, defensive-coding suggestions on code paths that cannot occur; burying a real idempotency finding under twelve trivia comments trains the team to skim.
• They under-flag anything requiring business contextthe reviewer cannot know that plan_code = 'LEGACY' rows must be excluded from revenue, because that fact lives in a conversation, not the diff.

The mitigations are mechanical: tune the checklist to suppress categories you routinely dismiss, and keep the business rules in the checked-in context pack, where the reviewer can see them. The failure mode to manage above all is automation complacency — an AI approval is an input to your review, never a substitute, and the moment "the bot passed it" ends the conversation, you have stopped reviewing.

Check yourself: An AI-generated dbt model builds green, its row counts look plausible, and it passed code review — name the specific quiet traps you must still check before trusting its numbers, and why review missed them.

Where this lands in your week

• Monday morningevery non-trivial model or job starts with a context pack, not a cold prompt, and the pack for each mart lives in the repo so it compounds.
• Dailyparsers, connectors, and conversions are generated with their adversarial tests and round-trip proofs in the same breath, never accepted bare.
• Every PRthe DE review checklist runs as the first reviewer; your verification pass (run-twice, null fixture, timezone fixture, grain uniqueness, incremental parity) runs before you ask a human for their time.
• Monthlyone agentic refactor of the ugliest model in the repo, gated on a data diff.

On cost, one honest note for India-based engineers: a single paid assistant (~₹1,700/month for Claude-, Cursor-, or Copilot-class tooling) is the one recurring spend this track asks for — everything else in this module runs on dbt Core, open-source tooling, and free warehouse tiers.

This module anchors LAB-01 — AI-accelerated dbt build-out: given a raw e-commerce schema, use an assistant to build staging→marts models, generate tests and docs, then run the structured verification pass to catch three seeded errors in the AI's output. The artifact — a dbt repo with CI passing plus your written verification log — is the evidence that you can both accelerate with AI and catch what it gets wrong, which is the pair employers are actually screening for. Do this at work this week → pb-01 Generate a dbt Model from a Spec with Schema in Context, pb-03 Generate Ingestion Parsers and Connector Scaffolds from Samples, and pb-09 Review Pipeline PRs with a Data-Engineering AI Checklist.