Chapter 44: Analytical SQL & Data Warehousing

Why Dimensional Modeling?

Relational normalization — decomposing data into third normal form (3NF) to eliminate redundancy — is ideal for transactional systems where writes dominate. For analytics, however, 3NF schemas are painful: a single business question might require joining ten or fifteen tables, the query plans are complex, and the SQL is hard to reason about. Dimensional modeling, popularized by Ralph Kimball, trades some write efficiency for dramatically better analytical ergonomics and query performance.

The core idea is to organize data around business processes (e.g., a sale, a web page view, a support ticket opened) rather than around entities. Each business process becomes a fact table whose rows represent individual events or measurements at a consistent grain. Surrounding the fact table are dimension tables that provide descriptive context.

Facts, Measures, and Grain

A fact table row represents one occurrence of a business event at a defined grain. Choosing the right grain is the most important modeling decision: it determines what questions the table can answer. Common choices are:

• Transaction grain: one row per individual sale line-item. Most flexible — any aggregation is possible.
• Snapshot grain: one row per entity per time period (e.g., one row per customer per day showing their account balance). Useful for period-over-period analysis.
• Accumulating snapshot grain: one row per business process instance that gets updated as the process moves through stages (e.g., an order that passes through placed → shipped → delivered).

Fact columns are either measures (additive numbers like revenue, quantity, duration<em>seconds) or foreign keys to dimension tables. Additive measures can be summed across any dimension. Semi-additive measures (e.g., account balance) can be summed across some dimensions but not time. Non-additive measures (e.g., ratios, percentages) should be stored as their components and computed at query time.

Dimension Tables

Dimension tables hold the who, what, where, when, why, and how of each event. They are typically wide (many columns, often 50–100) and relatively short (millions of rows at most, compared to billions in a fact table). Dimensions always have a surrogate primary key — a system-generated integer — separate from the natural key from the source system. This decoupling is essential for slowly changing dimensions (covered in the next section).

A date dimension is nearly universal. Pre-populating it with derived columns (day of week, fiscal quarter, isholiday, etc.) moves expensive EXTRACT and CASE logic out of every query:

CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,  -- e.g., 20240315
    full_date       DATE NOT NULL,
    year            SMALLINT,
    quarter         SMALLINT,
    month           SMALLINT,
    month_name      VARCHAR(9),
    week_of_year    SMALLINT,
    day_of_week     SMALLINT,
    is_weekend      BOOLEAN,
    is_holiday      BOOLEAN,
    fiscal_year     SMALLINT,
    fiscal_quarter  SMALLINT
);

Star Schema vs. Snowflake Schema

In a star schema, every dimension table connects directly to the fact table. The schema looks like a star when drawn as an entity-relationship diagram. Dimension tables are deliberately denormalized: a dim<em>product table might contain both product</em>name and category<em>name even though category is logically a separate entity.

In a snowflake schema, dimension tables are normalized — dim</em>product holds a foreign key to dim<em>category, which holds a foreign key to dim</em>department, and so on. The schema looks like a snowflake when drawn.

Which to choose? Star schemas are almost always preferred for analytics:

• Fewer joins means simpler SQL and faster queries.
• Modern columnar warehouses cache dimension tables aggressively; the storage cost of denormalization is negligible.
• Analysts can understand the schema without knowing the internal normalization hierarchy.

Snowflake schemas are occasionally justified when a dimension table is extremely large (tens of millions of rows) and the normalized sub-dimension adds significant filtering, or when ETL processes write to dimension sub-tables independently.

A Worked Example: E-Commerce Sales

Suppose an operational database has tables orders, order_items, customers, products, and stores. The business question is: "What was daily revenue by product category and store region?"

-- Fact table at order-line grain
CREATE TABLE fct_sales (
    sale_key        BIGINT PRIMARY KEY,
    date_key        INT REFERENCES dim_date(date_key),
    customer_key    INT REFERENCES dim_customer(customer_key),
    product_key     INT REFERENCES dim_product(product_key),
    store_key       INT REFERENCES dim_store(store_key),
    quantity        INT         NOT NULL,
    unit_price      NUMERIC(10,2) NOT NULL,
    discount_amount NUMERIC(10,2) NOT NULL DEFAULT 0,
    revenue         NUMERIC(10,2) GENERATED ALWAYS AS
                        (quantity * unit_price - discount_amount) STORED
);

