DP-203 Practice Test 14 — 15 Questions

Question 1

Your company stores sensitive customer data in Azure SQL Database. You need to encrypt the data at rest and ensure that only your application can decrypt it, even from database administrators. What should you implement?

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

Accepted Answer

Always Encrypted. Always Encrypted is correct because it ensures that sensitive data is encrypted at rest and in use, and the encryption keys are stored client-side, so only the application can decrypt the data. Database administrators (DBAs) cannot access the plaintext data because they lack the column encryption keys, even though they have full administrative access to the database.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Azure Storage Service Encryption

Question 2

You are designing a data storage solution for IoT sensor data. The data is written thousands of times per second and requires low-latency reads for real-time dashboards. Which Azure storage solution should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides single-digit millisecond read and write latency at any scale, with automatic indexing and multi-region distribution. Its support for multiple APIs (SQL, MongoDB, Cassandra, etc.) and configurable consistency levels makes it ideal for IoT sensor data requiring high-throughput writes and low-latency reads for real-time dashboards.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 3

You are monitoring an Azure Stream Analytics job that processes streaming data from Event Hubs to Azure Synapse Analytics. Which TWO Azure Monitor metrics should you set alerts on to detect data loss or processing delays?

Accepted Answer

InputEvents and OutputEvents metrics. The correct metrics for detecting data loss or processing delays in an Azure Stream Analytics job are InputEvents and OutputEvents (Option A). By comparing these, you can determine if every event entering the job is being output; a mismatch indicates potential data loss. Backlogged input events (Option E) directly measures the number of events waiting to be processed, and a high or growing value signals processing delays. Option B (Duration) is not a direct indicator of data loss or processing delays in Stream Analytics. Option C (Data read/written) is not a metric for Stream Analytics. Option D (Pipeline run count) is not applicable to Stream Analytics streaming jobs.

Answer

Duration metric

Answer

Data read and data written metrics

Answer

Pipeline run count metric

Question 4

You are monitoring an Azure Synapse Pipeline that uses a Mapping Data Flow. The data flow processes 2 GB of data from a CSV source and writes to a Delta sink. The pipeline fails with a 'DataFlowException: Operation aborted' error after running for 45 minutes. The cluster is configured with 8 cores. What is the most likely cause?

Accepted Answer

The data flow cluster's time-to-live (TTL) is set to 45 minutes and the job exceeded it.. The error 'Operation aborted' after exactly 45 minutes aligns with the default time-to-live (TTL) setting for Azure Synapse Mapping Data Flow clusters. When the TTL expires, the cluster is terminated, and any running job is aborted. The 8-core cluster and 2 GB data volume are not inherently problematic for a 45-minute window, but the TTL default of 45 minutes causes the abort if the job runs longer than that.

Answer

The cluster size is too small for the data volume.

Answer

The CSV source contains malformed rows that cause parsing errors.

Answer

The data flow is using the Spark cluster's default timeout setting.

Question 5

A company uses Azure Synapse Analytics to process large datasets. They need to transform JSON data stored in Azure Data Lake Storage Gen2 into a star schema. Which data processing approach minimizes data movement and leverages the compute closest to the data?

Accepted Answer

Create external tables on the JSON files using PolyBase, then use CREATE EXTERNAL TABLE AS SELECT (CETAS) to write transformed Parquet files.. It uses PolyBase external tables and CETAS to transform JSON data directly in Azure Data Lake Storage Gen2, minimizing data movement by leveraging the compute power of the dedicated SQL pool or serverless SQL pool closest to the data. This approach reads JSON in place, transforms it into Parquet format, and writes the star schema tables back to the data lake without copying data to an intermediate store.

Answer

Use Azure Data Factory to copy the JSON data into Azure SQL Database, then use T-SQL to transform.

Answer

Use Azure Data Factory with SSIS to transform and load into dedicated SQL pool.

Answer

Load data into a Spark DataFrame in Synapse notebooks, transform, and write back.

Question 6

Refer to the exhibit. You have an Azure Synapse Analytics workspace. You need to ensure that data processing jobs can access the Data Lake Storage Gen2 account using a managed identity. What should you do?

