Free Data Engineering on Microsoft Azure Exam DP-203 Exam Practice Test

You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables.Which distribution type should you recommend to minimize data movement?

• HASH
• REPLICATE
• ROUND ROBIN

You are designing an Azure Databricks table. The table will ingest an average of 20 million streaming events per day.You need to persist the events in the table for use in incremental load pipeline jobs in Azure Databricks. The solution must minimize storage costs and incremental load times.What should you include in the solution?

• Partition by DateTime fields.
• Sink to Azure Queue storage.
• Include a watermark column.
• Use a JSON format for physical data storage.

You are designing an Azure Synapse solution that will provide a query interface for the data stored in an Azure Storage account. The storage account is only accessible from a virtual network.You need to recommend an authentication mechanism to ensure that the solution can access the source data.What should you recommend?

• a managed identity
• anonymous public read access
• a shared key

You have a SQL pool in Azure Synapse.You discover that some queries fail or take a long time to complete.You need to monitor for transactions that have rolled back.Which dynamic management view should you query?

• sys.dm_pdw_request_steps
• sys.dm_pdw_nodes_tran_database_transactions
• sys.dm_pdw_waits
• sys.dm_pdw_exec_sessions

You are designing a star schema for a dataset that contains records of online orders. Each record includes an order date, an order due date, and an order ship date.You need to ensure that the design provides the fastest query times of the records when querying for arbitrary date ranges and aggregating by fiscal calendar attributes.Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

• Create a date dimension table that has a DateTime key.
• Use built-in SQL functions to extract date attributes.
• Create a date dimension table that has an integer key in the format of yyyymmdd.
• In the fact table, use integer columns for the date fields.
• Use DateTime columns for the date fields.

You have an Azure data factory.You need to examine the pipeline failures from the last 180 flays.What should you use?

• the Activity tog blade for the Data Factory resource
• Azure Data Factory activity runs in Azure Monitor
• Pipeline runs in the Azure Data Factory user experience
• the Resource health blade for the Data Factory resource

You have an Azure Data Factory pipeline that performs an incremental load of source data to an Azure Data Lake Storage Gen2 account.Data to be loaded is identified by a column named LastUpdatedDate in the source table.You plan to execute the pipeline every four hours.You need to ensure that the pipeline execution meets the following requirements:Automatically retries the execution when the pipeline run fails due to concurrency or throttling limits.Supports backfilling existing data in the table.Which type of trigger should you use?

• Storage event
• on-demand
• schedule
• tumbling window

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

• a table that has an IDENTITY property
• a system-versioned temporal table
• a user-defined SEQUENCE object
• a table that has a FOREIGN KEY constraint

You have the following Azure Data Factory pipelines* ingest Data from System 1* Ingest Data from System2* Populate Dimensions* Populate factsingest Data from System1 and Ingest Data from System1 have no dependencies. Populate Dimensions must execute after Ingest Data from System1 and Ingest Data from System* Populate Facts must execute after the Populate Dimensions pipeline. All the pipelines must execute every eight hours.What should you do to schedule the pipelines for execution?

• Add an event trigger to all four pipelines.
• Create a parent pipeline that contains the four pipelines and use an event trigger.
• Create a parent pipeline that contains the four pipelines and use a schedule trigger.
• Add a schedule trigger to all four pipelines.

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1. DB1 contains a fact table named Table1.You need to identify the extent of the data skew in Table1.What should you do in Synapse Studio?

• Connect to the built-in pool and query sysdm_pdw_sys_info.
• Connect to Pool1 and run DBCC CHECKALLOC.
• Connect to the built-in pool and run DBCC CHECKALLOC.
• Connect to Pool! and query sys.dm_pdw_nodes_db_partition_stats.