Data pipeline & analytics-engineering code generation (ETL/ELT, dbt, Spark)

Data teams spend most of their time writing and maintaining transformation code, tests, documentation and orchestration, and fixing pipelines that broke overnight. AI assistants that see warehouse metadata, lineage and the existing project can draft dbt models and PySpark jobs, add tests and docs, and propose fixes for failed runs, while a data engineer reviews every change in a pull request. Typical benefit: faster delivery of routine models and far better test and documentation coverage, not unattended production changes.

Problem & value

• Today: engineers hand-write staging, intermediate and mart models, copy boilerplate YAML for tests and column descriptions, hand-build DAGs, and triage failures from logs. Documentation and tests lag behind; knowledge sits in a few heads.
• Why AI helps: pipeline code is highly structured (SQL, YAML, Python), conventions are explicit (dbt layering, naming), and the platform supplies rich context (schemas, lineage, run history). Boilerplate generation, test and doc authoring, and first-pass debugging are the best-fit tasks.
• Evidence: vendors document generation of documentation, tests, semantic models, metrics and SQL from natural-language prompts inside the dbt Studio IDE [1]. Independent, audited productivity numbers specific to data engineering are scarce; measure on your own repository rather than relying on vendor claims.
• Distinct from: DATA-001 (business users querying data in natural language), DATA-003/008 (cleaning and quality monitoring), CODE-013 (schema design and query tuning). This case is pipeline engineering as a software discipline.

Variants

• Analytics engineering (dbt/SQL): model, test, doc and semantic-layer generation on Snowflake, BigQuery, Databricks, Redshift, DuckDB, or EU warehouses.
• Lakehouse / Spark: PySpark and declarative pipeline (Lakeflow Spark Declarative Pipelines) authoring, incremental and streaming jobs [2].
• Orchestration: DAG generation and repair for Airflow, Dagster, Prefect, including backfill and retry logic.
• Pipeline debugging agent: reads failed task logs, query history and recent commits, and proposes a fix PR (overlaps CODE-010/011).
• Migration: converting legacy ETL (stored procedures, SSIS, Informatica, Talend) to dbt/Spark (see CODE-005, CODE-021).
• SMB: a single analyst using an IDE assistant plus dbt Core and DuckDB/Postgres; no platform team.

How it works

1. ticket / requirement + catalog, lineage & schema context (MCP / metadata API)
2. plan (sources, layers, grain, keys, tests)
3. generate code (dbt SQL + YAML, PySpark, DAG)
4. compile & dry-run in a dev/sandbox schema (limited sample, no production write)
5. run generated tests + data-diff vs. current output + SQL lint (sqlfluff)
6. pull request with lineage impact and cost estimate
7. human review & CI (state-aware slim CI)
8. merge
9. scheduled run
10. failure agent: logs + query history
11. hypothesis
12. fix PR

• Context is the key: quality depends on exposing schemas, column descriptions, existing project conventions and lineage to the model, usually via an MCP server or metadata API (dbt provides an MCP server; Databricks Assistant uses Unity Catalog metadata) [1][7].
• Grounded execution: the agent compiles and runs in a sandbox and uses test and data-diff results as feedback, instead of trusting generated SQL.
• Human-in-the-loop: every change lands as a reviewed pull request; production credentials and write permissions are never given to the agent; schema-breaking changes and PII-touching models need explicit approval.

Data preparation

The AI needs context, not raw rows: table and column definitions, dbt projects, existing SQL and orchestration code, data dictionaries and business rules. In an SMB these live in the ERP/CRM databases, a warehouse (or just a Postgres/SQL Server), Excel files on file shares, and in the heads of one or two people.

Typical gaps: undocumented tables with cryptic column names, no tests or data contracts, duplicate "truth" in several spreadsheets, inconsistent KPI definitions, no staging environment, and production data with personal fields available to the assistant.

Preparation steps: 1. Export schemas and add column descriptions, at first with AI help and expert review (PREP-012, PREP-011). 2. Write a KPI glossary and a few gold-standard queries (PREP-016). 3. Put pipelines into Git and add basic dbt tests. 4. Give the assistant a dev/staging copy with pseudonymised or synthetic data (PREP-022). 5. Restrict credentials to read-only on metadata.

Tools: dbt docs, DataHub or OpenMetadata, Great Expectations. Share of effort: medium, around 30 to 40 percent, because the quality of generated pipelines depends directly on documented schemas. See DATA-003, DATA-008 and GUIDE-003 for readiness checks.

