DP-750 Implementing Data Engineering Solutions Using Azure Databricks Questions and Answers

Two separate cluster settings address the two requirements:

Autoscaling handles the first requirement — dynamically adding workers when the workload is heavy and removing them when it lightens. You set a minimum and maximum node count, and Databricks adjusts the cluster size between those bounds based on task queue depth.

Auto-termination handles the second — the cluster shuts itself down after a configurable idle period (e.g., 30 minutes with no active queries), preventing wasted spend on a development cluster left running overnight.

These two settings are independent and complementary: autoscaling manages horizontal elasticity during active use, while auto-termination manages complete shutdown during inactivity. Both are configured in the cluster creation UI under the Compute section.

The correct answers are B and E.

The goal is to reduce storage costs while preserving 30 days of time travel. Two actions are needed:

Set delta.deletedFileRetentionDuration to a value aligned with the 30-day requirement (Option B). Note: the answer option states '10 days' which would be insufficient for 30-day time travel — in practice this property should be set to at least 30 days. This property defines the retention floor: VACUUM will not delete any file newer than this threshold.

Run VACUUM on Table1 (Option E). VACUUM physically removes unreferenced data files older than the retention duration from storage. Without running VACUUM, obsolete files accumulate indefinitely regardless of the retention property setting — the property tells VACUUM what to keep; VACUUM is what does the actual cleanup.

Option C (OPTIMIZE) compacts small files for better query performance but never deletes anything. Option D (logRetentionDuration) keeps the transaction log for time travel but doesn't free up the data file storage.

Two actions are needed to reclaim storage while keeping tables queryable:

Set delta.deletedFileRetentionDuration and delta.logRetentionDuration to 90 days on each table. These properties define the retention floor — VACUUM will not touch anything newer than this threshold, so no data needed for time travel within 90 days can be accidentally removed.

Run VACUUM on each table. VACUUM is the Delta Lake command that physically removes data files and transaction log entries older than the retention duration. Importantly, VACUUM runs as a background operation — it uses Delta Lake's MVCC (multi-version concurrency control) to ensure that concurrent reads against the table continue uninterrupted while cleanup happens. Tables are fully available throughout.

OPTIMIZE compacts small files for query performance but doesn't delete anything. Manually deleting files outside the Delta protocol would corrupt the table.

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 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 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 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 B — No.

A Databricks Access Connector is an Azure resource that provides a managed identity for Databricks to authenticate to Azure Storage services. It is an authentication bridge — nothing more. Creating one doesn't register any external database, doesn't create any Unity Catalog objects representing DB1, and doesn't enable querying of SQL Server data from Databricks.

An Access Connector is a prerequisite for setting up storage credentials and external locations in Unity Catalog (to access ADLS Gen2, for example), but it has no role in Lakehouse Federation or foreign catalog creation. It cannot expose an external database's schemas and tables in Unity Catalog alongside native objects.

The correct solution for this scenario is a foreign catalog (Q47), which requires a connection object pointing to DB1 — not an Access Connector.

Attribute-based access control (ABAC) in Unity Catalog is implemented through row filters. A row filter is a SQL function registered on a table that evaluates the identity or group membership of the querying user and returns only the rows they're entitled to see.

The key functions are CURRENT_USER() (returns the logged-in user's email) and IS_ACCOUNT_GROUP_MEMBER() (returns true if the user belongs to a specified group). By building filter logic around these, you create access rules that are data-driven — a user in the 'EMEA' group sees EMEA rows, a user in 'APAC' sees APAC rows — without maintaining separate table-level grants per data segment.

This is what distinguishes ABAC from role-based access control: decisions are based on the user's attributes evaluated at query time, not on static grant lists. The filter is transparent to end users — they query the table normally and only receive rows the policy allows.

The correct answer is B — configure a checkpoint location.

A checkpoint is the Structured Streaming mechanism for fault tolerance. Databricks writes the committed offset (i.e., how far through the source stream the job has successfully read and processed) to a durable path in ADLS Gen2 or DBFS after each micro-batch. When the cluster restarts, the engine reads that offset and resumes from the next unprocessed record — nothing is reprocessed, nothing is skipped.

Option A (increase trigger interval) affects how frequently micro-batches run but does nothing to record progress between runs. Option C (watermark) handles late-arriving events in event-time windows but doesn't control source offset tracking. Option D (enable CDF on the target table) tracks changes made to a Delta table for downstream consumers — it has no bearing on the streaming job's own fault tolerance or offset management.

Checkpointing is a required configuration for any production streaming job. Without it, every cluster restart triggers a full replay from the source.