-- Answering the business question becomes a simple two-join query
SELECT
    d.full_date,
    p.category_name,
    s.region,
    SUM(f.revenue)  AS total_revenue
FROM fct_sales f
JOIN dim_date     d ON f.date_key    = d.date_key
JOIN dim_product  p ON f.product_key = p.product_key
JOIN dim_store    s ON f.store_key   = s.store_key
GROUP BY 1, 2, 3
ORDER BY 1, 4 DESC;

This query is readable, efficient, and would have required five or six joins against the normalized operational schema. The fact/dimension split makes the analytical layer both faster and more maintainable.

The Problem: Dimensions Are Not Static

Customers move cities. Products are re-categorized. Sales reps transfer to new regions. Any dimension attribute can change over time, and how you handle those changes determines whether historical reports remain accurate. Slowly changing dimensions (SCDs) are the set of standard patterns for managing this problem.

Type 1: Overwrite

The simplest approach: just update the existing row. No history is kept.

-- A customer changes their email
UPDATE dim_customer
SET    email = 'new@example.com',
       updated_at = CURRENT_TIMESTAMP
WHERE  customer_key = 4219;

When to use it: For corrections (a typo in a name), or when history genuinely does not matter (e.g., a phone number used only for outbound contact). All historical fact rows will now join to the updated attribute value.

Pitfall: If you later ask "what region was this customer in when they made their purchase in 2022?", a Type 1 dimension cannot answer that — the old value is gone.

Type 2: Add a New Row

Type 2 is the workhorse of SCD management. When an attribute changes, close the current row by setting an end date and an is<em>current flag, then insert a new row with a new surrogate key, the updated attribute, a start date, and an open end date.

-- Step 1: Close the old row
UPDATE dim_customer
SET    valid_to   = CURRENT_DATE - INTERVAL '1 day',
       is_current = FALSE
WHERE  customer_nk = 'C-8801'   -- natural key
  AND  is_current  = TRUE;

-- Step 2: Insert the new row
INSERT INTO dim_customer (
    customer_nk, first_name, last_name, city, state,
    valid_from, valid_to, is_current
)
VALUES (
    'C-8801', 'Maria', 'Santos', 'Austin', 'TX',
    CURRENT_DATE, DATE '9999-12-31', TRUE
);

Now fact rows that were loaded when the customer lived in Boston carry the surrogate key that points to the Boston row, and new fact rows carry the Austin surrogate key. Queries automatically get the right historical snapshot:

-- Revenue by the customer's city AT TIME OF PURCHASE
SELECT c.city, SUM(f.revenue)
FROM   fct_sales f
JOIN   dim_customer c ON f.customer_key = c.customer_key
GROUP  BY 1;

No date-range join is needed because the surrogate key already encodes the historical version.

Schema requirements for Type 2:

