DP-203 Practice Test 33 — 15 Questions

Question 1

You are developing an Azure Data Factory pipeline that processes data from an on-premises SQL Server. The pipeline uses a self-hosted integration runtime. You need to ensure that the pipeline can handle schema changes in the source table without failing. What should you do?

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

Accepted Answer

Use a Mapping Data Flow with 'Allow schema drift' enabled and a sink that supports schema evolution.. Mapping Data Flows with 'Allow schema drift' enabled can dynamically handle schema changes by reading and writing columns that are not predefined. This prevents pipeline failures due to new or removed columns. When paired with a sink that supports schema evolution, such as Delta Lake, the data flow can automatically adapt to changing schemas, ensuring continuous data processing.

Answer

Configure the Copy activity to use a stored procedure that dynamically generates the column list.

Answer

Enable the 'Allow schema drift' option in the Copy activity source settings.

Answer

Set the 'Auto create table' option in the sink dataset to handle new columns.

Question 2

You are designing a data storage solution in Azure Synapse Analytics. You need to load data incrementally from an Azure Data Lake Storage Gen2 source into a dedicated SQL pool. The source files are appended daily with new data, and you must ensure that only new records are loaded without duplicating existing records. The target table has a column named LoadDate that records when each row was inserted. Which approach should you use?

Accepted Answer

Use PolyBase to load all source files into a staging table, then use a MERGE statement to insert only rows that do not exist in the target.. Loading into a staging table with PolyBase and then using MERGE to insert only new rows is a standard pattern for incremental loads in a dedicated SQL pool. It avoids reloading historical data and prevents duplicates by comparing keys. The LoadDate column can be used to filter or track inserted rows, ensuring that only new records are added to the target.

Answer

Use the COPY statement to load all source files directly into the target table, relying on the primary key to reject duplicates.

Answer

Use Azure Data Factory to copy all source files to the target table with a daily schedule, and use a tumbling window trigger to avoid duplicates.

Answer

Use CREATE TABLE AS SELECT (CTAS) to create a new table from the source each day, replacing the target table.

Question 3

A retail analytics team stores Parquet files in Azure Data Lake Storage Gen2 partitioned by year, month, and day. Queries in Azure Synapse serverless SQL pools filter on a transaction date column, but performance is poor because the engine scans all files in the folder hierarchy. You need to reduce the amount of data scanned without changing the file layout. What should you implement?

Accepted Answer

Use the filepath() function in the OPENROWSET query to filter on the year, month, and day segments of the folder path.. Files stored in a year/month/day hierarchy can be pruned by exposing the path segments as virtual columns through the filepath() function and filtering on them. The serverless engine then skips folders that do not match, cutting the amount of data scanned. External tables without partition elimination, dedicated pool loading, or result set caching do not address the scan volume for arbitrary filtered queries.

Answer

Enable result set caching on the serverless database and set the cache retention period to seven days.

Answer

Create an external data source and external table over the folder and query the external table instead of using OPENROWSET directly.

Answer

Create a partitioned table in a dedicated SQL pool and load the Parquet files into it using PolyBase.

Question 4

You are using Azure Databricks to process a large dataset stored in Azure Data Lake Storage Gen2. The data is in Parquet format and you need to optimize read performance for a query that filters on a specific column. What should you do?

Accepted Answer

Partition the data by the filter column when writing the Parquet files.. Partitioning Parquet data by the filter column allows Spark to skip reading irrelevant partitions, drastically reducing I/O. This is a fundamental optimization for large datasets in data lakes. Other options either do not address the read pattern or could worsen performance. Partitioning is a best practice for improving query performance on filtered columns.

Answer

Cache the entire dataset in memory using the Databricks cache.

Answer

Increase the number of shuffle partitions in the Spark session configuration.

Answer

Convert the Parquet files to CSV format to enable predicate pushdown.

Question 5

You have an Azure Data Lake Storage Gen2 account used by an Azure Synapse Analytics serverless SQL pool. Analysts run ad-hoc queries against CSV and Parquet files. You need to reduce the amount of data scanned by these queries without changing file contents. What should you do?

Accepted Answer

Create a partitioned folder hierarchy by date and query with OPENROWSET using a wildcard path.. Serverless SQL pools charge per byte scanned, so the effective lever is reducing the files a query must read. Organizing data into a folder hierarchy that mirrors common filter columns, such as date, allows partition elimination, and an OPENROWSET path that targets those folders avoids reading unrelated data. This keeps files unchanged while cutting scanned volume and cost.

Answer

Convert all CSV files to Parquet and store them in a single folder.

Answer

Increase the DWU setting on the serverless SQL pool.

Answer

Enable hierarchical namespace on the storage account.

Question 6

