Memory hook: Fix the grain before fixing the formula.
Must remember
Declare what one fact row represents. Build dimensions with stable unique keys and facts with validated foreign keys. Denormalizing descriptive attributes can simplify analytical queries, but repeating measures across joined rows can inflate totals. Aggregate only to a grain that still answers the required questions.
An inner join keeps matches; a left join preserves all left-side rows and reveals missing matches with nulls. Union/append stacks compatible rows; it does not enrich columns by key. Duplicated dimension keys can multiply fact records. Compare row counts and aggregates before and after joins, and decide explicitly whether duplicates are invalid or represent legitimate repeated events.
Treat null, empty text and zero separately. Convert types with attention to locale, precision and invalid values. Filter early when it preserves correctness and reduces processed data. Adding a derived column can simplify later modeling, but agree on time zone, rounding and business definitions first.
Views provide reusable query definitions; functions package supported reusable computations; stored procedures encapsulate supported procedural operations. Availability and write capability depend on the Fabric engine. Parameterize approved operations and avoid embedding environment-specific secrets. A view is not automatically a stored copy or a performance cache.
For slowly changing dimensions, choose whether to overwrite attributes or preserve historical versions with effective dates and surrogate keys. A fact must resolve to the intended historical dimension row. This is particularly important when a customer changes region: yesterday's revenue should follow the agreed historical or current-region reporting rule.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Retain unmatched facts for diagnosis | Left join and inspect missing dimension keys. |
| Preserve customer-region history | A versioned dimension with correct effective-date matching. |
| Repeat a supported SQL transformation consistently | A suitable view/function/procedure for the selected engine. |
Traps
- Joining on nonunique keys can silently inflate totals.
- Replacing every null with zero changes business meaning.
Active recall
1. What is fact grain?
The meaning of a single fact-table row.
2. Why compare counts after a join?
To catch lost or multiplied records.
3. View versus materialized result?
A query definition versus stored computed data; do not assume a view caches its output.
4. When preserve dimension versions?
When historical reporting must reflect attributes as they were at the event time.
5. Why check engine support?
Lakehouse SQL endpoints and warehouses do not support identical write/procedural operations.