3.4 Transform & Model for Analytics

From Source Shape to Analytics Shape

Operational sources are usually normalized to third normal form (3NF) to avoid update anomalies in transactional apps. Analytics wants the opposite: a star schema that is fast to filter and easy for business users and DAX to reason about. DP-600 tests whether you can perform that transformation in a lakehouse or warehouse before semantic modeling, because a clean star schema is what makes Direct Lake and DAX measures perform.

Star Schema Fundamentals

• Fact table — the events or measurements (sales line items, sessions, payments). Long and narrow: foreign keys plus numeric measures. The grain (one row = one what?) must be explicit and consistent across every row.
• Dimension table — descriptive context (Product, Customer, Date, Store). Wide and short: one row per entity, many descriptive attributes used for slicing and grouping.
• Conformed dimension — a single shared dimension (most commonly Date) reused by multiple fact tables so analysis is consistent across subject areas. A conformed Date dimension lets Sales and Returns facts be sliced by the same fiscal calendar.

Normalized vs. Star (Denormalization)

Denormalization is the deliberate flattening of those snowflaked source tables — for example, merging Product, Subcategory, and Category into one wide Product dimension — so the model has fewer relationships and faster filter propagation. This is an exam-favored answer when a scenario complains about complex relationships, snowflaked tables, or slow filtering: flatten toward a star rather than adding more bridge tables.

Surrogate Keys vs. Business Keys

• Business (natural) key — the identifier from the source system (e.g., CustomerEmail, SKU). It can change, be reused, or be non-numeric.
• Surrogate key — a system-generated, typically integer key the warehouse assigns to each dimension row. Fact tables store the surrogate key as the foreign key.

Why the exam cares: surrogate keys decouple the model from source-key changes, give compact integer joins (better compression and faster filter propagation), and are required to implement Type 2 slowly changing dimensions because the same business entity can have multiple historical rows that each need a unique key.

Slowly Changing Dimensions (SCD)

When a dimension attribute changes over time (a customer moves cities), you choose how to handle history:

Worked example (Type 2): Customer C100 lives in "West" with surrogate key 5 and IsCurrent = true. When they move to "East," you expire row 5 (ExpiryDate = today, IsCurrent = false) and insert a new row with surrogate key 6, region "East," IsCurrent = true. Sales recorded before the move still join to key 5 (West); later sales join to key 6 (East). Type 2 is the most exam-relevant pattern because it explains why surrogate keys exist and how facts stay tied to the dimension version that was true when the event occurred.

Where the Transformation Happens

• Dataflow Gen2 — low-code shaping: merge, group, type-fix, derive columns, output to lakehouse/warehouse.
• Notebook (Spark) — large-scale or programmatic builds, SCD logic, surrogate-key generation at volume.
• Warehouse T-SQL / stored procedures — set-based dimensional ETL for relational teams.

Match the tool to skill set and scale; the target is always a clean star schema regardless of engine.

Why the Star Schema Matters for Direct Lake

DP-600 connects this topic to performance: a clean star schema is what lets the downstream semantic model run well in Direct Lake mode. Wide, denormalized dimensions with integer surrogate keys compress efficiently in the Delta/V-Order columnar format, and single-hop relationships let filter context propagate from a dimension to the fact in one step. A snowflaked model with long relationship chains forces multi-hop propagation and inflates the model, so the preparation work you do here directly shows up as faster or slower DAX measures later.

Common Modeling Traps

• Pushing descriptive attributes onto the fact table to "avoid a join" destroys dimensional reuse and breaks conformance; keep attributes on dimensions.
• Using a mutable business key as the fact foreign key makes Type 2 history impossible and breaks joins when the source reissues keys — always join on the surrogate key.
• Many-to-many or bidirectional relationships everywhere to patch a bad shape; the exam-preferred fix is to denormalize toward a clean star, not to add ambiguous filter paths.
• Skipping a conformed Date dimension so each fact carries its own calendar; build one shared Date dimension instead so time intelligence works across subject areas.

The takeaway: the answer that flattens toward fewer, wider tables with integer surrogate keys and a shared Date dimension is almost always the modeling choice DP-600 rewards.