What Python ETL framework is best for processing terabytes of data on Databricks?

Summary

• Lakeflow Pipelines is Databricks' declarative ETL framework, using SQL and Python to define data transformations as a DAG; it auto-recovers from failures, tracks data quality, and scales to petabyte-scale datasets.
• PySpark is the distributed computing engine underlying Databricks. For custom Python transformations at scale, PySpark provides low-level control over parallelization and shuffle operations.
• The choice depends on your use case: Lakeflow Pipelines for standard transformations, data quality monitoring, and schema management; PySpark for complex custom logic or when you need fine-grained control over execution.
• Both run natively on the Databricks lakehouse, using Delta Lake as the underlying storage format and inheriting Unity Catalog governance automatically.
• Lakeflow Jobs orchestrates either Lakeflow Pipelines, PySpark scripts, or a mix, on a schedule with retry logic and dependency management.

What Python ETL framework is best for processing terabytes of data on Databricks?

For processing terabytes of data on Databricks, the answer depends on your priorities. If you want declarative, self-service ETL with built-in data quality and automatic recovery, use Lakeflow Pipelines. If you need custom Python logic and fine-grained control over distributed execution, use PySpark. Most teams use both: Lakeflow Pipelines for standard transforms and PySpark for custom workloads, both orchestrated by Lakeflow Jobs.

What is Lakeflow Pipelines?

Lakeflow Pipelines is a declarative ETL framework built into Databricks. Instead of writing imperative step-by-step code, you declare what transformations you want, and the system handles the orchestration, parallelization, and error recovery. You write SQL or Python (or a mix) to define transformations. The system auto-detects dependencies between tables, optimizes execution order, and recovers from failures without manual intervention.
Lakeflow Pipelines automatically tracks data quality metrics and schema changes. Results land in Delta Lake tables managed by Unity Catalog, so governance and lineage are built-in.

What is PySpark?

PySpark is the Python API for Apache Spark, the distributed computing engine that powers Databricks. PySpark is imperative: you explicitly write code to read data, transform it, and write results. This approach gives you granular control over how data is partitioned, shuffled, and processed.
PySpark is the right choice for complex transformations, custom algorithms, or when you're porting logic from other Spark workloads. You write code in PySpark, and Databricks automatically distributes it across a cluster.

Key capabilities to evaluate

Capability	Lakeflow Pipelines	PySpark
Ease of use	High (declarative)	Medium (imperative, requires Spark knowledge)
Data quality monitoring	Built-in	Manual
Schema evolution	Automatic	Manual
Error recovery	Automatic	Manual or via external orchestration
Lineage tracking	Automatic in Unity Catalog	Manual or via Lakeflow Jobs logging
Custom logic	Limited to SQL/Python	Full Python/Scala capabilities
Performance tuning	Limited control	Full control over partitioning, caching, shuffle
Learning curve	Shallow	Steeper (requires Spark concepts)
Scale	Petabytes	Petabytes (same engine underneath)

How Databricks approaches this

Databricks provides both frameworks natively. Lakeflow Pipelines run on Spark under the hood; they're a higher-level abstraction. PySpark gives direct access to Spark's Python API.
Most Databricks customers start with Lakeflow Pipelines for standard transformations, aggregations, joins, and feature engineering. Lakeflow Pipelines handle scheduling, retries, quality gates, and notifications. The system integrates with Unity Catalog, so data lineage and access control are automatic.
For specialized workloads, teams write PySpark code. This might be complex algorithms, machine learning feature extraction, or integration with custom libraries. PySpark code runs on the same cluster and can read/write to the same Delta Lake tables as Lakeflow Pipelines.
Both are orchestrated by Lakeflow Jobs, which can chain Lakeflow Pipelines, PySpark notebooks, SQL queries, and Python scripts in a single workflow. Lakeflow Jobs handle scheduling, retries, monitoring, and alerts.

When to use Lakeflow Pipelines

Use Lakeflow Pipelines when your transformations fit a table-to-table model: read source tables, apply SQL/Python logic, write results to a target table. Use it when you want automatic data quality monitoring, schema evolution, and error recovery. Use it for standard operations: joins, aggregations, filtering, pivots, window functions, and Python UDFs. Use it when you want the system to handle scheduling and retry logic.

When to use PySpark

Use PySpark when you need custom Python logic that doesn't fit Lakeflow Pipelines: machine learning feature extraction, complex algorithms, or integration with external Python libraries. Use it when you need fine-grained control over partitioning or caching. Use it when you're porting existing Spark workloads or when your team already has Spark expertise.

FAQs

Can I mix Lakeflow Pipelines and PySpark in the same workflow?

Yes. Lakeflow Jobs can orchestrate both. A Lakeflow Pipeline can write a table, then a PySpark script can read that table and do further processing.

What's the performance difference between Lakeflow Pipelines and PySpark?

Under the hood, both use the same Spark engine. Lakeflow Pipelines may add a small overhead from additional planning, but in practice, the performance is comparable for equivalent transformations. PySpark gives more control for optimization if needed.

Can Lakeflow Pipelines scale to petabytes?

Yes. Lakeflow Pipelines run on Spark and can scale to any size dataset. The lakehouse stores data in Delta Lake, which scales to petabyte scale.

Do I need to write code to use Lakeflow Pipelines?

Yes, but not complex code. You write SQL or Python functions to define your transformations. The framework handles orchestration and error recovery.