Data Engineering with Azure Databricks · 33% of the exam

Prepare and process data: free practice questions

5 sample questions from our 83-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Microsoft exam.

1. A data engineer at an insurance company needs to perform a batch load of 400 GB of historical claim records stored as Parquet files in Azure Data Lake Storage Gen2 into a Unity Catalog managed Delta table. The load is a one-time operation, and the files are already clean and schema-conformant. Which loading approach is most appropriate?

A. Use `COPY INTO` with the `FORMAT = 'PARQUET'` option, because it is idempotent, tracks ingested files, and is optimized for bulk loading of cloud-stored files.✓ Correct
B. Use Spark Structured Streaming with Auto Loader in `trigger(once=True)` mode to process all files exactly once.
C. Use a CTAS (CREATE TABLE AS SELECT) statement that reads the ADLS path directly, then write the result into the Unity Catalog table with `INSERT INTO`.
D. Use Lakeflow Connect to configure a Parquet source connector and schedule a one-time sync.

Explanation

`COPY INTO` is the correct answer for a one-time, idempotent bulk load of clean, schema-conformant Parquet files from cloud storage into Delta. It tracks which files have been loaded, is optimized for large batch ingestion, and requires no streaming infrastructure. Option B (Auto Loader with `trigger(once=True)`) works technically but adds unnecessary complexity — Auto Loader is designed for continuous or incremental ingestion scenarios, not simple one-time bulk loads. Option C (CTAS + INSERT INTO) is a two-step workaround; CTAS alone would create a new table overwriting the target, and the combination is less clean and idempotent than `COPY INTO`. Option D (Lakeflow Connect) is intended for ongoing connector-based replication from SaaS or database sources, not for loading static Parquet files from cloud storage in a one-time operation.

2. A data engineering team at a logistics company is building a Lakeflow Spark Declarative Pipeline to process package delivery events. The Silver table must deduplicate records by `package_id`, keeping only the most recent event based on `event_ts`. The team is considering the following implementation approaches: 1. Use a window function (`ROW_NUMBER() OVER (PARTITION BY package_id ORDER BY event_ts DESC)`) in the Silver table query and filter for `row_num = 1`. 2. Use `MERGE INTO` with a `WHEN MATCHED AND source.event_ts > target.event_ts THEN UPDATE` clause to upsert records into the Silver table. 3. Use `@dlt.expect_or_drop("no_duplicates", "package_id IS NOT NULL")` to remove duplicate rows. 4. Use `dropDuplicates(["package_id"])` in a streaming Silver table definition, relying on Spark's built-in deduplication within micro-batches. The pipeline is incremental and the Silver table is defined as a streaming table (`CREATE OR REFRESH STREAMING TABLE`). Which approach is most appropriate for correctly deduplicating across all historical batches in this streaming context?

A. Approach 1, because the window function will correctly rank all records across all historical batches and retain only the latest event per package.
B. Approach 2, because MERGE INTO with a timestamp-based condition is the standard pattern for applying upserts in a streaming Delta table, ensuring only the latest event per package survives across incremental runs.✓ Correct
C. Approach 3, because the pipeline expectation will enforce uniqueness on `package_id` by dropping null keys, which prevents duplicates from accumulating.
D. Approach 4, because `dropDuplicates` is the native Spark Structured Streaming deduplication method and guarantees exactly-once semantics across all micro-batches when used with a watermark.
E. Approach 1 combined with Approach 4, because the window function handles historical deduplication while `dropDuplicates` handles within-batch deduplication in real time.

Explanation

Approach 2 (MERGE INTO with a timestamp guard) is the correct pattern for incremental deduplication in a streaming Delta table across multiple batches. In Lakeflow Spark Declarative Pipelines, a `CREATE OR REFRESH STREAMING TABLE` processes new records incrementally; MERGE INTO allows each new batch to update the target only when the incoming record is newer than the existing one, correctly maintaining the latest state per `package_id` over time. Approach 1 (window function) works well for batch/materialized view patterns but is problematic for streaming tables because it would re-rank all historical data on every refresh — streaming tables process only new data incrementally, so a full re-rank is not performed each run. Approach 3 (`expect_or_drop` on null `package_id`) does not deduplicate at all — it only drops rows where the key is null, which is a null-check, not a uniqueness check. Approach 4 (`dropDuplicates`) only deduplicates within a single micro-batch; it does not deduplicate across batches unless combined with a watermark and stateful processing, and even then it cannot compare timestamps to keep the latest — it simply removes duplicates within a window, which would incorrectly drop legitimate re-delivery updates. Approach 5 (combination) adds complexity without solving the cross-batch problem correctly and conflates two different processing models.

