Free Microsoft DP-203 Practice Questions & Answers 2026 Part2

Question #1

You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1.You plan to create a database named DB1 in Pool1.You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool.Which format should you use for the tables in DB1?

A. CSV

B. ORC

C. JSON

D. Parquet

View answer

Correct Answer: D

Question #2

You have an Azure Synapse Analytics dedicated SQL pool named Pool1. Pool1 contains a partitioned fact table named dbo.Sales and a staging table named stg.Sales that has the matching table and partition definitions.You need to overwrite the content of the first partition in dbo.Sales with the content of the same partition in stg.Sales. The solution must minimize load times.What should you do?

A. Insert the data from stg

B. Switch the first partition from dbo

C. Switch the first partition from stg

D. Update dbo

View answer

Correct Answer: C

Question #3

You have files and folders in Azure Data Lake Storage Gen2 for an Azure Synapse workspace as shown in the following exhibit.You create an external table named ExtTable that has LOCATION='/topfolder/'.When you query ExtTable by using an Azure Synapse Analytics serverless SQL pool, which files are returned?

A. File2

B. File1

C. File1

D. File1

View answer

Correct Answer: B

Question #4

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1. Table1 contains the following:-One billion rows-A clustered columnstore index-A hash-distributed column named Product Key-A column named Sales Date that is of the date data type and cannot be nullThirty million rows will be added to Table1 each month.You need to partition Table1 based on the Sales Date column. The solution must optimize query performance and data loading.How often should you create a partition?

A. once per month

B. once per year

C. once per day

D. once per week

View answer

Correct Answer: B

Question #5

You plan to ingest streaming social media data by using Azure Stream Analytics. The data will be stored in files in Azure Data Lake Storage, and then consumed by using Azure Databricks and PolyBase in Azure Synapse Analytics.You need to recommend a Stream Analytics data output format to ensure that the queries from Databricks and PolyBase against the files encounter the fewest possible errors. The solution must ensure that the files can be queried quickly and that the data type information is retained.What should you recommend?

A. JSON

B. Parquet

C. CSV

D. Avro

View answer

Correct Answer: B

Question #6

You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.Table1 is a Type 2 slowly changing dimension (SCD) table.You need to apply updates from a source table to Table1.Which Apache Spark SQL operation should you use?

A. CREATE

B. UPDATE

C. ALTER

D. MERGE

View answer

Correct Answer: D

Question #7

You have a table in an Azure Synapse Analytics dedicated SQL pool. The table was created by using the following Transact-SQL statement.You need to alter the table to meet the following requirements:✑ Ensure that users can identify the current manager of employees.✑ Support creating an employee reporting hierarchy for your entire company.✑ Provide fast lookup of the managers' attributes such as name and job title.Which column should you add to the table?

A. ManagerEmployeeID] [smallint] NULL

B. ManagerEmployeeKey] [smallint] NULL

C. ManagerEmployeeKey] [int] NULL

D. ManagerName] [varchar](200) NULL

View answer

Correct Answer: C

Question #8

You need to implement a Type 3 slowly changing dimension (SCD) for product category data in an Azure Synapse Analytics dedicated SQL pool.You have a table that was created by using the following Transact-SQL statement.Which two columns should you add to the table? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A.

B.

C.

D.

E.

View answer

Correct Answer: BE

Question #9

You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns.FactPurchase will have 1 million rows of data added daily and will contain three years of data.Transact-SQL queries similar to the following query will be executed daily.SELECTSupplierKey, StockItemKey, COUNT(*)FROM FactPurchaseWHERE DateKey >= 20210101AND DateKey <= 20210131GROUP By SupplierKey, StockItemKeyWhich table distribution will minimize query times?

A. replicated

B. hash-distributed on PurchaseKey

C. round-robin

D. hash-distributed on DateKey

View answer

Correct Answer: B

Question #10

You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns.FactPurchase will have 1 million rows of data added daily and will contain three years of data. Transact-SQL queries similar to the following query will be executed daily.SELECTSupplierKey, StockItemKey, IsOrderFinalized, COUNT(*)FROM FactPurchaseWHERE DateKey >= 20210101AND DateKey <= 20210131GROUP By SupplierKey, StockItemKey, IsOrderFinalizedWhich table distribution will minimize query times?

A. replicated

B. hash-distributed on PurchaseKey

C. round-robin

D. hash-distributed on IsOrderFinalized

View answer

Correct Answer: B

Question #11

You plan to implement an Azure Data Lake Gen 2 storage account.You need to ensure that the data lake will remain available if a data center fails in the primary Azure region. The solution must minimize costs. Which type of replication should you use for the storage account?

A. geo-redundant storage (GRS)

B. geo-zone-redundant storage (GZRS)

C. locally-redundant storage (LRS)

D. zone-redundant storage (ZRS)

View answer

Correct Answer: D

Question #12

You are designing the folder structure for an Azure Data Lake Storage Gen2 container.Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQLpools. The data will be secured by subject area. Most queries will include data from the current year or current month.Which folder structure should you recommend to support fast queries and simplified folder security?

A. {SubjectArea}/{DataSource}/{DD}/{MM}/{YYYY}/{FileData}_{YYYY}_{MM}_{DD}

B. {DD}/{MM}/{YYYY}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}

C. {YYYY}/{MM}/{DD}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}

D. {SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}

View answer

Correct Answer: D

Question #13