• customer</em>key — surrogate primary key (changes with each version)
• customer<em>nk — natural key (stable across versions)
• valid</em>from — inclusive start date
• valid<em>to — exclusive or inclusive end date (choose one convention and stick to it)
• is</em>current — Boolean flag for the latest version (avoids filtering on valid<em>to = &#039;9999-12-31&#039; everywhere)

Common pitfall: forgetting to use the natural key when updating — using the surrogate key will close only one version, not all active ones.

Type 3: Add a New Column

Type 3 tracks only the current and previous value of a specific attribute. Instead of new rows, you add columns:

ALTER TABLE dim_customer
    ADD COLUMN prev_city   VARCHAR(100),
    ADD COLUMN city_changed_date DATE;

-- On change:
UPDATE dim_customer
SET    prev_city        = city,
       city             = 'Austin',
       city_changed_date = CURRENT_DATE
WHERE  customer_nk = 'C-8801';

Type 3 is rarely the right choice because it handles only one change and only for pre-designated columns. It is occasionally useful for "as-of" queries where you want to compare current vs. prior values without a full Type 2 history explosion.

Type 4 and Hybrid Approaches

Type 4 uses a separate history table alongside the current-state dimension. The main dimension always reflects the current record; a dim</em>customer<em>history table stores all prior versions. This keeps the main dimension clean for analysts who rarely need history while preserving the full audit trail.

Modern warehouse tools like dbt expose a snapshot command that automates Type 2 logic — given a source query and a unique key, dbt manages valid</em>from, valid<em>to, and dbt</em>scd<em>id columns automatically:

# snapshots/customer_snapshot.yml
snapshots:
  - name: customer_snapshot
    config:
      strategy: timestamp
      unique_key: customer_nk
      updated_at: updated_at
      target_schema: snapshots

-- snapshots/customer_snapshot.sql
{% snapshot customer_snapshot %}
    {{ config(target_schema='snapshots') }}
    SELECT * FROM {{ source('ops', 'customers') }}
{% endsnapshot %}

After dbt snapshot runs, the resulting table contains dbt</em>valid<em>from, dbt</em>valid<em>to, and dbt</em>scd_id columns, fully managed.

Choosing the Right Type

• Use Type 1 when the change is a data correction or history is contractually irrelevant.
• Use Type 2 as the default for any attribute where historical accuracy matters.
• Use Type 3 only when you need exactly one prior value for a specific attribute and disk/query complexity is a concern.
• Consider dbt snapshots or a purpose-built CDC (change data capture) system when managing SCDs at scale.

OLTP vs. OLAP: A Fundamental Divide

Online Transaction Processing (OLTP) systems are built for high-throughput, low-latency individual record operations. An e-commerce checkout writes a few rows to orders and order<em>items and commits in milliseconds. Row-oriented storage — where all columns of a single row are stored contiguously on disk — is ideal for this: fetching one order means one sequential disk read.

Online Analytical Processing (OLAP) systems answer questions over millions or billions of rows. A query computing monthly revenue by region reads revenue and order</em>date from every row in fct<em>sales but ignores customer</em>email, shipping<em>address, and twenty other columns. Row-oriented storage is terrible for this: every row must be read from disk to extract two columns.

Columnar Storage: The Analytical Engine

Columnar databases store each column as a separate on-disk file or block. Only the columns referenced in a query are read. For a table with 50 columns, a query touching 5 columns reads 10% of the data a row store would read. This is the primary reason modern warehouses (BigQuery, Snowflake, Redshift) are so fast.

Columnar storage also enables aggressive compression:

• Run-length encoding (RLE): A column with values [TX, TX, TX, TX, CA, CA, ...] stores (TX, 4), (CA, 2), ... instead of repeating strings. On low-cardinality columns like state or status, compression ratios of 10–100x are common.
• Dictionary encoding: Replace repeated string values with integer IDs; store the dictionary separately.
• Delta encoding: Store differences between successive values in sorted numeric columns — effective for timestamps and sequential IDs.

Compression reduces I/O and also enables SIMD vectorized processing: CPU instructions that operate on 256 or 512 bits at once can process 8–16 integer values per cycle when columns are dense and compressed.

Partition Pruning and Clustering

Even with columnar storage, scanning billions of rows for last month's data is expensive. Partitioning divides a table's storage into non-overlapping segments based on a column value (usually a date).

-- BigQuery: declare a partitioned table
CREATE TABLE `project.dataset.fct_sales`
PARTITION BY DATE(order_date)
CLUSTER BY product_category, store_region
AS SELECT ...

When a query filters on WHERE order</em>date BETWEEN &#039;2024-01-01&#039; AND &#039;2024-03-31&#039;, BigQuery reads only the partitions for those three months — a process called partition pruning. With clustering, rows within each partition are physically sorted by product<em>category and then store</em>region, so additional filter conditions can skip large blocks of data entirely.

Snowflake uses micro-partitions (50–500 MB compressed files, each storing min/max metadata per column) and Automatic Clustering to keep related rows together. Redshift uses sort keys (similar to clustering) and distribution keys (controlling which node stores each row) to reduce network shuffle during joins.

Query Execution: What the Warehouse Actually Does

Understanding execution helps you write faster queries. When you submit a SQL query to a columnar warehouse:

1. The planner parses SQL into a logical plan (a tree of relational algebra operators).
2. The optimizer rewrites the logical plan — pushing filters down (predicate pushdown), reordering joins, choosing between broadcast and hash joins.
3. The physical plan is compiled to vectorized native code or interpreted bytecode.
4. Execution is distributed across worker nodes; each node reads its assigned data partitions in parallel.
5. Shuffle (redistribution of rows for joins and aggregations) is the most expensive step — minimizing it is the main goal of distribution key choices in Redshift and join strategy choices in BigQuery.

Broadcast joins copy a small dimension table to every worker, eliminating shuffle entirely. BigQuery does this automatically for tables under ~100 MB. In Snowflake you can hint it: SELECT /<em>+ broadcast(dim_customer) </em>/ ....

Reading and Writing Query Plans

-- PostgreSQL / Redshift (EXPLAIN)
EXPLAIN (ANALYZE, BUFFERS)
SELECT p.category_name, SUM(f.revenue)
FROM   fct_sales f
JOIN   dim_product p ON f.product_key = p.product_key
WHERE  f.order_date >= '2024-01-01'
GROUP  BY 1;

Key things to look for in the output:

• Seq Scan vs. Index Scan: A sequential scan on a large fact table is normal and expected in a warehouse (columnar engines batch-scan efficiently). On a small dimension table, a sequential scan when a tiny result is expected signals a missing index.
• Hash Join vs. Nested Loop: Hash joins are preferred for large tables. Nested loops are efficient only when the inner relation is tiny or accessed via index.
• Actual Rows vs. Estimated Rows: Large discrepancies signal stale statistics — run ANALYZE or VACUUM ANALYZE.
• Cost numbers: These are arbitrary units but relative comparisons matter. A subtree with cost 1,000,000 dominating a plan with total cost 1,010,000 is your optimization target.

Materialized Views

For aggregations that are expensive but queried repeatedly, a materialized view pre-computes and stores the result:

-- Snowflake / BigQuery / PostgreSQL (syntax varies slightly)
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    d.full_date,
    p.category_name,
    s.region,
    SUM(f.revenue)   AS total_revenue,
    COUNT(*)         AS transaction_count
FROM fct_sales f
JOIN dim_date    d ON f.date_key    = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store   s ON f.store_key   = s.store_key
GROUP BY 1, 2, 3;

BigQuery and Snowflake can transparently rewrite queries to use a materialized view even when the query does not reference it by name — called intelligent view matching — so existing dashboards automatically benefit without any changes.

Why Window Functions?

Aggregate functions with GROUP BY collapse rows — the result has one row per group. Window functions compute an aggregate without collapsing rows, letting you attach group-level statistics to individual rows. This enables an entire class of analytical patterns: running totals, moving averages, ranks, percentiles, lag/lead comparisons.

The syntax is: function() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...). Every clause is optional.

