Microsoft DP-750 Implementing Data Engineering Solutions Using Azure Databricks Exam Practice Test

Production ingestion: Job compute

BI: Serverless SQL warehouse

Job compute is designed for automated production workloads executed through Lakeflow Jobs. Its lifecycle can be tied to the job run, providing workload isolation and avoiding the cost of maintaining an interactive all-purpose cluster continuously. It is therefore appropriate for scheduled ingestion and transformation processing. A serverless SQL warehouse is designed for BI and Databricks SQL workloads. It provides rapid startup, automatic infrastructure management, scaling, and optimized SQL-query execution for dashboards and reporting tools. All-purpose compute is intended primarily for interactive notebook development and exploration, while shared compute does not provide the same job-specific isolation or SQL-serving experience. Consequently, job compute should support production ingestion, and a serverless SQL warehouse should serve the BI workload.

The right ingestion tool depends on the source characteristics:

File-based telemetry and maintenance data → Auto Loader (cloudFiles). It monitors ADLS Gen2 for new arrivals, handles schema inference and evolution for the frequent schema drift, and requires minimal operational effort.

Real-time telemetry from Event Hubs → Spark Structured Streaming with the azure-eventhubs-spark connector. This provides exactly-once semantics and checkpoint-based recovery, satisfying ' resume processing after failures without reprocessing. '

Structured maintenance data from PostgreSQL → JDBC connector. Databricks supports direct JDBC reads from relational databases with pushdown predicates.

Daily CSV ERP extracts (50–100 GB) → COPY INTO or Auto Loader. Both support idempotent incremental batch loading into Delta tables with minimal code.

The correct answer is D. Breaking the pipeline into separate tasks for ingestion, cleansing, and curation is the foundation of well-designed Lakeflow Jobs pipelines. Each task should own one responsibility — when a task does too much, debugging a failure becomes a hunt through unrelated code, and retry logic becomes expensive because you re-execute work that already succeeded.

Contoso ' s planned changes explicitly call for ' a clear execution order and dependencies ' and ' orchestrate multi-step ingestion and transformation workflows. ' Separate tasks map directly to those goals: Lakeflow Jobs tracks each task ' s status independently, so if cleansing fails, ingestion doesn ' t re-run.

Option A bundles everything into one notebook, which means a curation bug forces a full re-ingestion. Option B copies logic three times — any future change must be applied in triplicate, which is a maintenance hazard. Option C forces everything through SQL MERGE, which is the wrong tool for raw-event ingestion and doesn ' t address cleansing or schema drift.

The solution requires spark.readStream with format( ' cloudFiles ' ) for Auto Loader, paired with .writeStream using mergeSchema=true and a checkpointLocation.

Auto Loader ' s cloudFiles source incrementally processes new JSON files without rescanning the entire directory. The mergeSchema option handles schema drift — when sensors add new fields, the target Delta table schema expands automatically instead of throwing a parse error. This directly addresses Contoso ' s requirement to ' support schema drift. '

The checkpointLocation is what gives the pipeline its resilience. Databricks writes the stream ' s committed offset and schema state to that path. If the cluster restarts, the engine reads the checkpoint and picks up exactly where it left off — no events are reprocessed, satisfying ' exactly-once semantics ' and ' resume processing after failures without reprocessing the data. '

Without a checkpoint, the stream would restart from the beginning on every cluster bounce, which is precisely the problem Contoso is trying to eliminate.

The correct mapping is SCD Type 1 for equipment metadata and SCD Type 2 for IoT sensor ownership history.

SCD Type 1 overwrites the existing record whenever an attribute changes — no history is kept. Contoso ' s requirement for equipment metadata (name, manufacturer, model, commissioning date) states ' historical values are NOT required, ' which is the textbook definition of Type 1. A MERGE INTO with WHEN MATCHED THEN UPDATE handles this cleanly in Delta Lake.

SCD Type 2 creates a new row for each change, preserving the full history through effective-date or version columns. Contoso requires that ' analysts must track the full history of ownership ' as sensors change hands over time — that full audit trail is only possible with Type 2. Type 3 (keeping just the previous value in an extra column) would lose earlier ownership records, so it doesn ' t satisfy the ' full history ' requirement.

The correct answer is A. Photon is Azure Databricks ' native vectorized query engine, written in C++, designed to accelerate data ingestion and SQL-heavy workloads significantly over the standard Spark JVM path. Enabling it on a job compute cluster directly addresses Contoso ' s requirement for ' fast and consistent performance for BI workloads ' and ' production ingestion workloads that can scale automatically during telemetry spikes. '

Photon integrates transparently — no code changes are needed — and pairs well with autoscaling job clusters to handle the bursty 40,000-sensor telemetry load.

Option B contradicts the isolation requirement: Contoso explicitly needs production and development separated, not merged onto shared compute. Option C with a fixed large node gives peak capacity at all times, driving up costs even during quiet periods. Option D disabling autoscaling is the opposite of what ' s needed — telemetry spikes require elastic scaling, not a locked node count.

The correct answer is C. Column masks are the right tool when you need to partially expose sensitive data rather than hide it entirely. A column mask is a SQL function attached to a column that rewrites the returned value based on who is querying. For email_address, the mask returns only the substring after ' @ ' . For credit_card_number, it returns only the last four digits. Business analysts get useful data without seeing anything sensitive, and they can still query the table without errors.

