Transform data

Objective coverage

• Create views, functions, and stored procedures
• Enrich data by adding new columns or tables
• Implement a star schema for a lakehouse or warehouse
• Denormalize data
• Aggregate data
• Merge or join data
• Identify and resolve duplicate data, missing data, or null values
• Convert column data types
• Filter data

Start with a row contract

Before transforming data, finish this sentence: “One row represents…” The answer is the grain. A sales-line table, a shipment-event table, and a monthly budget table can all contain a Product column while representing incompatible grains. A technically valid join can multiply revenue if it ignores that difference. DP-600 transformation work should produce analytical tables whose keys, types, null rules, and measures are explainable, not merely tables that load successfully.

The examples use three order lines: order A, line 1 sells two units for 20 each; order A, line 2 sells one unit for 15; order B, line 1 sells three units for 10 each. Gross line revenue is therefore 40, 15, and 30, totaling 85. These are invented values for hand-checking transformations. A returns table, product dimension, and customer dimension will be added selectively. Always state whether a test concerns rows, business entities, quantities, or monetary amounts.

Create views, functions, and stored procedures

A view names a query for reuse; it does not normally materialize a separately maintained copy just because it has a name. A function returns a value or table according to its supported type and can encapsulate reusable query logic. A stored procedure encapsulates an executable operation, often with parameters and multiple statements. Fabric SQL surfaces differ, so verify support on the warehouse or SQL analytics endpoint actually in use. Familiar SQL Server syntax is not automatically available on every Fabric endpoint.

Choose the object from its contract: reusable relational projection, parameterized expression, or controlled operational workflow. Define inputs, output columns, grain, permissions, and error behavior. Check the current T-SQL surface area before writing code. Create the object in a test environment, execute representative cases, and inspect dependent queries. Keep transformations that require unsupported writes on the appropriate writable engine. Version definitions and use impact analysis before changing names, parameter types, or output schemas consumed downstream.

For a warehouse containing dbo.SalesLine, a simple teaching view could be:

CREATE VIEW dbo.vLineRevenue AS
SELECT OrderId, LineId, ProductKey,
       Quantity * UnitPrice AS GrossRevenue
FROM dbo.SalesLine;

The fixture returns 40, 15, and 30. A caller can aggregate those rows by ProductKey without repeating the multiplication. If the business changes to net revenue after discounts, decide whether the existing view's meaning should change or whether a new contract is safer. A stored procedure that refreshes a curated table would require additional write, transaction, and retry considerations; the view does not perform that workflow.

A reusable object can conceal expensive scans or inconsistent business logic if its contract is undocumented. Investigate incorrect totals by expanding its definition and checking join cardinality, filters, and types. Investigate deployment errors by verifying schema dependencies and endpoint support. Views, functions, and procedures are not interchangeable performance switches. For exam distinctions, ask whether the requirement is to expose a named result, calculate a reusable expression, or orchestrate an operation. Then verify what the selected Fabric SQL surface supports.

Scenario: An analyst needs several reports to reuse the same line-level revenue projection without creating another physical copy. Which SQL object is the natural starting point?

Answer: A supported view can expose the reusable projection. Validate its row grain, expression types, and downstream permissions. Do not assume the view stores a snapshot or makes a complex query inexpensive; measure its actual query behavior and use a physical transformation only when the requirements justify it.

Recall: How does a named view differ from a loaded table? Why must a procedure be checked against the specific Fabric endpoint's supported surface area?

Enrich data by adding new columns or tables

Enrichment adds useful analytical meaning: a derived amount, a category, a calendar attribute, a reference lookup, or a new conformed table. Choose the layer according to reuse and evaluation timing. A stable row-level attribute used across many models often belongs upstream. A measure that depends on report filter context belongs in the semantic layer. Adding columns indiscriminately can increase storage, memory, refresh cost, and privacy exposure. Every derived field should have a definition, type, and owner.

Specify the business rule and its inputs, including missing values and effective dates. Decide whether to calculate once during preparation or dynamically during analysis. Implement the transformation in a suitable engine and validate a small fixture before processing the full dataset. For lookups, assert key uniqueness and define unmatched-row handling. For new tables, document grain and relationships. Compare row counts and control totals before and after enrichment. Check that adding a field did not change the fact table's grain or expose restricted source attributes.

Northstar adds GrossRevenue as Quantity multiplied by UnitPrice and joins a product category lookup. The three-line fixture must still have three rows and total 85 after enrichment. Suppose ProductKey 10 appears twice in the lookup, once in Hardware and once in Accessories. A join can duplicate the corresponding fact rows and inflate revenue. The correct repair is to resolve the lookup's business key or effective-date rule, not to divide the inflated total by two. If a product is missing from the lookup, assign an explicit unknown category or quarantine according to the contract.

