How to Build, Test, and Ship SQL Pipelines on Databricks

[matthew_m]

SQL is a first-class language in SDP

SQL is a first-class language in Spark Declarative Pipelines (SDP). You can build, test, and ship an entire pipeline in SQL. This makes it a powerful tool for those with a SQL background, as SDP enables both batch and streaming, as well as data quality gates. 

This guide is for SQL developers building production-ready data pipelines on Databricks. It covers the end-to-end workflow: authoring pipelines, validating logic before execution, and deploying code across environments without manual rewriting.

Two ways to build, one dev loop

Consider a common pattern for building data pipelines on Databricks. Raw orders land in cloud storage as JSON. In this example, the landing zone is a Unity Catalog volume, a governed path over your cloud object storage that read_files can stream from. You clean data into a silver table, then aggregate it into a gold table for reporting. In SDP, you define each of these ingestion and transformation steps as a SQL statement, and the platform automatically resolves the dependencies between them.

There are two ways to build that pipeline, and they both follow the same development pattern. The first way is the new Pipelines Editor inside Databricks, a proper multi-file IDE for SDP. The second is local: you author in your own editor and drive everything from the command line with DABs. Pick whichever fits your team. For environments, the common setup is to isolate dev, staging, and production by workspace, with a separate Unity Catalog catalog per environment; keep the deeper isolation and access design to your standard Unity Catalog governance practices. Either way the pattern is the same: you author your SQL, validate it before you run it, build in data quality, parameterise it so it moves between environments, and ship it.

The rest of this post walks through that dev loop as five practical tips.

Tip 1: Author in the new editor, and read the incrementalization insights

Start in the new Pipelines Editor. It became generally available in May 2026 and it replaces the single-notebook experience that came before it. You can keep each table in its own file, or define several together, an asset browser, a data preview, an issues panel, and an interactive graph that shows how your tables depend on one another. 

Beyond project organization, the new editor streamlines development with two key features. First, it includes an interactive graph that updates as you dry run the pipeline, allowing you to visualize how each table and materialized view fits into your dependency DAG. Second, it provides proactive incrementalization insights. When you define a materialized view, SDP assesses whether it can be refreshed incrementally. If the query must fall back to a full recompute, the editor highlights the cause directly in the issues panel at author time, enabling you to address performance issues before execution.

For example, this materialized view looks completely ordinary:

CREATE OR REFRESH MATERIALIZED VIEW recent_orders AS
SELECT order_id, order_date, amount
FROM silver_orders
WHERE order_date >= '2026-01-01';

-- valid SQL, but it falls back to a full recompute without row tracking

This is valid SQL and reads fine, but it still falls back to a full recompute, because silver_orders does not have row tracking enabled. Row tracking is what lets SDP identify exactly which rows changed between updates, and it is required to incrementally refresh common operations like a filter or a projection. It is off by default, so a normal-looking view like this recomputes in full until you turn it on with ALTER TABLE silver_orders SET TBLPROPERTIES (delta.enableRowTracking = true). The editor surfaces this as an incrementalization insight at author time and points you at the fix, so you catch it before you ever run the pipeline. There are other patterns which we will flag that lead to full compute. You can also check the docs for the most up-to-date view.

When you want the full picture, pair the editor with EXPLAIN MATERIALIZED VIEW, which gives you a programmatic answer you can read in code review or in CI:

EXPLAIN MATERIALIZED VIEW
SELECT order_date, SUM(amount)
FROM silver_orders
GROUP BY order_date;
-- == Incremental Update Eligibility ==
-- The Materialized View can be incrementally refreshed.

A practical habit: treat the issues panel like build output, and read it on every edit. The editor gives you the visual reason an operator broke incrementalization, and EXPLAIN gives you the answer you can gate on. Between them, a problem that used to mean a multi-hour cost investigation is now a yellow badge you fix in a minute.

Tip 2: Dry run before you run anything

Before you run anything, dry run it. A dry run resolves every dataset and flow in your pipeline, checks your SQL syntax, validates the table and column references, and surfaces graph errors such as a flow that points at a table that does not exist. It reads no data, writes no tables, and you are not billed for a run. You get build-style feedback in seconds instead of waiting minutes for a real refresh.

A dry run does more than catch broken references. It also checks whether each materialized view can refresh incrementally, so you learn how a view will behave before you have spent the time and compute to build it even once. If one would fall back to a full recompute, that shows up here at validation time, not in a slow, expensive update later.