You are building an Azure Data Factory pipeline that processes files from Azure Blob Storage. The pipeline uses a Mapping Data Flow to transform the data and then writes the output to Azure Data Lake Storage Gen2. You need to ensure that the Data Flow can handle schema drift, where incoming files may have additional columns not present in the initial schema. What should you configure in the Data Flow?

Accepted Answer

Enable 'Allow schema drift' in the source transformation and use 'Auto mapping' in the sink transformation.. In Mapping Data Flow, schema drift is enabled at the source transformation, allowing it to read columns not defined in the projection. The sink must also be configured to write those columns, typically using auto mapping. This combination ensures that additional columns are processed and persisted, which is necessary when incoming files have evolving schemas.

Answer

Define a fixed schema in the source projection and use a Derived Column transformation to add new columns.

Answer

Use a Parameterized dataset and pass the schema as a parameter at runtime.

Answer

Set the source dataset to use a wildcard file path and enable 'Recursive' in the source options.

Question 7

You are developing an Azure Stream Analytics job that ingests telemetry from Azure Event Hubs and writes results to an Azure Synapse Analytics dedicated SQL pool. The job must compute a 5-minute tumbling window aggregation and write the aggregated rows to the dedicated SQL pool. You need to configure the output so that each window's aggregated rows are written efficiently. What should you do?

Accepted Answer

Configure the output to use Azure Synapse Analytics and specify the database and table. Set the batch size to a value that matches the expected number of rows per window.. Stream Analytics provides a native Azure Synapse Analytics output that uses bulk insert mechanisms. The batch size setting controls rows per bulk operation, which directly impacts write efficiency. Writing directly to the dedicated SQL pool avoids intermediate storage and extra processing steps, meeting the requirement for efficient writes of windowed aggregates.

Answer

Configure the output to use Event Hubs and then use a Synapse pipeline to read from Event Hubs and write to the dedicated SQL pool.

Answer

Configure the output to use Blob Storage and then use an Azure Data Factory Copy activity to load the data into the dedicated SQL pool.

Answer

Configure the output to use Azure SQL Database and then create a linked server in the dedicated SQL pool to pull the data.

Question 8

You need to grant a data analyst read access to a specific folder in an Azure Data Lake Storage Gen2 account. The analyst must not be able to read other folders in the same container. You want to follow the principle of least privilege. What should you use?

Accepted Answer

A POSIX access control list (ACL) on the target folder, with the analyst's Azure AD identity granted read and execute.. POSIX ACLs in Data Lake Storage Gen2 allow permission entries at the directory and file level, so you can grant an identity read and execute on one folder while leaving sibling folders inaccessible. This is the native mechanism for fine-grained, least-privilege access. Broad role assignments and container-scoped SAS or policies grant access to the whole container and are therefore unsuitable here.

Answer

The Storage Blob Data Contributor role assigned at the storage account scope.

Answer

A storage account shared access signature scoped to the container.

Answer

A stored access policy on the container that allows read operations.

Question 9

You manage an Azure Synapse Analytics dedicated SQL pool. A nightly ELT job loads a large fact table and then runs UPDATE statements on many rows. You observe that tempdb usage grows until the load fails. You need to reduce tempdb pressure during the update phase. What should you do?

Accepted Answer

Replace the row-by-row updates with a CTAS-based pattern that rebuilds the affected partitions.. Large UPDATE operations in dedicated SQL pools incur significant logging and data movement that consume tempdb. The recommended approach for substantial changes is to use CREATE TABLE AS SELECT to produce a new version of the affected partitions and then switch them in. This avoids the row-level update engine path, reduces tempdb growth, and is faster for bulk modifications.

Answer

Enable result set caching on the dedicated SQL pool.

Answer

Increase the size of tempdb by scaling the dedicated SQL pool to a higher DWU.

Answer

Change the distribution of the fact table to ROUND_ROBIN.

Question 10

You are building an Azure Synapse Analytics pipeline that uses a Mapping Data Flow to transform data from Azure Data Lake Storage Gen2. The data flow includes a derived column transformation that uses a custom expression to calculate a new field. You need to debug the data flow and preview the output at each transformation. Which feature should you use?

Accepted Answer

Enable the 'Data flow debug' toggle and use the Data Preview tab in each transformation.. Azure Synapse Mapping Data Flows provide a debug mode that enables interactive data preview at each transformation. By turning on the debug toggle and using the Data Preview tab, you can see the results of expressions and transformations immediately, which is essential for validating logic before deploying the pipeline.

Answer

Add a Sink transformation and write to a temporary folder, then read the data manually.

Answer

Run the pipeline with a trigger and check the output in the Monitor hub.

Answer

