ETL to ELT with dbt Blueprint
A blueprint to move from opaque ETL to in-warehouse ELT with dbt: load raw data via managed connectors, rebuild transforms as tested layered dbt models, and validate against legacy outputs before cutover.
Overview
Traditional ETL transforms data in a separate engine before loading it into the warehouse, which is opaque, hard to test, and limited by the ETL server. ELT loads raw data first and transforms it inside a modern cloud warehouse using SQL. dbt (data build tool) makes those SQL transformations modular, version-controlled, tested, and documented. This blueprint migrates ETL jobs to a dbt-based ELT workflow.
Phases
Assessment. Inventory existing ETL jobs and their transformation logic. Confirm the target warehouse (Snowflake, BigQuery, Redshift, or Postgres) can push down the transforms. Identify lineage and downstream consumers.
Ingestion setup. Replace the extract-and-transform step with extract-and-load. Use a managed loader (Fivetran, Airbyte, or native connectors) to land raw data into a staging schema unchanged.
Model migration. Re-implement transformation logic as dbt models in layered fashion: staging, intermediate, and marts, following the medallion (bronze/silver/gold) approach. Use ref() for dependencies so dbt builds the DAG and lineage automatically.
Testing. Add dbt tests (unique, not_null, relationships, and custom tests) and dbt docs for documentation. Validate outputs against the legacy ETL results.
Cutover. Schedule dbt runs (dbt Cloud, Airflow, or CI), point downstream consumers at the new marts, and retire the old ETL jobs.
Key Risks and Mitigations
- Data consistency: Run dbt and legacy ETL in parallel and diff outputs until they match.
- Logic translation: Proprietary ETL transforms may hide edge-case logic. Migrate model-by-model with tests capturing expected behavior.
- Skills gap: dbt and SQL-centric pipelines are a shift for ETL-tool teams. Train on the dbt style guide and Jinja templating.
Recommended Tooling
dbt Core or dbt Cloud for transformations and tests; Fivetran/Airbyte for EL ingestion; GitHub Actions or Airflow for orchestration; dbt docs and exposures for lineage.
Success Metrics
Shorter pipeline lead time, higher transformation test coverage, and improved data freshness from in-warehouse processing.
Prerequisites
A modern cloud warehouse with compute to push down transforms, a managed EL loader, version control for dbt models, and a parallel-run validation plan.