Every data engineer who has been around long enough has lived through at least one platform migration. Oracle to Teradata. Teradata to Hadoop. Hadoop to Spark. Spark to Snowflake. The ritual is always the same: hundreds of stored procedures, ETL mappings, or pipeline scripts are translated from old syntax to new, delivering exactly the same business logic as before. Twelve to eighteen months. Dozens of engineers. Zero new business value delivered.

The painful part is not the rewriting. It is the rediscovery. Somewhere in those stored procedures, someone encoded that fund NAV must be calculated net of management fees, that the strategy lookup joins on a composite key with a temporal validity window, that records missing an inception date get quarantined rather than dropped. None of that was written down outside the code. The migration team reconstructs business rules from SQL comments and tribal knowledge, one pipeline at a time.

The business rules, quality thresholds, and data contracts with downstream consumers all stayed the same. Only the execution engine changed. Yet the entire programme existed because the logic was entangled with the technology that ran it.

This is the most expensive architectural failure in data engineering, and it keeps happening.

Everything in config is something you already need to know

Consider what goes into building a single curated data product. A fund master, say. You need to know which source systems feed it, and which fields from each source map to which fields in the canonical model. You need to know the data types, the rename conventions, and which fields to drop. You need to know which reference tables enrich the record, what the join keys are, and what happens when a lookup fails. You need to know the quality rules: which fields must never be null, which values are acceptable, what severity each violation carries. If multiple sources describe the same entity, you need to know the resolution strategy and the survivorship rules when sources disagree. You need to know what the published contract looks like, because downstream consumers depend on a specific schema.

All of that is true regardless of whether you implement the pipeline in dbt, Spark, Informatica, raw SQL, or anything else. The technology is the how. Everything above is the what, and you cannot build the pipeline without it.

Writing code by hand lets you skip collecting these requirements explicitly. You discover them as you go, embed them in SQL, and they become invisible. The field mapping lives in a CAST expression. The quality rule lives in a WHERE clause. The enrichment logic lives in a LEFT JOIN that nobody documented. It all works, until the platform changes and none of it can be found without reading every line of code.

A declarative pipeline configuration makes the same information explicit:

pipeline:
  name: "curated.fund_master"
  version: "1.0.0"
  owner: "data-platform"

steps:
  - pattern: "schema_transform"
    mappings:
      - source_field: "fund_nm"
        target_field: "fund_name"
        data_type: "varchar(200)"
      - source_field: "incptn_dt"
        target_field: "inception_date"
        data_type: "date"

  - pattern: "data_enrichment"
    reference_model: "ref_fund_strategy"
    join_on:
      - source_field: "strategy_cd"
        reference_field: "strategy_code"
    fallback:
      strategy: "default"
      value: "'UNMAPPED'"

  - pattern: "data_validation"
    rules:
      - field: "nav_per_share"
        test: "not_null"
        severity: "error"
      - field: "inception_date"
        test: "not_null"
        severity: "warn"

  - pattern: "data_contracts"
    columns:
      - name: "fund_id"
        data_type: "varchar(50)"
        constraints: [not_null, primary_key]
      - name: "fund_name"
        data_type: "varchar(200)"

Nothing in that configuration is specific to any engine. No Jinja. No Spark API. No SQL dialect. It captures the same information you would need to collect before writing a single line of code in any technology. The difference is that it captures it in a form that is reviewable, testable, and reusable rather than buried in implementation.

The discipline of requirements first

The declarative approach does not add work. It moves work forward that would happen anyway, and makes it visible.

Gaps surface in the config, not in production. A missing field mapping is obvious in YAML. A missing quality threshold is a gap in the validation section. An unspecified join key for an enrichment step is a config error, caught before any code is generated. Compare that with pipelines written by hand, where the same gaps surface as data quality incidents weeks after deployment.

The config is also reviewable by people who do not read SQL. A domain expert can look at a field mapping table and confirm that fund_nm should indeed become fund_name, that the strategy lookup is correct, that NAV per share should never be null. They cannot do the same review against a chain of CTEs with COALESCE expressions and window functions. The declarative format moves the quality conversation earlier and makes it accessible to the people who actually know the business rules.

Testing changes shape too. When the config is the specification, testing becomes verification against that spec. Does the output schema match the contract? Did every validation rule produce a corresponding test? Is every enrichment join implemented? These are answerable questions with binary outcomes. Contrast that with the typical approach, where “testing” means “the SQL runs without errors and the row count looks reasonable.”

Composable building blocks

The composition model matters as much as the separation. The idea of reusable transformation patterns is well established, from Kimball’s dimensional modelling stages through to modern data mesh thinking. I wrote about data framework patterns and their evolution in a series back in late 2024, working through the layered architecture, quality gates, and processing patterns that most data platforms need. The contribution here builds on that earlier work: making the patterns explicit, finite, and ordered as a configuration vocabulary rather than leaving them as architectural guidance that each team interprets differently.

Every curated data product is assembled from the same set of fifteen patterns: source declaration, schema transform, calculated fields, enrichment, filtering, entity resolution, validation, aggregation, contracts, lineage capture, metadata, schema publication, data publication, and checkpointing. Not every product uses every pattern. A simple product with one source might only need schema transform and contracts. A product with multiple sources and entity resolution needs curation and survivorship rules.