An enrichment can be correct row by row but wrong over time. A current product category may reclassify historical sales if the business expected historical classification. Decide between current-state and effective-dated semantics. Troubleshoot unexpected row growth by checking lookup cardinality before examining formulas. Avoid embedding frequently changing business rules in several independent reports when an upstream definition can be governed once. In exam scenarios, distinguish a materialized column from a context-sensitive measure and a lookup from a many-to-many expansion.

Scenario: Adding category names doubles sales totals for one product while the arithmetic expression is unchanged. What should be inspected first?

Answer: Inspect the category lookup for multiple matching rows per product and examine the join's effective-date conditions. Reconcile the intended one-row-per-line grain and expected total of 85. Fix the lookup contract before applying deduplication or changing the revenue measure.

Recall: When should an enrichment be a separate dimension table? Why might a current-state lookup change historical reporting without changing any fact rows?

Implement a star schema for a lakehouse or warehouse

A star schema separates measurable business events in fact tables from descriptive attributes in dimension tables. The physical lakehouse or warehouse design prepares stable keys and grains for analytical consumption. A surrogate key identifies a dimension row independently of a source system's natural key. Slowly changing dimensions specify whether historical facts should retain the descriptive state that existed at event time. The semantic model later adds relationships and measures, but it cannot reliably repair a physical table whose grain is undefined.

Choose a business process and declare its fact grain. Identify conformed dimensions and their keys. Define how source business keys map to surrogate keys, including unknown members and late arrivals. Decide which attributes use current-state replacement and which require effective-dated history. Load or resolve dimensions before assigning fact keys, then validate uniqueness and referential coverage. Reconcile fact counts and additive measures against source control totals. Keep budget, inventory snapshots, and transactions separate when their grains differ.

The SalesLine fact has one row per order line, with DateKey, ProductKey, CustomerKey, Quantity, and GrossRevenue. Customer C7 moves from West to East on July 1. If the business needs historical regional attribution, two dimension versions can have different surrogate keys and effective intervals. A June sale references the West version; an August sale references the East version. Joining facts to the latest customer row by business key would instead report both sales in East. The choice is a business-history decision, not merely a join optimization.

Validate effective-date intervals for overlaps and gaps. A fact that matches two dimension versions can multiply totals, while a fact that matches none can vanish in an inner join. Use an explicit unknown-member policy rather than silently dropping facts. Surrogate keys do not remove the need for source-key lineage. On the exam, distinguish the physical preparation of dimensional tables from semantic-model relationship configuration. Also distinguish a transaction fact from a periodic snapshot: inventory balances usually should not be summed over dates like sales amounts.

Scenario: A customer moved regions, and last year's sales must remain attributed to the old region. Is replacing the dimension's Region value sufficient?

Answer: No. Use an effective-dated historical dimension design where required, assign the correct dimension version to each fact, and validate the date intervals. Replacing the current attribute produces current-state reporting and changes historical attribution. Document which interpretation the business intends.

Recall: Why is the natural customer key insufficient to distinguish historical dimension versions? What checks detect overlapping validity intervals?

Denormalize data

Denormalization reshapes related information for a consumption pattern, often reducing the number of joins required by downstream analysis. It can flatten a normalized category hierarchy into a product dimension or attach stable descriptive attributes to a curated table. The goal is useful analytical shape, not flattening every relationship into one huge table. Repeating a dimension attribute on many fact rows has storage and consistency costs; repeating a measure across a one-to-many join can change its meaning.

Identify the target grain and the relationship cardinality of every source. Select the descriptive attributes consumers need. Resolve duplicate keys or effective-date ambiguity before joining. Use a join that preserves the intended population and define unmatched behavior. Compare row counts, distinct business keys, and measure totals before and after the transformation. Document the refresh dependency for copied attributes. If a source attribute changes, decide whether the denormalized representation should reflect the current value or retain the historical one.

Product refers to Subcategory, which refers to Category. Northstar flattens the two names into DimProduct so report users can navigate Product, Subcategory, and Category without a chain of separate dimension tables. Each ProductKey must still appear once in the current-state dimension. In contrast, adding all shipment events directly to SalesLine can repeat a sale several times. If the analytical question needs the latest shipment status, first reduce shipment events to the agreed single row per shipment or build a separate event fact with explicit relationships.

The principal failure is a hidden grain change. A row-count increase might be legitimate when producing a new event-level table, but it is wrong when the target contract still says one row per sales line. A DISTINCT added after the join may conceal the symptom without resolving which event or attribute is authoritative. Trace duplicates back to their business meaning. For exam distinctions, flattening a descriptive hierarchy is different from multiplying facts through a many-side table. Good denormalization preserves a deliberate analytical contract.

Scenario: A denormalized sales table repeats every order line once for each delivery status event. The business wants line-level revenue and the latest status. What transformation is needed?

Answer: Reduce events to a deterministic latest-status result at the required join key before joining to sales, or keep events as a separate fact if event analysis is needed. Validate tie handling and preserve the line-level total. Do not sum repeated revenue or use arbitrary duplicate removal.

