DP-203 Practice Test 8 — 10 Questions

Question 1

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0. A Mapping Data Flow with a Derived Column transformation allows you to replace NULL values in the 'Amount' column with a default value (e.g., 0) before writing to the Synapse dedicated SQL pool. This resolves the NULL constraint violation without losing any rows, as the data is transformed inline within the pipeline. The copy activity alone cannot perform such transformations, making the Mapping Data Flow the appropriate choice for this ETL scenario.

Answer

Add a filter in the copy activity to exclude rows with NULL Amount

Answer

Modify the sink table to have a default value for the Amount column

Answer

Change the sink table column to allow NULLs

Question 2

Refer to the exhibit. You submit a Spark job in Azure Synapse Analytics using the Azure CLI. The job runs slowly during the shuffle phase. The input data is about 200 GB. Which configuration change would best improve performance for this shuffle-heavy workload?

Accepted Answer

Increase spark.sql.shuffle.partitions to 800.. Increasing spark.sql.shuffle.partitions to 800 improves parallelism for a 200 GB shuffle-heavy workload, reducing partition sizes and shuffle time. Option A is incorrect because executor count alone does not directly address shuffle partitioning. Option B may help but is less impactful than partition tuning. Option D would worsen the problem by making partitions larger.

Answer

Increase the number of executors to 4.

Answer

Change executor size to 'Large' to increase memory per executor.

Answer

Decrease spark.sql.shuffle.partitions to 200 to reduce overhead.

Question 3

You are designing a real-time analytics solution for IoT devices that emit telemetry data every second. The data must be aggregated every minute and stored in Azure SQL Database for historical analysis. You need to minimize latency and operational overhead. Which approach should you recommend?

Accepted Answer

Use Azure Stream Analytics with a tumbling window of 1 minute and output to Azure SQL Database. Azure Stream Analytics natively supports real-time stream processing with tumbling windows, allowing you to aggregate IoT telemetry data every minute and output directly to Azure SQL Database with minimal latency. This approach avoids the overhead of managing clusters (Databricks) or orchestrating batch loads (Data Factory), directly meeting the requirement for low latency and operational simplicity.

Answer

Use Azure Databricks with Structured Streaming to aggregate and write to SQL Database

Answer

Use Event Hubs Capture to store raw data in blob storage, then use Azure Data Factory to load into SQL Database hourly

Answer

Use Azure Functions to process events and write to SQL Database

Question 4

You are an administrator for an Azure Synapse Analytics dedicated SQL pool. You execute the T-SQL statements shown in the exhibit. The external table 'dbo.Orders' is created. Which statement about querying this external table is true?

Accepted Answer

You can query the external table using standard T-SQL SELECT statements.. An external table in Azure Synapse Analytics dedicated SQL pool is a read-only abstraction over data stored externally (e.g., in Azure Blob Storage or Azure Data Lake Store). You can query it directly using standard T-SQL SELECT statements without importing data into the pool, leveraging PolyBase to push down predicate filtering and read only the required data.

Answer

Querying the external table automatically imports data into a round-robin distribution.

Answer

The table cannot be queried until the data is imported into the dedicated SQL pool.

Answer

You must first create a PolyBase external table before querying.

Question 5

You are reviewing the ARM template snippet for an Azure Data Lake Storage Gen2 account. The template fails to deploy with an error that the encryption key cannot be accessed. What is the most likely cause?

Accepted Answer

The Data Lake Storage account does not have Get and Wrap Key permissions on the key vault.. The error occurs because the Data Lake Storage Gen2 account uses a customer-managed key from a key vault for encryption. The account's managed identity must have the 'Get' and 'Wrap Key' permissions on the key vault to access the key. Without these permissions, the deployment fails. Option B correctly identifies this. Option A (soft-delete) is not required for initial access but is recommended for protection. Option C (key name/version) would cause a different error (key not found). Option D (URI format) would also cause a different error.

Answer

The key vault does not have soft-delete enabled.

Answer

The key name or version is incorrect.

Answer

The key vault URI is incorrectly formatted.

Question 6

You are designing a data processing solution in Azure Synapse Analytics that uses serverless SQL pools to query Parquet files in Azure Data Lake Storage Gen2. The files are partitioned by year and month. You need to optimize query performance and reduce data scanned. What should you do?

Accepted Answer

Create views that filter on partition columns.. Serverless SQL pools in Azure Synapse Analytics support partition elimination only when queries use views or inline queries that explicitly filter on partition columns (e.g., year, month) in the WHERE clause. This allows the pool to skip scanning irrelevant partitions, reducing data scanned and improving performance. Creating views that encapsulate these filters ensures consistent partition pruning across queries.

Answer

Use CREATE EXTERNAL TABLE AS SELECT (CETAS) to create new external tables.

Answer

Use OPENROWSET with the DATA_SOURCE parameter.

Answer

Increase the number of files per partition.

Question 7

You need to audit all data access to an Azure Storage account. Which Azure service should you enable?

Accepted Answer

Azure Storage analytics logs and send to Log Analytics workspace. Azure Storage analytics logs capture detailed information about successful and failed requests to a storage account, including authentication details, IP addresses, and operation types. By sending these logs to a Log Analytics workspace, you can query and analyze them using KQL, enabling comprehensive auditing of all data access. This is the correct service for auditing because it provides the granular, queryable logs required for security and compliance audits.

Answer

Azure Policy to audit storage account access

Answer

Azure Monitor metrics

Answer

Azure Security Center

Question 8

A multinational bank needs to store customer transaction records for 10 years to meet regulatory compliance. The data is rarely accessed after the first year. The solution must minimize storage costs while allowing queries on recent data with low latency. Which tiering strategy should you implement?

Accepted Answer

Use Azure Blob Storage with lifecycle management to transition from Hot to Cool to Archive tiers. Azure Blob Storage lifecycle management automatically transitions blobs from Hot to Cool to Archive tiers based on age, minimizing storage costs for rarely accessed data after the first year while keeping recent data in Hot tier for low-latency queries. This aligns with the 10-year retention requirement and cost optimization goal without manual intervention.

Answer

Store all data in Azure SQL Database with partitioning and drop older partitions

Answer

Use Azure Data Lake Storage Gen2 with a single storage tier

Answer

Store data in Azure Cosmos DB with time-to-live (TTL) and use Azure Blob Storage for backups

Question 9

You are designing a data storage solution for real-time analytics on IoT telemetry. The system must ingest 10,000 events per second and support sub-second query latency. Which Azure data store should you use?

Accepted Answer

Azure Data Explorer (ADX).. Azure Data Explorer (ADX) is purpose-built for high-velocity telemetry and log analytics, ingesting 10,000+ events per second with sub-second query latency via its columnar storage and distributed query engine. It supports real-time analytics on streaming IoT data without requiring pre-defined schemas or indexing, making it the optimal choice for this scenario.

Answer

Azure Table Storage.

Answer

Azure SQL Database with in-memory OLTP.

Answer

Azure Cosmos DB with analytical store.

Question 10

You need to store semi-structured JSON data from a web application. The data schema may change over time. The solution must support low-latency queries and be globally distributed. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it natively supports semi-structured JSON documents with a flexible schema, offers single-digit-millisecond latency for queries, and provides global distribution with turnkey multi-region replication. Its API for MongoDB or SQL API allows direct ingestion of JSON data, and its schema-agnostic indexing adapts automatically to schema changes over time.

Answer

Azure Table Storage

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database