Microsoft Microsoft Certifications DP-203 Questions & Answers

• Question 261:

  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.

  SELECT

  SupplierKey, StockItemKey, IsOrderFinalized, COUNT(*)FROM FactPurchase WHERE DateKey >= 20210101

  AND DateKey <= 20210131GROUP By SupplierKey, StockItemKey, IsOrderFinalized

  Which table distribution will minimize query times?

  A. replicated

  B. hash-distributed on PurchaseKey

  C. round-robin

  D. hash-distributed on IsOrderFinalized

  Correct Answer: B

  Hash-distributed tables improve query performance on large fact tables. To balance the parallel processing, select a distribution column that:

  1.

  Has many unique values. The column can have duplicate values. All rows with the same value are assigned to the same distribution. Since there are 60 distributions, some distributions can have > 1 unique values while others may end with zero values.

  2.

  Does not have NULLs, or has only a few NULLs.

  3.

  Is not a date column. Incorrect Answers:

  C: Round-robin tables are useful for improving loading speed.

  Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

• Question 262:

  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)

  Correct Answer: D

  Geo-redundant storage (GRS) copies your data synchronously three times within a single physical location in the primary region using LRS. It then copies your data asynchronously to a single physical location in the secondary region. Incorrect Answers:

  B: Zone-redundant storage (ZRS) copies your data synchronously across three Azure availability zones in the primary region. For applications requiring high availability, Microsoft recommends using ZRS in the primary region, and also

  replicating to a secondary region.

  C: Locally redundant storage (LRS) copies your data synchronously three times within a single physical location in the primary region. LRS is the least expensive replication option, but is not recommended for applications requiring high

  availability.

  D: GZRS is more expensive compared to GRS.

  Reference:

  https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy

• Question 263:

  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. [EffectiveStartDate] [datetime] NOT NULL,

  B. [CurrentProductCategory] [nvarchar] (100) NOT NULL,

  C. [EffectiveEndDate] [datetime] NULL,

  D. [ProductCategory] [nvarchar] (100) NOT NULL,

  E. [OriginalProductCategory] [nvarchar] (100) NOT NULL,

  Correct Answer: BE

  A Type 3 SCD supports storing two versions of a dimension member as separate columns. The table includes a column for the current value of a member plus either the original or previous value of the member. So Type 3 uses additional columns to track one key instance of history, rather than storing additional rows to track each change like in a Type 2 SCD.

  This type of tracking may be used for one or two columns in a dimension table. It is not common to use it for many members of the same table. It is often used in combination with Type 1 or Type 2 members.

  

  Reference: https://k21academy.com/microsoft-azure/azure-data-engineer-dp203-q-a-day-2-live-session-review/

• Question 264:

  You have an Azure Data Lake Storage Gen2 container that contains 100 TB of data.

  You need to ensure that the data in the container is available for read workloads in a secondary region if an outage occurs in the primary region. The solution must minimize costs.

  Which type of data redundancy should you use?

  A. geo-redundant storage (GRS)

  B. read-access geo-redundant storage (RA-GRS)

  C. zone-redundant storage (ZRS)

  D. locally-redundant storage (LRS)

  Correct Answer: B

  Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a failover from the primary to secondary region. When you enable read access to the secondary region, your data is available to be read at all times, including in a situation where the primary region becomes unavailable.

  Incorrect Answers:

  A: While Geo-redundant storage (GRS) is cheaper than Read-Access Geo-Redundant Storage (RA-GRS), GRS does NOT initiate automatic failover. C, D: Locally redundant storage (LRS) and Zone-redundant storage (ZRS) provides redundancy within a single region.

  Reference: https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy

• Question 265:

  You have an enterprise data warehouse in Azure Synapse Analytics.

  Using PolyBase, you create an external table named [Ext].[Items] to query Parquet files stored in Azure Data Lake Storage Gen2 without importing the data to the data warehouse.

  The external table has three columns.

  You discover that the Parquet files have a fourth column named ItemID.

  Which command should you run to add the ItemID column to the external table?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: C

  Incorrect Answers:

  A, D: Only these Data Definition Language (DDL) statements are allowed on external tables:

  1.

  CREATE TABLE and DROP TABLE

  2.

  CREATE STATISTICS and DROP STATISTICS

  3.

  CREATE VIEW and DROP VIEW

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql

