Data Warehouse ETL Pipeline: Modern Data Stack Architecture Guide [2026]

Data warehouse projects fail 60% of the time — not because the technology fails, but because teams underestimate what it takes to build a reliable ETL pipeline. The warehouse platform is 30% of the problem; the pipeline design, data quality controls, and orchestration are the other 70%.

This guide covers the full data warehouse ETL pipeline stack: architecture choices (ETL vs ELT, batch vs streaming), platform selection (Snowflake, BigQuery, Databricks), transformation with dbt, orchestration with Airflow, and the data lineage and governance layer that enterprise teams require. By the end, you will have a clear map of what a production-grade data warehouse ETL pipeline looks like and where most implementations go wrong.

Data Warehouse ETL Pipeline: Core Architecture

Modern data warehouse architecture centers on a simple premise: separate storage from compute, and run transformations inside the warehouse rather than outside it. This is the ETL-to-ELT shift that cloud warehouses enabled.

ETL (Extract, Transform, Load) is the traditional approach. Data is extracted from sources, transformed in a separate staging environment (a dedicated ETL server or tool), then loaded into the warehouse. Informatica, IBM DataStage, and older Talend deployments follow this pattern. It works, but the staging layer adds cost, latency, and a brittle middle tier.

ELT (Extract, Load, Transform) reverses the transform step. Raw data lands in the warehouse first, then SQL-based transformations run inside the warehouse engine. Snowflake, BigQuery, and Redshift are purpose-built for this. Compute scales independently of storage, transformations run in parallel across distributed compute, and the entire pipeline is observable from one place.

The shift from ETL to ELT is not just a naming convention — it changes where compute lives, who owns transformation logic, and how data teams debug failures. Most greenfield data warehouse projects built after 2020 use ELT by default.

Batch vs. Streaming: Choosing the Right Processing Mode

Batch processing moves data on a schedule: hourly, daily, or weekly. A daily Airflow DAG that runs at 2:00 AM, pulls the prior 24 hours of records, applies dbt transformations, and loads aggregates into the reporting layer — that is batch. It covers 90% of analytics use cases and is significantly cheaper to operate than streaming infrastructure.

Streaming (real-time) processing handles data as it arrives. Kafka ingests events, Spark Streaming or Flink applies transformations, and results land in the warehouse within seconds. Fraud detection, anomaly alerting, and real-time personalization require streaming. But streaming infrastructure costs more and introduces operational complexity that most teams underestimate.

The right decision rule: if business users can wait an hour for data freshness, use batch. If the business value degrades measurably if data is more than 60 seconds old, evaluate streaming. Most companies that deploy streaming pipelines on their first data project do so prematurely — batch is the right starting point.

Medallion Architecture: Bronze, Silver, Gold

The Medallion Architecture (popularized by Databricks) provides a layered model for organizing data inside the warehouse:

Bronze layer — raw, unmodified source data. Every record from every source lands here, preserving original schemas and values. No transformation, no filtering. This layer is the audit trail.

Silver layer — cleaned, validated, and joined data. Null values handled, data types cast, duplicates removed, referential integrity enforced. dbt runs here, and Great Expectations validations fire before any record graduates to Silver.

Gold layer — aggregated, business-ready tables. Fact tables for revenue, patient appointments, or order metrics. Dimension tables for customers, products, locations. This is what BI tools and dashboards query.

In production ML systems at Smart Maple — including a healthcare scheduling application that processes thousands of appointment records daily — we use the Bronze-Silver-Gold pattern to maintain a clean audit trail at Bronze while keeping the Silver and Gold layers consistently reliable for downstream consumers.

Platform Selection: Snowflake vs. BigQuery vs. Databricks

The warehouse platform choice shapes the entire pipeline architecture. Three platforms dominate modern data warehouse ETL pipeline deployments.

Snowflake

Snowflake separates storage (S3/GCS/Azure Blob) from compute (virtual warehouses). You pay for compute only when queries run, and you can spin up multiple virtual warehouses for different workloads without contention. The Data Marketplace enables sharing data across organizations without moving it.

Best for: SaaS companies, healthcare, financial services — organizations with structured analytical workloads, multi-tenant data sharing requirements, and teams that want operational simplicity over raw performance tuning. The SQL-first interface is accessible to analytics engineers without distributed systems expertise.

