AI-Enabled Data Migration: Generating Verified dbt Models, From Scratch and Incrementally

Circini Limited · Data Engineering

Data migration is often the longest pole in any data-platform program. The heavy lifting — translating a mapping document into reliable transformation logic — is slow, repetitive, and unforgiving of small errors.

So we set out to answer a focused question: can generative AI take a mapping document and produce complete, reliable dbt models — under strict governance, with a human expert in the loop?

The short answer is yes. Below are two use cases we validated, the four-step flow that keeps the output consistent, and the controls that keep it trustworthy.

What is AI-enabled data migration?

flowchart LR
  A["Mapping document"] --> B["Structured prompt"]
  E["Existing SQL (incremental)"] --> B
  B --> C["Generative AI"]
  C --> D["Draft dbt model / SQL"]
  D --> V{"SME validation & tuning"}
  V -->|"Needs changes"| B
  V -->|"Approved"| S["Verified model shipped"]

AI-enabled data migration is the use of generative AI to produce and update the transformation code — here, dbt (data build tool) models and SQL — that moves and reshapes data from source to target. Existing mapping specifications are the input, and a subject-matter expert stays in the validation loop. The goal isn't to remove engineers; it's to compress the most repetitive part of migration while keeping human judgment where it matters.

One governed flow, applied to every script

Both use cases run through the same four steps:

1. Input — the mapping document (plus existing SQL, for updates).
2. Enhance — descriptive, structured prompting frames the task and the expected logic.
3. Validate — an SME reviews the proposed logic in a human Q&A loop.
4. Generate — complete, verified dbt / SQL is returned.

The validation step is the trust anchor. Structured prompting and good inputs get you a strong draft; the SME makes it production-ready.

Use case 1 — Initial load script generation

Here the AI builds complete dbt models from scratch, using the mapping document as the single source of truth. We used it to generate migration scripts for areas such as Additional Info and Contracts. The input is a mapping document; the output is generated SQL, reviewed before use.

Use case 2 — Incremental additions

The second use case handles change. We feed the existing SQL plus the mapping into the same flow, and the AI returns an updated model that adds new fields and updates — without a full rebuild. A representative example: adding Supplier Performance Reports to an existing model.

What makes the output reliable?

Three lessons stood out:

• Prompt engineering matters. Structured prompts significantly improve output quality and logic.
• Input quality is everything. AI performance directly reflects the precision of the mapping documents — vague inputs produce vague models.
• Human validation is non-negotiable. SME oversight remains essential for verifying generated SQL models and tuning them.

Security and governance, by design

Because this involves enterprise data, the guardrails came first, not last:

• Policy alignment — all work was conducted under the official company AI policy.
• Data privacy — every term was masked; no sensitive data was used.
• Security first — testing happened only within approved boundaries.

In practice that means anonymising all terminology to prevent data leakage, keeping data strictly within approved cloud environments, and applying a security-first approach to every code-generation task.

Why it matters

• Development efficiency — accelerated model building from scratch and faster incremental updates.
• Governance — full alignment with company AI policy and data masking.
• Scalability — a proven flow for generating verified dbt models at scale.

Frequently asked questions

#DataMigration · #GenerativeAI · #DataEngineering · #dbt · #DataGovernance · #SQL · #DataPlatform · #AIAdoption · #ETL