Recall: Why can a dimension hierarchy often be flattened safely? What evidence would justify a deliberate change in grain?

Aggregate data

Aggregation summarizes rows at a new grain. SUM, COUNT, MIN, MAX, and averages answer different questions, and not every measure is additive across every dimension. Sales amount is often additive; inventory balance across dates is usually not. A ratio such as margin percentage should often be recomputed from aggregated numerator and denominator rather than averaged from row percentages. The output grain must be explicit because detail removed by grouping cannot later be recovered from the aggregate alone.

Specify grouping columns and the meaning of every output measure. Decide how nulls, duplicates, returns, and currency affect the calculation. Reduce or resolve duplicate business events before aggregation. Validate both group-level results and the grand total using a small fixture. If an aggregate will serve a semantic model, retain the dimensions and security granularity required by its queries. Measure performance only after verifying that the aggregate can answer the intended questions without silently changing definitions.

The three sales lines total 85. Quantities are two, one, and three, so the total quantity is six. Average unit revenue weighted by quantity is 85 divided by 6, approximately 14.17. Averaging the three unit prices gives 15, which answers a different question: the unweighted mean price across lines. A monthly revenue aggregate can store total revenue and total quantity so the weighted result remains computable. Storing only an average discards the weights needed for a correct higher-level rollup.

Always label counts: COUNT of non-null values, COUNT of rows, and distinct count of orders can differ. For snapshot measures, define the relevant date selection rather than summing all snapshots. When totals disagree, inspect grouping keys, duplicate inputs, null behavior, and the measure's additivity. A faster pre-aggregation is unsuitable if it removes the region needed for RLS or the date detail needed for drilldown. Exam scenarios often reward preserving numerators and denominators instead of averaging averages.

Scenario: Two stores report average selling prices of 10 and 30, but one sold 100 units and the other sold one. Is 20 the combined average price per unit?

Answer: No. Compute total revenue divided by total units: (1,000 + 30) divided by 101, approximately 10.20. Retain additive revenue and quantity when building aggregates. The unweighted average of store averages gives each store equal influence and answers a different question.

Recall: Why can a monthly average be insufficient for a quarterly rollup? Which inventory question requires a last-date balance rather than a sum over dates?

Merge or join data

A join combines rows according to matching conditions. Inner joins retain matches; left joins preserve the left population; anti joins help find unmatched records. In Power Query, merge is a join-like combination of tables, while append stacks compatible rows. Join keys require compatible types and business meaning. Two columns both named Id do not necessarily identify the same entity. Cardinality determines whether one input row remains one output row or expands into several matches.

Write the intended output grain, key columns, expected cardinality, and unmatched-row policy. Normalize types and valid key formatting without destroying meaningful distinctions such as leading zeros. Check uniqueness on the side expected to have one row per key. Choose the join type from the required population. Execute a small fixture with matched, unmatched, duplicate-key, and null-key cases. Compare output counts and sums. For effective-dated joins, include the time predicate and verify a single valid dimension match for each fact.

Suppose SalesLine has three rows and DimProduct has one row for each of two products, but the third product is missing. An inner join yields two rows and can understate total revenue. A left join retains all three, making the missing product visible for an unknown-member or quarantine policy. If DimProduct instead contains two rows for one ProductKey, the same left join can produce four rows. Both failures are diagnosed by examining match cardinality and unmatched keys, not by changing the SUM expression in the report.

Null equality and type conversions deserve explicit tests. SQL does not treat NULL = NULL as true in ordinary join predicates. Other transformation tools can have different matching semantics, so do not transfer assumptions blindly. Trimming whitespace may be appropriate for a messy natural key but harmful if whitespace is meaningful. Concatenating compound keys without delimiters can create collisions. For the exam, distinguish append from merge and left joins from inner joins using the population that must survive the transformation.

Scenario: A product lookup should enrich every sales line, including lines with an unknown product. Which join should be considered, and what additional checks are required?

Answer: Consider a left join from sales to the lookup. Assert lookup uniqueness, inspect unmatched keys, assign the documented unknown handling, and reconcile row count and revenue. A left join preserves the left population but does not prevent multiplication when the lookup contains duplicate keys.

Recall: Why can a left join still inflate totals? When does append answer a different business question from merge?

Identify and resolve duplicate data, missing data, or null values

Data quality rules should express business identity and meaning. A duplicate may be a repeated event delivery, a second version of the same record, or a legitimate separate transaction. Null may mean unknown, not applicable, not yet available, or failed conversion. Those meanings should not all be replaced with zero. Define a deterministic winner for versioned records and a visible policy for invalid or unresolved rows. Quality work is part of analytical correctness, not merely cosmetic cleanup.