• Question 266:

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You are designing an Azure Stream Analytics solution that will analyze Twitter data.

  You need to count the tweets in each 10-second window. The solution must ensure that each tweet is counted only once.

  Solution: You use a hopping window that uses a hop size of 10 seconds and a window size of 10 seconds.

  Does this meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Instead use a tumbling window. Tumbling windows are a series of fixed-sized, non-overlapping and contiguous time intervals.

  Reference: https://docs.microsoft.com/en-us/stream-analytics-query/tumbling-window-azure-stream-analytics

• Question 267:

  You have an Azure Data Factory instance that contains two pipelines named Pipeline1 and Pipeline2. Pipeline1 has the activities shown in the following exhibit.

  

  Pipeline2 has the activities shown in the following exhibit.

  

  You execute Pipeline2, and Stored procedure1 in Pipeline1 fails. What is the status of the pipeline runs?

  A. Pipeline1 and Pipeline2 succeeded.

  B. Pipeline1 and Pipeline2 failed.

  C. Pipeline1 succeeded and Pipeline2 failed.

  D. Pipeline1 failed and Pipeline2 succeeded.

  Correct Answer: A

  Activities are linked together via dependencies. A dependency has a condition of one of the following: Succeeded, Failed,Skipped, or Completed.

  Consider Pipeline1:

  If we have a pipeline with two activities where Activity2 has a failure dependency on Activity1, the pipeline will not fail just because Activity1 failed. If Activity1 fails and Activity2 succeeds, the pipeline will succeed. This scenario is treated as a

  try-catch block by Data Factory.

  

  The failure dependency means this pipeline reports success.

  Note:

  If we have a pipeline containing Activity1 and Activity2, and Activity2 has a success dependency on Activity1, it will only execute if Activity1 is successful. In this scenario, if Activity1 fails, the pipeline will fail.

  Reference:

  https://datasavvy.me/category/azure-data-factory/

• Question 268:

  You have an Azure Data Factory that contains 10 pipelines.

  You need to label each pipeline with its main purpose of either ingest, transform, or load. The labels must be available for grouping and filtering when using the monitoring experience in Data Factory.

  What should you add to each pipeline?

  A. a resource tag

  B. a correlation ID

  C. a run group ID

  D. an annotation

  Correct Answer: D

  Annotations are additional, informative tags that you can add to specific factory resources: pipelines, datasets, linked services, and triggers. By adding annotations, you can easily filter and search for specific factory resources.

  Reference: https://www.cathrinewilhelmsen.net/annotations-user-properties-azure-data-factory/

• Question 269:

  You are designing a statistical analysis solution that will use custom proprietary Python functions on near real-time data from Azure Event Hubs.

  You need to recommend which Azure service to use to perform the statistical analysis. The solution must minimize latency.

  What should you recommend?

  A. Azure Synapse Analytics

  B. Azure Databricks

  C. Azure Stream Analytics

  D. Azure SQL Database

  Correct Answer: C

  Reference: https://docs.microsoft.com/en-us/azure/event-hubs/process-data-azure-stream-analytics

• Question 270:

  You plan to perform batch processing in Azure Databricks once daily. Which type of Databricks cluster should you use?

  A. High Concurrency

  B. automated

  C. interactive

  Correct Answer: B

  Azure Databricks has two types of clusters: interactive and automated. You use interactive clusters to analyze data collaboratively with interactive notebooks. You use automated clusters to run fast and robust automated jobs.

  Example: Scheduled batch workloads (data engineers running ETL jobs)

  This scenario involves running batch job JARs and notebooks on a regular cadence through the Databricks platform.

  The suggested best practice is to launch a new cluster for each run of critical jobs. This helps avoid any issues (failures, missing SLA, and so on) due to an existing workload (noisy neighbor) on a shared cluster.

  Reference:

  https://docs.databricks.com/administration-guide/cloud-configurations/aws/cmbp.html#scenario-3-scheduled-batch-workloads-data-engineers-running-etl-jobs