The Modern Data Stack Explained: Warehouses, dbt, and BI

Most analytics failures trace back to one root cause: the data arrives late, inconsistent, or unmodeled. The modern data stack (MDS) fixes this by splitting the pipeline into modular, cloud-native layers: ingestion, storage, transformation, and consumption. Each layer swaps independently. Each scales on demand. And the transformation layer lives in version-controlled SQL instead of opaque ETL tools.

Quick Takeaways

• The core shift is ETL to ELT. Load raw data into a cloud warehouse first, then transform it inside the warehouse using its elastic compute.
• dbt is the transformation layer. It turns SQL SELECT statements into tested, documented, version-controlled models.
• BI tools consume modeled marts, not raw tables. Looker, Tableau, Power BI, and Metabase all perform better on star-schema marts than on raw ingestion tables.
• Start small. A warehouse, one ingestion tool, dbt, and one BI tool cover most teams under 50 people.

What Is the Modern Data Stack?

The modern data stack is a set of cloud-native, loosely coupled tools that move data from operational systems to analytical consumers. Each tool does one job well and connects through standard interfaces: SQL, REST APIs, and warehouse connectors.

Contrast this with the legacy approach. Traditional stacks relied on on-premise databases, monolithic ETL suites (Informatica, SSIS), and tightly bundled reporting servers. Scaling meant buying hardware. Changing a pipeline meant opening a GUI and hoping nobody else had it checked out.

The modern stack changes three things:

1. Storage and compute separate. You scale each independently and pay for usage.
2. Transformation moves into the warehouse. SQL replaces proprietary ETL logic.
3. Analytics engineering emerges as a discipline. Analysts apply software engineering practices (version control, CI/CD, testing) to data models.

ETL vs. ELT

ELT wins on flexibility. Because raw data stays in the warehouse, you can rebuild any model when business logic changes without re-extracting from the source.

Layer 1: Ingestion

Ingestion tools replicate data from SaaS apps, databases, and event streams into the warehouse. Two patterns dominate:

• Batch replication. Scheduled syncs pull incremental changes (via timestamps or change data capture).
• Streaming ingestion. Kafka, Kinesis, or Snowpipe Streaming deliver events with seconds of latency.

Managed connectors (Fivetran, Airbyte) handle schema drift, retries, and API rate limits. Build custom ingestion only when no connector exists.

Rule of thumb: land data in a raw schema, untouched. Never transform during load.

Layer 2: The Cloud Data Warehouse

The warehouse is the stack’s center of gravity. It stores raw and modeled data and executes all transformation SQL.

Warehouse Comparison

Cost Control Essentials

• Partition and cluster large tables on frequently filtered columns (usually date).
• Auto-suspend idle compute (Snowflake warehouses, Databricks clusters).
• Select only needed columns. Columnar storage charges for what you read.
• Set query and budget alerts before the first invoice surprises you.

Layer 3: Transformation with dbt

dbt (data build tool) compiles templated SQL into warehouse-native queries, resolves dependencies between models, and runs them in order. It does not extract or load data. It only transforms what already sits in the warehouse.

The Three-Layer Model Pattern

Project Structure

# Install dbt with the adapter for your warehouse
pip install dbt-core dbt-snowflake   # swap for dbt-bigquery, dbt-postgres, etc.

# Scaffold a project
dbt init analytics_project
cd analytics_project

# Verify warehouse connectivity
dbt debug

Staging Model Example

-- models/staging/stg_orders.sql
-- Purpose: standardize raw order data. No business logic here.

with source as (

    -- source() links to the raw table and enables lineage tracking
    select * from {{ source('shop_raw', 'orders') }}

),

renamed as (

    select
        id                              as order_id,
        customer_id,
        cast(created_at as timestamp)   as ordered_at,
        lower(status)                   as order_status,
        cast(total_cents as numeric) / 100 as order_total_usd
    from source

)

select * from renamed

Source Declaration and Tests

# models/staging/_sources.yml
version: 2

sources:
  - name: shop_raw
    database: raw
    schema: shop
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

# models/staging/_staging.yml
version: 2

models:
  - name: stg_orders
    description: "One row per order, cleaned and typed."
    columns:
      - name: order_id
        description: "Primary key."
        tests:
          - unique
          - not_null
      - name: order_status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled', 'refunded']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

Mart Model with Incremental Materialization

-- models/marts/fct_orders.sql
-- Incremental: only process new or updated rows on each run.

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='append_new_columns'
    )
}}

with orders as (

    select * from {{ ref('stg_orders') }}

    {% if is_incremental() %}
    -- Only pull rows newer than the latest row already in this table
    where ordered_at > (select max(ordered_at) from {{ this }})
    {% endif %}

),

customers as (

    select * from {{ ref('stg_customers') }}

)

select
    o.order_id,
    o.customer_id,
    c.customer_segment,
    o.ordered_at,
    o.order_status,
    o.order_total_usd
from orders o
left join customers c using (customer_id)

Core dbt Commands

Why dbt Changed Analytics

• Version control. Every model change is a Git commit with a reviewable diff.
• Testing. unique, not_null, accepted_values, and relationships catch bad data before dashboards do.
• Lineage. The ref() function builds a dependency graph automatically.
• Documentation. Descriptions live beside the code and compile into a browsable site.
• CI/CD. Run dbt build --select state:modified+ on every pull request to validate only the changed models.

