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.

Option D is correct because 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.

Create external tables on the JSON files using PolyBase, then use CREATE EXTERNAL TABLE AS SELECT (CETAS) to write transformed Parquet files.

Option D is correct because 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.

Use the GRANT statement to grant SELECT on the schema to the Azure AD group.

The GRANT statement in Azure Synapse dedicated SQL pool allows you to assign permissions directly to Azure AD groups. By granting SELECT on the schema to the specific Azure AD group, only members of that group can query objects within that schema, meeting the requirement precisely.

Enable 'Enable staging' and set 'Degree of copy parallelism' to a higher value.

Option D is correct because enabling staging allows the copy activity to use Azure Blob Storage as an intermediate staging area, which breaks the 500 MB files into manageable chunks and uses parallel staging writes to the Dedicated SQL Pool. This reduces the load on the single copy session and prevents timeout errors by leveraging the staging engine's retry and parallelization capabilities.

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

Option B is correct because 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 Event Hubs Capture to write data to Azure Data Lake Storage and then load using PolyBase.

Option D is correct because Event Hubs Capture continuously writes data to Azure Data Lake Storage, providing a durable buffer that prevents data loss during transient failures. Once data is in ADLS, PolyBase reliably loads it into the dedicated SQL pool. This architecture ensures data is persisted at the event hub level before any pipeline processing, minimizing loss.