Data Warehousing and Business Intelligence
This domain tests warehouse architecture, dimensional design, and BI delivery. CDP candidates should be able to declare grain, distinguish fact and dimension tables, manage history, and explain how semantic layers produce consistent metrics.
Core rules and concepts
- Warehouse characteristics: a data warehouse is subject-oriented, integrated, time-variant, and nonvolatile. An operational data store supports near-real-time integration; a data mart serves a narrower subject or department.
- Kimball dimensional design: declare the grain first, then identify facts and dimensions. A fact table stores measures and foreign keys; a dimension stores descriptive context. A star schema keeps dimensions denormalized; a snowflake normalizes dimension hierarchies.
- Conformed dimensions: shared dimensions with consistent keys and definitions allow facts from separate processes to be compared. The bus matrix maps business processes to conformed dimensions.
- Measure additivity: additive measures sum across all dimensions; semi-additive measures, such as balances, cannot be summed across time; non-additive measures, such as ratios, must be recalculated from components.
- Slowly changing dimensions: Type 1 overwrites; Type 2 adds a version row with effective dates and a current flag; Type 3 keeps a prior-value column. Type 2 preserves history but requires careful current-row filtering.
- ETL/BI delivery: staging, quality gates, transformations, loads, lineage, and a governed semantic layer prevent report-by-report metric drift.
Worked example
A sales fact table uses the grain “one row per order line.” Date, product, customer, and store are dimensions. If customer attributes change, Type 2 creates a new customer version so historical sales retain the old segment. Reports that need current segment must filter the current flag. Inventory balances use a daily snapshot; summing balances across dates would overstate stock because the measure is semi-additive.
Traps that cost marks
Mixing grains in one fact table; failing to state grain; double-counting Type 2 rows without current-filter logic; snowflaking without a reason; treating all measures as additive; and assuming a BI tool’s calculation matches the governed metric definition.