DP-700 - Ingest and Transform Data (33% of the exam) - Section 2.2

Prepare data for loading into a dimensional model, including slowly changing dimensions and surrogate keys.

Design star schema fact and dimension tables within the medallion architecture (bronze, silver, gold), generating surrogate keys to decouple from business keys. Choose between slowly changing dimension Type 1 overwrite and Type 2 history-preserving row versioning to meet retention requirements.

star schema fact and dimension tablesslowly changing dimensions (Type 1 and Type 2)surrogate keys and business keysthe medallion architecture (bronze, silver, gold)conformed dimensions

Practice question for this objective

Free sampleIngest and Transform Datahard

A Microsoft Fabric medallion solution lands raw source extracts in a bronze Lakehouse layer and applies cleansing, deduplication, and type conformance in a silver layer. The team now needs to add the star schema for Power BI reporting, with surrogate keys, Type 2 dimension history, and fact tables built at a defined grain. In which layer of the medallion architecture should this dimensional model be built?

AIn the bronze layer, alongside the raw extracts, so the dimensional model sits as close to the original ingested data as possible and avoids an extra transformation hop.
BIn the silver layer, replacing the cleansed tables, because once data is cleansed it should immediately be reshaped into facts and dimensions to avoid maintaining a separate layer.
CSplit across silver and gold, keeping dimensions in silver and facts in gold, because dimensions change slowly and facts are query-facing while dimensions are not.
DIn the gold layer, built from the cleansed silver tables, because gold is where business-ready, query-optimised star schemas with surrogate keys and conformed dimensions belong. Correct

In the medallion architecture, the business-ready dimensional star schema with surrogate keys and Type 2 history is built in the gold layer from cleansed silver data. The medallion architecture layers raw landing in bronze, cleansed and conformed data in silver, and business-ready consumption models in gold; a star schema with surrogate keys, Type 2 dimensions, and defined-grain facts is a consumption model, so it is built in gold from the silver tables, keeping raw, cleansed, and modelled responsibilities cleanly separated.

Why A is wrong: Bronze holds raw, unmodelled data exactly as ingested; building surrogate keys and Type 2 history there mixes raw landing with curated modelling and defeats the layering the medallion architecture exists to provide.

Why B is wrong: Silver is for cleansed, conformed but still largely source-shaped tables; overwriting it with the star schema collapses two distinct responsibilities and removes the reusable cleansed layer that multiple gold models may share.

Why C is wrong: Facts and their dimensions form one consumption model and must sit together so surrogate keys line up; placing dimensions in silver and facts in gold separates the star schema across layers and breaks the clean consumption boundary.

Why D is correct: The gold layer holds business-ready, consumption-optimised models, so the star schema with surrogate keys, Type 2 dimensions, and defined-grain facts is built there from cleansed silver data, ready for Power BI reporting.

See more DP-700 practice questions, answers explained.

Exam traps in Ingest and Transform Data

Answers that look right on this material and are not. Each one is a distractor from a different question in the DP-700 bank for this domain.

Store CustomerCode and ProductCode directly on the fact row and resolve them to dimension rows at query time, so the fact stays close to the source and no lookup is needed during loading.
Why it is wrong: Keeping business keys on the fact pushes the version resolution to every query and cannot pin a Type 2 row, since a business key maps to many versions; the fact would join to all versions rather than the one current at the sale.
Use the source CustomerCode as the dimension primary key, since the business key already identifies each customer uniquely in the source system.
Why it is wrong: CustomerCode is a business key that can be reassigned and maps to many Type 2 versions, so it cannot uniquely identify a single dimension row that facts must pin to.
Update the existing row in place with the new category, leaving the surrogate key, EffectiveFrom, and EffectiveTo unchanged so the dimension always holds one row per product.
Why it is wrong: Updating in place is the Type 1 pattern; it overwrites the prior category so historical facts can no longer report the old value, which directly contradicts the requirement to keep full history.

Examworthy is not affiliated with or endorsed by Microsoft. Original, blueprint-aligned practice material only.