What's the best way to build SQL ETL in a data warehouse?

Summary

• Build SQL ETL declaratively: write each target table as a query and let the engine handle dependency order, incremental processing, and retries. On Databricks, streaming tables ingest new data and materialized views hold transformations and aggregations.
• Run the whole flow from a Databricks SQL warehouse with CREATE STREAMING TABLE and CREATE MATERIALIZED VIEW (each backed by a serverless pipeline that Databricks manages), or in a Lakeflow Pipelines project where SQL and Python can be mixed.
• Land files with Auto Loader (read_files in SQL) or Lakeflow Connect managed connectors, organize bronze, silver, and gold layers, and enforce quality with expectations that warn, drop, or fail.
• Refresh on a schedule or when upstream data changes, orchestrate multi-step work with Lakeflow Jobs, and use Unity Catalog for lineage, access control, and audit logs.

What's the best way to build SQL ETL in a data warehouse?
The best way is declarative: write each target table as a SQL query and let the platform work out ordering, incremental refresh, and retries. On Databricks, a streaming table ingests and appends new data, a materialized view computes transformations and aggregations, and both run from a SQL warehouse or from a Lakeflow Pipelines project.

What makes SQL ETL effective in a data warehouse?

Capability	What to look for
Declarative definitions	Each table is a query, and the engine works out dependency order
Incremental processing	Only new or changed data is reprocessed where possible
Ingestion	Incremental, idempotent file loading plus change data capture from databases and SaaS sources
Data quality rules	Row-level checks with a choice to warn, drop, or fail
Change handling	Out-of-order change records and slowly changing dimensions are handled for you
Scheduling	Refresh on a schedule or when upstream data changes
Governance	Lineage, access control, and audit logs on every table

How does Databricks build SQL ETL?

Ingest. Databricks recommends streaming tables for most ingestion and Auto Loader for files in cloud storage, which you call in SQL with read_files. For databases and SaaS applications, use a Lakeflow Connect managed connector where one exists.

CREATE OR REFRESH STREAMING TABLE sales
  SCHEDULE EVERY 1 hour
  AS SELECT * FROM STREAM read_files(
    "/Volumes/my_catalog/my_schema/my_volume/path/to/data",
    format => "json"
  );

Transform. A materialized view caches query results and refreshes them on a schedule or, with TRIGGER ON UPDATE, when upstream data changes. Databricks tries to choose the cheapest update strategy and often computes incrementally.

CREATE OR REPLACE MATERIALIZED VIEW mv_trigger
  TRIGGER ON UPDATE
AS SELECT date, sum(sales) AS sum_of_sales
FROM base_table1
GROUP BY date;

Check quality. Add CONSTRAINT name EXPECT (condition) to either statement, with ON VIOLATION DROP ROW or ON VIOLATION FAIL UPDATE. Without ON VIOLATION, violating rows are kept and counted.
Apply changes. For change data capture feeds, use AUTO CDC, which handles out-of-order records and supports SCD Type 1 and Type 2. The docs warn that MERGE INTO can produce incorrect results with out-of-sequence records.
Orchestrate. In a Lakeflow Pipelines project, the pipeline runs flows in dependency order with maximum parallelism and retries transient failures progressively, from the Spark task to the flow to the whole pipeline. Serverless is the default for new pipelines. For multi-step work, a Lakeflow Jobs SQL task runs a query, alert, or .sql file on a serverless or pro SQL warehouse.
Govern. Pipeline tables are Delta tables in Unity Catalog, which captures lineage automatically down to the column level. Pipelines also run predictive optimization maintenance, including OPTIMIZE and VACUUM.

How should I structure the layers?

Use the medallion pattern. Bronze holds raw ingestion, silver holds cleaned and validated data, and gold holds dimensional models and aggregations. Databricks calls it a recommended best practice, not a requirement. A typical flow lands raw orders in a bronze streaming table, cleans and validates them in silver, and builds daily totals in a gold materialized view.

FAQs

Can I build SQL ETL without a separate orchestration tool?

Often, yes. Streaming tables and materialized views refresh on their own schedule or when upstream data changes. Add Lakeflow Jobs when you need dependencies across tasks, such as running a SQL query, alert, or file after a refresh.

Do I need Python for complex transformations?

Not for the transformations themselves, which you express as SQL queries. When a source has no SQL support for ingestion, you can mix SQL and Python sources in the same Lakeflow Pipelines pipeline.

What happens when upstream data changes schema?

Pipeline tables are Delta tables, which enforce schema. For files, Auto Loader detects new columns. In its default addNewColumns mode the stream stops with an UnknownFieldException after adding them to the schema, and a restart resumes with the new schema, so Databricks recommends restarting automatically with Lakeflow Jobs. The rescue mode records new columns in _rescued_data instead.

Is the compute serverless?

Standalone streaming tables and materialized views run on serverless pipelines that Databricks creates and manages. For Lakeflow Pipelines, Databricks recommends serverless for new pipelines.

How do teams share results without copying data?

Grant SELECT on the Unity Catalog tables. Privileges inherit downward, so a grant on a schema or catalog covers its tables, and activity is recorded in the audit log system table.