{"id":4602,"date":"2026-10-12T20:52:21","date_gmt":"2026-10-12T20:52:21","guid":{"rendered":"https:\/\/www.exam-topics.com\/blog\/microsoft-dp-700-dimensional-loading-sql-duplicates-late-facts\/"},"modified":"2026-10-21T19:37:55","modified_gmt":"2026-10-21T19:37:55","slug":"microsoft-dp-700-dimensional-loading-sql-duplicates-late-facts","status":"publish","type":"post","link":"https:\/\/www.exam-topics.com\/blog\/microsoft-dp-700-dimensional-loading-sql-duplicates-late-facts\/","title":{"rendered":"Microsoft DP-700: Dimensional Data Loads, SQL, Duplicates and Late Facts"},"content":{"rendered":"

Dimensional loading begins with table grain and a business event<\/h2>\n

A dimensional model is not created by randomly denormalizing tables. In DP-700, preparing data for dimensional use means understanding facts, dimensions, grain, identifiers and how changes are represented so downstream questions can be answered consistently. A fact row must correspond to an explicit business event or snapshot. Without that contract, a join can multiply revenue or leave relationships ambiguous even when it executes successfully.<\/p>\n

Suppose a retailer records order line items, customers and product changes. The sales fact might be one row per order line, with a stable order-line key, quantity, amount and the relevant dimension references. A customer dimension may store descriptive attributes separately. If the fact grain is not defined, someone may aggregate at order-level and then join to line-level discounts, inflating total sales. The transformation requires reasoning about cardinality before writing SQL.<\/p>\n

The current Microsoft DP-700 exam objectives<\/a> explicitly include preparing a dimensional model, denormalizing, grouping, aggregating and treating duplicate, missing and late-arriving data. These are engineering correctness tasks, not simply semantic-model design. The downstream Power BI or warehouse consumer depends on the engineered grain being trustworthy.<\/p>\n

Stage and validate source identities before assigning surrogate keys<\/h2>\n

Source identifiers are not always globally unique or stable. An order system might reuse an integer key after migration, while a product code may change when a catalog is reorganized. Decide which combination constitutes a business key and which attributes should be managed as surrogate keys in analytical dimensions. Record source-system identity when several business applications feed the same mart.<\/p>\n

For example, if two regional systems both contain product ID 100, they may represent different products. A pipeline that deduplicates only on numeric product ID could merge unrelated records. In the staging area, preserve region or source namespace, validate null business keys and quarantine cases without enough evidence to match. Only then assign a deterministic relationship to the appropriate dimensional record.<\/p>\n

Lakehouse data quality<\/a> requires source-contract checks before data enters analytic tables. In a dimensional load the specific objective is referential and analytical correctness: does every fact connect to the right version of a dimension, and does the join preserve the intended business grain?<\/p>\n

Choose a slowly changing dimension policy intentionally<\/h2>\n

A dimension attribute can either reflect the current description or preserve history. Type 1 correction overwrites a value when historical reporting should use the newest attribute. Type 2 preserves an earlier version with effective dating or equivalent version markers when the business must analyze facts under the attribute state at the time. Neither is correct universally. The right policy comes from the reporting contract.<\/p>\n

Consider a customer moving between sales regions. If historical sales must remain attributed to the region in effect when the order occurred, the dimension needs a reliable version boundary and the fact load must look up the matching effective version. If reports instead always reclassify old sales under the customer’s current region, a different model could be acceptable. Failing to decide can make two apparently identical reports disagree.<\/p>\n

A Type 2 merge is not just one overwrite statement. The engineer must close the prior version, insert the next one, avoid overlapping effective ranges and handle duplicate change events. Backdated corrections can require adjustments to fact references or reprocessing, so test on historical samples instead of only inserting a new customer.<\/p>\n

Late-arriving dimensions and facts require a reversible policy<\/h2>\n

An event can arrive before its related dimension record, and transactions can be recorded after the reporting day closes. A dimension lookup that fails should not automatically drop the fact without notice. Depending on the business contract, engineers may use an explicit unknown member, a staged holding queue, or a controlled repair process. Each choice has reporting implications: an unknown bucket might preserve the total while temporarily reducing descriptive quality.<\/p>\n

For a late-arriving fact, decide whether the analytic period should be reopened, what watermark window should be replayed and whether downstream aggregates must be rebuilt. A common error is querying only records later than the last successful event timestamp, losing delayed corrections with older business times. The incremental load<\/a> reference examines source watermarks and replay safety in more depth.<\/p>\n

Define both processing time and event time when the distinction matters. The processing timestamp says when the system observed a record; the business effective timestamp determines where its financial or operational meaning belongs. A record with an old business time should not silently disappear solely because the daily pipeline executed on schedule.<\/p>\n

A late dimension can create an especially subtle error: a fact is loaded against a provisional ‘unknown customer’ surrogate key, and a subsequent lookup inserts the real customer dimension without repairing the fact. The financial amount is still present, but customer-level reports continue to show an unknown bucket. An engineer should define a bounded reconciliation process that finds these provisional references, updates them using supported warehouse or Delta operations, and checks that totals remain invariant. Historical correction must not quietly rewrite attribution rules that the business has already closed and audited.<\/p>\n

Denormalize, group and aggregate without changing meaning<\/h2>\n

Denormalization can make a curated consumer table easier to query, but every join can change row count when its keys are not unique. A product mapping with two active matches will duplicate sales facts; a missing mapping may exclude them under an inner join. Before aggregation, check distinct key counts, expected multiplicity, null handling and the source totals that should be preserved.<\/p>\n

A grouped monthly revenue calculation should identify currency, refunds, tax treatment, time zone and late adjustments. Adding numeric amounts across currencies without conversion is a data-model defect, not a SQL syntax issue. Similarly, calculating an average of averages without considering group weights can produce misleading metrics. The engineering responsibility includes encoding defensible arithmetic and knowing which aggregations consumers will reuse.<\/p>\n

Choose T-SQL or PySpark based on storage and transformation scale, query behavior and supported engine capabilities. A fact load built in a Fabric warehouse can leverage relational operations; a large semi-structured source may be better prepared in Spark before loading dimensional tables. The tool is secondary to preserving grain, provenance and correctness.<\/p>\n

Test a full and incremental data load as different states<\/h2>\n

A first load establishes a complete baseline for the intended population. Subsequent runs may ingest only changes or recent time windows. The state needed for incremental loading\u2014a checkpoint, change token or watermark\u2014must be protected, and the process should be safe after a partial failure. A full reload can be expensive and may change history unintentionally if dimensions are reconstructed without stable keys.<\/p>\n

For test data, include one new order, one corrected amount, one deleted source row, a duplicated source event and a customer whose region changed. Specify the expected fact and dimension output after the first run and after an identical replay. If the second run creates more facts than the first, the process is not idempotent. If a delete should reverse a quantity but the pipeline only appends, the report can become permanently overstated.<\/p>\n

Document exactly what evidence supports each outcome: source control totals, staged rejected rows, a fact-table uniqueness check, effective-dated dimension constraints and the last successful checkpoint. A green orchestrator status is only one piece of that evidence.<\/p>\n

Separate correction of data from hiding a quality failure<\/h2>\n

A source duplicate might be a retransmitted event or a genuine second order sharing an unreliable key. Discarding it without understanding the source identity can remove valid revenue. A missing dimension may represent an integration delay or a serious data governance defect. An automatic repair should be transparent and reversible and should not silently replace unknown customer attributes with plausible invented values.<\/p>\n

Build a quarantine route for records whose business meaning cannot be established. Retain enough provenance to correct and replay them later, protect sensitive fields, and notify the source owner about recurring patterns. The rate of exceptions helps identify defects in source systems and mappings, not merely failures of the batch job itself.<\/p>\n

Once repaired, reprocess the affected boundary, confirm that the analytical totals change as intended, and record the correction. The goal is an auditable fact table, not a dashboard that looks plausible because bad rows were suppressed.<\/p>\n

Use scenarios to select the right dimensional design<\/h2>\n

For a slowly changing customer status field, ask whether history matters. For late-arriving orders, define the authoritative business period and incremental replay window. For multiple source systems, identify the namespace of each business key. For aggregation, verify the fact grain and whether the operation preserves source totals. Each question leads to a specific validation rule and a reason for the chosen transformation.<\/p>\n

A fictional order mart can be built in a small lakehouse or warehouse and validated without exposing real customer information. If no tenant is available, label the example as conceptual and specify expected input and output tables. Do not imply a SQL deployment succeeded merely because the DDL or MERGE logic looks reasonable.<\/p>\n

DP-700 candidates should be able to defend how a table supports downstream analysis while remaining repairable after source delays or corrections. That is the practical link between data ingestion, transformation and trustworthy analytics.<\/p>\n","protected":false},"excerpt":{"rendered":"

Dimensional loading begins with table grain and a business event A dimensional model is not created by randomly denormalizing tables. In DP-700, preparing data for […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[348,2],"tags":[],"et_vendor":[],"et_program":[],"et_certification":[],"et_exam":[],"et_topic":[],"class_list":["post-4602","post","type-post","status-publish","format-standard","hentry","category-microsoft-dp-700","category-post"],"_links":{"self":[{"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/posts\/4602","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/comments?post=4602"}],"version-history":[{"count":2,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/posts\/4602\/revisions"}],"predecessor-version":[{"id":5846,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/posts\/4602\/revisions\/5846"}],"wp:attachment":[{"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/media?parent=4602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/categories?post=4602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/tags?post=4602"},{"taxonomy":"et_vendor","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/et_vendor?post=4602"},{"taxonomy":"et_program","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/et_program?post=4602"},{"taxonomy":"et_certification","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/et_certification?post=4602"},{"taxonomy":"et_exam","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/et_exam?post=4602"},{"taxonomy":"et_topic","embeddable":true,"href":"https:\/\/www.exam-topics.com\/blog\/wp-json\/wp\/v2\/et_topic?post=4602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}