Profile keys, null rates, allowed values, and control totals. Classify errors by their effect on the business question. Deduplicate using the correct business key and an ordered version or timestamp with a tie-breaker. Route records that cannot be resolved safely to a documented exception path. Preserve source values and reason codes where appropriate for investigation. After correction, reconcile accepted, rejected, and pending populations. Reprocess a known corrected record to prove that the quality workflow does not permanently lose recoverable data.

Order A line 1 appears twice because an ingestion retry replayed it. Both copies share the same source version, so keeping one is appropriate. Later, the source sends a new version with quantity three rather than two. Deduplicating only on the complete row would retain both versions and double-count the business event. Deduplicating on the line key with the newest trusted version preserves the current-state contract. If two rows have the same version but conflicting amounts, quarantine or escalate the conflict instead of choosing whichever row happens to be read first.

A smaller row count is not proof of improved quality. Arbitrary duplicate removal can erase valid lines, and replacing missing price with zero can hide lost revenue. Use controls that explain every removed or changed row. Inspect nulls introduced by failed joins separately from nulls present at the source. For exam scenarios, select a correction that preserves business meaning and auditability. “Remove duplicates” is only defensible when the key and survivor rule are specified. A quality threshold should fail visibly when the unresolved population exceeds the agreed tolerance.

Scenario: A repeated order line contains a newer correction to its price. Should complete-row DISTINCT be used to remove the duplicate?

Answer: No. The two rows differ, so complete-row DISTINCT would preserve both. Resolve versions by the business line key and trusted ordering, retaining the correct version for the intended current-state or historical contract. Investigate conflicting ties and reconcile the corrected total.

Recall: Why is unknown price different from a confirmed zero price? How would you prove that quarantined records can be repaired and replayed?

Convert column data types

Types determine which operations are valid and how values are interpreted. A numeric-looking product code may need to remain text to preserve leading zeros. Money requires deliberate precision and rounding. Dates and timestamps require locale and time-zone interpretation. Converting a column is therefore a semantic transformation, not just a storage optimization. Errors should be visible rather than silently converted into valid-looking defaults. The same string can mean different dates under different cultures.

Profile the source format and specify the target type, precision, scale, locale, and time-zone policy. Test boundary values, invalid strings, nulls, and overflow cases. Preserve original values when conversion failures need investigation. Convert before comparisons or joins that depend on the target semantics, while checking whether the operation can be pushed to the source. Validate round trips where appropriate, and compare totals after numeric conversions. Document where rounding occurs so that row-level and aggregate calculations do not unexpectedly disagree.

The string 03/04/2026 could mean March 4 or April 3. Northstar's supplier contract specifies day/month/year, so the intended date is April 3. A locale-aware transformation should enforce that interpretation and flag impossible dates. Product code 0012 remains text so it does not become 12 and collide with another coding system. A price of 19.995 requires an explicit business rounding rule; rounding each line and rounding the final total can produce different results. The expected result belongs in the acceptance fixture.

A conversion that succeeds on a preview sample may fail later on a different locale or extreme value. Inspect error rows and distribution changes, not just the column's displayed type icon. Avoid implicit casts on large join or filter keys when they obscure index or pushdown opportunities and can produce conversion failures. SQL types, Power Query types, and semantic-model types are related but not identical contracts. For exam distinctions, choose the type based on meaning and supported operations, not the appearance of the first few values.

Scenario: Customer codes lose leading zeros after a numeric conversion, causing unmatched joins to a text-key dimension. What correction preserves the business identifier?

Answer: Treat the customer code as text with a documented normalization rule and restore it from the original source representation where necessary. Do not invent missing zeros unless a fixed-width contract establishes the correct value. Retest matches, uniqueness, and unmatched populations.

Recall: Why should locale be explicit when parsing dates? How can rounding each transaction differ from rounding a summed amount?

Filter data

Filtering defines the population available to later steps. A source filter can reduce transfer and computation, but an incorrect filter can permanently exclude required rows from a curated dataset. Distinguish business scope filters, quality filters, incremental extraction boundaries, and security controls. Removing cancelled orders may fit a booked-sales analysis but undermine a cancellation-rate analysis. A transformation filter is not a substitute for a governed authorization mechanism, especially when users can access the unfiltered source.

Write the inclusion rule in business language, then translate it into an explicit predicate. Specify boundary behavior for dates, nulls, status changes, and time zones. Apply the filter at an efficient supported stage without changing its meaning. Verify folding or source query behavior when pushdown matters. Test a record just before, exactly at, and just after each boundary. Reconcile included and excluded populations. For incremental extraction, ensure updates and late arrivals cannot fall permanently outside the selection window.

To select a July time window, use a half-open interval: timestamp greater than or equal to July 1 and less than August 1, in the agreed time zone. This avoids relying on a fabricated last millisecond of July and prevents an August 1 boundary row from appearing in both months. If OrderStatus is nullable, a predicate such as OrderStatus <> 'Cancelled' in SQL does not automatically include null statuses; unknown values require an explicit policy. Test that policy rather than assuming every nonmatching string survives.