Implementation paths

Which path for whom

• smb (A): Copilot, Cursor or warehouse-native assistants (dbt, Snowflake, Databricks) work out of the box for a small data team on a standard stack.
• enterprise (B): Pipelines run against governed warehouses and internal standards, so an own agent via API with metadata, lineage and guardrails fits best.
• developer (C): An open-weight code model with Continue/Aider keeps schemas and queries local and costs only hardware.

Path A — Buy (recommended for most teams)

• dbt platform: dbt Copilot (docs, tests, semantic models, metrics, SQL) and the agentic dbt Wizard preview [1]. dbt Labs is US-based; check EU region options.
• Lakehouse vendors: Databricks Assistant and Genie Code with Lakeflow pipelines, Snowflake Cortex Code / Copilot, Google BigQuery Gemini data engineering agents, Microsoft Fabric Copilot [7].
• Specialised: Paradime (dbt-focused IDE), Keboola (Czech, EU), Datafold (data-diff for AI-generated changes), Coalesce.
• General coding agents: Claude Code, Cursor, GitHub Copilot, JetBrains AI with dbt/Spark skills or MCP servers (see CODE-001/002).
• Choose A when: you already run one of these platforms; the assistant is a licence upgrade, not a project.

Path B — Build on cloud APIs

• Models: frontier code models (Claude, GPT, Gemini) or Mistral (Codestral, EU provider) on EU regions (Bedrock Frankfurt, Azure OpenAI EU, Vertex EU, Mistral La Plateforme).
• Orchestration: a coding agent (Claude Agent SDK, LangGraph) with tools for dbt compile/run/test, warehouse dry-run, catalog lookup (OpenMetadata, DataHub, Unity Catalog) and Git/PR APIs; trigger from a ticket or a failed Airflow/Dagster run.
• Guardrails: read-only sandbox credentials, row-limit and cost caps, masking of PII columns before samples reach the model.
• Choose B when: you want failure-triage agents or ticket-to-PR flows tailored to your repository.

Path C — Self-host (open source)

• Open-weight code models: Qwen3-Coder, DeepSeek-V3-class, Codestral/Devstral (Mistral, EU), gpt-oss; served with vLLM or Ollama, driven by Continue, Aider, OpenHands or Cline.
• Open data stack: dbt Core or SQLMesh, Apache Spark, Airflow/Dagster, DuckDB, OpenMetadata/DataHub for lineage context, sqlfluff and dbt-expectations/Great Expectations for checks.
• Hardware: HW2 for ~30B code models on single-file tasks; HW3 for repository-scale agentic work with larger models.
• Choose C when: source data is regulated (health, finance, public sector) and metadata or samples may not leave your network.

Path D — Custom training

Rarely justified. Fine-tuning on an organisation's own dbt conventions and merged PR history can improve style adherence, but retrieval of project conventions plus rules files usually gets most of the gain. Needs thousands of reviewed, merged examples.

State of the art (2026)

• Assistants are shifting from autocomplete to agents that plan, run commands and iterate on test failures; dbt, Databricks and Snowflake each ship agentic modes on top of their metadata layers [5][7].
• Strong: staging models, renames and casts, boilerplate tests, column documentation, simple joins and aggregations, Spark to SQL translation, DAG scaffolding.
• Weak: business-logic correctness (grain, fan-out joins, slowly changing dimensions, late-arriving data), silent semantic errors that still compile and pass shallow tests, idempotency and incremental-model edge cases, and cost-inefficient queries.
• Mitigations that work: data-diff against the previous output, uniqueness/not-null/relationship tests on every key, contract-enforced models, and semantic-layer definitions as a governed source of metric truth.
• Published limits: dbt notes Copilot does not yet cover the latest semantic YAML spec, and requires human review [1].
• Benchmarks for data-engineering agents are still immature; treat public scores cautiously and build an internal eval set from your own tickets (see CODE-018).

Shared resources & combinations

• Same stack as CODE-001/002 (IDE and PR agents), CODE-004 (test generation), CODE-006 (documentation), CODE-013 (SQL tuning), CODE-021 (refactoring at scale).
• Metadata layer (catalog + lineage, e.g. OpenLineage, OpenMetadata) is shared with DATA-001 (NL-to-SQL), DATA-003 and DATA-008 (quality monitoring), and is the most valuable investment for all of them.
• Downstream: CODE-010/011 (incident detection and log analysis) feed the failure-repair agent; CODE-009 for infrastructure of the data platform.
• Evaluation and tracing: CODE-018.

