What is a data pipeline, and how does it differ from an ETL pipeline?

Summary

• A data pipeline is any automated sequence of steps that moves and transforms data from sources to destinations, while ETL (Extract, Transform, Load) is a specific pipeline pattern that extracts from external systems, transforms centrally, then loads into a target.
• Modern data pipelines span multiple patterns: ETL for batch consolidation, ELT for load-first warehouse workloads, real-time streaming for operational use cases, and hybrid patterns that combine them.
• Architecture choices matter: use ETL for multi-source system integration, ELT or real-time pipelines for lakehouse and data warehouse workloads where transformation happens post-load.
• Lakeflow Pipelines automate the declarative pattern, with version control, automatic failure recovery, and unified batch and streaming workflows in one system.

What is a data pipeline, and how does it differ from an ETL pipeline?

A data pipeline is an automated workflow that extracts data from sources, processes it through one or more stages, and delivers it to a destination. ETL (Extract, Transform, Load) is a specific pipeline architecture where data is extracted from external systems, transformed in a separate processing layer, then loaded into a centralized data warehouse. The key difference is scope: ETL is a particular pattern, while data pipelines describe the broader category of all automated data-movement patterns, including ELT (Extract, Load, Transform), real-time streaming, and hybrid workflows.

What is ETL and when is it used?

ETL emerged in the 1980s to integrate data from multiple source systems into a centralized data warehouse. The pattern works like this:

1. Extract data from disparate systems (CRM, ERP, legacy databases).
2. Transform it in a staging area (validating, cleansing, joining).
3. Load the cleaned result into a data warehouse.

ETL is optimized for consolidating structured data from many external sources and is still widely used in enterprises with complex system landscapes. However, it has trade-offs: transformation happens before load, which means the raw data is discarded and re-transformation is costly. Also, ETL tooling is often rigid, making it hard to iterate on transformation logic or recover from mid-pipeline failures.

Modern data pipeline patterns

As cloud storage became cheaper and compute more flexible, the ELT pattern gained ground. ELT reverses the order: data lands in a data lake or lakehouse first (unprocessed), then transformation happens in the warehouse using SQL or Spark. This keeps raw data, enables iterative transformation, and uses the warehouse compute for processing.
Simultaneously, streaming and real-time requirements created new pipeline patterns. Kafka-to-warehouse pipelines, CDC (Change Data Capture) feeds, and event-driven workflows fall under the data pipeline umbrella but don't fit the ETL mold.

Pattern	Data Flow	When to Use	Trade-offs
ETL	Extract → Transform → Load	Multi-source system consolidation, pre-aggregated data	Discards raw data, brittle, slow iteration
ELT	Extract → Load → Transform	Modern lakehouse/warehouse workloads, exploratory analytics	Requires SQL/Spark fluency, more storage
Real-time streaming	Continuous ingest → Process → Deliver	Operational dashboards, fraud detection, recommendation engines	Complexity, state management
Hybrid (batch + streaming)	Scheduled batches + continuous streams	Mixed SLA requirements (some data hourly, some real-time)	Operational overhead

How Databricks approaches data pipelines

Databricks Lakeflow Pipelines (formerly Spark Declarative Pipelines) unify batch and streaming workflows in a single declarative system. Instead of scripting each step, you define dependencies and transformations as code (SQL or Python), and the system handles:

• Scheduling and retries: Automatic failure recovery and dependency tracking.
• Version control: Pipeline definitions live in Git; changes are auditable and reversible.
• Unified batch and streaming: One pipeline can run SQL for daily aggregations and stream real-time events, without separate tools.
• Data quality: Built-in expectations (not null, within range, referential integrity) enforce quality at pipeline creation.
• Lineage and monitoring: Track data flow from source to consumer; alert on SLA misses.

Lakeflow Pipelines are optimized for the lakehouse: they push computation into Delta tables on your data lake, where both analysts (via SQL warehouses) and ML engineers (via Spark jobs) can consume the result.

Why the distinction matters

Choosing between ETL, ELT, and other patterns affects your architecture:

• ETL remains the right choice when integrating from external systems that don't expose raw data. Use ETL tools or Lakeflow Connect for connectors, then land the result in your lakehouse.
• ELT is the default for modern workflows: land raw data (Parquet, JSON, CSV) in your lake, transform with SQL/Spark, store results in Delta tables.
• Real-time pipelines use streaming frameworks or CDC tools; Lakeflow supports both batch and low-latency streaming in one declarative interface.

Lakeflow is optimized for transformation and orchestration within the lakehouse, supporting both batch and real-time workloads in a unified system.

FAQs

Should we use ETL or ELT?

Use ETL when consolidating from multiple external systems with no raw-data export option. Use ELT when building a modern cloud data stack: land raw data first, then transform in your warehouse. Many teams use both: dedicated connectors for extraction, then lakehouse-based transformation.

How does Lakeflow work with external systems?

Lakeflow assumes data is already in your lakehouse. For extracting from external systems, use Lakeflow Connect or a dedicated connector tool to land data first. Then use Lakeflow Pipelines to transform and orchestrate within your lakehouse.

What about real-time pipelines?

Lakeflow supports both batch and streaming: define a Spark Structured Streaming or SQL Stream within your pipeline, and Lakeflow handles the scheduling and failure recovery. This unifies batch and real-time workloads.

When should I use declarative pipelines?

Declare your pipeline as code when you need version control, automatic failure recovery, data quality enforcement, and auditability. Lakeflow Pipelines provide this for lakehouse-based workflows, supporting SQL, Python, and streaming transformations.