Data Warehouse Engineer

Data Warehouse Engineer

Overview

Design and implement data warehouses. This skill covers star/snowflake schemas, dimensional modeling, SQL optimization, ETL/ELT patterns, partitioning strategies, and warehouse-specific features (Snowflake, BigQuery, Redshift).

Features

• Dimensional modeling: star schema, snowflake schema, fact/dimension table design
• SQL optimization: query tuning, indexing, materialized views, partition pruning
• ETL/ELT patterns: incremental loads, CDC, data quality checks, error handling
• Partitioning strategies: range, list, hash, and composite partitioning
• Warehouse-specific features: Snowflake clustering, BigQuery partitioning, Redshift sort keys

Usage

1. Identify the user's warehouse need (schema design, SQL optimization, ETL, or partitioning) 2. Follow the corresponding workflow below 3. Produce structured outputs: ER diagrams, optimized SQL queries, ETL pipeline designs, or partitioning plans

Examples

• User: "Design a star schema for sales"

Agent: Runs Dimensional Modeling workflow, identifies fact table (sales), dimension tables (date, product, customer, region), creates ER diagram

• User: "Optimize a slow query"

Agent: Runs SQL Optimization workflow, analyzes execution plan, recommends indexes, rewrites query with CTEs

• User: "Set up incremental loads"

Agent: Runs ETL/ELT workflow, designs CDC pattern, implements watermark-based extraction, adds data quality checks

When to Use

• Diagnosing slow warehouse SQL and improving partition, cluster, or join plans
• Designing star/snowflake schemas, SCDs, and fact/dimension tables
• Building idempotent, incremental, or CDC ETL with observability and quality checks
• Comparing Snowflake, BigQuery, Databricks, or Redshift syntax and trade-offs

When NOT to Use

• Enterprise-wide data mesh, governance pillars, or compliance program design → use data-architect
• BI dashboard layout, chart selection, or stakeholder-facing metrics → use bi-analyst
• dbt layers, mart tests, exposures, and analytics engineering workflows → use analytics-data-engineer
• ML feature stores, model serving, or experiment analysis → use data-scientist
• On-call leadership for the full data platform org → use data-system-ops-lead
• Application runtime profiling, API load tests, OLTP latency SLOs → use performance-engineer

Core Workflows

1. Query Performance Diagnostics

Step-by-step checklist (follow exactly):

1. Identify the slow query and capture its execution plan 2. Check for full table scans; add partitioning or clustering if present 3. Verify join order: smallest/ most selective table first when possible 4. Look for selective predicates pushed to the partition/clustering key 5. Check for redundant aggregations or exploding joins (many-to-many without bridge) 6. Compare estimated vs actual rows; cardinality misestimates indicate stale stats 7. Consider materialized views or pre-aggregated summary tables for repeated patterns 8. Document the before/after execution time and cost

2. Data Model Design

Decision tree:

• Need fast aggregations and simple joins? → Star schema
• Need normalized dimensions to reduce redundancy? → Snowflake schema
• Tracking historical changes in dimensions? → Slowly Changing Dimension (SCD) type 2
• Event-based data with high volume? → Fact table with partitioning on event date
• Need real-time-ish analytics? → Streaming ingestion + micro-batch fact tables

3. ETL Pipeline Design

Essential properties every pipeline must satisfy:

4. Platform Selection Quick Reference

Data Modeling

Dimensional Modeling

Star Schema

• One central fact table surrounded by denormalized dimension tables
• Best for: query simplicity, fast aggregations, BI tool compatibility

Snowflake Schema

• Dimensions normalized into sub-dimensions
• Best for: reducing storage redundancy, complex hierarchies
• Trade-off: more joins, slightly slower queries

Fact Table Design

Dimension Table Patterns

Slowly Changing Dimension (SCD)

SCD Type 2 SQL Template:

-- Close existing row
UPDATE dim_customer
SET valid_to = CURRENT_DATE, is_current = FALSE
WHERE customer_id = :id AND is_current = TRUE;

-- Insert new current row
INSERT INTO dim_customer (customer_id, name, region, valid_from, valid_to, is_current)
VALUES (:id, :name, :region, CURRENT_DATE, '9999-12-31', TRUE);

Junk Dimensions

Combine low-cardinality flags into a single dimension to reduce fact table width.

Bridge Tables

Use for many-to-many relationships (e.g., patient → multiple diagnoses).

Naming Conventions

Data Vault (Alternative)

Use when:

• Source systems change frequently
• Need full auditability and traceability
• Agility > query performance (sacrifice some speed for flexibility)

Components: Hubs (business keys), Links (relationships), Satellites (attributes).

ETL Pipeline Patterns

Incremental Load Strategies

1. Timestamp-Based

INSERT INTO target
SELECT * FROM source
WHERE updated_at > (SELECT MAX(updated_at) FROM target);

• Pros: Simple, widely supported
• Cons: Misses hard deletes; clock skew risk

2. Change Data Capture (CDC)

CDC with Hash (Delete Detection)

-- Stage current source hash
CREATE TEMP TABLE staging AS
SELECT pk, MD5(CONCAT(col1, col2, col3)) AS row_hash FROM source;

-- Find deletes
SELECT pk FROM target_hash LEFT JOIN staging USING(pk) WHERE staging.pk IS NULL;

-- Find inserts/updates
SELECT s.pk, s.row_hash FROM staging s
LEFT JOIN target_hash t ON s.pk = t.pk
WHERE t.pk IS NULL OR s.row_hash != t.row_hash;

3. Streaming Ingestion

• Kafka → Snowpipe / BigQuery Streaming API / Delta Live Tables
• Use for: event data, clickstreams, IoT telemetry

Idempotency Patterns

Orchestration & Scheduling

dbt Patterns

• {{ config(materialized='incremental', unique_key='order_id') }}
• Use is_incremental() macro to branch logic
• Tests: not_null, unique, accepted_values, relationships

Airflow Patterns

• One DAG per source system or business domain
• Use TaskFlow API for simple pipelines
• Sensor tasks to wait for upstream data readiness
• on_failure_callback to alert Slack/PagerDuty

Data Quality Checks

Error Handling

• Schema drift: Versioned schemas; fail pipeline on unexpected column additions
• Bad records: Dead-letter queue table with reject_reason, raw_payload, loaded_at
• Partial failures: Transaction wrap or staging table with rollback capability
• Retries: Exponential backoff for transient API/database errors

Backfilling

• Always backfill with the same idempotent logic as incremental loads
• Use BETWEEN date ranges in small chunks to avoid resource exhaustion
• For large backfills: disable indexes/constraints, load, rebuild

Platform Cheatsheets

Data Type Mapping

Query Syntax Differences

Loading Data

Cost Gotchas

Security

Useful System Tables

SQL Optimization & Query Tuning

Execution Plans

Capture and read execution plans per platform:

Common Anti-Patterns & Fixes

Partitioning & Clustering

Materialization Patterns

• Materialized View: Platform-native auto-refresh (limited flexibility)
• Manual Summary Table: Full control; refresh via MERGE or INSERT OVERWRITE
• Pre-join: Flatten star schema into wide table for specific dashboards

Use manual summary tables when:

• Refresh logic is complex (e.g., SCD type 2 dimensions)
• You need incremental refreshes with custom business rules
• Platform MV limitations prevent required joins

Cost Optimization