About this skill

Problem It Addresses

Data warehouse design often stalls on practical questions: how to split business processes, what belongs in a fact table, how to store historical versions, and who owns metric definitions. Reporting directly from ODS can pull dirty data, repeated calculation, and maintenance cost into production. If dimension attributes are stuffed into fact tables, business IDs are used for joins, or multiple CROSS JOIN layers are written, queries slow down and the model becomes harder to evolve. This skill turns those recurring pain points into a practical modeling checklist for engineers designing, reviewing, or refactoring a data warehouse.

How The Skill Works

It follows a common data warehouse delivery path: compare Kimball, Inmon CIF, and Data Vault 2.0, then move into layered architecture, SCD, fact table patterns, metric management, and dbt workflows. Key capabilities include:
- Layer design: from ODS, DWD, DWS to ADS, with ODS not used directly for reporting and DWS split by business domain to reduce update locks and storage bloat.
- Slowly changing dimensions: covers six SCD strategies and Type 2 zipper-table patterns, including separating business ID from surrogate key to distinguish historical versions.
- Metric governance: splits definitions into atomic, derived, and composite metrics to avoid multiple versions of the same business concept.
- Anti-pattern checks: lists common issues such as slow string joins, missing partition keys in WHERE clauses, and Cartesian product explosions, with corrected modeling practices.
- Deliverable checklist: provides bus matrix, CDM, LDM, PDM, ETL mapping, data dictionary, lineage, metric definitions, and SCD strategy list for project review.

Scope And Caveats

The skill is oriented toward data warehouse architecture and modeling methodology. It fits OLAP, enterprise data warehouses, metric platforms, and dbt data projects. Real-time stream processing, data governance process, or business-specific extraction still need additional design for permissions, streaming compute, and business process documentation. Because the source notes the content may be AI-generated, validate the recommendations against existing models, performance baselines, and team standards before production use.

Screenshots

Use Cases

When designing an order-domain warehouse, use it to split business processes, choose fact table types, and check dimension consistency.
When reviewing a user dimension change plan, use SCD strategies to decide whether to build a zipper table and separate surrogate keys.
Before building a metric platform, use it to break atomic, derived, and composite metrics into a unified definition document.
During a dbt project review, use anti-pattern checks to inspect wide tables, business ID joins, and missing partition keys.

Best For

Data engineers designing order-domain warehouses who need clear fact table types and dimension splits.
Data analysts maintaining user-profile slowly changing dimensions who need zipper-table and surrogate-key decisions.
BI engineers building metric definitions who need atomic, derived, and composite metrics in a unified document.
dbt engineers modeling data pipelines who need checks for wide tables, join keys, and partition conditions.