The patterns are finite, the ordering is constrained, and the configuration for each is standardised. This has a compounding effect. The first data product built this way takes comparable effort to writing it by hand. The tenth takes a fraction, because the patterns are proven and the configuration is the only variable. At scale, every product built this way is structurally consistent: the same CTE conventions, the same test coverage, the same documentation format, the same contract enforcement. That consistency is something no style guide or code review process achieves across a team of twenty engineers writing freehand SQL.

The translator problem

Separation is the principle. But someone still has to bridge the gap between the config and executable code for a specific platform. A fund master config still needs to become dbt models, or Spark scripts, or whatever the current engine requires.

The traditional answer is a code generator: a Python framework that reads config, applies templates, and emits artefacts for a specific platform. I have built versions of this over the years, in SSIS, in stored procedure generators, in Python, each time for a different platform and each time learning the same lesson again: the generator works, but it becomes its own product. It is deterministic and predictable. It also requires engineering effort to build, test, maintain, and extend as the target platform evolves. At the right scale, that is exactly the correct investment. An organisation with hundreds of data products and a stable platform gets enormous value from a deterministic generator that a team owns and evolves. For smaller teams, or teams in the middle of a migration who have not yet proven the pattern, the overhead is harder to justify.

There is now another option, and it is one I did not expect to work as well as it does.

Prompts as translators

An AI agent can serve as the target translator, as a full architectural component in the translation loop.

The translation from declarative config to platform code is mechanical in structure but varied in expression. Each platform has its own idioms, naming conventions, and optimisation patterns. An agent that understands both the YAML schema and the target platform’s practices can generate idiomatic code that reads like something a competent engineer would write. This works because the translation is constrained. The config defines what must happen. The agent expresses it in the target’s native syntax.

I built a prompt pack that does this for dbt: a generator prompt (~600 lines of structured translation rules), four reviewer prompts (200-300 lines each), and an orchestrator that manages the iteration loop. These are specifications, not code. They describe what correct output looks like and how to verify it, rather than how to produce it procedurally.

The critical design decision is trust. The agent is not trusted by default. Generated code is verified against the config through a structured review pipeline:

Config (source of truth) produces generated dbt models. Four reviewers run in sequence: platform best practices, pattern compliance (did every config step get implemented?), contract conformance (does the output match the declared contract exactly?), and SSOT compliance (is any business logic duplicated across models?). Each reviewer produces typed findings. Errors must be fixed before proceeding. The orchestrator iterates until the output passes all four reviews or hits a safety cap.

The generated code is disposable by design. Delete it and regenerate from the config. If the config changes, code is regenerated, not patched. The config is the asset. The generated code is the artefact.

The tradeoff is worth stating honestly. A prompt translator is less deterministic than a code generator. The same config might produce slightly different SQL on different runs. The review pipeline compensates: if the output passes all four reviews, matches the contract, and implements every config step, the exact SQL phrasing does not matter. But if you need identical output every time, a traditional generator is the right tool.

The economics favour the prompt approach in most cases. A code generator requires engineering effort to build and maintain. A prompt pack requires architectural effort to design and editorial effort to maintain. The latter is cheaper, faster to iterate, and requires expertise in the domain rather than in template engine internals.

Specification driven AI

Step back from the data engineering specifics and there is a broader pattern.

AI agents are most reliable when they work from a specification rather than from intent. “Build me a data pipeline” is intent. The output depends entirely on the agent’s interpretation, and interpretation is where hallucination lives. “Generate dbt models that implement this YAML config, following these translation rules, and verify the output against these four review criteria” is a specification. The output is constrained. Deviation is measurable. Quality is verifiable.

The IaC movement established this principle for infrastructure: declare desired state, verify actual state. The data contracts movement established it for schema: declare what consumers receive, verify what producers emit. The extension here is applying the same principle to the transformation logic between source and contract, and using AI as the execution layer rather than a bespoke framework.

The config serves four roles from a single artefact: requirements documentation (what the pipeline must do), generation input (what the translator reads), test specification (what the reviewers verify against), and migration insurance (what survives when the platform changes). One document, four uses. That only works because the config captures information you need regardless of implementation approach.

What this means practically

If you are building data products today, capture the requirements in a declarative format even if you write the SQL by hand. The config is valuable on its own: it is documentation that cannot drift from intent, and it is a review artefact that people outside engineering can validate. You do not need a framework or a prompt pack to get that benefit.

If you are planning a platform migration, audit how much of your existing pipeline logic is business rules versus plumbing specific to the current engine. The ratio is typically around 80/20. The migration is an opportunity to extract the business logic into config before translating it to the new platform. Do the extraction during migration and the next migration becomes a translator swap instead of a full programme.

If you are evaluating AI for data engineering work, point it at constrained translation problems rather than unconstrained generation. The difference in reliability is not incremental. An agent working from a specification with structured verification produces output you can trust after review. An agent working from a paragraph of intent produces output you have to rewrite. The specification is the difference.

The technology will change again. It always does. The business logic will still be there. The question is whether you will be able to find it.

The prompt pack described in this article, including the generator, four reviewers, orchestrator, config spec, and worked examples, is available as open source: dbt Data Product Builder.