Featured

SQL-only DLT pipelines in Databricks: what the docs don’t tell you

6 min readJul 1, 2026

A practitioner’s guide to building production-grade medallion pipelines — without writing a single line of transformation Python. Yes, really.

The problem nobody talks about

Open any Databricks DLT tutorial. What do you see? Python. Python everywhere. @dlt.table, @dlt.view, @dlt.expect — as if the only people allowed to build data pipelines are those who spent their weekends debugging list comprehensions.

The SQL path exists. It is fully supported. Databricks just buried it like a secret menu item nobody asked you about.

This matters because a huge chunk of data engineering teams are SQL-first. Analytics engineers, BI developers, data modellers — people who can write a window function in their sleep but who eye Python with the same suspicion they reserve for NFTs. Forcing them into decorators adds friction where there is none needed.

Get Smit Shilu’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This article is the guide that should have existed. Complete production pipelines, medallion architecture, data quality, deduplication — all in SQL.

Scope check: SQL DLT is a fully capable alternative to Python DLT — not a simplified version of it. Once you understand three table types and when to use each, the rest is just SQL you already know.

What SQL DLT can (and cannot) do

Before you throw away your Python environment (don’t, actually), here’s where SQL DLT draws the line:

The three table types you need to know

This is the most important decision you will make. Get this wrong and you’ll end up with expensive full recomputes where you expected incremental processing. Get it right and DLT basically runs itself.

The medallion architecture in SQL DLT

The classic bronze → silver → gold pattern maps neatly onto the three table types. Here’s what that looks like end-to-end:

Bronze layer: raw ingestion with Autoloader

The bronze layer’s job is simple: get data off cloud storage and into Delta, fast, with as little transformation as possible. Autoloader handles the hard parts (schema inference, new-file tracking, schema evolution). You just write the SQL.

Naming convention tip: Use the __tbl_ prefix (double underscore) for internal/intermediate tables. Consumers in Unity Catalog will know to look at mv_ tables, not at your raw bronze layer.

CREATE OR REFRESH STREAMING TABLE __tbl_raw_transactions
COMMENT "Raw transaction records from source system export"
TBLPROPERTIES (
  "delta.appendOnly" = "true",
  "pipelines.reset.allowed" = "false"  -- 🛑 Critical: prevents accidental wipes
)
AS SELECT
  *,
  CURRENT_TIMESTAMP() AS __record_created_time,
  input_file_name()   AS __source_file
FROM cloud_files(
  "s3://my-company-data-bucket/transactions/incoming/",
  "json",
  map(
    "cloudFiles.inferSchema",          "true",
    "cloudFiles.schemaLocation",       "dbfs:/checkpoints/bronze/transactions/schema",
    "cloudFiles.schemaEvolutionMode",  "addNewColumns",
    "cloudFiles.includeExistingFiles", "true"
  )
);

Don’t skip pipelines.reset.allowed = "false". Without it, an accidental pipeline reset in the UI will reprocess all your historical files from scratch. On a large dataset, that's hours of compute and a very awkward conversation with your cloud bill.

Silver layer: typing + deduplication

Raw data is a mess. The silver layer does two things and only two things: enforce correct types, and remove duplicates. The canonical SQL DLT pattern splits these into two steps deliberately.

-- Step 1: type casting (streaming — incremental)
CREATE OR REFRESH STREAMING TABLE __tbl_transactions_typed AS
SELECT
  CAST(transaction_id AS STRING)    AS transaction_id,
  CAST(unit_price AS DECIMAL(10,2)) AS unit_price,
  CAST(transaction_date AS DATE)    AS transaction_date,
  UPPER(TRIM(currency_code))        AS currency_code,
  __record_created_time
FROM STREAM(LIVE.__tbl_raw_transactions)
WHERE transaction_id IS NOT NULL;

-- Step 2: deduplication (Apply Changes / CDC pattern)
-- First, define the target table
CREATE OR REFRESH STREAMING TABLE mv_transactions;