Filters interact with joins and aggregation. Filtering the right side of a left join in a WHERE clause can remove unmatched left rows, effectively changing the intended population. Filtering after aggregation answers a group-level question, while filtering before aggregation changes the records contributing to groups. When results are too small, inspect each stage's row counts and rejected keys. Exam questions may distinguish a row filter from HAVING, a date boundary from a refresh schedule, or a transformation rule from RLS.

Scenario: Consecutive monthly extracts both include rows exactly at midnight on August 1. How should the boundary predicates be corrected?

Answer: Use nonoverlapping half-open intervals, such as JulyStart <= timestamp < AugustStart and AugustStart <= timestamp < SeptemberStart, using the same defined time zone. Test the boundary row explicitly and reconcile totals before loading the corrected extracts.

Recall: Why can a WHERE predicate on the right table undermine a left join? What is the difference between filtering rows before aggregation and filtering groups afterward?

Shared Fabric foundations

The following field notes adapt this project’s original DP-700 explanations for the shared Fabric foundation. They provide supporting engineering context for the DP-600 objectives above; they do not add separate measured objectives.

Denormalize data

Denormalization joins entities into a consumption-oriented table to simplify queries and reduce repeated joins. First declare output grain, then select the correct one-to-one or many-to-one joins. A one-to-many join can multiply rows and measures; validate counts and key uniqueness before and after.

Group and aggregate data

Grouping changes grain. Every nonaggregated output column must be a grouping key. Decide whether measures are additive, semi-additive, or nonadditive. SUM(Amount) can be additive; inventory snapshots should not be summed across time; ratios should normally be recomputed from numerator and denominator.

Handle duplicate, missing, and late-arriving data

• Duplicates: define the business/event key, rank by trusted version/time, retain one winner, and record discarded rows.
• Missing values: distinguish unknown, not applicable, and invalid. Impute only with a defensible rule; otherwise quarantine or preserve null with a quality flag.
• Late data: use overlap windows and event/business time; reopen affected partitions or dimensions and restate downstream aggregates when policy requires it.

Quality handling must be observable. Track rule, count, sample, source, run, and disposition. Silently dropping bad rows makes a pipeline look successful while corrupting completeness.

Dataflow Gen2 transformation clinic

Tool choice combines authoring model and execution locality. Dataflow Gen2 uses the Power Query engine and a visual sequence of M transformations. A notebook uses code and Spark for distributed, library-driven processing. T-SQL executes set-based relational logic near warehouse data. KQL executes pipeline-shaped event and time-series logic near Eventhouse data. Moving data to a favorite language can cost more than using the engine already holding it.

A disciplined Dataflow Gen2 build follows this order: connect with a governed connection; profile source columns; set locale-aware types; remove unused columns and filter rows early; normalize text and null representations; define keys; merge or append; reshape; calculate derived values; validate row counts and errors; select a supported destination and update method; publish and monitor refresh. Name queries and steps for business meaning. Separate staging queries from destination queries and disable load for helpers when appropriate.

Types are semantic, not cosmetic. Text "01/02/2026" can mean different dates by locale; decimal currency and floating point have different guarantees; joining numeric 42 to text "42" can fail or coerce unexpectedly. Assign type with an explicit locale, inspect conversion errors, and decide whether invalid values are corrected, replaced, or quarantined. Use trim/clean and case rules before key comparison. Replace null only when zero or a default has defensible business meaning; “unknown” and “none” are not generally interchangeable.

Merge joins columns using key equality and a join kind: left outer preserves all left rows; inner keeps matches; anti joins isolate missing/unexpected keys. Append stacks rows and aligns columns by name, producing null for absent fields. Before merging, prove uniqueness on the expected one-side. Afterward, compare row counts and unmatched keys. Before appending monthly extracts, standardize names and types and add source-period metadata. A fuzzy merge may help entity matching but requires thresholds and human-reviewed error cases; it is not a substitute for a governed key.

Group By changes grain and should output only grouping keys plus aggregates. Pivot turns values into columns and needs an aggregation when a cell has multiple rows. Unpivot turns repeated columns such as Jan, Feb, Mar into attribute/value rows and is often safer for evolving periods. Split, extract, and conditional columns should preserve the original value until validation is complete. Compute ratios from aggregated numerator and denominator rather than averaging row-level percentages unless the measure definition explicitly calls for it.

Query folding lets the connector translate supported Power Query steps into a source query. Place selective filters, projections, and supported joins early; inspect folding indicators or native query where available. A custom function, unsupported type conversion, privacy boundary, or nonfoldable source can stop folding at that step and everything after it. The consequence is often a large source read and mashup-engine processing—not refresh failure. Use staged/native queries cautiously, preserve parameterization, and compare source rows scanned plus refresh duration before and after a change.

