DP-600 Data Modeling: Trust the Grain

The most damaging data-modeling defects often look perfectly reasonable on a dashboard. A revenue figure grows after a new customer attribute is added. A margin calculation changes when a department filter is applied. A transaction table contains all the expected rows, yet two reports disagree about the same quarter. These are not necessarily visualization problems. They can result from decisions about grain, keys, relationships, or calculation context made long before anyone chose a chart.

Microsoft’s DP-600 requires analytics engineers to prepare data and build enterprise semantic models that remain correct under ordinary business questions. The best starting point is the meaning of one record. Once that is explicit, transformations and measures can be tested against evidence rather than patched until a visual shows an attractive number.

Define one row before defining one measure

Take an online marketplace. Its order header table has one row per order, its order-line table has one row per product purchased, and its payments table may contain several attempts or partial captures for one order. A careless join from headers to lines to payments can multiply a header total by the number of line items or repeat a payment amount across each product. The SQL query may execute without error and still create an unusable fact table. This is why grain should be documented before writing the join.

Choose the business event the model must measure. If product revenue is analyzed by item, an order-line fact is often appropriate. If payment recovery is analyzed, payment attempts may deserve a separate fact. Some questions require comparing two facts through conformed dimensions rather than merging all events into one enormous table. Write test cases with one order containing two lines and two payment attempts. If the transformed output has four repeated business amounts where it should have two independent events, fix the data shape before proceeding to DAX.

Separate facts, dimensions, and historical meaning

Fact tables express measurable events at a defined grain: quantities, transaction values, durations, and keys. Dimensions provide the descriptive context for filtering and grouping: products, customers, dates, stores, and account categories. A star schema keeps those roles clear. Report authors can select a region or date without needing to understand a maze of source-system joins. The engineer still needs to design the relationships and the business definitions behind those apparently simple choices.

History introduces an important tradeoff. If a product moves between categories, should last year’s sales appear under the category at the time of sale or today’s category? Both can be valid, but they answer different questions. A slowly changing dimension pattern can preserve historical attributes when necessary; a current-state dimension may be appropriate for a reclassified operational view. Decide with the business owner, then test the result. “Product category” is not a complete analytical definition until its treatment of change is specified.

Clean source data without erasing useful exceptions

Transformations include resolving missing values, aligning data types, removing ingestion duplicates, standardizing codes, and reconciling records from different systems. The temptation is to eliminate every row that looks irregular. That can hide precisely the exceptions a business needs to investigate. A claim with no matching policy identifier, for instance, may indicate a late reference-data load or an operational error. Discarding it silently makes the report look cleaner while potentially understating actual work.

Build a testable rule for each change. Preserve source keys, document whether a transformation is reversible, and record exceptions separately when they need steward review. An aggregate that excludes unresolved rows should say so in its definition. For money, explicitly handle null amounts, refunds, reversals, currency conversion dates, and multiple posting dates. These details are best resolved as data contracts before dozens of downstream measures replicate subtly different logic. Data preparation is successful when the resulting table is more reliable, not merely smaller.

Design relationships for filtering behavior, not convenience

Relationships determine how a filter reaches a measure. A clean one-to-many relationship between a unique product dimension and an order-line fact is straightforward. A many-to-many relationship between customer groups and customers may require a bridge table to avoid ambiguous filtering. Bidirectional filters can solve a particular interaction but also create paths that are difficult to reason about. Before enabling them, draw the intended filter direction and explain the business question that requires the extra behavior.

Test relationships using a small dataset whose expected answers can be calculated by hand. Select one product that appears in two orders and one customer that belongs to two permitted groups. Predict which rows should contribute to a measure after each slicer selection. If the semantic model returns unexpected totals, inspect key uniqueness, null keys, inactive relationships, and cross-filter direction. Changing a DAX formula to compensate for a broken relationship may hide the symptom on one visual and create a new defect elsewhere.

A slowly changing dimension is another useful test of grain. Suppose product P1 belonged to category A when an order was placed but is reassigned to category B next quarter. If the business asks for category performance “as sold,” a historical dimension key or another explicit record of the original category is needed. If it asks how today’s product catalog performed historically, a current-category interpretation may be appropriate. Both results can be valid; a model becomes misleading only when it switches between them without disclosure. Ensure each measure’s meaning aligns with the dimension history used by the relationship.