Use the 'Debug' button on the pipeline canvas and set breakpoints on the data flow activity.

Question 11

You are developing an Azure Databricks notebook that processes a large Delta Lake table. You must add a derived column that depends on the latest value of a watermark stored in a small reference table, and the notebook must refresh this value before each micro-batch. You need to ensure the reference data is re-read on every micro-batch rather than cached once. Which approach should you use?

Accepted Answer

Use the foreachBatch sink and, inside the function, query the reference table with a fresh read on each invocation.. Structured Streaming captures static DataFrames when a query starts, so joins against them do not see later changes. foreachBatch executes a user-defined function for each micro-batch and permits arbitrary operations, including a fresh read of the reference table. Reading the watermark inside that function ensures the latest value is used for the derived column on every micro-batch, which is precisely the stated requirement.

Answer

Persist the reference table with the MEMORY_AND_DISK storage level before starting the stream.

Answer

Broadcast the reference table as a static DataFrame and join it to the streaming DataFrame.

Answer

Enable the spark.databricks.delta.cache.enabled option on the streaming query.

Question 12

A logistics company uses Azure Blob Storage to store shipping manifests as block blobs. The manifests are accessed frequently for the first 30 days, then rarely accessed for the next 60 days, and after 90 days they must be retained for seven years for compliance but are almost never accessed. You need to minimize storage costs while ensuring the data remains available for compliance audits. What should you do?

Accepted Answer

Move blobs to the Cool access tier after 30 days, then to the Archive tier after 90 days.. Azure Blob Storage access tiers optimize costs based on access frequency. The Cool tier is cost-effective for data accessed infrequently but still requiring quick retrieval, suitable for the 30-90 day period. The Archive tier offers the lowest storage cost for data that can tolerate hours of retrieval latency, ideal for long-term compliance retention. Transitioning blobs through these tiers minimizes costs while ensuring availability for audits.

Answer

Create a lifecycle management policy that deletes blobs after 90 days and stores them in Azure Backup for seven years.

Answer

Move blobs to the Archive tier after 30 days and keep them there for seven years.

Answer

Enable soft delete and versioning, and leave all blobs in the Hot tier for seven years.

Question 13

You are using Azure Data Factory to copy data from an on-premises Oracle database to Azure Data Lake Storage Gen2. You need to ensure the copy activity can connect to the Oracle database without storing credentials in the pipeline JSON. What should you configure?

Accepted Answer

A linked service that references an Azure Key Vault secret for the Oracle credentials.. Azure Data Factory integrates with Azure Key Vault so linked services can reference secrets instead of embedding credentials. Storing the Oracle password as a Key Vault secret and referencing it from the linked service keeps sensitive values out of pipeline and dataset JSON. Network components such as integration runtimes and private endpoints address connectivity, not credential protection.

Answer

A parameterized dataset that prompts for credentials at runtime.

Answer

A self-hosted integration runtime with a service account that has Oracle access.

Answer

A managed private endpoint from the data factory to the Oracle server.

Question 14

You have an Azure Data Factory pipeline that must copy data from an on-premises Oracle database to Azure Blob Storage every night. The on-premises server cannot accept inbound connections, and no VPN or ExpressRoute is available. You need to enable connectivity with minimal administrative overhead. What should you deploy?

Accepted Answer

A self-hosted integration runtime installed on a machine in the on-premises network.. The self-hosted integration runtime is the standard Azure Data Factory mechanism for reaching on-premises data sources when inbound connectivity is not possible. It initiates outbound HTTPS connections to the Data Factory service, so it works without VPN, ExpressRoute, or firewall changes. It also supports the Oracle connector and Blob Storage sink required by this nightly copy pipeline.

Answer

An Azure VPN Gateway with a site-to-site connection to the on-premises network.

Answer

An Azure ExpressRoute circuit with private peering to the on-premises datacenter.

Answer

An Azure Integration Runtime with a managed virtual network and private endpoints to the Oracle server.

Question 15

A media company uses Azure Data Lake Storage Gen2 to store video files and metadata. They need to ensure that when a user is deleted from Azure Active Directory, their access to the data lake is immediately revoked. They also want to minimize administrative overhead. What should they do?

Accepted Answer

Use Azure Active Directory security groups and assign ACLs to the groups.. Using Microsoft Entra ID security groups to manage access to Data Lake Storage Gen2 ensures that when a user is deleted from Microsoft Entra ID, their group memberships are removed, and their access is revoked immediately. This approach centralizes permission management, reducing administrative overhead. Direct ACLs, SAS tokens, and storage account keys do not provide automatic revocation upon user deletion.

Answer

Assign POSIX ACLs directly to each user.

Answer

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

Answer

Store the storage account key in Azure Key Vault and grant users access to the key.