Choose Dataflow Gen2 when maintainers value visual Power Query, transformations are tabular, and managed destinations fit. Choose notebooks for complex algorithms, custom packages, reusable tests, distributed tuning, or mixed file/table work. Choose T-SQL for relational transformations within a warehouse, especially joins, window functions, and dimensional loads. Choose KQL for logs, dynamic payloads, time bins, sequence, and high-rate event analysis. Limitations include Dataflow folding/connector variability, notebook startup and engineering overhead, T-SQL's relational boundary, and KQL's different update/serving model.

Worked Dataflow. Import Orders and Customers; explicitly type OrderDate, CustomerId, and Amount; filter to the requested period; left anti-join Orders to Customers to create an unmatched-key quality query; left join valid orders to Customers; unpivot monthly target columns; group by Region and Month; calculate Revenue and OrderCount; load the curated output and quality output separately. Verify folding through the source filter and projection, then compare input rows, valid rows, unmatched rows, and output totals.

For Dataflow failures, open refresh details and find the first failing query and step. Separate connection/gateway errors, source schema drift, type conversion, privacy/firewall constraints, folding/performance, and destination write errors. Reproduce with a filtered representative sample but retest at scale. For wrong results, check join cardinality, nulls, locale/types, grouping grain, and implicit conversions. Mini-lab: build the worked flow, deliberately duplicate a Customer key and add an invalid date; prove the quality outputs expose both without silently changing sales totals.

Denormalization, aggregation, and quality

Denormalization creates a consumer-oriented shape by joining descriptive data at a declared output grain. Confirm every joined lookup has at most one winning row for each fact at the relevant time. Select only needed attributes and decide whether history is current-state or as-of-event. Compare fact row count, distinct fact key, unmatched count, and additive totals before and after. A flattened table simplifies consumption and can improve scans, but repeats attributes, increases storage/update cost, and can become inconsistent if refresh timing is not governed. Mini-lab: introduce two current dimension rows for one key and show how a $10 fact becomes $20 after the join; repair the winning-row rule.

Aggregation replaces detail grain with grouping grain. Define keys and measure algebra first: additive measures sum across all intended dimensions; semi-additive measures such as balance may sum across accounts but not time; nonadditive ratios and distinct counts need special handling. Track numerator and denominator for recomputable ratios, define null behavior, and avoid double-counting after joins. Incremental aggregate maintenance must restate a bucket when a late change affects it. Mini-lab: calculate daily revenue, closing inventory, average price, and distinct customers; explain why four identical SUM operations would be wrong.

Data quality is an explicit disposition system. Define rules for key uniqueness, required values, valid domains/ranges, referential integrity, and timeliness. For duplicates, keep a winner only from a trusted ordering and retain evidence of losers. For missing data, distinguish unknown, not applicable, delayed, and invalid before defaulting. For late data, use overlap/replay, reopen affected partitions, resolve late dimensions, and restate downstream aggregates. Each run records rule ID, evaluated count, failed count/rate, sample/reference, source version, disposition, and owner.

Quarantine preserves bad records for correction without contaminating curated outputs; it is not a permanent trash folder. Define replay and expiry. If a rule fails suddenly, check source schema/version and code deployment before blaming the data. Mini-lab: process a fixture with one exact duplicate, one newer update, one null amount, one unknown customer, and one yesterday event arriving today; predict the winner, quarantine, unknown-member handling, and which daily aggregate must be restated.

Shortcuts reference selected data in place; mirroring continuously represents a supported database or catalog; Copy Activity moves a bounded selection. Merge joins columns; append stacks rows. Query folding is source execution, not merely successful refresh. Denormalization changes shape and may preserve grain; aggregation intentionally changes grain. A null-replacement step is not a data quality strategy unless the business meaning and audit trail are defined.

Recall. Which store fits concurrent star-schema T-SQL? What evidence proves a Dataflow filter folded? Why can a left join increase row count? How does a shortcut's credential path differ from copied data? Which mirroring mode uses a published change-file contract? Why should aggregate ratios retain components? For practice, take one customer/order dataset through Dataflow, PySpark, SQL, and KQL; document types, joins, quality dispositions, output grain, and expected totals before comparing results.

Batch scenario drills

Dataflow transformation review

A Dataflow reads 40 million warehouse rows, then filters to the last day after a custom text function. Refresh is slow. Inspect folding. Move supported date filter and column projection before the nonfolding custom step, while preserving semantics, and compare source rows/bytes plus duration. If the custom function can be rewritten with foldable native operations, validate it on edge cases.

The same flow merges Customers to Orders. Before accepting it, test Customer key uniqueness, unmatched orders, row count before/after, and total Amount. A single duplicate customer can multiply facts even though refresh succeeds. Keep an anti-join quality output for missing customers and route invalid types rather than turning all errors into null.

Shortcut, mirroring, or copy

