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 a customer-managed key in Azure Key Vault encrypts the dedicated SQL pool's data at rest (data files, log files, backups) and allows the organization to control and rotate the encryption key. TDE is the native at-rest encryption mechanism for Azure Synapse dedicated SQL pools. Configuring it with a customer-managed key in Key Vault meets the requirement precisely.

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

To ensure no public IPs on cluster nodes and all outbound traffic routed through a firewall, you must deploy the Azure Databricks workspace into your own VNet (VNet injection) with forced tunneling enabled, and route egress through a firewall such as Azure Firewall or a user-defined route to an NVA. VNet injection gives you control over the subnets, NSGs, and routing, and forced tunneling redirects all outbound traffic to your inspection appliance. This is the documented architecture for secure, no-public-IP Databricks deployments.

Set ACLs on the directory with default ACLs for the service principal.

Azure RBAC roles for Azure Storage cannot be scoped to a directory or file; they are assigned at the storage account or container level. To grant a service principal read and write access to a specific directory in an Azure Data Lake Storage Gen2 account with hierarchical namespace enabled, use access control lists (ACLs) on that directory. Default ACLs on the directory will be inherited by newly created child items. Option A is incorrect because the Storage Blob Data Contributor role cannot be assigned at the directory level. Option B is incorrect because a managed identity is an identity, not a permission mechanism. Option C is incorrect because stored access policies are used for shared access signatures (SAS), not for granting directory access.

Queries classified as 'HeavyLoader' will not have a timeout.

When queryExecutionTimeoutSeconds is set to 0, it means no timeout is enforced. Queries classified as 'HeavyLoader' can run indefinitely without being terminated by the timeout mechanism. Option A is incorrect because a value of 0 does not mean indefinite waiting for resources; it refers to the timeout duration. Option B is incorrect; 0 is a valid configuration that disables the timeout. Option D is incorrect; a timeout of 0 does not cause immediate timeout but rather no timeout.

sys.dm_exec_requests

Sys.dm_exec_returns detailed information about each request currently executing on the serverless SQL pool, including its state, command, and session ID. This DMV is specifically designed for monitoring active queries. Option A (sys.dm_resource_governor_workload_groups) shows workload group configuration and resource statistics, not current requests. Option B (sys.dm_exec_query_stats) provides cumulative performance statistics for cached query plans, not currently executing queries. Option D (sys.dm_exec_sessions) contains session-level information but does not indicate which sessions are actively executing a request.

Column-level security (CLS)

Column-level security (CLS) in Azure Synapse Analytics allows you to restrict access to specific columns in a table based on the user's role or permissions. It is implemented using GRANT and DENY statements on individual columns, ensuring that only authorized users can view sensitive columns. This directly addresses the requirement to apply column-level security to a specific table.

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 a Copy activity with pagination rules enabled in the source.

Azure Data Factory's Copy activity supports pagination rules that automatically handle paging through REST API responses. By configuring pagination rules in the source dataset, the Copy activity can iterate through pages until all data is copied, without needing explicit looping activities. This is the most efficient and recommended approach.

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.

Azure Databricks with Structured Streaming

Azure Databricks with Structured Streaming is the correct choice because it natively supports exactly-once semantics when writing to Delta Lake from Event Hubs. Structured Streaming uses checkpointing and a write-ahead log to ensure each record is processed exactly once, even in the face of failures. Azure Databricks runs on Spark, which integrates seamlessly with both Event Hubs (via the Event Hubs connector) and Delta Lake (as a sink). Other options are either batch-oriented or lack the necessary transactional guarantees for exactly-once delivery to Delta Lake.

FTP

Azure Data Factory provides a native FTP connector that supports username/password authentication for connecting to FTP servers. This linked service type is specifically designed to handle the FTP protocol (RFC 959) and allows you to copy data directly from an FTP server to Azure Data Lake Storage without requiring any additional gateways or custom activities.

Replicate the dimension table to all compute nodes.

Replicating the small dimension table (1 million rows) to all compute nodes eliminates data movement during the join with the large fact table (10 billion rows). In Azure Synapse dedicated SQL pool, replicated tables store a full copy on each distribution, so the join can be performed locally on every node without shuffling data across the network, drastically reducing query latency.

Microsoft Entra ID pass-through authentication.

Microsoft Entra ID pass-through authentication (option A) is correct because it allows the serverless SQL pool to use the caller's identity when accessing Azure Data Lake Storage Gen2. This ensures that Azure RBAC permissions (e.g., Storage Blob Data Reader) assigned to the user are evaluated for each query, providing fine-grained access control without exposing storage account keys or tokens.

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.