Using Python and Tools for development

The Databricks Data Engineer Professional exam evaluates how fluently an engineer combines Python and SQL inside the workspace tooling that production teams actually ship through. The development surface is not a single notebook; it is a layered stack of authoring environments (notebooks, the file editor, local IDE through Databricks Connect), execution surfaces (all-purpose compute, job compute, SQL warehouses, serverless Lakeflow SDP), and a packaging system (Databricks Asset Bundles) that binds source files to deployable jobs and pipelines. The engineer's job is to know which surface to reach for in any given situation, because the same line of PySpark can run in a notebook against an interactive cluster, inside a Lakeflow Jobs task on job compute, inside a @dlt.table decorated function on serverless Lakeflow SDP, or against a remote workspace through databricks-connect. Each surface has different cost, isolation, debugger, and dependency semantics, and the platform expects the engineer to make those choices consciously rather than defaulting to whatever ran during prototyping.

Python on Databricks is rarely standalone Python. The platform sits on the PySpark API plus an enriched layer that adds dbutils, mlflow, pyspark.dbutils, dlt (the SDK for Lakeflow SDP), and the databricks-sdk Python client. Engineers should write production code as importable modules, not as notebook scripts that depend on cell ordering, because Asset Bundles deploy .py source files and Lakeflow Jobs tasks support a python_wheel_task and a python_file_task that execute modules with no notebook in the loop. SQL plays the symmetric role: Spark SQL is the native dialect for declarative transformations, materialized views, streaming tables, and the APPLY CHANGES INTO API. Mixing the two languages inside a single Lakeflow SDP pipeline is supported and idiomatic, because each @dlt.table Python function and each CREATE STREAMING TABLE SQL statement is just a node in the same dependency graph the engine compiles.

The platform exposes a built-in pipeline debugger for Lakeflow SDP authoring. When a Python source file is opened inside the workspace editor and the engineer hits Debug on a @dlt.table definition, the debugger materialises a temporary instance of the table in the assigned schema, exposes a row-level inspection panel, lets the engineer set conditional breakpoints inside the function body, and reports the upstream lineage the engine resolved. This is the recommended path for iterating on a transformation before promoting the file to the pipeline's source list. Engineers who used to copy dlt code into a notebook with mocked input data should drop the habit, because the debugger evaluates the function against the same compute and metastore the production pipeline targets, which makes the cycle both faster and more representative.

A distinctive aspect of the Databricks development model is the relationship between code and compute. Notebooks and source files are deployable artefacts that live in a Git folder, in a Workspace folder, or inside a Bundle deployment. Compute is a separate resource that may be allocated per task (job compute, the cost-optimal choice for scheduled batch), shared across users (all-purpose, expensive but interactive), or fully managed by the engine (serverless SQL warehouses, serverless job compute, and serverless Lakeflow SDP). The exam frequently presents a scenario where a team has been running scheduled ETL on an all-purpose cluster to give analysts a place to drop in and query the data; the correct response is to split the workload, route the scheduled batch onto job compute, and let analysts hit the data through a SQL warehouse against the persisted Delta tables. The shared cluster is the wrong shape for either party.

Using APPLY CHANGES APIs to simplify CDC in Lakeflow Spark Declarative Pipelines

Change data capture is the canonical use case where Lakeflow Spark Declarative Pipelines provides the largest productivity gain over hand-written PySpark. The APPLY CHANGES INTO API takes a source streaming table that holds CDC events (insert, update, delete) and applies them to a target streaming table in either SCD Type 1 (overwrite the latest state) or SCD Type 2 (preserve history with __START_AT and __END_AT columns). The engine handles the deduplication on the natural key, applies the sequence column to order events, prunes irrelevant columns, and writes the result to a Delta table in the pipeline's target schema. The same effect implemented through bare PySpark requires a MERGE INTO with a windowed deduplication step, a separate streaming join, manual handling of out-of-order events, and explicit checkpoint plumbing. The declarative form is shorter, audited by the event log, and replayable on full-refresh.