In the editor, the “Dry Run” function is next to the "Run Pipeline" button. From the command line it is one command, which means you can run it automatically on save and, more importantly, make it a gate in CI:

databricks bundle validate
databricks pipelines dry-run sales_pipeline --target dev

This makes the dry run a genuine merge gate: if a pull request introduces a broken reference, a missing table, or a malformed flow, the check fails and the change never reaches your main branch. Because it is the same dry run command you run locally, there is no drift between what you test on your laptop and what CI enforces, and because it reads no data and costs nothing, you can afford to run it on every push rather than saving validation for a scheduled job.

- name: Dry-run pipeline on PR
  run: |
    databricks bundle validate
    databricks pipelines dry-run sales_pipeline --target dev

While powerful, a dry run does not execute your logic, meaning it won’t catch issues like unexpected data shapes, complex join errors, or filters that drop rows unintentionally. For these, you should use expectations, which we cover in the next tip, alongside periodic test runs against a dev catalog. Ultimately, dry runs ensure that many errors like typos, missing tables, incrementalization feedback and invalid references never reach a reviewer or incur unnecessary compute costs.

Tip 3: Express data quality as expectations

To build reliable pipelines, express your data quality as expectations directly within the SQL code. Expectations are SQL constraints on a streaming table or materialized view that check every row as it flows through the pipeline. Each one has a name, a Boolean predicate, and an action: WARN keeps the row and records a metric, DROP ROW filters it out, and FAIL UPDATE stops the pipeline. If you have used dbt tests, expectations do the same job, except the check runs inline at write time rather than as a separate step you schedule and wire up.

You declare them right where you define the table:

CREATE OR REFRESH STREAMING TABLE silver_orders (
  CONSTRAINT pk_present      EXPECT (order_id IS NOT NULL)        ON VIOLATION FAIL UPDATE,
  CONSTRAINT positive_amount EXPECT (amount > 0)                  ON VIOLATION DROP ROW,
  CONSTRAINT recent_date     EXPECT (order_date >= '2020-01-01')  -- default WARN, just track it
)
AS SELECT * FROM STREAM bronze_orders;

A pattern worth adopting: when you drop rows, keep them elsewhere. A second flow that captures the rejects lets you investigate and backfill later, rather than losing the data:

CREATE OR REFRESH STREAMING TABLE silver_orders_quarantine
AS SELECT * FROM STREAM bronze_orders WHERE NOT (amount > 0);

Every outcome lands in the pipeline event log and on the Data Quality tab, so you get pass and fail counts per update without building anything. If you come from dbt, this is the equivalent of your data test results and run history, available out of the box rather than wired up to a separate store.

A few habits that work well in practice: layer your checks by medallion stage, with minimal checks on bronze, and business rules plus integrity checks on silver where records are conformed. Start a new rule on WARN so you can see how often it fires, then graduate it to DROP ROW once you trust it, and reserve FAIL UPDATE  for the things you cannot let through, like a null primary key.

Expectations are evaluated on every row as it flows through the query, so each predicate has to be a plain per-row test: a Boolean expression the engine can resolve from that single row's own columns and built-in SQL functions. 

Tip 4: Parameterise once, deploy everywhere

Hardcoding catalog names, paths, and date ranges into your SQL ties a pipeline to a single environment. SQL parameters break that coupling: you define values at the pipeline level and reference them in your SQL with a colon prefix, so one definition runs across dev, staging, and production by swapping the values at deploy time. The same mechanism can swap what a pipeline reads, not just the values it filters on, so one definition can serve many tables.

-- Pipeline parameters source_path (the read path) and start_date = "2026-01-01"
CREATE OR REFRESH STREAMING TABLE bronze_orders AS
SELECT *
FROM STREAM read_files(:source_path, format => 'json')
WHERE order_ts >= :start_date;

For catalogs, schemas, and table names you need IDENTIFIER(), which is the one piece of syntax people miss. USE CATALOG :source_catalog will not work, but this will:

USE CATALOG IDENTIFIER(:source_catalog);
USE SCHEMA IDENTIFIER(:source_schema);

The point of this becomes clear when you bind the parameters in a DAB target, so the deployment and the parameter values are version-controlled together and your SQL stays free of environment constants:

# databricks.yml
variables:
  source_catalog:
    description: Catalog containing raw source tables