Ranking and Ordering

-- Rank products by revenue within each category,
-- breaking ties differently with RANK vs DENSE_RANK
SELECT
    product_name,
    category,
    SUM(revenue)                                               AS total_rev,
    RANK()       OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS dense_rnk,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS row_num
FROM fct_sales f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY product_name, category;

RANK leaves gaps after ties (1, 2, 2, 4). DENSE<em>RANK does not (1, 2, 2, 3). ROW</em>NUMBER assigns a unique number regardless of ties. Use RANK/DENSE<em>RANK when business rules care about ties; use ROW</em>NUMBER when you need exactly one row per group (e.g., deduplication).

Running Totals and Moving Averages

The ROWS BETWEEN frame specification controls which rows are included in the window calculation relative to the current row.

SELECT
    order_date,
    daily_revenue,
    -- Running total (all rows from start to current)
    SUM(daily_revenue)
        OVER (ORDER BY order_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_total,
    -- 7-day moving average
    AVG(daily_revenue)
        OVER (ORDER BY order_date
              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        AS ma_7d
FROM (
    SELECT order_date, SUM(revenue) AS daily_revenue
    FROM   fct_sales
    GROUP  BY order_date
) daily
ORDER BY order_date;

RANGE BETWEEN uses the value of the ORDER BY column rather than row position — useful for time-based windows where data may have gaps.

LAG and LEAD: Period-over-Period Comparisons

SELECT
    year_month,
    monthly_revenue,
    LAG(monthly_revenue, 1) OVER (ORDER BY year_month)  AS prev_month_rev,
    monthly_revenue
        - LAG(monthly_revenue, 1) OVER (ORDER BY year_month)
        AS mom_change,
    ROUND(100.0 * (
        monthly_revenue
        / NULLIF(LAG(monthly_revenue, 1) OVER (ORDER BY year_month), 0)
        - 1
    ), 2)                                               AS mom_pct_change
FROM (
    SELECT
        TO_CHAR(order_date, 'YYYY-MM') AS year_month,
        SUM(revenue)                   AS monthly_revenue
    FROM   fct_sales
    GROUP  BY 1
) monthly
ORDER BY 1;

Always wrap the lag value in NULLIF(..., 0) before division to avoid division-by-zero errors when a prior period had no revenue.

GROUPING SETS, ROLLUP, and CUBE

Standard GROUP BY gives one aggregation level. GROUPING SETS lets you compute multiple aggregation levels in a single scan — critical for OLAP-style summary tables.

-- Three aggregation levels: date+category, date alone, category alone, grand total
SELECT
    d.full_date,
    p.category_name,
    SUM(f.revenue)              AS revenue,
    GROUPING(d.full_date)       AS is_date_subtotal,
    GROUPING(p.category_name)   AS is_cat_subtotal
FROM fct_sales f
JOIN dim_date    d ON f.date_key    = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY GROUPING SETS (
    (d.full_date, p.category_name),  -- detail level
    (d.full_date),                   -- daily totals
    (p.category_name),               -- category totals
    ()                               -- grand total
);

ROLLUP(a, b, c) is syntactic sugar for grouping sets going from most detailed to grand total: (a,b,c), (a,b), (a), (). CUBE(a, b) generates all $2^n$ combinations.

The GROUPING() function returns 1 when the column is part of a subtotal row (i.e., NULL because it was rolled up), enabling you to distinguish a genuine NULL value from a subtotal null.

CTEs and the WITH Clause

Common Table Expressions (CTEs) name intermediate results, making complex multi-step queries readable and maintainable. They are not a performance optimization in most engines (the planner inlines them), but they are essential for code clarity.

WITH
daily AS (
    SELECT order_date, SUM(revenue) AS rev
    FROM   fct_sales
    GROUP  BY order_date
),
with_ma AS (
    SELECT
        order_date,
        rev,
        AVG(rev) OVER (
            ORDER BY order_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS ma_7d
    FROM daily
),
anomalies AS (
    SELECT *
    FROM   with_ma
    WHERE  rev > 2.5 * ma_7d   -- revenue spike > 2.5x moving average
)
SELECT * FROM anomalies ORDER BY order_date;

Recursive CTEs are covered in Chapter 43. For analytical work, non-recursive CTEs dominate.

Percentiles and Distribution Functions

-- PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual row value
SELECT
    category_name,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY revenue) AS median_order_value,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY revenue) AS p95_order_value,
    NTILE(4) OVER (ORDER BY revenue)                       AS quartile
FROM fct_sales f
JOIN dim_product p ON f.product_key = p.product_key;

NTILE(n) divides the ordered result into n buckets and assigns a bucket number. It is commonly used to label rows as top decile, top quintile, etc.

ETL vs. ELT

The traditional Extract-Transform-Load (ETL) pattern moves data from source systems, transforms it in an intermediate compute environment (often a dedicated ETL server), and loads clean data into the warehouse. This was pragmatic when warehouse compute was expensive and storage was cheap.

Modern cloud warehouses have inverted these economics: compute scales elastically and is billed per-second, while storage is pennies per gigabyte. Extract-Load-Transform (ELT) loads raw data directly into the warehouse and performs all transformations inside it, using SQL. The warehouse becomes both the storage layer and the transformation engine.

Advantages of ELT:

• No separate transformation infrastructure to maintain.
• Raw data is always preserved — transformations can be rerun from the original source.
• SQL is a universal language accessible to analysts, not just engineers.
• Incremental transformations can exploit warehouse-native features (partitioning, clustering).

dbt: The Transformation Framework

dbt (data build tool) is the dominant framework for ELT transformations. It compiles SQL SELECT statements (called models) into DDL/DML and runs them in the correct dependency order. Key concepts:

• Model: A .sql file containing a single SELECT statement. dbt wraps it in CREATE TABLE AS or CREATE VIEW AS based on its materialization setting.
• Source: A declaration of raw tables loaded by an ingestion tool (Fivetran, Airbyte, etc.).
• Ref: {{ ref(&#039;model<em>name&#039;) }} — the Jinja macro that declares a dependency between models. dbt builds a DAG from all ref() calls.
• Test: Out-of-the-box not</em>null, unique, accepted<em>values, and relationships tests, plus custom SQL tests.

Project Structure

A production dbt project typically follows this layered convention:

models/
  staging/         -- 1:1 with source tables; rename columns, cast types
    stg_orders.sql
    stg_customers.sql
  intermediate/    -- joins and transformations not ready for final use
    int_order_items_enriched.sql
  marts/           -- final dimensional tables for analytics
    fct_sales.sql
    dim_customer.sql
    dim_product.sql

A Complete dbt Model: fct</em>sales

-- models/marts/fct_sales.sql
{{ config(
    materialized = 'incremental',
    unique_key   = 'sale_key',
    partition_by = {"field": "order_date", "data_type": "date"},
    cluster_by   = ['product_category', 'store_region']
) }}

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
order_items AS (
    SELECT * FROM {{ ref('stg_order_items') }}
),
customers AS (
    SELECT * FROM {{ ref('dim_customer') }}
),
products AS (
    SELECT * FROM {{ ref('dim_product') }}
)

SELECT
    {{ dbt_utils.generate_surrogate_key(['oi.order_id', 'oi.line_num']) }}
        AS sale_key,
    o.order_date,
    c.customer_key,
    p.product_key,
    oi.quantity,
    oi.unit_price,
    oi.discount_amount,
    oi.quantity * oi.unit_price - oi.discount_amount  AS revenue,
    p.category_name                                    AS product_category,
    s.region                                           AS store_region
FROM order_items oi
JOIN orders   o ON oi.order_id   = o.order_id
JOIN customers c ON o.customer_nk = c.customer_nk AND c.is_current
JOIN products  p ON oi.product_nk = p.product_nk
{% if is_incremental() %}
WHERE o.order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

The is_incremental() block appends only new rows on subsequent runs, dramatically reducing runtime for large tables. The first run (or a dbt run --full-refresh) creates the table from scratch.

Schema Tests

# models/marts/schema.yml
models:
  - name: fct_sales
    columns:
      - name: sale_key
        tests:
          - unique
          - not_null
      - name: customer_key
        tests:
          - not_null
          - relationships:
              to: ref('dim_customer')
              field: customer_key
      - name: revenue
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 100000

Run dbt test after every dbt run. These tests catch data quality issues before they reach dashboards.

Sources and Freshness

# models/staging/sources.yml
sources:
  - name: raw
    database: my_project
    schema: raw_data
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    loaded_at_field: _loaded_at
    tables:
      - name: orders
      - name: order_items

dbt source freshness alerts when the ingestion pipeline has stalled, catching data gaps before they silently produce wrong numbers.

Running and Deploying dbt

# Install and initialize
pip install dbt-bigquery          # or dbt-snowflake, dbt-redshift
dbt init my_project

# Run everything
dbt run

# Run only changed models and their downstream dependents
dbt run --select state:modified+

# Test, document, and serve docs locally
dbt test
dbt docs generate
dbt docs serve

In production, dbt is typically orchestrated by Airflow, Prefect, or dbt Cloud on a schedule, with Slack/email alerts on failures.

Why Bridge SQL and Python?

SQL is unrivaled for set-based transformations at scale, but Python owns statistical modeling, machine learning, and visualization. Production data science workflows constantly cross this boundary: pull a cohort from the warehouse into pandas for survival analysis, train a model, write predictions back to the warehouse for serving. Understanding the tools for this bridge — and their performance characteristics — is essential.

SQLAlchemy: The Universal Adapter

SQLAlchemy provides a common Python interface to dozens of databases. Its Core layer generates SQL programmatically; its ORM layer maps Python classes to tables. For analytics, Core is usually preferable — it keeps you close to SQL without the complexity of object mapping.

from sqlalchemy import create_engine, text
import pandas as pd

# PostgreSQL via psycopg2
engine = create_engine(
    "postgresql+psycopg2://user:password@host:5432/warehouse",
    pool_size=5,
    connect_args={"options": "-c statement_timeout=120000"}  # 120s timeout
)

# Execute parameterized queries (NEVER use f-strings for user input)
with engine.connect() as conn:
    result = conn.execute(
        text("SELECT * FROM fct_sales WHERE order_date >= :start"),
        {"start": "2024-01-01"}
    )
    df = pd.DataFrame(result.fetchall(), columns=result.keys())

Always use parameterized queries. String interpolation into SQL is a SQL injection vulnerability even in internal analytical tools.

pandas read<em>sql and to</em>sql

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://...")

# Pull a large result set in chunks to avoid OOM
query = """
    SELECT customer_nk, order_date, revenue
    FROM   fct_sales f
    JOIN   dim_customer c ON f.customer_key = c.customer_key
    WHERE  c.is_current
      AND  order_date >= '2023-01-01'
"""

chunks = []
for chunk in pd.read_sql(query, engine, chunksize=100_000):
    # Apply any Python-side transformations before accumulating
    chunk['revenue_log'] = chunk['revenue'].apply(lambda x: max(x, 0.01))
    chunks.append(chunk)

df = pd.concat(chunks, ignore_index=True)
print(f"Loaded {len(df):,} rows")

# Write predictions back to the warehouse
predictions = df[['customer_nk', 'order_date']].copy()
predictions['churn_probability'] = model.predict_proba(features)[:, 1]

predictions.to_sql(
    name='ml_churn_predictions',
    con=engine,
    schema='analytics',
    if_exists='replace',     # or 'append'
    index=False,
    method='multi',          # batch INSERT; much faster than row-by-row
    chunksize=5_000
)

to<em>sql performance note: The default single-row insert is extremely slow for large DataFrames. Always set method='multi' or, for even better performance on PostgreSQL, use psycopg2's execute</em>values via a custom method callable.

Native Warehouse Connectors

For BigQuery, Snowflake, and Redshift, native Python connectors outperform the generic SQLAlchemy path by 2–10x because they use warehouse-specific bulk transfer protocols.

# BigQuery: google-cloud-bigquery + pandas-gbq
from google.cloud import bigquery

client = bigquery.Client(project='my-gcp-project')

query = """
    SELECT product_key, SUM(revenue) AS total_rev
    FROM   `my-gcp-project.analytics.fct_sales`
    WHERE  order_date BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP  BY 1
"""

# Returns a DataFrame directly
df = client.query(query).to_dataframe()

# Write back using BigQuery Storage Write API (fastest)
job_config = bigquery.LoadJobConfig(
    write_disposition='WRITE_TRUNCATE',
    autodetect=True
)
job = client.load_table_from_dataframe(
    df_predictions,
    'my-gcp-project.analytics.churn_scores',
    job_config=job_config
)
job.result()   # wait for completion
print(f"Loaded {job.output_rows} rows")

# Snowflake: snowflake-connector-python with write_pandas
import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

conn = snowflake.connector.connect(
    account   = 'org-account',
    user      = 'analyst',
    password  = 'secret',
    warehouse = 'TRANSFORM_WH',
    database  = 'ANALYTICS',
    schema    = 'MARTS'
)

# write_pandas uses Snowflake's COPY INTO for bulk loading
success, nchunks, nrows, _ = write_pandas(
    conn, df_predictions,
    table_name='CHURN_SCORES',
    auto_create_table=True
)
print(f"Wrote {nrows} rows in {nchunks} chunk(s), success={success}")

Type Alignment Between SQL and pandas

Type mismatches cause silent data corruption. Key rules:

• SQL BIGINT → pandas int64 — but if the column has NULLs, pandas will use float64 unless you use pd.Int64Dtype() (nullable integer).
• SQL NUMERIC(10,2) → may become Python Decimal rather than float64. Force the cast in the query: CAST(revenue AS FLOAT8) or use pd.to<em>numeric(df.revenue).
• SQL TIMESTAMP WITH TIME ZONE → pandas datetime64[ns, UTC]. Be explicit: pd.to</em>datetime(df.ts, utc=True).
• SQL DATE → python datetime.date objects in the cursor, not pd.Timestamp. Use pd.to<em>datetime to normalize.

Performance Patterns for Large Transfers

• Push work into the warehouse: filter, aggregate, and join in SQL before pulling to Python. Never SELECT * from a fact table and filter in pandas.
• Use columnar formats for intermediate storage: write to GCS/S3 as Parquet, then load into pandas with pd.read</em>parquet. Faster than JDBC for bulk transfers.
• Predicate pushdown with read_sql: pass WHERE clauses as query parameters, not post-hoc DataFrame filters.
• Connection pooling: reuse connections rather than opening a new connection for each query — connection setup can take 100–500ms on cloud warehouses.