3. A data engineer is designing a Lakeflow Spark Declarative Pipeline. A Gold-layer table must aggregate Silver streaming data, but the business requires that the aggregation results always reflect COMPLETE totals (e.g., total revenue per region), not just incremental updates. The Gold table is queried by a BI tool that expects full result sets. Which output mode should the streaming aggregation use when writing to the Gold Delta table?

A. Append mode, because Delta Lake handles deduplication automatically.
B. Update mode, because only changed aggregation keys are rewritten, reducing write amplification.
C. Complete mode, because the entire aggregation result set is rewritten on every trigger, ensuring the BI tool always sees full totals.✓ Correct
D. Foreach mode, because it allows custom write logic to merge incremental aggregates into the target table.

Explanation

Complete mode rewrites the entire aggregation result set to the sink on every micro-batch trigger. This is required when the downstream BI tool needs full totals (e.g., all regions) rather than just the rows that changed. Delta Lake supports complete mode sinks. Option A (append mode) is incompatible with aggregations that can update previously emitted keys (e.g., late-arriving data changing a regional total). Option B (update mode) only writes changed rows, which means the BI tool's query would see only a partial result set unless it joins with a previous snapshot. Option D (`foreach`) is a write sink option for custom logic, not an output mode, and does not solve the completeness requirement.

4. A data engineer is building a real-time pipeline to ingest order events from Azure Event Hubs into a Unity Catalog Delta table. The pipeline must ensure exactly-once semantics for the end-to-end write. Which approach correctly achieves this requirement?

A. Use Spark Structured Streaming with the Azure Event Hubs connector and write to Delta Lake; Delta Lake's idempotent sink combined with Structured Streaming's checkpointing provides exactly-once guarantees.✓ Correct
B. Use COPY INTO in a scheduled job polling the Event Hubs capture storage account every minute to achieve near-real-time exactly-once ingestion.
C. Use a Lakeflow Connect connector for Event Hubs, which natively deduplicates all messages before writing to Delta.
D. Use Spark Structured Streaming with foreachBatch and implement manual deduplication logic inside the batch function to guarantee exactly-once semantics.

Explanation

Spark Structured Streaming combined with Delta Lake as a sink achieves exactly-once end-to-end semantics. Structured Streaming uses checkpointing to track offsets and ensure at-least-once delivery from Event Hubs, while Delta Lake's transactional write mechanism (idempotent sink) eliminates duplicates, resulting in exactly-once semantics. COPY INTO is a batch ingestion command not designed for streaming from Event Hubs; polling capture storage would introduce latency and potential duplicate processing complexity. Lakeflow Connect provides pre-built connectors for SaaS sources and databases, not for Azure Event Hubs streaming; it also does not inherently guarantee message-level deduplication. Using foreachBatch with manual deduplication can work but requires significant custom engineering and is error-prone — the built-in Delta sink already handles this correctly, making the manual approach unnecessary and complex.

5. A data engineering team needs to bring data from a cloud-hosted Workday HR system into Unity Catalog on a daily basis. The team has limited Spark expertise and wants to minimize custom code. They also need automatic schema mapping and built-in incremental load support. Which ingestion tool is MOST appropriate?

A. A Databricks notebook using the requests library to call the Workday REST API and write results with spark.write.format('delta').
B. Lakeflow Connect, which provides a managed, no-code connector for SaaS applications including Workday with built-in incremental extraction.✓ Correct
C. Azure Data Factory with a Workday linked service and a Copy Activity writing to ADLS Gen2, followed by a COPY INTO command.
D. Spark Structured Streaming reading from a Workday Kafka topic using the Kafka source connector.

Explanation

Lakeflow Connect (formerly Fivetran-powered connectors in Databricks) provides pre-built, managed connectors for SaaS sources like Workday. It handles authentication, incremental extraction, schema mapping, and writing to Unity Catalog Delta tables with minimal code, directly matching the team's requirements. A notebook using the REST API requires custom incremental tracking logic, error handling, and schema management — all of which add significant engineering effort contradicting the 'minimize custom code' requirement. An ADF pipeline with COPY INTO adds two-hop complexity and requires maintaining an ADF pipeline in addition to the Databricks environment. Workday does not expose a Kafka topic natively, making Structured Streaming with a Kafka connector inapplicable here.

78 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free