Option A (row-level filters) controls which rows a user sees, not the values within a row — it can ' t partially redact a column. Option B (grant SELECT only on non-sensitive columns) removes the columns entirely, so analysts can ' t see even partial email or card values — that doesn ' t meet the ' can see only partial values ' requirement. Option D (column-level encryption) requires key management infrastructure and decryption at query time, which is significantly more complex than column masking for this use case.

The correct answer is C — INSERT INTO.

The scenario is clear: the daily file contains only new records, no updates to existing rows. INSERT INTO is the right command because it appends the new rows to Sales.orders without touching the existing historical data. All three requirements are satisfied: existing data is preserved, only new records are added, and the loading strategy is as simple as it gets.

Option A (INSERT OVERWRITE) replaces the existing data with just today ' s file. After the first day you ' d have only 24 hours of history — all prior sales records would be gone. Option B (UPDATE) modifies specific existing rows based on a match condition; it doesn ' t load new records at all.

Because the source file is guaranteed to contain only new records and no duplicates of existing rows, a full MERGE is unnecessary overhead. Simple INSERT INTO is both correct and the most efficient approach.

The correct answers are B and D. Delta Lake ' s data retention behaviour is controlled by two distinct properties that work together:

delta.deletedFileRetentionDuration governs how long physically deleted data files are kept before VACUUM can remove them. Setting this to 30 days ensures deleted records remain on storage for the full audit window.

delta.logRetentionDuration controls how long the transaction log is retained. Time-travel queries (SELECT ... VERSION AS OF or TIMESTAMP AS OF) rely on the log to reconstruct historical table states — without 30 days of log, you can ' t query 30-day-old snapshots even if the data files are still there.

Both properties need to be set to 30 days to fully honour the requirement. Option A (delta.timeUntilArchived) doesn ' t exist in Delta Lake. Option C (autoCompact) manages file sizing, not retention. Option E (enableDeletionVectors) speeds up deletes but has no bearing on how long data is kept.

The correct answer is B — implement checkpointing.

A checkpoint is a durable record of the streaming job ' s progress written to ADLS Gen2 or DBFS after each successfully committed micro-batch. When the job restarts after a transient failure, it reads the checkpoint to find the last committed offset and resumes from that exact point — no data is reprocessed, no data is lost.

This satisfies all three requirements directly: checkpointing enables resumption from the failure point (not from the beginning), restart is fast because there ' s no replay overhead, and costs are minimised because no compute is wasted reprocessing records already handled.

Option A (decrease retry interval) makes the job retry sooner but doesn ' t control where it resumes from. Option C (alert and manual restart) adds human latency and doesn ' t prevent reprocessing without a checkpoint. Option D (increase minimum nodes) reduces the likelihood of resource-related failures but increases cost and doesn ' t address the recovery behaviour itself.

groupBy

pivot

The groupBy( " Region " ) operation establishes one aggregation group for each geographical region in the DataFrame. The subsequent pivot( " Product " ) operation converts every distinct Product value into a separate output column. Finally, agg(F.sum( " Sales " )) calculates the total sales for every product within each region. The resulting DataFrame contains one row per region and one column for each product, with the corresponding aggregated sales value. cogroup combines grouped datasets and is not used for creating a conventional pivot table. coalesce controls the number of DataFrame partitions or replaces null values, depending on its context. The sum function is already provided inside the aggregation expression and therefore is not required in either blank.

Schema evolution allows the target Delta table to incorporate compatible new source columns instead of failing when the incoming schema changes. This is the appropriate response to additive schema drift and avoids manually rebuilding tables whenever the source evolves. Creating a separate table for every schema version would fragment the dataset and increase operational effort. Disabling schema enforcement removes valuable protection against incompatible or corrupt data rather than handling legitimate evolution safely. Row filters operate on records and cannot remove an unexpected column from the incoming schema. With schema evolution enabled, the pipeline can detect new fields, update the target schema, and continue processing while retaining Delta Lake’s transactional guarantees. The solution therefore supports changing source data without sacrificing the managed-table architecture.

The correct answer is C — a Lakeflow Spark Declarative Pipelines (SDP) pipeline.

SDP is tailor-made for exactly this pattern: ingest from cloud storage, transform through cleansing and enrichment stages, and publish Delta tables to Unity Catalog. What sets it apart from the other options is built-in monitoring (the pipeline graph shows row counts, expectation metrics, and run history) and automatic retries (failed tasks retry automatically based on pipeline settings, without manual re-run triggers).

Option A (Structured Streaming job) gives you the streaming engine but nothing else — monitoring, alerting, and retry logic all have to be built from scratch. Option B (scheduled notebook job) is a batch approach that requires manual monitoring and lacks the declarative lineage tracking SDP provides. Option D (Azure Data Factory with data flows) works but adds a separate Azure service to manage, introduces ADF licensing costs, and doesn ' t integrate natively with Unity Catalog governance.

A foreign catalog mirrors the external ERP database and provides read-only access through Lakehouse Federation. Consequently, it cannot host managed volumes or locally created Delta tables, and its foreign tables cannot be altered to add analytics columns. The appropriate design is to create governed views in the managed analytics catalog and have those views reference the foreign tables through fully qualified catalog.schema.table names. This preserves the ERP data in its source system, avoids duplication, and exposes standardized curated objects through Unity Catalog. Permissions can then be granted on the managed views while access to underlying foreign objects remains controlled. Unity Catalog uses a three-level namespace, and foreign catalogs are specifically intended to make external database data queryable without copying it into Databricks-managed storage. Microsoft Learn