Microsoft DP-900 Practice Exam

• A. Tabular data
• B. Structured data
• C. Semi-structured data Correct answer
• D. Unstructured data

Option C is correct because JSON documents are semi-structured: they carry self-describing fields, but each record can have a different set of fields. Tabular and structured data require a fixed schema, and unstructured data has no organising fields at all.

• A. A table of customer records in a database
• B. A recorded customer support call Correct answer
• C. A JSON order message
• D. A CSV file of daily sales

Option B is correct because an audio recording has no predefined schema or fields, which makes it unstructured data. Database tables and CSV files are structured, and a JSON message is semi-structured.

• A. Semi-structured data in a key-value file
• B. Semi-structured data in a markup file
• C. Structured data in a delimited text file Correct answer
• D. Unstructured data in a text file

Option C is correct because a delimited text file with a header row and consistent columns has a fixed schema, so it is structured data. It is not semi-structured, since no tags or per-record varying fields are involved, and it is not unstructured because every value sits in a defined column.

• A. CSV
• B. JSON
• C. XML Correct answer
• D. Parquet

Option C is correct because XML defines elements and attributes using opening and closing tags. CSV uses delimiters, JSON uses braces and key-value pairs, and Parquet is a binary columnar format.

• A. CSV Correct answer
• B. Avro
• C. XML Correct answer
• D. JSON Correct answer
• E. Parquet
• F. ORC

Options A, C and D are correct because CSV, XML and JSON all store data as human-readable plain text. Avro, Parquet and ORC are binary formats that cannot be read in a text editor.

• A. INSERT
• B. ALTER TABLE
• C. DELETE
• D. UPDATE Correct answer

Option D is correct because UPDATE modifies column values in rows that already exist. INSERT adds new rows, DELETE removes rows, and ALTER TABLE changes the table's structure rather than its data.

• A. Rows from either table that have no match
• B. All rows from Customers, matched or not
• C. Every pairing of a customer with an order
• D. Rows with matching values in both tables Correct answer

Option D is correct because an INNER JOIN returns only the rows where the join condition matches in both tables. Returning all Customers rows describes a LEFT JOIN, unmatched rows from either side describe an anti-join or outer join, and every pairing describes a CROSS JOIN.

• A. A composite primary key on the Students table
• B. A foreign key in Courses that references Students
• C. A separate table that holds a foreign key to each Correct answer
• D. A foreign key in Students that references Courses

Option C is correct because a many-to-many relationship is modelled with a junction (linking) table that holds a foreign key to each side, such as an Enrolments table. A single foreign key in either table can only express a one-to-many relationship.

• A. UNIQUE Correct answer
• B. NOT NULL
• C. DEFAULT
• D. CHECK

Option A is correct because a UNIQUE constraint prevents duplicate values in a column. NOT NULL only forbids missing values, DEFAULT supplies a value when none is given, and CHECK validates a condition but does not enforce uniqueness.

• A. SUM Correct answer
• B. GETDATE
• C. MAX Correct answer
• D. ROUND
• E. COUNT Correct answer
• F. UPPER

Options A, C and E are correct because SUM, MAX and COUNT each combine many rows into a single value, which is what an aggregate function does. GETDATE returns the current date and time, and ROUND and UPPER transform a single value row by row, so they are scalar functions.

• A. Azure Table storage
• B. Azure Blob storage
• C. Azure Files
• D. Azure Queue Storage Correct answer

Option D is correct because Azure Queue Storage is the service built to hold messages that decoupled application components exchange asynchronously. Table storage holds NoSQL entities, Blob storage holds unstructured objects, and Azure Files provides file shares, so none of them acts as a message queue.

• A. Three copies in one datacenter Correct answer
• B. Zone copies plus a second-region copy
• C. Three copies in separate zones
• D. Six copies across two regions

Option A is correct because LRS keeps three synchronous copies of the data within a single datacenter in the primary region. Three copies across separate zones describes ZRS, and copies in a second region describe GRS or GZRS, which add cross-region or zone-level protection that LRS does not provide.

• A. Zone-redundant storage (ZRS)
• B. Locally redundant storage (LRS)
• C. Read-access GRS (RA-GRS) Correct answer
• D. Geo-zone-redundant storage (GZRS)

Option C is correct because RA-GRS adds read access to the secondary region's copy at any time, even while the primary is healthy. Plain GRS only allows reads from the secondary after a failover, while ZRS and LRS have no secondary region. GZRS also has no secondary read access unless it is RA-GZRS.

• A. Azure Files
• B. Azure managed disks Correct answer
• C. Azure Blob storage
• D. Azure Table storage

Option B is correct because Azure managed disks are block-level storage volumes that act as the disks attached to a virtual machine. Azure Files offers SMB file shares, Blob storage holds unstructured objects, and Table storage holds NoSQL key-attribute data, so none of them presents a VM disk.

• A. Azure Cosmos DB for NoSQL
• B. Azure Cosmos DB for Apache Cassandra Correct answer
• C. Azure Cosmos DB for Apache Gremlin
• D. Azure Cosmos DB for MongoDB
• E. Azure Cosmos DB for Table Correct answer

Options B and E are correct because Azure Cosmos DB for Apache Cassandra exposes a wide-column model, while Azure Cosmos DB for Table exposes a key-value model. Option A (NoSQL) and option D (MongoDB) store JSON documents, and option C (Gremlin) stores graph data.

• A. Process high-volume order transactions
• B. Replicate operational databases for failover
• C. Serve analytical queries over historical data Correct answer
• D. Hold raw files in their native formats

Option C is correct because a data warehouse stores structured, historical data modelled for analytical queries and reporting. Option A describes an OLTP system, option B describes replication for failover, and option D describes a data lake.

• A. Azure Analysis Services
• B. Azure Stream Analytics
• C. Azure SQL Managed Instance
• D. Azure Synapse Analytics Correct answer

Option D is correct because Azure Synapse Analytics unifies enterprise data warehousing and big data analytics, with serverless and dedicated SQL pools. Azure Analysis Services is a semantic modelling service, Stream Analytics processes streaming events, and SQL Managed Instance is an operational PaaS database.

• A. While it travels through a message broker
• B. Before it is loaded, on a separate staging server
• C. After it has been loaded into the target store Correct answer
• D. Each time a report is opened in the client tool

Option C is correct because ELT loads raw data into the target store first and then transforms it there, using the store's own compute. Transforming before loading on a separate server describes ETL (option B), and the other options are not transformation stages of ELT.

• A. Dimensional modelling
• B. Schema-on-read Correct answer
• C. Change data capture
• D. Schema-on-write

Option B is correct because schema-on-read applies structure only when data is queried, which suits raw files in a data lake. Schema-on-write (option D) enforces structure when data is stored, as in a relational warehouse. Dimensional modelling and change data capture are different concepts.

• A. Azure Data Studio
• B. Azure Synapse Studio
• C. Microsoft Purview portal
• D. Power BI Desktop Correct answer
• E. Power BI service Correct answer

Options D and E are correct because Power BI Desktop is the free authoring application and the Power BI service is the cloud SaaS where content is shared and consumed. Azure Data Studio, Azure Synapse Studio and the Microsoft Purview portal are separate Azure tools, not components of Power BI.