You need to design an Azure Synapse Analytics dedicated SQL pool that meets the following requirements:-Can return an employee record from a given point in time.-Maintains the latest employee information.-Minimizes query complexity.How should you model the employee data?

A. as a temporal table

B. as a SQL graph table

C. as a degenerate dimension table

D. as a Type 2 slowly changing dimension (SCD) table

View answer

Correct Answer: D

Question #14

You plan to implement an Azure Data Lake Storage Gen2 container that will contain CSV files. The size of the files will vary based on the number of events that occur per hour.File sizes range from 4 KB to 5 GB.You need to ensure that the files stored in the container are optimized for batch processing. What should you do?

A. Convert the files to JSON

B. Convert the files to Avro

C. Compress the files

D. Merge the files

View answer

Correct Answer: D

Question #15

You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements. What should you create?

A. a table that has an IDENTITY property

B. a system-versioned temporal table

C. a user-defined SEQUENCE object

D. a table that has a FOREIGN KEY constraint

View answer

Correct Answer: A

Question #16

You are implementing a batch dataset in the Parquet format.Data files will be produced be using Azure Data Factory and stored in Azure Data Lake Storage Gen2. The files will be consumed by an Azure Synapse Analytics serverless SQL pool.You need to minimize storage costs for the solution. What should you do?

A. Use Snappy compression for files

B. Use OPENROWSET to query the Parquet files

C. Create an external table that contains a subset of columns from the Parquet files

D. Store all data as string in the Parquet files

View answer

Correct Answer: A

Question #17

You are designing the folder structure for an Azure Data Lake Storage Gen2 container.Users will query data by using a variety of services including Azure Databricks and Azure Synapse Analytics serverless SQL pools. The data will be secured bysubject area. Most queries will include data from the current year or current month.Which folder structure should you recommend to support fast queries and simplified folder security?

A. /{SubjectArea}/{DataSource}/{DD}/{MM}/{YYYY}/{FileData}_{YYYY}_{MM}_{DD}

B. /{DD}/{MM}/{YYYY}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}

C. /{YYYY}/{MM}/{DD}/{SubjectArea}/{DataSource}/{FileData}_{YYYY}_{MM}_{DD}

D. /{SubjectArea}/{DataSource}/{YYYY}/{MM}/{DD}/{FileData}_{YYYY}_{MM}_{DD}

View answer

Correct Answer: D

Question #18

You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.Table1 is a Type 2 slowly changing dimension (SCD) table.You need to apply updates from a source table to Table1.Which Apache Spark SQL operation should you use?

A. CREATE

B. UPDATE

C. ALTER

D. MERGE

View answer

Correct Answer: D

Question #19

You are designing a partition strategy for a fact table in an Azure Synapse Analytics dedicated SQL pool. The table has the following specifications:-Contain sales data for 20,000 products.-Use hash distribution on a column named ProductID.-Contain 2.4 billion records for the years 2019 and 2020.Which number of partition ranges provides optimal compression and performance for the clustered columnstore index?

A. 40

B. 240

C. 400

D. 2,400

View answer

Correct Answer: A

Question #20

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1. Table1 contains the following:-One billion rows-A clustered columnstore index-A hash-distributed column named Product Key-A column named Sales Date that is of the date data type and cannot be nullThirty million rows will be added to Table1 each month.You need to partition Table1 based on the Sales Date column. The solution must optimize query performance and data loading.How often should you create a partition?

A. once per month

B. once per year

C. once per day

D. once per week

View answer

Correct Answer: B

Question #21

You have an enterprise-wide Azure Data Lake Storage Gen2 account. The data lake is accessible only through an Azure virtual network named VNET1.You are building a SQL pool in Azure Synapse that will use data from the data lake.Your company has a sales team. All the members of the sales team are in an Azure Active Directory group named Sales. POSIX controls are used to assign the Sales group access to the files in the data lake.You plan to load data to the SQL pool every hour.You need to ensure that the SQL pool can load the sales data from the data lake.Which three actions should you perform? Each correct answer presents part of the solution.NOTE: Each area selection is worth one point.

A. Add the managed identity to the Sales group

B. Use the managed identity as the credentials for the data load process

C. Create a shared access signature (SAS)

D. Add your Azure Active Directory (Azure AD) account to the Sales group

E. Use the snared access signature (SAS) as the credentials for the data load process

F. Create a managed identity

View answer

Correct Answer: ABF

Question #22

You have an Azure Databricks workspace that contains a Delta Lake dimension table named Table1.Table1 is a Type 2 slowly changing dimension (SCD) table.You need to apply updates from a source table to Table1.Which Apache Spark SQL operation should you use?

A. CREATE

B. UPDATE

C. ALTER

D. MERGE

View answer

Correct Answer: D

DON'T WANT TO MISS A THING?

Latest Cisco, PMP, AWS, CompTIA, Microsoft Materials on SALE
Get Now

Free Microsoft DP-203 Practice Questions & Answers 2026 Part2

View The Updated DP-203 Exam Questions

View The Updated Microsoft Exam Questions

View Answers after Submission

DON'T WANT TO MISS A THING?

Latest Cisco, PMP, AWS, CompTIA, Microsoft Materials on SALE Get Now

Free Microsoft DP-203 Practice Questions & Answers 2026 Part2

View The Updated DP-203 Exam Questions

View The Updated Microsoft Exam Questions

View Answers after Submission

Latest Cisco, PMP, AWS, CompTIA, Microsoft Materials on SALE
Get Now