Accepted Answer

Enable the system-assigned managed identity on the Synapse workspace and assign it the 'Storage Blob Data Contributor' role on the storage account. Azure Synapse Analytics supports system-assigned managed identities, which provide a secure, passwordless authentication method for accessing Azure Data Lake Storage Gen2. By enabling the managed identity on the Synapse workspace and assigning it the 'Storage Blob Data Contributor' role, you grant the workspace's data processing jobs the necessary permissions to read, write, and delete data in the storage account without managing credentials.

Answer

Use the SQL admin login credentials to access the storage account

Answer

Create a private endpoint connection between the workspace and the storage account

Answer

Configure the storage account firewall to allow access from the Synapse workspace

Question 7

You are using Azure Synapse Analytics serverless SQL pool to query Parquet files in Azure Data Lake Storage Gen2. The query is slow and you suspect that the file layout is not optimized. You examine the files and find that each file is 50 MB. What should you do to improve query performance?

Accepted Answer

Merge the small files into larger files of at least 100 MB each. Azure Synapse serverless SQL pool performs best when reading files of at least 100 MB each. Small files (50 MB) cause excessive metadata operations, partition discovery, and I/O overhead, leading to slower queries. Merging them into larger files reduces the number of file open/close operations and improves parallelism efficiency.

Answer

Partition the data into folders by date

Answer

Compress the files with Gzip

Answer

Convert the files to CSV format to reduce overhead

Question 8

A manufacturing company uses Azure Data Lake Storage Gen2 with hierarchical namespace enabled and Azure Databricks for analytics. The security team requires that all data stored in the 'raw' container be encrypted at rest using customer-managed keys. The data is ingested via Azure Data Factory. What should the data engineer configure to meet the requirement?

Accepted Answer

Configure the storage account to use Azure Key Vault for customer-managed key encryption.. Azure Data Lake Storage Gen2 with hierarchical namespace supports encryption at rest using customer-managed keys (CMK) via Azure Key Vault. To meet the security requirement, the data engineer must configure the storage account's encryption settings to use a key from Azure Key Vault, which allows the organization to control and rotate the encryption keys independently of Azure.

Answer

Assign an Azure Policy that requires encryption at rest.

Answer

Enable Azure Information Protection on the storage account.

Answer

Enable the 'require secure transfer' setting on the storage account.

Question 9

You are designing a data storage solution for a manufacturing company that collects sensor data from machines. The data is stored in Azure Data Lake Storage Gen2. You need to ensure that the solution can handle large volumes of streaming data (up to 100 MB/s) and provide real-time dashboards. Which TWO services should you include?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is correct because it is a real-time analytics service designed to process high-velocity streaming data (up to 100 MB/s) from sources like Event Hubs and output to dashboards and storage. It provides low-latency, SQL-based querying for real-time dashboards, making it ideal for manufacturing sensor data scenarios.

Answer

Azure Analysis Services

Answer

Azure Data Factory

Answer

Azure Databricks

Question 10

A company uses Azure Synapse Analytics with dedicated SQL pools. They need to allow a data scientist to read all tables in the 'sales' schema but prevent access to columns containing personally identifiable information (PII). Which feature should be used?

Accepted Answer

Column-level security. Column-level security (C) is the correct choice because it allows you to restrict access to specific columns in a table, such as PII columns, while granting read access to all other columns in the 'sales' schema. This is achieved by defining a GRANT SELECT statement on the table with a column list, or by using a security policy with a filter predicate that blocks access to sensitive columns. Unlike Dynamic Data Masking, which obfuscates data at query time but does not prevent the user from seeing the masked values in certain scenarios, Column-level security actually denies access to the specified columns entirely.

Answer

Dynamic data masking

Answer

Row-level security

Answer

Azure Active Directory authentication

Question 11

You are running a pipeline in Azure Data Factory that uses a Mapping Data Flow. The data flow reads from Azure SQL Database and writes to Azure Synapse Analytics. You find that the data flow is very slow. Which configuration change would most likely improve performance?

