migrating-matillion-to-dbt

Migrating Matillion to dbt

This skill migrates Matillion pipelines/jobs into a governed dbt project — reproducing the transformation logic as dbt models, snapshots, and macros with tests, docs, and contracts, then proving parity against warehouse data.

The core approach: Matillion is a push-down ELT tool — a transformation pipeline is a graph of components (Table Input, Calculator, Filter, Aggregate, Join, Rank, Unite, Rewrite Table…) that Matillion compiles to SQL and executes in the target warehouse. dbt does the same thing. That makes a Matillion transformation pipeline map onto dbt models almost 1:1. We inventory every component, translate using the component answer key, validate each output against the warehouse, and report coverage and cost.

Scope — what maps and what doesn't:

• Transformation pipelines (.tran.yaml, or transformation jobs in METL JSON) → dbt models. This is the migratable workload and the coverage denominator.
• Orchestration pipelines (.orch.yaml) → mostly out of dbt's scope. Their data-load components (S3 Load, Database/API/connector Query) are EL ingestion (keep in a loader / EL tool); Run Transformation steps become the dbt DAG/run; scheduling and control flow move to a platform job. Document these; do not force them into models.

Success criteria: Migration is complete when:

1. dbt compile finishes with 0 errors and 0 warnings
2. Every generated model builds (dbt build) and its tests pass
3. Data parity is proven for each mart (row-for-row or aggregate baseline — see Step 5)
4. ≥95% of the inventoried transformation components are migrated and validated, with the residual (and the out-of-scope orchestration/EL pieces) explicitly listed

Validation cost: dbt compile is the free iteration gate. Only dbt build, dbt test, and the parity queries touch the warehouse — run those after compile is clean.

This skill shares its workflow with the legacy-to-dbt-migration-foundations skill; steps below link into its references for the common work. Assume the migrator may be new to dbt — explain each dbt concept (materializations, incremental, snapshots, contracts, Fusion) in plain language as it comes up (foundations → dbt-concepts-explained.md), and explain the reason behind each choice, not just the mechanics.