Best SQL ETL tool

Summary

• A good SQL ETL tool lets you define each dataset as a SQL query, then handles execution order, incremental processing, data quality checks, scheduling and monitoring. On Databricks that means SQL in Lakeflow Pipelines, built from streaming tables and materialized views.
• Judge any tool on eight things: dependency handling, incremental processing, schema changes, data quality, change data capture, scheduling, observability and governance.
• In Lakeflow Pipelines the pipeline analyzes the dependencies between your datasets and orchestrates their order of execution and parallelization automatically, so you write queries instead of a task graph.
• Expectations (CONSTRAINT ... EXPECT) keep invalid records by default, and can also drop rows or fail the update. The results land in the pipeline event log.
• Run a pipeline from a Lakeflow Jobs pipeline task, or schedule standalone streaming tables and materialized views in Databricks SQL with a SCHEDULE clause. Existing dbt projects run as dbt tasks.

Best SQL ETL tool

The best SQL ETL tool is one where a SQL query is enough to define a dataset and the platform handles ordering, incremental refresh, quality checks, scheduling and monitoring. On Databricks, that is SQL in Lakeflow Pipelines. You declare streaming tables and materialized views, and the pipeline works out how to run them.

What is SQL ETL?

Databricks defines ETL as "the process data engineers use to extract data from different sources, transform the data into a usable and trusted resource, and load that data into the systems end-users can access and use downstream to solve business problems." ELT changes the order: "data is loaded as soon as it is extracted, without being transformed first." SQL ETL means the transform step is written in SQL. A SQL ETL tool adds what a folder of SQL scripts lacks: execution order, incremental processing, tests, scheduling and run history.

What should a SQL ETL tool do?

Capability	What to check
Dependency handling	Does the tool infer execution order from your queries, or do you wire a graph by hand?
Incremental processing	Does it process only new or changed data, and can you see which path it took?
Schema changes	What happens when a source adds a column: fail, evolve or rescue?
Data quality	Can you declare checks beside the dataset and choose warn, drop or fail?
Change data capture	Can it keep history (SCD type 2) as well as current state (SCD type 1)?
Scheduling	Does it support schedules, event triggers and continuous runs?
Observability	Do you get lineage, run history, an event log and alerts?
Governance	Do permissions and lineage live in the same catalog as the data?

How does Databricks approach SQL ETL?

Lakeflow Pipelines provides "a declarative framework for building batch and streaming data pipelines in SQL and Python." A streaming table is "a form of Unity Catalog managed table that is also a streaming target," and a materialized view is a batch target. You write CREATE OR REFRESH statements, and while the pipeline runs it "analyzes the dependencies of your defined objects and orchestrates their order of execution" and parallelization automatically. This pair, adapted from the Databricks tutorial, cleans a raw table (songs_raw, a streaming table) and then aggregates it:

CREATE OR REFRESH MATERIALIZED VIEW songs_prepared
(CONSTRAINT valid_artist_name EXPECT (artist_name IS NOT NULL),
CONSTRAINT valid_title EXPECT (song_title IS NOT NULL),
CONSTRAINT valid_duration EXPECT (duration > 0))
AS SELECT artist_id, artist_name, duration, release, tempo, time_signature, title AS song_title, year
FROM songs_raw;

CREATE OR REFRESH MATERIALIZED VIEW top_artists_by_year
AS SELECT artist_name, year, COUNT(*) AS total_number_of_songs
FROM songs_prepared
WHERE year > 0
GROUP BY artist_name, year
ORDER BY total_number_of_songs DESC, year DESC;

• Data quality: an expectation with no action keeps invalid records and records metrics. ON VIOLATION DROP ROW drops them, and ON VIOLATION FAIL UPDATE stops the update. Query the pipeline event log for the metrics.
• Incremental processing: a streaming table refresh "only evaluates new rows that have arrived after the last update, and appends only the new data." For a materialized view, Databricks chooses a full or incremental refresh based on cost and on whether the query supports incremental refresh. "Only materialized views updated using serverless pipelines can use incremental refresh."
• Change data capture: AUTO CDC INTO handles change data capture and supports SCD type 1 and type 2 (STORED AS SCD TYPE 2).
• Scheduling: add a pipeline task to a Lakeflow Jobs job, or schedule a standalone streaming table with SCHEDULE EVERY 1 hour. A pipeline runs in triggered or continuous mode.
• Monitoring and lineage: the pipeline UI shows the graph, per-table row counts, data quality metrics and update history. Unity Catalog captures lineage "down to the column level."

FAQs

Should I write SQL or Python?

Databricks' guidance is: "If you can express your logic in SQL, use SQL. If you need programmatic control or a Python-only feature, use Python." A single pipeline can combine both, but each language must be in a separate source file.

Do I need dbt?

No. Lakeflow Pipelines is its own declarative framework in SQL and Python. If you already have a dbt project, the dbt task in Lakeflow Jobs configures and runs it on Databricks using the dbt-databricks package.

What if my data lives in other systems?

Use Lakeflow Connect. Its managed connectors produce ingestion pipelines "governed by Unity Catalog and powered by serverless compute and Lakeflow pipelines". Connectors are in various release states, so check each one before you depend on it.

How do I test before production?

Updates for non-continuous pipelines started from the pipeline editor use fast-start, debugging-focused behavior: a reused cluster and no pipeline retries, so errors surface immediately. Updates started from Jobs use automatic retry and restart behavior. You can also reprocess only selected tables while you iterate.

What happens when a source adds a column?

With Auto Loader, the default addNewColumns mode stops the stream with an UnknownFieldException when it detects a new column. The other modes are addNewColumnsWithTypeWidening, rescue, failOnNewColumns and none.