Library · Data Practice OS
ADR-003: dbt as the transformation standard
Decision record · Proposed · Source reviewed
Status: proposed.
Context
Legacy ETL keeps business logic in tool-specific mappings, stored procedures and scheduler scripts that few people can read. We want transformation logic that is reviewable, testable, documented, and as portable as possible between Snowflake AI Data Cloud and Databricks Data Intelligence Platform. Both platforms also have native declarative options: Dynamic Tables and Lakeflow (Connect, Declarative Pipelines, Jobs) declarative pipelines.
Decision drivers
- Logic in version control, peer-reviewed, tested in CI (Data as code)
- Portability across both platforms, and through any later migration
- A large hiring pool and community
- Built-in tests, docs, Lineage and model contracts (Data contract)
Options considered
| Option | For | Against |
|---|---|---|
| A. dbt (Core or platform-hosted) | SQL-first, portable, testable; adapters for both platforms; model contracts; semantic layer | Another tool to run; streaming is not its job |
| B. Native only (Dynamic Tables / Lakeflow) | No extra tool; incremental refresh and streaming handled by the platform | Logic is tied to one platform; two dialects if both are used |
| C. GUI ETL tool | Familiar to some legacy teams | Logic stays hard to review and port. Recreates the problem we are leaving |
Decision
Option A, with a defined place for native tools:
- dbt owns batch transformation from silver to gold and all published data products, following dbt project standards.
- Native declarative pipelines are allowed for ingestion-adjacent and streaming work (bronze to silver), and dbt may materialise models as Dynamic Tables or streaming tables where the adapter supports it.
- One dbt project per domain, deployed by GitHub Actions (CI/CD and environments).
Consequences
- Migration converts legacy logic into dbt models, not into another proprietary tool (Code conversion).
- Write models in portable SQL where possible. Isolate dialect-specific code in macros.
- Whether to use dbt Core or dbt's hosted platform is a separate, later decision, driven by the semantic layer choice (ADR-006: Define metrics once in a semantic layer) and support needs.
Revisit when
- Native declarative tools on the chosen platform make dbt redundant for most models
- dbt licensing or product direction changes materially