resources:
  pipelines:
    sales_pipeline:
      name: sales_${bundle.target}
      catalog: ${var.source_catalog}
      configuration:
        source_catalog: ${var.source_catalog}
      libraries:
        - file:
            path: ./src/orders.sql
targets:
  dev:
    variables:
      source_catalog: dev_raw
  prod:
    variables:
      source_catalog: prod_raw

You can also see and set the current parameter values directly in the pipeline's settings in the editor, which is handy for a quick check or a one-off change. Keep the DAB target as the source of truth, though, so a UI edit does not drift from your repo.

One thing to keep in mind: standardise on the colon syntax for new pipelines. The older ${var} style turns up in old examples and is a common cause of a query quietly returning zero rows when it gets mixed with the newer syntax

Tip 5: Drive the loop from the command line and promote with DABs

In Databricks there are multiple approaches you can use to isolate environments, a common approach is to have one workspace and one catalog per workspace, which is what we are showcasing here. For larger organizations having a catalog per team or project per environment also is a good approach, for more info here are some best practices.

To interact with your pipelines through the CLI databricks pipelines command group is the whole dev loop in one place: init, deploy, run, dry-run, logs, stop, and history. Author in your own editor, run from the command line, and the same commands work on your laptop and in CI. It is scriptable and it shows up cleanly in a pull request diff, which the UI cannot give you.

databricks pipelines init        # scaffolds a DAB project with src/ and resources/
cd orders_pipeline
databricks bundle validate
databricks pipelines dry-run orders_etl --target dev
databricks pipelines deploy --target dev
databricks pipelines run orders_etl --target dev
databricks pipelines logs orders_etl

Promotion to production is the same project with a different target. Because the environment differences live in your DAB targets and your parameters, you change one flag, not your SQL:

databricks bundle validate
databricks pipelines deploy --target prod
databricks pipelines run orders_etl --target prod

A couple of habits that save pain: generate your bundle YAML for an existing pipeline with databricks bundle generate pipeline rather than writing it by hand, and pin the CLI version in CI so a new release does not change behaviour underneath you.

Putting it together

Here is the whole loop in one screen. One SQL file with a streaming table, expectations, and a parameterised materialized view:

-- orders.sql
CREATE OR REFRESH STREAMING TABLE bronze_orders AS
SELECT * FROM STREAM read_files(:source_path, format => 'json')
WHERE order_ts >= :start_date;

CREATE OR REFRESH STREAMING TABLE silver_orders (
  CONSTRAINT pk_present      EXPECT (order_id IS NOT NULL)  ON VIOLATION FAIL UPDATE,
  CONSTRAINT positive_amount EXPECT (amount > 0)            ON VIOLATION DROP ROW
)
AS SELECT * FROM STREAM bronze_orders;

CREATE OR REFRESH MATERIALIZED VIEW daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM silver_orders
GROUP BY order_date;

One CLI session that validates, ships, and promotes it:

databricks pipelines dry-run orders_etl --target dev   # validate, no run
databricks pipelines deploy --target dev               # ship to dev
databricks pipelines deploy --target prod              # same code, prod values

That is a complete, environment-portable SQL pipeline with data quality built in, validated before it ran, in under a screen of code. Every statement above is a real, runnable SDP definition, so you can lift it straight into a pipeline.

The practical takeaways

• Author in the new editor and read the incrementalization insights before you run.
• Dry run on every save and in every pull request.
• Express data quality as expectations, and quarantine rather than just drop.
• Parameterise your SQL and bind the values in DAB targets.
• Drive the loop from the CLI, and promote between environments by changing one flag.

Try it out

Getting started is quick. Create a pipeline in the new Pipelines Editor, or scaffold one from the command line with databricks pipelines init, point it at a SQL file, and dry run it before your first real update. The docs walk through setting up your first SDP project.

From there, the loop in this post (author, validate, add data quality, parameterise, and ship) is the whole workflow. The SQL story on SDP is still moving quickly, so it is worth keeping an eye on the release notes.

Further reading

• New Pipelines Editor docs
• SQL parameters docs
• Pipeline updates and dry run
• Expectations docs
• Pipeline event log schema
• Pipelines CLI reference
• Unity Catalog volumes (what they are and how to use them)
• Incremental refresh for materialized views (row tracking and eligibility)
• Deploy pipelines with Databricks Asset Bundles (tutorial)