Data Warehouse Pack

We've all inherited a warehouse that's really just a database with a SELECT habit. You open the SQL, and there's no star schema. Just a massive denormalized table with duplicate customer addresses and a fact table that doesn't sum correctly because the grain is wrong. Slowly changing dimensions are the usual culprit. You need to track when a customer moves or changes their tier, but the schema overwrites the old value. Or worse, you're trying to implement Type 2 history manually, writing MERGE statements that break on race conditions. You're spending hours debugging why dim_customer has two "current" records, or why the fact table is missing rows. This isn't data engineering; it's triage.

Imagine a retail analytics team migrating from a legacy ERP dump to a cloud warehouse. They start with a flat file export. By week two, they have a fact_sales table that duplicates customer records on every load. The dim_customer table overwrites the previous address, so the finance team can't reconcile returns against the original purchase. The Spark jobs are slow because everything is partitioned by load_date while the query filters are on transaction_date. The team is drowning in SELECT queries and broken historical reports.

Star schema design is critical for analytical workloads, separating facts from dimensions to optimize query performance ^[1]. In this scenario, the team needed Type 2 to track address changes, but their flat file approach made that impossible without a complete refactor ^[5]. SCD types range from overwriting to full history, and choosing the right type depends on the business requirement. They needed to preserve the address at the time of sale while tracking the customer's current location. This required a surrogate key in the dimension, effective_date and end_date columns, and a flag to identify the current record. Without these patterns, the warehouse is just a slower version of the ERP.

Star Schema, SCD Type 2, and Spark Optimization Out of the Box

Once the Data Warehouse Pack is installed, the agent generates a star schema that actually holds. fact_sales.sql uses on_schema_change='fail' and contract enforcement, so bad data breaks the pipeline before it hits the warehouse. dim_customer_scd2.sql implements proper Type 2 logic with is_current flags and effective_date ranges, preserving full history without manual merge hacks. Spark queries leverage DISTRIBUTE BY and bucketing hints, reducing shuffle volume by orders of magnitude.

The validate_project.sh script runs in CI, catching missing partition hints or malformed SCD columns before they reach production. You get a warehouse where facts are granular, dimensions are history-aware, and queries are optimized by design. For governance policies downstream, the data-lake-pack covers metadata cataloging, but the warehouse itself needs to be rock-solid. Testing SCD logic is critical; the dbt-analytics-pack provides CI/CD integration for deployments, and this pack ensures your models are correct before they get there. We built this so you don't have to reinvent the wheel for every project. Install the pack, and the agent handles the star schema, SCD Type 2, and Spark optimization patterns automatically.

What's in the Data Warehouse Pack

• skill.md — Orchestrator skill that defines the data warehouse design workflow, references all templates, references, validators, and examples, and instructs the agent on when to apply star schema, SCD, partitioning, and optimization patterns.
• templates/fact_sales.sql — Production-grade dbt incremental model for a sales fact table. Uses on_schema_change='fail', contract enforcement, and Spark partitioning hints for query optimization.
• templates/dim_customer_scd2.sql — SCD Type 2 dimension model using dbt incremental materialization, window functions for history tracking, and audit columns. Implements surrogate keys and effective date logic.
• templates/spark_optimization.sql — Curated Spark SQL patterns for partitioning, bucketing, storage partition joins, and ORC/Hive optimization. Includes DISTRIBUTE BY, REPARTITION hints, and Iceberg bucketing configs.
• references/dimensional-modeling.md — Canonical knowledge on star schema design, Kimball methodology, SCD types (1, 2, 3), surrogate keys, audit columns, and denormalization trade-offs for analytical workloads.
• references/spark-performance.md — Canonical knowledge on distributed partitioning, shuffle optimization, bucketing vs partitioning, storage partition joins, ORC filter pushdown, and foreachPartition streaming patterns.
• scripts/validate_project.sh — Executable validator that scans a target directory for required DWH artifacts (fact/dim patterns, dbt config, Spark hints). Exits non-zero if critical patterns are missing or malformed.
• validators/dbt_schema.yml — Production dbt schema.yml with enforced contracts, primary_key/not_null constraints, and data_tests. Validates that star schema models adhere to dimensional modeling standards.
• tests/test_scd_type2.py — Python test script that validates SCD Type 2 logic against sample data. Uses assertions and exits 1 on failure to ensure historical tracking correctness.
• examples/retail_warehouse_spec.yaml — Worked example: complete retail data warehouse design specification including table definitions, partitioning strategy, SCD handling, and query optimization notes.

Install and Ship

Stop guessing partition keys and manually coding SCD logic. Upgrade to Pro to install the Data Warehouse Pack. Your warehouse should be a star schema, not a flat file with delusions of grandeur. Install now and ship.