Effort, cost & KPIs

• Path A: days to weeks; per-seat or consumption pricing on top of the platform.
• Path B: 1-3 months for a ticket-to-PR agent with sandbox, CI and catalog integration; token cost is small relative to engineer time, warehouse compute for agent test runs is the hidden driver.
• Path C: 2-4 months plus GPU operations.
• KPIs: lead time ticket-to-merge, share of models with tests and documentation, PR rejection/rework rate for AI-generated changes, escaped data incidents, mean time to repair failed runs, warehouse cost per change.

People & change

Involve: management and the head of data/IT decide scope and budget. Data owners of ERP, CRM and finance sources must approve which schemas and sample data the assistant may see. IT provides environments and credentials; the DPO is needed if staging data contains personal data (customers, employees), including AVV with the tool vendor and, for large-scale personal data, a DPIA check (GUIDE-005).

Works council: depends. Assistants log prompts and activity per developer, which makes the tool objectively suitable for monitoring (§87(1) no. 6 BetrVG, BAG). Tenant-level analytics per person should be disabled or regulated in a works agreement; §90(1) no. 3 BetrVG requires timely information. An expert may be engaged under §80(3). Public sector: Personalrat.

AI literacy: support the data team (Art. 4 AI Act) with training on verifying generated SQL, spotting silent logic errors and not pasting production personal data into prompts (GUIDE-006, GUIDE-007).

Pilot and communication: pick one analytics engineer and one non-critical pipeline; compare review time and defects for four weeks. Concerns: "my job is automated" and "wrong numbers reach management." Answer: AI drafts, humans review and own every merge, tests gate deployment (GUIDE-008, GUIDE-009).

Risks, regulation & compliance

• EU AI Act: minimal risk (developer tooling, no Annex III use); AI literacy duty (Art. 4) applies to the team [3]. If the generated pipelines feed a high-risk system (credit scoring, HR), data governance duties (Art. 10) apply to that system, not to the coding tool.
• GDPR: schema metadata is usually non-personal, but samples, query results and logs sent to model providers can contain personal data. Mask or synthesise samples, use EU regions with a data processing agreement, and apply data minimisation (Art. 5, 25, 28 GDPR) [4]. Generated pipelines must preserve retention, deletion and access-control logic.
• Security: prompt injection via column comments, table contents or ticket text; over-privileged credentials; secrets in logs. Use least-privilege roles and no production write access.
• Quality and lineage: silent logic errors are the main business risk; require tests, data-diff and review. Keep audit trail of AI-authored commits.
• Licences: check code-model licence and vendor terms for generated code; EU Data Act and cloud portability favour keeping pipelines in open formats (dbt, SQL, Iceberg/Delta).

Getting started

1. Pick a bounded domain (one source system, one mart) with an existing dbt project or Spark repo and clear conventions; write them into a rules file.
2. Expose catalog, schema and lineage metadata to the assistant (MCP or platform-native) with masked samples only.
3. Start with docs and tests generation, then staging models, then marts; always in a dev schema with CI.
4. Add data-diff and contract tests to CI so AI-generated changes are checked automatically.
5. Track KPIs for 4-6 weeks, then add a failure-triage agent for read-only diagnosis before allowing fix PRs.

Sources

1. dbt Labs — Use dbt Copilot (docs, tests, semantic models, SQL) — https://docs.getdbt.com/docs/platform/use-dbt-copilot
2. dbt Labs — Develop with AI in the Studio IDE — https://docs.getdbt.com/docs/platform/studio-ide/develop-copilot
3. Regulation (EU) 2024/1689 (AI Act) — https://eur-lex.europa.eu/eli/reg/2024/1689/oj
4. Regulation (EU) 2016/679 (GDPR) — https://eur-lex.europa.eu/eli/reg/2016/679/oj
5. dbt Labs — About dbt Wizard — https://docs.getdbt.com/docs/cloud/dbt-copilot
6. dbt Labs — Introducing dbt Copilot — https://www.getdbt.com/blog/introducing-dbt-copilot
7. Databricks documentation — Lakeflow Spark Declarative Pipelines — https://docs.databricks.com/aws/en/dlt/
8. OpenLineage project — https://openlineage.io/