2.3. Transform and Load the Data
💡 First Principle: Raw data arrives shaped for the system that produced it: wide spreadsheets for humans, nested JSON for APIs, normalized tables for transaction processing. A semantic model needs a different shape: tall fact tables and descriptive dimensions with clean keys. Transformation is the translation between the two, and every step should move the data closer to a star schema.
Why care? This is the largest skill area in the domain, and it's heavily tested through drag-and-drop step ordering and "which transformation should you use?" stems. It also decides whether Phase 3 is easy or painful: a model built on well-shaped tables needs simple DAX; a model built on unshaped data needs workarounds in every measure.
The mental model is an assembly line. Each applied step is a station that receives the previous station's output. Stations near the start should be cheap and reduce volume (remove columns, filter rows, ideally folding to the source); stations near the end refine shape (pivot, merge, add keys). Reordering stations changes both the result and the cost.
⚠️ Common Misconception: A referenced query is an independent copy, and merge and append are two words for combining. Reference creates a dependent query that inherits changes; Duplicate creates an independent copy. Merge joins columns side by side; Append stacks rows. These two confusions anchor a large share of the domain's distractors (2.3.5).