-- Next, apply the changes incrementally
APPLY CHANGES INTO LIVE.mv_transactions
FROM STREAM(LIVE.__tbl_transactions_typed)
KEYS (transaction_id)
SEQUENCE BY __record_created_time;

Data quality with EXPECT clauses

This is one of the most underused features in DLT. You get built-in data quality monitoring for free — no extra instrumentation, no custom logging. Just add constraints to your CREATE statement.

 CREATE OR REFRESH MATERIALIZED VIEW mv_customers
CONSTRAINTS (
  CONSTRAINT valid_email  EXPECT (email LIKE '%@%.%'),
  CONSTRAINT valid_region EXPECT (region IN ('North', 'South', 'East', 'West')),
  CONSTRAINT non_null_id  EXPECT (customer_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT valid_status EXPECT (status IN ('active', 'inactive', 'suspended'))
)
AS SELECT
  customer_id,
  TRIM(email)         AS email,
  UPPER(TRIM(region)) AS region,
  LOWER(TRIM(status)) AS status,
  created_at
FROM LIVE.__tbl_raw_customers;

Gold layer: where SQL DLT really shines

Complex multi-source joins, business aggregations, rolling window calculations — this is standard SQL territory, and SQL DLT handles all of it. No Python needed, no special syntax. Just SQL that any analytics engineer can read and maintain.

-- Daily sales by region + product category (BI-facing)
CREATE OR REFRESH MATERIALIZED VIEW mv_daily_sales_summary AS
SELECT
  t.transaction_date,
  c.region,
  p.category                       AS product_category,
  COUNT(DISTINCT t.transaction_id) AS transaction_count,
  COUNT(DISTINCT t.customer_id)    AS unique_customers,
  SUM(t.quantity * t.unit_price)   AS gross_revenue,
  AVG(t.quantity * t.unit_price)   AS avg_basket_size
FROM LIVE.mv_transactions t
JOIN LIVE.mv_customers c ON t.customer_id = c.customer_id
JOIN LIVE.mv_products  p ON t.product_id  = p.product_id
GROUP BY t.transaction_date, c.region, p.category;

Common pitfalls (and how to sidestep them)

1. Using SELECT * without pinning your schema

cloudFiles.inferSchema = true infers from the first batch of files. If your source adds columns later, what happens depends entirely on schemaEvolutionMode. Set it explicitly — addNewColumns for flexible sources, failOnNewColumns for sources that should never change.

2. Mixing LIVE. and catalog references

Inside a DLT notebook: LIVE.table_name references a table managed by this pipeline. catalog.schema.table_name references an external table in Unity Catalog. Mixing these up is the number-one SQL DLT error — the pipeline either fails silently or resolves the wrong table entirely.

3. Stuffing everything into one pipeline

Bronze + silver + gold in a single pipeline works, but you lose flexibility — you can’t run just the bronze layer when you need to re-ingest without also re-running all your transforms. Consider separate pipelines per layer, with explicit Unity Catalog paths as handoffs between them.

4. Expecting materialized views to be incremental

They’re not. Materialized views fully recompute when upstream data changes. For huge datasets, that can get expensive fast. Use streaming tables as long as possible and only switch to materialized views for the final aggregation step where you genuinely need to see the full history.

Key takeaways

SQL DLT is production-ready and supports the full medallion architecture. It’s not a toy, it’s not a simplified wrapper — it’s a first-class pipeline system for SQL-native teams.

The mental model: streaming tables for append-only ingestion, `APPLY CHANGES` for deduplication, and materialized views for aggregations. The __tbl_ naming convention distinguishes your internal plumbing from consumer-facing tables. EXPECT constraints give you data quality monitoring at zero extra cost. And never, ever skip pipelines.reset.allowed = "false" on a bronze streaming table.

Next in this series: Passing custom variables to Databricks DLT pipelines using Asset Bundles — because hardcoding environment paths is a choice you will regret.