1: Developing Code for Data Processing using Python and SQL

Learning Objectives

By the end of this chapter, you will be able to:

• Author Python and SQL code in the patterns the platform expects, including APPLY CHANGES INTO for CDC inside Lakeflow Spark Declarative Pipelines.
• Design a scalable Python project layout for Databricks Asset Bundles, and decide between Spark Structured Streaming and Lakeflow Spark Declarative Pipelines for any ETL scenario.
• Install and troubleshoot third-party libraries from PyPI, local wheels, and source archives, and compose pipeline components with control-flow operators.
• Develop Pandas and Python UDFs and pick the right environment, dependency, memory, and retry configuration for each notebook task.
• Build and test ETL pipelines with Lakeflow Spark Declarative Pipelines, including unit and integration tests using assertDataFrameEqual, assertSchemaEqual, DataFrame.transform, and the built-in pipeline debugger.
• Operate reliable batch and streaming pipelines that combine Lakeflow Spark Declarative Pipelines with Auto Loader.
• Create and automate ETL workloads using Lakeflow Jobs through the UI, REST API, and CLI.
• Choose between streaming tables and materialized views with full awareness of the cost, freshness, and recompute implications.

Executive Summary

• Lakeflow Spark Declarative Pipelines is the canonical declarative engine for ETL on the Lakehouse, and its APPLY CHANGES INTO API is the simplest path to SCD Type 1 and Type 2 CDC against an upstream change feed.
• The shape of a production Python project is dictated by Databricks Asset Bundles: a databricks.yml at the root, a src/ package of unit-testable transformations, a resources/ folder of declarative job and pipeline manifests, and a tests/ tree consumed by the CI pipeline that runs databricks bundle validate and databricks bundle deploy --target prod.
• The choice between Spark Structured Streaming and Lakeflow Spark Declarative Pipelines comes down to the level of operational control the team needs versus the operational burden they want to outsource: Structured Streaming gives bare-metal control over triggers, watermarks, checkpoints, and sinks, while Lakeflow Spark Declarative Pipelines internalises checkpoint management, dependency resolution, and lineage at the cost of some surface flexibility.
• Streaming tables process new rows incrementally and grow append-style; materialized views recompute their result from source on each refresh. Reaching for the wrong shape inflates either latency or cost, and the exam tests this trade-off directly.

Assumptions

• All examples assume Unity Catalog is the active metastore, the legacy Hive Metastore is treated as out of scope, and the workspace runs against the latest Databricks Runtime supported by the Lakeflow SDP CURRENT channel.
• Code uses three-level names (catalog.schema.table) throughout. Pipeline source files are organised under src/ and configured through databricks.yml.
• Where this chapter still uses the abbreviation DLT in Python decorators (@dlt.table, @dlt.expect), this is the in-source SDK surface, not the product name. The product name is Lakeflow Spark Declarative Pipelines.