The AI-Assisted Data Engineer: A Practical Daily Workflow

AI assistants have changed what a productive day looks like for a data engineer. Not because they write your pipelines for you — they don’t, reliably — but because they compress the boring 60% of the job: boilerplate, lookups, refactors, and docs.

Here is a workflow that holds up on production PRs, and the places where trusting the machine will burn you.

flowchart LR
  spec[Write the grain] --> gen[Generate SQL / dbt]
  gen --> run[Run against real data]
  run --> review[Review writes, retries, cost]
  review --> diff[Diff vs previous output]
  diff --> ship[Commit + docs]

Spec the grain. Run the SQL. Diff the output. Then commit.

Spec before you prompt

The usual failure is not syntax. It is an invented column or a wrong grain. Write three lines first, as a comment the assistant can see:

Grain: one row per customer_id per calendar_date
Inputs: raw.orders, raw.customers
Output: customer_id, order_date, gmv, orders, days_since_prev
Must: completed orders only; idempotent for a re-run of one date

Then paste the real column list. If the model has not seen the table, it will invent order_ts or country_code with complete confidence.

A prompt that works:

Table orders(order_id, customer_id, country, amount, status, ordered_at).
One row per order. Write DuckDB SQL: latest completed order per customer,
plus days since their previous completed order. Do not add columns that
are not in the table.

The more precisely you state the grain, the fewer hallucinated joins you get. Then run the SQL. Our SQL Playground is a zero-setup place for this kind of check before the snippet hits a PR.

Where AI genuinely saves hours

1. SQL scaffolding, not SQL authorship

Asking for “top 3 orders per country” gets a window-function skeleton in seconds. The pattern is the machine’s job; the business rule is yours. You still decide what “completed” means and which timestamp is the source of truth.

Use the assistant for:

• Window-function skeletons (row_number, lag, running totals)
• qualify vs subquery rewrites
• Unnest / array boilerplate you write twice a year

Do not use it as the author of a financial definition. That stays in the PR description, written by a human.

2. Translating between dialects

Moving a query from BigQuery to Snowflake, or T-SQL to Spark SQL, is mechanical work assistants do well. DATE_DIFF vs DATEDIFF argument order, QUALIFY support, array syntax — this is lookup-table knowledge.

Still verify:

• Date functions and time zones
• Semi-structured paths (: vs JSON_VALUE)
• Integer division and implicit casts
• Whether the target engine supports the window frame you used

A dialect port that “looks right” can still full-scan. See the Snowflake cost guide before you congratulate yourself.

3. dbt and Airflow boilerplate

Schema YAML with column descriptions and tests, a TaskFlow DAG skeleton with retries and alerting defaults, a Jinja macro you write twice a year — generate first, edit second. Anything you would have copied from an old project is a safe delegation.

Write a dbt schema.yml for fct_orders with unique + not_null on
order_id, accepted_values on status, and a relationship to dim_customers.
Do not invent extra columns.

Then open the YAML and delete anything you did not ask for. Assistants love extra description sentences that sound true and are not.

Airflow: generate the decorator DAG, then you add idempotency and the write path. The first pipeline tutorial is the checklist the model will skip (no giant XCom, retries, test before schedule).

4. Reading unfamiliar code

Pasting a 300-line stored procedure and asking “what is the grain of the output and which tables feed it?” is often faster than tracing it yourself. The assistant summarizes; you verify the two or three claims that matter — usually the grain, the join keys, and the filter that drops rows.

Ask for a lineage list, not a rewrite, until you understand the procedure. Rewrite second.

5. Documentation and PR descriptions

Column descriptions from a model’s SQL, first-draft runbooks, lineage summaries for a PR — the quality bar for a draft is low and the time saved is real. Correct it the same day, while you still remember what you meant.

Same rule as LLM documentation in pipelines: the model drafts, you approve.

The daily loop

1. Morning triage. Paste failing task logs for a first hypothesis before you open the repo. Wrong often enough that you still read the log. Right often enough that the habit pays.
2. Spec, then generate. Grain, inputs, outputs, idempotency. Then the prompt.
3. Generate → run → read. Never commit SQL you have not executed against real data. Row counts, null rates, and a select * of the edges (cancelled, $0, first-ever customer).
4. Refactor with a snapshot. Before an AI-assisted dbt change, snapshot current output (dbt show, a temp table, or a clone) and diff after.
5. End of day. Draft docs for what you shipped. Correct them while context is fresh.

flowchart TD
  fail[Failed task] --> paste[Paste log + runbook]
  paste --> hyp[Hypothesis]
  hyp --> verify[Open the actual SQL]
  verify --> fix[Fix + test]

The assistant drafts a hypothesis. You open the actual SQL before you restart anything.