Cost profile: Compute-per-second billing. A 50-person company running daily batch pipelines typically spends $200–500/month on Snowflake compute, with storage separate.

Google BigQuery

BigQuery is serverless — no virtual warehouse management, no cluster sizing. Queries run against a fully managed columnar engine. The integration with Google Cloud's ML stack (Vertex AI, Dataflow) is tight. Pricing is per-byte-scanned for on-demand queries or flat-rate slots for predictable billing.

Best for: Analytics-heavy organizations already in Google Cloud, teams running ad-hoc exploratory queries at scale, and use cases where BigQuery ML or Vertex AI integration is a requirement.

Cost watch: Ad-hoc queries that scan large unpartitioned tables generate unexpected costs. Partition pruning and column selection discipline are mandatory for cost control.

Databricks (Delta Lake)

Databricks is a unified analytics platform built on Apache Spark, using Delta Lake as the open table format. It handles batch and streaming in one engine, supports ML workflows natively, and uses open formats (Parquet + transaction log) that avoid vendor lock-in.

Best for: Organizations that need both analytics and ML workloads on the same data, teams with Spark expertise, and use cases requiring streaming and batch on a single platform. Higher operational complexity than Snowflake or BigQuery.

dbt: SQL-First Transformation

dbt (data build tool) is the transformation layer in most modern data warehouse ETL pipelines. It lets data engineers write SQL SELECT statements and handles the plumbing: compiling to the target SQL dialect, running in dependency order, materializing results, and executing tests.

A dbt model is a SQL file that defines a transformation. Dependencies between models form a DAG. dbt resolves that DAG and runs models in the correct order. No manual dependency tracking, no procedural scripts.

dbt's test framework applies data quality checks automatically:

• unique — every value in the column is distinct
• not_null — no null values
• accepted_values — values match an explicit allowed list
• Custom tests — SQL-based assertions for domain rules (appointment duration > 0, balance >= 0)

The Medallion Architecture maps directly onto dbt project structure: staging/ models clean raw Bronze data into Silver, marts/ models aggregate Silver into Gold reporting tables. Version-controlled in Git, deployed through CI/CD — dbt makes the transformation layer a software engineering artifact, not an ad-hoc SQL script collection.

Airflow: Pipeline Orchestration

Apache Airflow is the industry-standard orchestration platform for data warehouse ETL pipelines. A DAG (Directed Acyclic Graph) defines tasks and their dependencies. Airflow schedules and monitors execution, handles retries, sends alerts on failure, and provides a UI for inspecting pipeline state.

A typical healthcare pipeline DAG illustrates the pattern:

Extract — pull yesterday's appointment records from the production PostgreSQL database using an incremental query (records modified after the last successful run). Incremental extraction avoids re-processing historical data on every run.

Validate — run Great Expectations checks against the raw extract: appointment_id completeness >99%, patient_age within 0–150, status in allowed values. Failures halt the pipeline before bad data enters the warehouse.

Load — upsert records into the Bronze layer of Snowflake. Records with existing appointment_ids update; new records insert.

Transform — trigger dbt to run staging and mart models, applying Silver-layer cleaning and Gold-layer aggregation. dbt tests run automatically as part of this step.

Alert — on any task failure, Airflow sends an email or Slack notification to the data engineering team with the task name, error message, and DAG run link.

Data Lineage and Governance

Data lineage tracks the path from every source record to every downstream report. When a CEO dashboard shows unexpected revenue numbers, lineage answers: did the error come from the CRM extract, the dbt transformation, or the BI layer?

Without lineage, debugging a data quality incident requires manually tracing through pipeline code. In a large warehouse with hundreds of tables, this takes days. With lineage tooling (dbt Docs, OpenLineage, DataHub, or Collibra), the dependency graph is visible in minutes.

Data catalog is the companion to lineage. A catalog stores column-level definitions, data owner contacts, freshness SLAs, and quality metrics for every table in the warehouse. Analytics engineers find the table they need; data owners receive alerts when their table's quality drops below threshold.

Governance roles that production pipelines require:

• Data owner — a named person responsible for each source table's quality and definition. Not a role on the data team; a business stakeholder in the originating system.
• Data steward — monitors quality metrics, investigates failures, coordinates between data engineers and business owners.
• Analytics engineer — owns Silver and Gold layer transformations in dbt, maintains test coverage, reviews model changes.