-- SQL form of APPLY CHANGES INTO with SCD Type 1
CREATE OR REFRESH STREAMING TABLE customers;

APPLY CHANGES INTO live.customers
FROM stream(live.customers_cdc_raw)
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'
APPLY AS TRUNCATE WHEN operation = 'TRUNCATE'
SEQUENCE BY change_seq
COLUMNS * EXCEPT (operation, change_seq)
STORED AS SCD TYPE 1;

# Python form of dlt.apply_changes() with SCD Type 2
import dlt
from pyspark.sql.functions import col

dlt.create_streaming_table("customer_history")

dlt.apply_changes(
 target="customer_history",
 source="customers_cdc_raw",
 keys=["customer_id"],
 sequence_by=col("change_seq"),
 apply_as_deletes="operation = 'DELETE'",
 except_column_list=["operation", "change_seq"],
 stored_as_scd_type=2,
)

The sequence_by column is the single most important argument and the most common source of incorrect output. The engine uses it to order events that may arrive out of order from the upstream CDC source. If sequence_by is null for a row, the engine treats that event as the latest, which is the textbook cause of a phantom "latest" state in the target. Engineers should make this column non-nullable in the upstream pipeline; if the source genuinely has nulls, coalesce them to a sentinel value that sorts before any real sequence number.

💡 Exam Trap: Reaching for MERGE INTO inside a @dlt.table function to implement CDC is the wrong shape. Streaming-source MERGE INTO is supported in classic Lakeflow Jobs, but inside a Lakeflow Spark Declarative Pipeline the engine offers APPLY CHANGES INTO precisely so the developer never writes a merge by hand. Picking MERGE INTO from a list of options means the exam is testing whether the engineer knows the SDP-native path.

💡 Exam Trap: SCD Type 2 in APPLY CHANGES emits __START_AT and __END_AT columns automatically. Trying to declare these columns yourself in the target streaming table schema causes a column conflict at materialisation time. Let the engine own the history columns.

⚠️ Anti-Pattern: Authoring CDC pipelines as ordinary notebook code attached to an interactive all-purpose cluster, then scheduling the notebook through a one-task Lakeflow Job. This skips the event log, skips the dependency resolver, and forces every recovery to be a manual checkpoint reset. The pattern survives on small teams but breaks at scale. The corrective is to move the same logic into a dlt.apply_changes() call inside a Lakeflow SDP source file and let the engine own the orchestration.

🎯 Scenario: An upstream OLTP system emits CDC events to a Kafka topic. Events occasionally arrive out of order because of retry logic in the producer. The data engineer needs a target table that reflects the current state of every customer and that recovers correctly on full refresh. The right answer is a Lakeflow SDP pipeline with a streaming table that reads from Kafka (or Auto Loader against an object-storage landing zone if Kafka is unavailable), followed by APPLY CHANGES INTO target FROM source KEYS (customer_id) SEQUENCE BY change_seq STORED AS SCD TYPE 1. The engine deduplicates on the key, orders by the sequence column, and writes deterministically.

📊 Limit: A single APPLY CHANGES INTO statement processes one source-to-target mapping per call. SCD Type 2 tables add __START_AT / __END_AT metadata columns per row; the engine emits exactly one open row (__END_AT IS NULL) per natural key and the historical row count per key is unbounded — plan storage and VACUUM retention against the change frequency of the hottest keys.

Decision Anchor: Choose APPLY CHANGES INTO when the pipeline lives inside Lakeflow Spark Declarative Pipelines and the source emits a CDC stream with a natural key and a sequence column. Choose a bare MERGE INTO inside a classic Lakeflow Job when the team is not yet on Lakeflow SDP, the source is a static snapshot, or the merge logic includes complex multi-table joins the declarative API does not express.