Accepted Answer

Increase the 'Compute type' to 'Memory Optimized' and the 'Core count'. Mapping Data Flows in Azure Data Factory execute on Spark clusters. The default compute configuration may not provide sufficient memory or parallelism for large data volumes. Increasing the 'Compute type' to 'Memory Optimized' and raising the 'Core count' directly allocates more memory and processing cores to the Spark cluster, which accelerates transformations and data movement between Azure SQL Database and Azure Synapse Analytics.

Answer

Set the 'Staging' option to 'Use staging'

Answer

Enable staging for the sink and use PolyBase

Answer

Set the 'Partition option' to 'Round robin' on the source

Question 12

A company has an Azure Data Lake Storage Gen2 account. They want to ensure that only users with the 'Data Reader' role can access files in a specific container, while other users cannot list or read files. The storage account has hierarchical namespace enabled. What is the most secure and manageable approach?

Accepted Answer

Set POSIX-like access control lists (ACLs) on the container folder for the Data Reader role. Azure Data Lake Storage Gen2 with hierarchical namespace enabled supports POSIX-like access control lists (ACLs) at the container and folder level. By setting ACLs on the specific container folder to grant 'Read' and 'Execute' permissions only to the 'Data Reader' role (or its associated security group), you enforce least-privilege access without affecting other containers. This approach is both secure and manageable, as ACLs are inherited by default and can be centrally managed via Azure RBAC integration.

Answer

Assign the Storage Blob Data Reader role at the storage account level and use row-level security

Answer

Generate a shared access signature (SAS) token for each user

Answer

Configure a storage firewall to allow only the Data Reader role's IP addresses

Question 13

You are designing a data processing pipeline that ingests data from a REST API endpoint every hour. The API returns JSON data with a varying schema. You need to store the raw data in Azure Data Lake Storage Gen2 and later process it using Azure Databricks. Which file format should you use for the raw data storage?

Accepted Answer

JSON. C is correct because the raw data arrives from a REST API with a varying JSON schema, and storing it in JSON format preserves the exact structure and schema variability without data loss or transformation. JSON is schema-on-read, meaning the raw data can be ingested as-is into Azure Data Lake Storage Gen2 and later processed by Azure Databricks, which natively supports JSON parsing. This avoids premature schema enforcement that would occur with columnar or binary formats.

Answer

Parquet

Answer

CSV

Answer

Avro

Question 14

A data engineer needs to store semi-structured JSON logs from IoT devices. The data will be queried using SQL and must support high-throughput writes. Which Azure data store is most appropriate?

Accepted Answer

Azure Cosmos DB with Core (SQL) API. Azure Cosmos DB with Core (SQL) API is the most appropriate choice because it natively stores semi-structured JSON documents, supports high-throughput writes with single-digit millisecond latency, and allows querying the JSON data directly using SQL syntax. Its schema-agnostic design and automatic indexing make it ideal for IoT workloads where device telemetry arrives at high velocity and must be immediately queryable.

Answer

Azure Blob Storage with JSON blobs

Answer

Azure Data Lake Storage Gen2 with JSON files and PolyBase

Answer

Azure SQL Database with JSON columns

Question 15

A data engineer is tasked with optimizing a Spark job in Azure Synapse Analytics that processes 10 TB of data daily. The job currently uses 50 executors with 4 cores each. The performance is bottlenecked by shuffle operations. The engineer wants to reduce shuffle data size. Which technique should be applied?

Accepted Answer

Use column pruning to select only required columns before shuffle.. Column pruning (selecting only required columns) reduces the amount of data shuffled, decreasing shuffle size. Option A is incorrect because coalescing reduces the number of partitions but does not reduce the data volume shuffled. Option B is incorrect because increasing the number of shuffle partitions can increase shuffle overhead, not reduce it. Option C is incorrect because broadcasting all tables is impractical for large datasets (10 TB) and does not reduce shuffle data.

Answer

Coalesce the number of partitions before the shuffle.

Answer

Increase the number of shuffle partitions.

Answer

Broadcast all tables to avoid shuffles.