Triage is pattern 4 from the LLM pipelines post, applied to your own day. Do not let the assistant restart the DAG from a guess.

Where it will burn you

Idempotency and writes. Generated MERGE statements frequently miss “what happens on rerun.” Late data, duplicate keys, delete handling — assistants optimize the happy path. Review anything that writes.

-- Ask the assistant, then you check:
-- 1. What is the unique key?
-- 2. What happens if the same date runs twice?
-- 3. What happens if a source row disappears?

A generated MERGE often answers (1) and skips (2) and (3). The boring version that survives a rerun is a partition replace:

delete from mart.fct_orders
where order_date = date '{{ ds }}';

insert into mart.fct_orders (
    order_id, customer_id, amount, status, order_date
)
select
    order_id,
    customer_id,
    amount,
    status,
    order_date
from staging.orders_clean
where order_date = date '{{ ds }}'
  and status = 'completed';

If those three answers are not in the SQL, the SQL is not done.

Invented columns and functions. Always provide the schema. Never trust an unverified identifier. A cheap gate: grep the generated SQL against information_schema or the dbt manifest.

Cost-blind SQL. Assistants write correct queries that scan full tables. They do not know your partition column unless you say so. A where date(created_at) = current_date() can pass review and still kill pruning.

Tell the prompt the partition or cluster keys. Then explain the plan.

Silent schema assumptions. Generated ingestion hard-codes today’s columns. Pair every generated load with tests or contracts so drift is loud.

Secrets in the prompt. Do not paste .env, warehouse passwords, or customer payloads into a cloud assistant. Use fake rows with the real shape.

Tests the model wrote and the model will pass. If it generated both the SQL and the unit test, write one assertion yourself (row count, grain, a known fixture).

Evals for assistant output

Treat generated SQL like an untrusted extract.

Check	How
Grain	count(*) vs count(distinct key)
Stability	Re-run; row count must match
Partition filter	Plan shows pruning / partition scan
Schema	Every column exists
Refactor diff	Old vs new output on a fixture date

-- fixture: one known date
select
    (select count(*) from new_fct where order_date = date '2026-08-01') as new_n,
    (select count(*) from old_fct where order_date = date '2026-08-01') as old_n;

If new_n and old_n disagree, the refactor is a business change. Stop and explain it in the PR, or revert. Do not “clean up” a 3% row drop because the assistant preferred an inner join.

For window functions, keep a tiny fixture (four rows) and compute the expected lag by hand. The window functions guide is the reference; the assistant is the typist.

Prompt patterns that stay useful

Dialect port

Rewrite this BigQuery SQL for Snowflake. Keep the grain. List every
function you changed. Do not add filters.

Explain only

What is the grain? Which join can drop rows? Do not rewrite.

Cost pass

This table is partitioned by event_date and clustered by account_id.
Rewrite only the WHERE clause so pruning works. Do not change the grain.

Idempotent load

Write a MERGE keyed on (account_id, event_date). Re-running the same
date must replace that partition only.

Short prompts with constraints beat a paragraph of “be careful.”

What you should still type yourself

• Metric definitions (revenue, active, churn)
• Access filters and row-level security
• Anything that deletes or overwrites gold
• On-call runbooks that page humans
• The three-line spec at the top of the model

The assistant is a fast junior with no warehouse access and no memory of last quarter’s incident. Superb at patterns. Dangerous on meaning.

Pitfalls

Accepting the first query that runs. Running is not correctness. Check grain and a known customer.

Letting it “simplify” a working incremental model. Incremental logic is where assistants drop is_incremental() filters.

Past entire prod schemas into the prompt. Give the three tables you need.

Using chat history as documentation. If the decision is not in the PR or the YAML, it does not exist next month.

Skipping the playground / warehouse run because the SQL “looks idiomatic.” Looks are not plans.

FAQ

Should I commit SQL I have not run? No. Row counts, null rates, and a known customer. “Looks idiomatic” is not a plan.

Can the assistant write the unit test for its own SQL? Write one assertion yourself — grain, row count, or a fixture. Tests the model wrote will pass for the model.

Is a dialect port safe to merge after it runs? Check date functions, integer division, semi-structured paths, and the query plan. Running is not pruning.

What belongs in the prompt? Grain, real column lists, partition keys. Not .env, not warehouse passwords, not customer payloads.

Should I let it simplify a working incremental model? No. That is where is_incremental() filters disappear.

When is the playground enough? For syntax and grain on a fixture. Cost and pruning still need the warehouse plan.

The takeaway

Delegate the mechanical: dialects, YAML, DAG skeletons, first-draft docs. Verify the material: grain, writes, retries, cost, and the diff after a refactor. Bank the hours. Do not ship the hallucinations.