Three source cases look similar. Historical Delta tables already in supported cloud storage need shared in-place access: use a shortcut if source availability, security, and direct-read performance are acceptable. A supported operational database needs continuously synchronized analytics with minimal custom logic: evaluate database mirroring. A legacy source needs nightly extracts, column mapping, and a warehouse destination: use pipeline Copy.

For each, state ownership when the source moves or credentials rotate, how deletes/schema changes propagate, whether data is writable in Fabric, and how recovery works. “Avoid a copy” is not sufficient if consumers require isolation from source outages or native Eventhouse features.

Denormalization at the wrong grain

Orders has one row per order, OrderLines has many rows per order, and Payments can also have many rows per order. Joining all three then summing line amount multiplies each line by payment count. Declare the desired grain. Aggregate payments to one row per order before joining to line grain, or keep separate fact tables related through dimensions. Validate distinct line key, row count, and known total.

Denormalization is valuable when it simplifies a stable consumption pattern, but it does not waive dimensional grain. Repeated order attributes are expected; repeated line measures caused by a many-to-many join are not.

Semi-additive inventory

An inventory snapshot records ending quantity by product and warehouse each day. Summing products and warehouses for one day is meaningful; summing seven daily ending balances is not weekly inventory. Select the last valid snapshot for the period, or calculate a defined average, instead of SUM across date. Revenue, by contrast, may be additive across those days.

Create measures with their allowed aggregation dimensions documented. For a late correction to Tuesday, restate aggregates whose chosen Tuesday value or derived period metric changes. Retain numerator/denominator for ratios rather than averaging percentages.

Quality threshold and quarantine replay

A batch contains 0.02% invalid dates under a documented warning threshold of 0.05%. Publish accepted records, quarantine failures with rule and source reference, record the rate, and alert at warning severity. The next day reaches 2%; stop publish according to policy because this likely indicates a source contract change. Last known-good curated data remains available but freshness is now at risk.

After correcting parsing or source data, replay quarantined/bounded source rows, reconcile counts, update affected aggregates, and close the incident. Quarantine needs access control, retention, owner, and replay state; otherwise it merely hides data loss.

Capstone: build a trustworthy sales mart

The source landscape contains a supported operational sales database, customer master Delta tables in external storage, monthly CSV targets, and application logs. The product needs a governed dimensional sales mart, exploratory data science access, and recent operational monitoring.

Place and move the data

Evaluate database mirroring for supported sales tables that need low-latency analytical replication without custom transforms. Use an external OneLake shortcut for the customer master Delta table if source availability, credential path, security, and schema ownership are acceptable. Use pipeline Copy for CSV targets because they need scheduled acquisition, explicit mapping, file/run metadata, and quarantine. Land application logs in the route best suited to Eventhouse/KQL when time-series exploration dominates.

The lakehouse holds open bronze and engineering tables used by Spark. The warehouse serves the conformed star schema to T-SQL/BI consumers. This is not unnecessary duplication if each representation has a documented workload and refresh contract. Avoid copying the customer master again merely by habit, but create a governed snapshot if consumers require isolation from source change or outage.

Transform and validate

Build Customer shaping in Dataflow Gen2 for maintainers who own Power Query: explicit locale-aware types, trimmed/cased business key, anti-join for missing reference values, duplicate detection, address cleanup, and a curated destination. Preserve query folding for source filtering/projection as far as supported and inspect it. Use notebooks for high-volume/order logic requiring distributed processing and reusable tests. Use warehouse T-SQL for dimension and fact application near the final model. Use KQL for log/time-window analysis rather than moving logs into SQL only for language familiarity.

Declare SalesFact grain: one row per posted order line. Load Customer and Product dimensions before facts. Use surrogate keys, a stable unknown member, and explicit Type 1/Type 2 attributes. Validate one current Type 2 row per business key, nonoverlapping validity, fact foreign keys, distinct order-line key, row count, quantity and amount controls.

Quality fixture

Create a ten-row test fixture containing:

• two versions of one order line, with the newer source sequence winning;
• one exact duplicate delivery;
• one missing customer that uses the governed unknown/inferred path;
• one customer business key duplicated in the dimension staging data;
• one invalid localized date;
• one null amount whose business rule forbids publish;
• one late order affecting yesterday's regional total; and
• one valid order at a Type 2 validity boundary.

Write expected accepted, quarantined, superseded, and late-restatement counts before execution. The duplicate dimension key must block or quarantine the ambiguous dimension—not multiply fact totals. The late order must restate the affected daily aggregate. The invalid date and forbidden null remain diagnosable with rule and source reference.

Equivalent transformations

Implement one filter, join, and regional aggregate in PySpark, T-SQL, and KQL on the same normalized fixture. Compare key/type/null semantics and ordered output. Do not claim equivalence from similar syntax. Power Query's Merge and Group By version should produce the same declared output grain. Retain numerator and denominator for average order value, then compute the ratio after aggregation.

