DP-203 Flashcards — Free Microsoft Azure Data Engineer Associate DP-203 Study Cards

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.

Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault provides the required encryption for data at rest in Azure Synapse Analytics dedicated SQL pool. Option A is wrong because Always Encrypted protects data in transit and at rest in application logic, not at rest in the database. Option B is wrong because Dynamic Data Masking does not encrypt data; it obfuscates it for users. Option C is wrong because Azure Storage Service Encryption is for Azure Storage accounts, not for SQL pools.

Table tags with access control lists (ACLs) in Unity Catalog. [CORRECT]

In Unity Catalog, attribute-based access control (ABAC) is implemented through table tags combined with access control lists (ACLs). Table tags allow data to be classified, and ACLs can then be configured based on those tags to enforce attribute-based access. This directly satisfies the requirement of using table tags for ABAC. Option B (dynamic views with user context functions) can provide similar controls but does not inherently leverage table tags and is not the dedicated ABAC feature in Unity Catalog.

Deploy the workspace in a VNet with forced tunneling enabled and a firewall.

Deploying the Azure Databricks workspace in a VNet with forced tunneling enabled and a firewall ensures that cluster nodes have no public IP addresses and all outbound traffic is routed through the firewall. Option A is incorrect because service endpoints do not prevent public IPs on cluster nodes. Option C is incorrect because NSGs alone do not force traffic through a firewall; forced tunneling requires a route table with default route to the firewall. Option D is incorrect because Azure Private Link only provides private connectivity to the workspace, but does not control outbound traffic from cluster nodes.

The 'keyVersion' is missing a specific version, so Azure Storage defaults to Microsoft-managed key.

In the ARM template snippet, the 'keyVersion' property is empty. Azure Storage requires a specific key version to use a customer-managed key; if omitted, Azure defaults to Microsoft-managed keys if the key is not found or the vault is inaccessible. Option B is incorrect because 'keySource' should be 'Microsoft.Keyvault' for customer-managed keys, not 'Microsoft.Storage'. Option C is incorrect because infrastructure encryption (double encryption) is independent of the key source. Option D is incorrect because enabling 'infrastructureEncryption' does not override the key source; it can be used with customer-managed keys.

sys.dm_pdw_exec_requests

Sys.dm_pdw_exec_requests provides information about queries including their memory grant size, which can be used to identify queries experiencing memory pressure. Option B is incorrect because sys.dm_pdw_wait_stats shows wait statistics, not memory pressure. Option C is incorrect because sys.dm_pdw_query_stats_xe is for extended events, not directly for memory grants. Option D is incorrect because sys.dm_pdw_nodes_os_performance_counters shows OS-level performance counters, not query-level memory information.

Enable system-assigned managed identity on the Stream Analytics job and configure the output to use it.

To authenticate to Azure Synapse Analytics using a managed identity, you should enable the system-assigned managed identity on the Stream Analytics job (it is enabled by default) and then configure the output to use managed identity authentication. Option A is correct. Option B is incorrect because a SAS token is not a managed identity. Option C is incorrect because while a user-assigned managed identity could be used, the system-assigned managed identity is sufficient and is the simpler option. Option D is incorrect because SQL Server authentication does not use managed identity and is less secure than managed identity authentication.

Organize folders as /YYYY/MM/DD/DeviceID/ in ADLS Gen2 and use file naming that includes timestamp.

ADLS Gen2 with a hierarchical namespace allows folder-based partitioning by date and device ID (e.g., /YYYY/MM/DD/DeviceID/), which directly maps to the query optimization requirement. This structure enables efficient partition pruning for time-range and device-specific queries, and the high-frequency 2 KB JSON payloads are well-suited for append-friendly file naming with timestamps.

Enable Change Data Capture (CDC) on the source table and use Azure Data Factory with a 1-minute tumbling window to copy changes into Synapse.

Azure Data Factory (ADF) with Change Data Capture (CDC) on the source SQL database can incrementally copy only changed rows (inserts, updates, deletes) into Azure Synapse Analytics using a 1-minute tumbling window, meeting the sub-5-minute latency requirement while minimizing data volume. This approach efficiently handles 50 million rows by transferring only the 30 needed columns, avoiding full table scans and reducing network load.

Store the logs in Azure Blob Storage with cool access tier.

Azure Blob Storage with the cool access tier is the correct choice because it is optimized for storing large amounts of semi-structured data (like JSON logs) at low cost, with infrequent access (once a month) and long retention (7 years). The cool tier offers lower storage costs than hot or premium tiers, while still providing high durability and the ability to query logs using tools like Azure Data Lake Storage or serverless SQL. This meets the compliance requirement without the high compute or transaction costs of a database solution.

Broadcast the smaller table (1 million rows) to all worker nodes.

Broadcasting the smaller table (1 million rows) to all worker nodes is the correct optimization because it eliminates the need for a full shuffle during the join. With Photon runtime, broadcast joins are highly efficient as they replicate the small table to each executor, allowing map-side joins that avoid costly data movement across the network. Given the 10:1 row ratio, the 1-million-row table is well within the default broadcast threshold (10 MB compressed, configurable via spark.sql.autoBroadcastJoinThreshold), making this the most effective shuffle-minimization technique.

Increase the executor memory setting in the Spark configuration.

An out-of-memory error on executors indicates that the available memory per executor is insufficient for the data being processed. Increasing the executor memory setting in the Spark configuration directly addresses this by allocating more heap space, allowing transformations to complete without spilling to disk or failing. This is the first and most straightforward action to take before optimizing partitioning or caching.

Use Azure Synapse Analytics dedicated SQL pool as the sink dataset and use Stored Procedure with staging table and PolyBase.

It uses a staging table and PolyBase to efficiently load incremental data into Azure Synapse Analytics dedicated SQL pool. PolyBase provides high-throughput parallel loading, and the stored procedure handles the merge logic (upsert) to manage incremental changes. This approach is recommended for large-scale, daily incremental loads to Synapse.

Use a HoppingWindow with size 5 minutes and hop 1 minute.

A HoppingWindow with a size of 5 minutes and a hop of 1 minute allows you to compute rolling averages over a 5-minute window while producing results every minute. This satisfies the requirement of outputting results at a higher frequency than the window duration, which is not possible with a TumblingWindow (which only outputs at the end of the window) or a SlidingWindow (which outputs on each event, not at fixed intervals).

SnapshotIsolation

Snapshot isolation is the correct choice because it provides a consistent view of the table by reading only the latest committed data, ignoring any uncommitted transactions. In Delta Lake, snapshot isolation ensures that readers see a snapshot of the table at a specific version, which includes only committed changes, making it ideal for read consistency without blocking concurrent writes.

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.

Use a Copy activity with pagination rules enabled in the source.

The Copy activity in Azure Data Factory supports pagination rules, allowing it to automatically iterate through API pages without a separate loop activity. Option A is incorrect because a ForEach activity would need to know the exact number of pages upfront, which is not dynamic. Option B is incorrect because the Lookup activity retrieves a single value and cannot iterate through pages. Option C is incorrect because an Until activity would require custom logic to check for the next page, whereas the Copy activity with pagination rules handles this natively.

SELECT DeviceType, COUNT(*) FROM Input GROUP BY DeviceType, TumblingWindow(second, 10)

A TumblingWindow(second, 10) produces non-overlapping, fixed-size 10-second windows, which is exactly what is needed to count events per device type every 10 seconds. The GROUP BY clause groups by DeviceType and the window, ensuring each device type gets its own count per window. This query meets the requirement without overlapping or sliding behavior.