Modern Data Pipeline Stack: dbt, Airflow & Analytics Engineering

The Modern Data Stack

ELT (Extract, Load, Transform) replaced ETL: Extract raw data → Load to cloud warehouse → Transform with SQL using dbt. Benefits: leverage warehouse compute power, version-controlled transformations, testable data models, clear separation of concerns.

Stack components: Fivetran/Airbyte (extraction), Snowflake/BigQuery/Redshift/Databricks (storage + compute), dbt (transformation), Airflow/Prefect/Dagster (orchestration), Great Expectations/dbt tests (quality), Looker/Metabase/Tableau (BI).

dbt: SQL Transformation Layer

Model Organization

Sources (raw data from warehouse) → Staging models (clean, typed, one model per source table) → Intermediate models (join and aggregate staging) → Mart models (business-ready, denormalized for BI).

Naming convention: stg_ prefix for staging, int_ prefix for intermediate, fct_ prefix for fact tables, dim_ prefix for dimension tables. Example: stg_shopify_orders → int_order_items → fct_orders + dim_customers.

dbt Model Example

A staging model for orders: select cast columns to correct types (order_id as bigint, order_placed_at as timestamp), rename for consistency (created_at → order_placed_at), add basic cleaning (coalesce(status, 'unknown')), and reference the raw source with {{ source('shopify', 'orders') }}.

A mart model for monthly revenue: reference stg_orders with {{ ref('stg_orders') }}, filter to completed orders, group by month, sum revenue. Use {{ ref() }} for model dependencies—dbt builds the correct DAG.

dbt Testing

Data tests in schema.yml: not_null and unique on primary keys, accepted_values for status fields, relationships between tables (referential integrity), custom singular tests for business logic.

Run dbt test in CI/CD to prevent bad data from reaching production. dbt will fail with a detailed error message if any test fails.

dbt Documentation

Define column descriptions in schema.yml. Run dbt docs generate and dbt docs serve to create a searchable documentation site with lineage graph. Every model and column documented in code, not a separate wiki.

Apache Airflow for Orchestration

DAG Design Principles

Each task does one thing (extraction, transformation, quality check). Use idempotent tasks (safe to rerun). Store DAG code in Git. Use Airflow's retry mechanism for transient failures. Implement SLAs for critical pipelines.

Production DAG Example

Daily ELT pipeline DAG: extract_shopify_data (PythonOperator calling Fivetran API to trigger sync) → wait_for_sync (FivetranSensor waiting for completion) → run_dbt_staging (BashOperator: dbt run --select staging) → run_dbt_tests (BashOperator: dbt test --select staging) → run_dbt_marts (BashOperator: dbt run --select marts) → notify_slack (SlackWebhookOperator on success). On failure: alert PagerDuty.

Modern Alternatives

Prefect: Python-native, easier local testing, hybrid execution model. Dagster: asset-based orchestration (define data assets not tasks), built-in data lineage, stronger type system. For greenfield projects in 2025, consider Dagster for its superior observability.

Data Quality with Great Expectations

Expectation Suites

Define expectations for critical data: expect_column_values_to_not_be_null for required fields, expect_column_values_to_be_between for numeric bounds (age between 0-150), expect_column_value_lengths_to_be_between for string lengths, expect_column_distinct_values_to_be_in_set for categorical fields, custom SQL expectations for business rules.

Checkpoint: run expectations against incoming data batch. On failure: stop pipeline, alert data team, do not allow bad data to reach BI tools.

Data Observability

Monte Carlo, Acceldata, or dbt's built-in freshness checks monitor: table freshness (was this table updated on schedule?), volume anomalies (unexpected row count change), schema changes (field added, removed, type changed), value distribution shifts.

CI/CD for Data Pipelines

On every PR to dbt models: run dbt compile (check SQL syntax), run dbt test on staging data (validate logic), generate dbt docs (check documentation completeness), run slim CI (only test changed models + downstream dependencies). Merge only after all checks pass.

Deployment: promote to production after PR approval. Run full dbt build in production environment. Alert if any production test fails.

DataOps Culture

Version control everything: dbt models, Airflow DAGs, Great Expectations suites, all in Git. Code review data models like software code. Document every model and column. Define SLAs and alert on violations. Build a data team culture where data quality is everyone's responsibility.

The modern data stack enables data teams to move as fast as software engineers while maintaining the quality standards business decisions require.