DP-750 Practice Questions

The INSERT INTO command is the standard SQL method for appending new rows to an existing table. In this scenario, it will take all rows from the sales.schema1.table1 (current year's data) and add them to sales.schema1.table2 (historical data). This action fulfills the core requirements of the task: it loads the new data while preserving all the existing historical data in the target table. It is the most direct and efficient command for this operation.

B. CREATE TABLE ... AS SELECT would fail because the target table, sales.schema1.table2, already exists. This command is used only for creating new tables.

C. INSERT OVERWRITE would first delete all existing historical data from sales.schema1.table2 before inserting the new data, violating the requirement to preserve existing data.

D. CREATE OR REPLACE TABLE would completely drop and recreate sales.schema1.table2, deleting all existing historical data and replacing it with only the data from table1.

1. Databricks SQL Language Reference

INSERT INTO: "Inserts new rows into a table. You can specify the values for each column or provide the result of a query." This confirms the append behavior.

Source: Microsoft

Azure Databricks Documentation. Section: INSERT INTO.

2. Databricks SQL Language Reference

INSERT OVERWRITE: "Replaces the data in a table with the new values... Any existing data in the table is deleted." This confirms the destructive nature of the command.

Source: Microsoft

Azure Databricks Documentation. Section: INSERT OVERWRITE DIRECTORY and INSERT OVERWRITE TABLE.

3. Databricks SQL Language Reference

CREATE TABLE: "If you specify a table that already exists

an error is thrown." This explains why option B is incorrect. The CREATE OR REPLACE TABLE variant is also described

which "replaces the definition of the table if it exists."

Source: Microsoft

Azure Databricks Documentation. Section: CREATE TABLE.

4. Delta Lake Documentation

Table batch reads and writes: "To add new data to an existing Delta table

use the append mode... INSERT INTO in SQL." This explicitly links the append requirement to the INSERT INTO command for Delta tables.

Source: Databricks Documentation

Delta Lake guide. Section: Table batch reads and writes > Append.

The question requires a modular and maintainable design for a data processing pipeline with distinct stages: ingestion, cleansing, and curation. Creating separate tasks for each stage aligns with best practices for building robust data engineering workflows. This approach adheres to the principle of separation of concerns, where each task has a single, well-defined responsibility. This modularity makes the overall job easier to develop, debug, and maintain. If the logic for one stage needs to be changed (as implied by "support the planned changes"), only the corresponding task needs to be updated, minimizing the risk of introducing errors into other parts of the pipeline.

A. A single notebook task creates a monolithic script, which is difficult to maintain, debug, and modify, directly contradicting the requirements for modularity and support for future changes.

B. Creating three tasks with identical logic is functionally incorrect for a multi-stage pipeline and does not perform the distinct steps of ingestion, cleansing, and curation.

C. A single SQL task, similar to a single notebook, is a monolithic approach that violates the requirement for each task to contain only the logic for its specific step.

1. Microsoft Learn

"Orchestrate data processing with Azure Databricks jobs": This official learning path for Databricks explains the structure of jobs. It states

"A job can consist of a single task

or it can be a large

complex workflow with many tasks and dependencies between them. In a multi-task job

Databricks manages the orchestration of the tasks

creating a directed acyclic graph (DAG) of the task dependencies." This directly supports creating separate

dependent tasks for a multi-stage workflow like the one described.

Source: Microsoft Learn

Module: "Orchestrate data processing with Azure Databricks jobs"

Unit: "Create and run jobs in Azure Databricks".

2. Databricks Documentation

"What is Azure Databricks Jobs?": The official documentation emphasizes the composition of jobs from one or more tasks. It describes how to "create a job that is a complex workflow with multiple tasks with dependencies." This architectural pattern is precisely what is needed to separate ingestion

cleansing

and curation into distinct

manageable units of work.

Source: Databricks Documentation

Section: "Jobs"

Article: "What is Azure Databricks Jobs?".

3. Databricks Documentation

"Create and run Databricks Jobs": This guide provides practical steps for building multi-task jobs

stating

"You can use task dependencies to specify the order of execution of tasks in your job." This feature is essential for implementing a sequential pipeline where the curation task runs only after the cleansing task succeeds

which in turn runs after ingestion.

Source: Databricks Documentation

Section: "Jobs"

Article: "Create and run Databricks Jobs"

Subsection: "Configure task dependencies".

The most effective way to meet the performance requirements for telemetry data ingestion and processing is to enable Photon acceleration. Photon is a high-performance, vectorized query engine native to Databricks that significantly speeds up SQL and DataFrame API workloads, which are typical for ETL and ingestion tasks. Configuring this on a job compute cluster is the best practice for automated, production pipelines, as it provides an isolated and cost-effective environment tailored specifically for the job's execution, aligning perfectly with the requirements.

B. Move the ingestion pipelines to shared compute.

Shared (all-purpose) compute is designed for interactive, ad-hoc analysis by multiple users, not for running automated production pipelines, which is less cost-effective and provides less isolation.

C. Increase an all-purpose cluster to a larger fixed node type.

While increasing node size can improve performance, using an all-purpose cluster for a production ingestion job is inefficient. Photon offers performance gains without necessarily increasing hardware costs.

D. Disable autoscaling for a job compute cluster.

Disabling autoscaling for a workload like telemetry, which can have variable volume, is counterproductive. It can lead to either over-provisioning and unnecessary costs or under-provisioning and performance bottlenecks.

---

1. Microsoft Learn

Azure Databricks Documentation

"What is Photon?".

Reference Section: Overview.

Content: "Photon is a high-performance Azure Databricks-native vectorized query engine that runs your SQL workloads and DataFrame API calls faster to reduce your total cost per workload... Photon is enabled by default in clusters running Databricks Runtime 9.1 LTS and above." This directly supports using Photon to accelerate ingestion workloads.

2. Microsoft Learn

Azure Databricks Documentation

"Use job compute to run your Azure Databricks jobs".

Reference Section: "Why use job compute?".

Content: "Job compute is dedicated to the job

providing workload isolation... Using job compute for your scheduled or triggered jobs is a best practice." This confirms that a job cluster is the correct environment

invalidating options B and C which suggest all-purpose/shared compute.

3. Microsoft Learn

Azure Databricks Documentation

"Cluster size and autoscaling".

Reference Section: "Autoscaling".

Content: "Autoscaling makes it easier to achieve high cluster utilization

because you don't need to provision the cluster to match a workload... This can significantly reduce your overall costs compared to a statically-sized cluster." This explains why disabling autoscaling (Option D) is generally not the recommended approach for efficiency.

The proposed solution is correct. Creating a foreign catalog is the specific mechanism within Databricks Lakehouse Federation designed to meet these requirements. A foreign catalog mirrors the metadata of an external database, such as schemas and tables from Microsoft SQL Server, and registers them in Unity Catalog. This allows users to query the data in place using standard Databricks interfaces and SQL commands. Queries are delegated to the source SQL Server database, which fulfills the critical requirement that data is not copied into Databricks-managed storage. The objects appear alongside native Unity Catalog objects, providing a unified governance and discovery experience.

B. No: This option is incorrect. The solution is valid because creating a foreign catalog is the precise and intended method provided by Databricks Lakehouse Federation to achieve the stated goals of querying external data through Unity Catalog without data migration.

1. Microsoft Azure Databricks Documentation

"What is Lakehouse Federation": This document introduces the concept. It states

"Lakehouse Federation is the query federation platform for Azure Databricks... enable users and systems to run queries against multiple siloed data sources without needing to migrate all data to a unified system." This directly supports the "data is NOT copied" requirement.

Section: Introduction.

2. Microsoft Azure Databricks Documentation

"Create a foreign catalog": This guide details the implementation. It specifies

"A foreign catalog is a Unity Catalog object that mirrors a database in an external data system... You can use Catalog Explorer to view the schemas and tables in the foreign catalog and to manage permissions on them

just as you do with regular catalogs." This confirms that the objects appear alongside other Unity Catalog objects and can be queried.

Section: "What is a foreign catalog?".

3. Microsoft Azure Databricks Documentation

"Data sources supported by Lakehouse Federation": This page lists Microsoft SQL Server as a supported data source for creating connections and foreign catalogs

confirming the technical feasibility of the proposed solution.

Section: Table of supported data sources.

Data-entry mistakes by the equipment manufacturers require a Type 1 Slowly Changing Dimension (SCD). SCD Type 1 directly overwrites the existing data with new values without retaining any history, which perfectly aligns with the requirement stating that historical values are not needed for correcting simple data entry errors. Conversely, changes to IoT sensor ownership require a Type 2 SCD. SCD Type 2 preserves historical data by creating a new version of the record (often tracking start and end dates) for each change. This satisfies the business need for analysts to track the complete ownership history of the sensors over time.

Microsoft Learn: Change data capture and snapshots - Azure Databricks

Reference: "SCD Type 1: Current state only. SCD Type 1 overwrites old data with new data whenever changes occur, keeping only the latest version of each record. History is not retained."

URL: https://learn.microsoft.com/en-us/azure/databricks/data-engineering/what-is-cdc

Microsoft Learn: Change data capture (CDC) in Copy Job - Microsoft Fabric

Reference: "SCD Type 2 preserves historical data by creating new rows for changes while keeping previous versions of records... [It is used for] Historical analysis, audit trails, compliance."

URL: https://learn.microsoft.com/en-us/fabric/data-factory/cdc-copy-job

To configure an external location in Unity Catalog using a managed identity, you must first create an Azure Databricks Access Connector, which automatically provisions an Azure managed identity. Next, to adhere to the principle of least privilege, you assign the "Storage Blob Data Contributor" role (rather than "Owner") to this access connector on the target storage account, granting it necessary read and write permissions without administrative rights. Finally, you register the access connector as a storage credential within the Unity Catalog metastore (metastore1), as Unity Catalog manages credentials at the metastore level rather than the workspace level.

Microsoft Learn: Connect to Azure Data Lake Storage Gen2 and Blob Storage: "Step 1: Create an Access Connector for Azure Databricks... Step 2: Grant the managed identity access to the storage account... assign the Storage Blob Data Contributor role... Step 3: Create a storage credential in Unity Catalog..." (Microsoft Documentation, Azure Databricks, Unity Catalog).

Microsoft Learn: Manage storage credentials: Explains that storage credentials are securable objects in Unity Catalog defined at the metastore level, not the workspace level (Microsoft Documentation, Azure Databricks, Data Governance).

Auto Loader is specifically designed for incremental, exactly-once processing of file-based data from cloud storage (like ADLS Gen2) and features built-in schema inference and evolution. This makes it the ideal choice for both the file-based telemetry data (Event Hub captures) and the incremental, drift-prone maintenance logs. Lakeflow Connect provides native, managed connectors to directly and reliably ingest structured data from enterprise systems, including PostgreSQL, directly into the Lakehouse. For the external weather data, using A Databricks notebook allows engineers to write custom Python code (e.g., using the requests library) to retrieve data from REST APIs. This approach integrates seamlessly with Databricks Workflows, satisfying the requirement for centralized orchestration without relying on external tools like Azure Data Factory.

Microsoft Learn - Azure Databricks Documentation: "What is Auto Loader?" Details Auto Loader's capability to incrementally process files in ADLS Gen2 and its native support for schema evolution (cloudFiles.schemaEvolutionMode), which matches the requirement for handling telemetry and maintenance log schema drift.

Reference: https://learn.microsoft.com/en-us/azure/databricks/ingestion/auto-loader/

Databricks Official Documentation: "Lakeflow Connect." Outlines the managed connectors available for enterprise databases, explicitly listing PostgreSQL as a supported source for native ingestion into Unity Catalog.

Reference: https://docs.databricks.com/en/ingestion/lakeflow-connect/index.html

Microsoft Learn - Azure Databricks Patterns: "Read data from REST APIs." Highlights the standard architectural pattern of utilizing Python within Databricks notebooks to call REST APIs, write results to cloud storage, and orchestrate the process via Databricks Workflows.

Reference: https://learn.microsoft.com/en-us/azure/databricks/ingestion/orchestration/rest-api

Capture: Unity Catalog automatically tracks runtime data lineage for notebooks and jobs, provided that they execute on a Unity Catalog-enabled cluster (a cluster configured with an appropriate Unity Catalog access mode running Databricks Runtime 11.3 LTS or above).

Review: To view and analyze the captured relationship graph and column-level dependencies with minimal administrative effort, users navigate to the table definitions inside the built-in Catalog Explorer UI and select the Lineage tab.

Microsoft Learn / Databricks Documentation: Lineage in Unity Catalog - Azure Databricks, specifically the sections on Compute requirements and View lineage in Catalog Explorer.

Microsoft Learn / Databricks Documentation: Unity Catalog requirements and limitations, specifically the section detailing Compute requirements and access modes.

Failure handling behavior: To prevent downstream tasks (like Task2) from running when a preceding task (like Task1) fails, you must block downstream execution. In Databricks Workflows, controlling the conditional flow or failure path ensures that dependent tasks do not execute upon upstream failure unless specifically configured otherwise (e.g., via specialized conditional run rules).

Retry configuration: Automatic retries for specific task execution issues must be configured explicitly on the task itself. Configuring a retry policy on Task1 ensures that if a transient error or failure occurs, Task1 will automatically retry the specified number of times before failing completely and triggering the downstream blocking behavior.

Official Vendor Documentation:

Microsoft Learn (Azure Databricks): "Configure and edit tasks in Lakeflow Jobs" — Advanced task settings section explains that to determine how many times failed task runs are retried, you must define a retry policy at the task level.

Microsoft Learn (Azure Databricks): "Control the flow of tasks within Lakeflow Jobs" — Outlines how retries specify how many times a particular task should be re-run on failure, and how to manage the execution dependencies of sequential/downstream tasks to prevent execution upon failure.

Set action for rule1 to (dlt.expect_or_drop): Delta Live Tables / Lakeflow expectations define data quality constraints. The dlt.expect_or_drop decorator drops rows that fail validation while continuing to process valid rows seamlessly. In contrast, dlt.expect keeps invalid records, and dlt.expect_or_fail aborts pipeline execution entirely.

View expectation metrics in (The Data quality tab of the pipeline): The built-in Databricks user interface provides a dedicated Data quality view/tab directly within the pipeline graph interface. This provides data engineers with immediate, ready-to-view charts and metrics summarizing passing and failing records per rule, fulfilling the requirement of using minimal development effort compared to querying system tables or setting up Azure Monitor.

Official Azure Databricks Documentation: Manage data quality with Delta Live Tables - Expectation Types

Section: Expect or drop

Detail: Explicitly specifies that expect_or_drop drops records violating the expectation and processes the remaining valid records into the target dataset.

Official Azure Databricks Documentation: Monitor Delta Live Tables pipelines

Section: Delta Live Tables user interface

Detail: Outlines that the pipeline UI provides a built-in data quality dashboard charting the exact proportion of records that pass or fail each expectation without requiring any SQL queries or custom dashboard construction.