Layer 4: Orchestration

Orchestrators schedule ingestion, trigger dbt runs, and handle failure alerts.

A minimal Airflow DAG that runs dbt after ingestion:

from datetime import datetime
from airflow import DAG
from airflow.operators.bash import BashOperator

with DAG(
    dag_id="daily_dbt_build",
    start_date=datetime(2026, 1, 1),
    schedule="0 6 * * *",   # 06:00 UTC daily
    catchup=False,
    default_args={"retries": 2},
) as dag:

    freshness = BashOperator(
        task_id="check_source_freshness",
        bash_command="cd /opt/dbt/analytics_project && dbt source freshness",
    )

    build = BashOperator(
        task_id="dbt_build",
        bash_command="cd /opt/dbt/analytics_project && dbt build --target prod",
    )

    freshness >> build   # build only after freshness passes

Layer 5: Business Intelligence

BI tools sit on top of marts. Their job is visualization, exploration, and self-service access.

BI Tool Comparison

The Semantic Layer

A semantic layer defines metrics once (revenue, active users, churn rate) and serves them to every tool. Without one, “revenue” means something different in each dashboard. Options include:

• dbt Semantic Layer (MetricFlow). Define metrics in YAML; query through supported BI tools and APIs.
• LookML. Looker’s modeling language.
• Cube. Headless, tool-agnostic metrics API.

# models/marts/_metrics.yml (MetricFlow-style definition)
semantic_models:
  - name: orders
    model: ref('fct_orders')
    defaults:
      agg_time_dimension: ordered_at
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    dimensions:
      - name: ordered_at
        type: time
        type_params:
          time_granularity: day
      - name: customer_segment
        type: categorical
    measures:
      - name: order_total
        agg: sum
        expr: order_total_usd

metrics:
  - name: total_revenue
    label: Total Revenue
    type: simple
    type_params:
      measure: order_total

Real-World Use Cases

Use Case 1: E-Commerce Revenue Reporting

Scenario: A retailer pulls orders from Shopify, ad spend from Google Ads, and refunds from Stripe.

Pipeline: Fivetran lands all three sources in Snowflake’s raw database. dbt staging models standardize timestamps and currencies. A fct_orders mart joins them. Looker exposes a single total_revenue metric.

Result: Finance and marketing see identical revenue numbers. Month-end reconciliation drops from days to hours.

Use Case 2: Customer Churn Analysis

Scenario: A SaaS company wants to predict which accounts will cancel.

Pipeline: dbt builds a dim_accounts table with feature columns (login frequency, support tickets, seat utilization). Data scientists read that mart into Python:

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.ensemble import GradientBoostingClassifier
from sklearn.metrics import roc_auc_score

# Pull a tested, documented feature table straight from the warehouse
df = pd.read_sql("select * from analytics.marts.dim_accounts_features", con=engine)

X = df.drop(columns=["account_id", "churned_90d"])
y = df["churned_90d"]

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, stratify=y, random_state=42  # stratify preserves class balance
)

model = GradientBoostingClassifier(n_estimators=300, learning_rate=0.05)
model.fit(X_train, X_train.shape[0] and y_train)

# AUC-ROC evaluates ranking quality across all thresholds
auc = roc_auc_score(y_test, model.predict_proba(X_test)[:, 1])
print(f"AUC-ROC: {auc:.3f}")

Result: Feature logic lives in one tested dbt model. Training and scoring pipelines reuse it, which removes training-serving skew.

Use Case 3: Real-Time Operational Monitoring

Scenario: A logistics company tracks vehicle sensor streams.

Pipeline: Kafka streams events into the warehouse via Snowpipe Streaming. dbt incremental models aggregate to five-minute windows. Dashboards refresh on a short schedule.

Result: Operations teams detect anomalies within minutes without a separate streaming infrastructure.

Common Pitfalls

Minimal Stack Build Checklist

1. Pick a warehouse matching your cloud provider and workload.
2. Connect ingestion for your top three source systems.
3. Initialize dbt with staging, intermediate, and marts folders.
4. Add tests to every primary key and critical foreign key.
5. Schedule ingestion and dbt build with an orchestrator.
6. Connect one BI tool to the marts schema only.
7. Add CI so every pull request runs dbt build against changed models.
8. Monitor cost with warehouse budgets and query alerts.

Frequently Asked Questions

What is the modern data stack?

The modern data stack is a collection of cloud-native tools for ingestion, storage, transformation, and analytics. Data loads into a cloud warehouse (Snowflake, BigQuery, Redshift), dbt transforms it with SQL, and BI tools like Looker or Tableau visualize the results.

What does dbt do in the modern data stack?

dbt transforms data already inside the warehouse. It compiles SQL models, manages dependencies through ref(), runs data quality tests, and generates documentation. It does not extract or load data.

What is the difference between ETL and ELT?

ETL transforms data before loading it into the destination. ELT loads raw data first and transforms it inside the warehouse. ELT preserves raw history, scales with warehouse compute, and allows reprocessing without re-extracting from sources.

Do I need all layers of the modern data stack?

No. A warehouse, a managed ingestion tool, dbt, and one BI tool cover most small and mid-size teams. Add orchestration, a semantic layer, and observability tools as complexity grows.