Common Data Warehouse ETL Pipeline Failures

Most data warehouse ETL pipeline failures trace to five predictable root causes, observed consistently across enterprise engagements:

1. No data quality gate before the warehouse. Teams load raw data directly into the warehouse and discover quality problems weeks later when a report shows nonsense values. The fix: validate at ingestion, before Bronze. Great Expectations or dbt source tests catch problems at the origin.

2. Schema change propagation. An upstream application adds a column, renames a field, or changes a data type. The pipeline silently drops the new data or crashes. The fix: schema validation contracts (dbt source definitions with explicit column schemas) that fail loudly when upstream schemas drift.

3. No incremental extraction. Full-table extracts that re-pull all historical data daily. At small scale, this is slow. At enterprise scale (hundreds of millions of rows), it becomes impossible. The fix: design incremental extraction from day one using watermark columns (updated_at, created_at).

4. Transformation logic in application code. Business rules embedded in ETL scripts instead of dbt models. When rules change, they change in the script only, and the warehouse reflects stale logic. The fix: consolidate all transformation logic in dbt — version-controlled, tested, visible.

5. No monitoring or alerting. Pipelines fail silently over weekends. Data freshness degrades. BI users notice stale dashboards on Monday morning. The fix: Airflow alerting on task failure, Great Expectations integration for data quality alerts, and a data observability tool (Monte Carlo or Soda) for automatic anomaly detection.

Technology Stack Summary

A production-ready data warehouse ETL pipeline for a 50–500 person organization:

Layer	Tool	Role
Orchestration	Apache Airflow	DAG scheduling, monitoring, alerting
Ingestion	Fivetran / Airbyte	Connector-based ELT from SaaS sources
Storage	Snowflake or BigQuery	Cloud data warehouse
Transformation	dbt Cloud	SQL-first transformations, testing
Lineage	dbt Docs + OpenLineage	Column-level lineage tracking
Quality	Great Expectations	Row-level validation at ingestion
BI	Looker / Metabase	Dashboard and reporting layer

This stack represents the modern data warehouse ETL pipeline pattern that has become standard for organizations scaling past spreadsheets and into reliable, auditable analytics infrastructure. The components are modular — organizations can start with Fivetran + Snowflake + dbt and layer in observability and governance as the data platform matures.

Warehouse Design: Kimball vs. Inmon

Inside the warehouse, how tables are organized shapes query performance and analytics flexibility. Two classic design philosophies define the choices:

Kimball architecture (dimensional modeling) organizes data around business processes. A central fact table (orders, appointments, transactions) is surrounded by dimension tables (customer, product, date, geography). Queries are simple star joins. BI tools connect directly to fact and dimension tables without complex view layers. For SaaS companies with 10–500 users, Kimball is the default recommendation: fast to build, fast to query, easy for analysts without advanced SQL skills.

Inmon architecture (enterprise data warehouse) builds a highly normalized central warehouse from which department-specific data marts are derived. The central warehouse preserves full historical fidelity; the marts optimize for departmental reporting. Better for large enterprises with multiple business units that need shared reference data. More expensive and slower to build than Kimball.

The practical guidance: SaaS companies and growth-stage organizations should default to Kimball. Organizations with 10+ business units sharing reference data and strict historical audit requirements should evaluate a hybrid approach — normalized core warehouse with Kimball-style marts for reporting.

In the context of a data warehouse ETL pipeline, the design philosophy determines how dbt models are structured. Kimball implementations use dbt's staging layer to create Silver-equivalent cleaned models, then marts to build the star schema Gold layer. The pipeline design follows the data model.

Conclusion

A reliable data warehouse ETL pipeline requires more than platform selection. It requires a clear architecture (ELT not ETL, Medallion layers), a transformation discipline (dbt with test coverage), orchestration with observability (Airflow with alerting), and a governance layer (lineage, catalog, data owners) that scales as the organization grows.

The 60% project failure rate drops sharply when teams invest equally in pipeline design, data quality gates, and organizational structure — not just in choosing the right cloud warehouse. The technology choices are table stakes; the engineering discipline and governance practices are the differentiator.