daemon-blockint-tech 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
- Identify the user's warehouse need (schema design, SQL optimization, ETL, or partitioning)
- Follow the corresponding workflow below
- 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):
- Identify the slow query and capture its execution plan
- Check for full table scans; add partitioning or clustering if present
- Verify join order: smallest/ most selective table first when possible
- Look for selective predicates pushed to the partition/clustering key
- Check for redundant aggregations or exploding joins (many-to-many without bridge)
- Compare estimated vs actual rows; cardinality misestimates indicate stale stats
- Consider materialized views or pre-aggregated summary tables for repeated patterns
- 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:
| Property | Pattern |
|---|---|
| Idempotency | MERGE / INSERT OVERWRITE with deterministic keys |
| Incrementality | WHERE updated_at > (SELECT MAX(updated_at) FROM target) |
| Atomicity | Wrap multi-step loads in a transaction or use staging → swap pattern |
| Observability | Row counts, null rates, freshness checks logged per run |
| Error handling | Dead-letter queue for bad records; fail loudly on schema drift |
4. Platform Selection Quick Reference
| Need | Best Fit |
|---|---|
| Semi-structured JSON, auto-scaling | Snowflake |
| Tight GCP integration, nested/repeated fields | BigQuery |
| ML + Spark + SQL in one lakehouse | Databricks |
| AWS-native, predictable cost at scale | Redshift |