Room for pencil and paper.
Print a worksheet, or choose “Save as PDF” in your browser’s print dialog.
Mixed skills
10 original practice questions · Untimed · Follow each question’s response instructions
1. A fictional research team is sorting five representations. Select TWO that the descriptions identify as semi-structured, rather than fixed-schema tabular data or unstructured media.
Select all that apply.
dp900-q-0012. A fictional repairs table repeats a supplier telephone number on every repair row. SupplierId identifies a supplier, and one current telephone number belongs to that supplier. Two repairs now disagree after a telephone change. Which redesign directly addresses this update anomaly while retaining the supplier relationship?
dp900-q-0453. A fictional lake already uses Parquet. The team needs to identify which table-level mechanism records committed changes and versions, as distinct from metadata describing the layout of an individual file. Which mechanism directly supplies that role?
dp900-q-0074. Complete fictional tables contain Teams(TeamId): T1,T2,T3 and Visits(VisitId,TeamId): (V1,T1),(V2,T1),(V3,T3). TeamId is unique in Teams. What is the row count of SELECT Teams.TeamId, Visits.VisitId FROM Teams LEFT JOIN Visits ON Teams.TeamId=Visits.TeamId, without DISTINCT or further filtering?
dp900-q-0465. A fictional design asks which Microsoft Fabric component supplies the shared logical data lake used across integrated analytics experiences. Which component matches that role?
dp900-q-0356. A fictional import contract requires contacts to be a JSON array. Every array element must be an object pairing keys type and value. The two source pairs are email/[email protected] and phone/555-0100. Which payload retains both pairs in the required shape?
dp900-q-0047. A fictional Cosmos DB for NoSQL container uses TenantId as its partition key. Records (id='cart',TenantId='T1') and (id='stock',TenantId='T1') must change in one supported transactional operation; another record has TenantId='T2'. Select TWO conclusions supported by the stated design and documented logical-partition boundary.
Select all that apply.
dp900-q-0538. In a fictional booking system, one explicitly atomic transaction must reserve a seat and create its matching ticket. Earlier unrelated reservations are already committed in separate transactions. Reserving succeeds, but ticket creation fails before the transaction commits. Which outcome satisfies the stated all-or-nothing requirement?
dp900-q-0099. A complete fictional table Loans(LoanId, Days, Status) has four rows: (A, 2, 'Open'); (B, 6, 'Closed'); (C, 5, 'Open'); (D, 8, 'Open'). There are no NULL values. How many rows satisfy SELECT LoanId FROM Loans WHERE Status = 'Open' AND Days >= 5?
dp900-q-01710. A fictional order has a purchase-time delivery address. A customer also has a current address that may later change. An audit requires the original order address. Which interpretation of normalization respects what those attributes mean?
dp900-q-019Answers & reasoning
1. JSON device records whose optional property names vary from device to device; Tagged event records whose permitted payload fields vary by event type
Classify the representation described, rather than deciding from the product name alone. The varying optional properties and event-specific payload fields both provide organization without one fixed set of fields for every record. The declared CSV columns and SQL columns define fixed tabular schemas in these descriptions. Audio recordings are the unstructured media example. JSON can be used with a fixed schema too; the stated field variation controls this case. Check a changed condition: if the event contract instead required exactly the same declared fields on every record, variable payloads would no longer be the stated reason to call it semi-structured. The answer is tied to the descriptions, not to a permanent ranking of file extensions. Audio metadata can be tabular without making the audio itself a fixed-schema record.
dp900-q-0012. Store the current telephone once in Suppliers and reference SupplierId from Repairs · Choice B
The repeated value describes the supplier, not a separate fact about each repair. Updating only some repair rows leaves competing copies of that same current supplier fact. Separate Suppliers(SupplierId, Telephone) from Repairs(RepairId, SupplierId, ...), so one supplier record owns that current telephone value. The reference preserves the relationship; it does not erase the supplier information or turn telephone text into a stable repair identity. An actual design must also choose and enforce the appropriate key constraints. This task states that the value is current supplier information. If a repair instead required a historical contact snapshot, that different fact would need its own explicit design.
dp900-q-0453. The Delta Lake transaction log · Choice A
Distinguish table changes over time from information about reading a file. Delta Lake adds a transaction log over Parquet to support table transactions and versioning. Row-group statistics, partition directories and compression metadata serve useful storage/read purposes, but they are not that table-level record of committed versions. Imagine two writers producing new Parquet files: file-layout metadata can describe their columns, but the table still needs a record of which changes form a committed version. That is the table-level transaction-log role. A directory full of compatible files is not, just from the extension, evidence of a particular committed table state. This distinction also prevents treating the log as the payload of every data column.
dp900-q-0074. 4 rows · Choice B
Enumerate the matches for each left-side row. T1 matches V1 and V2 and therefore contributes two joined rows. T2 has no matching visit, so the LEFT JOIN retains one T2 row with NULL visit fields. T3 matches V3 and contributes one row. The total is 2+1+1=4. A left join retains every left row, not exactly one output row per left key. Multiple right-side matches still multiply that key in the result. There is no DISTINCT or aggregation to remove the two T1 outputs. The supplied values determine this result; it is a small portable SQL semantics exercise, not a claim about a service performance guarantee.
dp900-q-0465. OneLake · Choice D
The role is shared logical data-lake storage, not a particular curated warehouse, ingestion/orchestration experience or reporting experience. OneLake provides that Fabric foundation. The other experiences use or work with data but are not the shared logical lake identified here. Separate the shared storage foundation from an experience that ingests, transforms, queries or presents data. OneLake supplies the named logical-lake role across Fabric; a warehouse or report can depend on that foundation without being its synonym. The distinction prevents selecting a familiar analytics screen solely because it also accesses data. This is a conceptual role match, not a statement that all experiences expose identical interfaces.
dp900-q-0356. {"contacts":[{"type":"email","value":"[email protected]"},{"type":"phone","value":"555-0100"}]} · Choice B
All proposals can convey the two supplied pairs, but the receiver also requires a particular JSON structure. An array uses square brackets; each element must be an object with its own type/value pair. The second payload meets all of those conditions. An object keyed by contact type, parallel arrays and arrays of pairs are different shapes, even when a separate reader could interpret their information. Validate the nesting before reading the email text: contacts must be an array, and each array member must keep its own type/value association. Two unrelated arrays of types and values could lose those pairings after a later reorder, while one flat object cannot represent both required contact objects in the specified shape. Correct-looking strings are insufficient when their containing structure violates the import contract.
dp900-q-0047. The cart and stock records share a logical partition because both key values are T1; A supported transaction scoped to T1 can atomically include the two T1 records
Partition membership follows the chosen partition-key value, not the item name or container name alone. Both required records have TenantId T1, so a supported transaction within that logical partition can include them. Their different item ids do not separate the partition. The T2 record belongs to another logical partition even if it shares a container or item id text with one T1 record. The cited in-partition guarantee therefore does not establish a three-record cross-partition atomic update. This is a boundary check, not a recommendation to choose TenantId without workload and scale analysis.
dp900-q-0538. Undo the reservation so that neither change is committed · Choice B
Treat the two changes as one transaction, as the stem explicitly requires. Failure before commit means that a half-completed result is not an acceptable final state. Undoing the reservation leaves neither change committed. Atomicity does not mean that a successful first step may be retained while a failed second step is silently deferred. Keep the earlier unrelated committed reservations intact. They are outside the failed transaction; rolling back its two-operation unit does not undo those separate committed transactions. Draw the boundary around this reservation and ticket creation, not around the entire application's history. Keeping only the seat reservation would leave the promised pair incomplete. Earlier successful transactions belong to separate commit units and are not undone merely because this later unit fails. Atomicity decides whether this unit's changes persist together; it is not a guarantee that the underlying booking operation never encounters an error.
dp900-q-0099. 2 rows · Choice B
Check both predicates for each row: A fails Days >= 5; B fails Status = 'Open'; C passes both, including the equality boundary; D passes both. The result contains C and D, so the count is 2. Ignoring Status counts B, C and D (3). Replacing >= with > keeps only D (1). Replacing AND with OR admits A, B, C and D (4). Enumerate each supplied row against both predicates: A is Open but below five; B meets the numeric threshold but is Closed; C is Open at the inclusive boundary; D is Open above it. Only C and D survive. The boundary word >= matters independently of AND. Replacing AND with OR would admit rows satisfying only one condition and answer a different selection request.
dp900-q-01710. The purchase-time address is a fact about that order; the current address is a different, time-dependent customer fact · Choice B
Normalization is about organizing facts with the entities they describe, not deleting every repeated text value. The order snapshot and current customer address can happen to be equal while describing different facts. Retaining the order’s purchase-time fact preserves the stated audit requirement. Nothing makes an address text a unique order identity or makes a foreign key a most-recent-edit selector. Ask what fact would be lost if the old order address were replaced by the customer's current address. The historical delivery destination belongs to the purchase event, while the current contact location describes a changing customer state. They may have once been equal without being duplicate facts for all time. Normalizing blindly from matching text would destroy the required historical evidence rather than remove redundant copies of one current attribute.
dp900-q-019Original source-grounded practice. Content and reasoning checks are separate from professional educator review, which remains pending. Not an official exam paper.