Operations and change

Monitor mirroring lag/change application, shortcut connectivity, Copy rows and rejects, Dataflow refresh/folding/destination, notebook stages, warehouse load controls, and consumer freshness. Rotate the shortcut connection in Test and prove denied/allowed behavior. Rename a CSV column and verify schema handling fails visibly. Move a disposable shortcut target and diagnose the reference without deleting source data.

The capstone is complete when every source has an accountable access/update path; every tool owns a justified layer; the quality fixture matches expected disposition; star-schema totals reconcile; late data restates the right period; reruns are idempotent; and the published mart includes source/run/watermark and quality evidence.

Prepare data for loading into a dimensional model

Declare a fact table's grain in one sentence before selecting columns—for example, “one row per order line at posting time.” Every fact and dimension key must agree with that grain.

Dimensions describe business entities and normally use warehouse surrogate keys. Facts store measurements and foreign keys to dimensions. Load dimensions before facts so natural/business keys can be resolved to surrogate keys.

A Type 2 row commonly has ValidFrom, ValidTo, and IsCurrent. Detect a change in tracked attributes, expire the current record, and insert a new surrogate-keyed version. Facts resolve the version valid at the event's business time.

Late-arriving dimensions require an unknown/inferred member rather than rejecting every fact. Load the fact against that stable surrogate key, then update or restate according to the chosen policy when the dimension arrives. Degenerate dimensions such as order number can remain on the fact when they have no useful descriptive dimension row.

Validate uniqueness of dimension business keys, referential integrity, fact counts, additive behavior, and totals against a trusted control. A star schema is not merely denormalization; it is a deliberate grain-and-relationship model for analytics.

Dimensional loading field guide

Dimensional loading preserves a declared analytical grain while translating source business keys into warehouse surrogate keys. Dimensions supply durable descriptive context; facts record events or measurements at the chosen grain. A Type 1 change corrects or replaces current attributes. A Type 2 change creates a new version so a historical fact can resolve the attributes valid at its business time. The order is normally stage → validate → load dimensions → resolve keys → load facts → reconcile.

Profile each dimension business key for nulls and duplicates. Insert a stable unknown member. For Type 1, update changed tracked columns. For Type 2, compare a normalized attribute hash or explicit columns; expire the current record and insert a new surrogate-keyed row with nonoverlapping validity. Then join staged facts to the dimension on business key and event time where history applies. Reject or route facts that violate grain, and use the unknown/inferred member for permitted late dimensions. Validate foreign keys and totals before publish.

Use Type 1 for corrections or attributes whose history has no analytical value; use Type 2 when reports must reproduce the past. Do not make every attribute Type 2: it increases row count and fact-key lookup complexity. Choose an accumulating snapshot for a process whose milestones update one logical row, a periodic snapshot for measurements at regular periods, and a transaction fact for individual events. A degenerate identifier belongs on the fact when it has no useful dimension attributes.

Worked late member. Order 501 arrives for customer C42, but the customer dimension has no C42. The fact load uses surrogate key 0, the governed unknown member, and records the unresolved business key. When C42 arrives, the dimension receives a real surrogate key. Policy determines whether the order is restated to that key or remains unknown for the historical load. Silently dropping the order would preserve referential cleanliness by destroying completeness—an unacceptable trade.

If fact totals multiply, verify the fact grain and dimension join is one winning row per business key and event time. If a Type 2 dimension has two current rows, inspect concurrent loads and enforce a serialized/keyed transition. If many facts use the unknown key, alert on rate and age, then inspect dimension timing, normalization, and source keys. Mini-lab: load one customer, change a Type 1 attribute and a Type 2 attribute, then load facts before and after the change; write the expected surrogate keys and historical report output before running.

Sources and provenance

The measured objectives follow the DP-600 blueprint effective July 21, 2026. Product references were retrieved for this edition; the book manifest records the actual retrieval time and SHA-256 of each downloaded HTML response. Examples and scenarios are original teaching material. Shared field guides reuse this project’s original DP-700 writing where the underlying Fabric concepts apply. Microsoft Learn remains authoritative, especially for current feature support and capacity limits.

• Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
• T-SQL Surface Area in Fabric Data Warehouse - Microsoft Fabric
• Understand star schema and the importance for Power BI - Power BI
• Dimensional Modeling - Microsoft Fabric
• Understanding Query Evaluation and Query Folding in Power Query - Power Query
• Comparing Copy Job, Mirroring, Copy Activity, and Eventstreams in Microsoft Fabric - Microsoft Fabric
• Load Tables in a Dimensional Model - Microsoft Fabric
• Differences between Dataflow Gen1 and Dataflow Gen2 - Microsoft Fabric
• Unify Data Sources With OneLake Shortcuts - Microsoft Fabric
• Mirroring - Microsoft Fabric
• Stateful processing with Structured Streaming - Microsoft Fabric