A model that includes many-to-many relationships also deserves small counterexamples. Create two sales accounts associated with one shared regional manager and an additional account associated with two groups. Filter first by manager, then by group, and predict which transactions should appear. If a total doubles, identify whether bridge cardinality or filter direction permitted the same business event to enter the calculation twice. Do not celebrate a DAX workaround until the relationship’s intended behavior has been restored.

Write DAX with evaluation context in mind

A measure such as total revenue is not a fixed number stored in the model; it is an expression evaluated under the filters in the current query. CALCULATE can modify filter context, iterators such as SUMX evaluate expressions across rows, and variables can make multi-step logic clearer and sometimes avoid repeated work. The challenge is recognizing which rows the expression can see. A percentage-of-total measure may need to remove one dimension filter while preserving date and security constraints.

Build a simple comparison: revenue by supplier, supplier share of category revenue, and year-over-year change for a selected date range. Then change the slicers and ask whether each denominator should move. Distinguish an incorrect result caused by filter logic from one caused by missing transactions upstream. Calculation groups, dynamic format strings, and field parameters can improve a mature model, but they should not be used to disguise inconsistent core measures. Someone reviewing the expression should be able to explain its intended filter behavior in plain language.

Choose model storage from performance evidence

An Import model can provide fast interactive queries for an appropriate dataset but requires a refresh strategy and sufficient capacity. DirectQuery queries a source as reports are used and inherits part of that source’s performance characteristics. Direct Lake can query OneLake-backed Delta data without the conventional import process; design decisions differ between Direct Lake on OneLake and the SQL endpoint variant, especially around security and fallback. None of these modes repairs a faulty grain or an expensive calculation by itself.

Investigate performance in layers. Is the underlying data store partitioned and organized sensibly? Is a visual asking for an enormous high-cardinality result? Does a DAX iterator repeat an expensive computation? Is a security filter changing query shape? Work from observed bottlenecks instead of adding aggregates indiscriminately. A summary table that accelerates one report may become a source of incorrect answers if it does not preserve required dimensions or if refresh timing differs from the detailed fact. Optimize only after verifying semantic equivalence.

For an evaluation-context exercise, suppose a company has 120 units of sales in the north region and 80 in the south. A supplier contributes 30 units in the north and 20 in the south. Under a north-only slicer, its share of the selected region is 30/120, or 25%; its share of total-company sales is 50/200, also 25% in this invented example. Change the numbers so those percentages diverge, then ask which filters a particular business measure should remove. This prevents a coincidental numerical match from disguising an incorrect formula. Inspect the denominator under multiple selections, not only one screenshot.

Keep the model reusable and safe to change

A shared semantic model is an interface for other analysts, not a private collection of calculations. Meaningful names, hidden technical keys where appropriate, documented measures, and consistent formatting reduce the chance that report authors create their own competing definitions. Deprecating a measure or renaming a field should trigger an impact assessment. Reports, calculations, external tools, and downstream consumers may all depend on that interface. Source control and controlled releases help make those changes deliberate.

The boundary between DP-600 and the PL-300 Power BI analyst role matters here. Both involve semantic modeling and DAX, but DP-600’s engineering perspective is especially concerned with enterprise assets, storage decisions, governance, and lifecycle. A report author can reasonably ask for an additional product hierarchy; the engineer must decide whether that hierarchy belongs in a shared dimension and what happens to historical results when it changes. Those are different but complementary responsibilities.

Audit the answer from raw record to report

For a practical test, create a small set of marketplace transactions with a refund, duplicate delivery, missing product key, and category reassignment. Build one curated fact and the dimensions it needs. Verify revenue and refunds first through a straightforward query against curated tables, then through DAX measures in the semantic model. Compare outputs under date, category, and customer filters. Write down any intentional difference between historical and current-state classifications.

Finally, invite a second person to build a report without explaining the internal joins. Can they discover the correct measure? Does the model return the intended denominator when a slicer changes? If a product has an unresolved identifier, is its treatment visible rather than silently lost? The exercise measures whether the analytical contract survives a new consumer. For DP-600, trustworthy modeling is less about using the maximum number of features and more about making every reported figure explainable, repeatable, and defensible.