Spark Declarative Pipelines versus Snowflake Dynamic Tables

Summary

• Spark Declarative Pipelines (SDP) is a declarative framework for building batch and streaming data pipelines in SQL and Python, built from flows, streaming tables, materialized views and sinks.
• A Snowflake dynamic table materializes the results of a SELECT query and keeps them up to date. You set a target lag and a refresh mode, and Snowflake refreshes the table on a virtual warehouse.
• Both infer dependencies for you. SDP on Databricks runs flows in the correct order with automatic retries, and Snowflake builds a dependency graph from the queries of dynamic tables that read from each other.
• SDP on Databricks covers ingestion as well as transformation: streaming tables read from Auto Loader and message buses such as Apache Kafka, and AUTO CDC applies change data with SCD Type 1 or Type 2.
• SDP is an open-source Apache Spark framework, and Databricks runs it as a managed product on serverless or classic compute.

Spark Declarative Pipelines versus Snowflake Dynamic Tables

Spark Declarative Pipelines (SDP) and Snowflake Dynamic Tables are both declarative: you describe the result you want and the platform works out refresh order and incremental processing. The difference is scope. A dynamic table is one SQL object, a SELECT query plus a target lag, and a chain of dynamic tables forms a pipeline. SDP on Databricks is a pipeline framework that spans ingestion, change data capture, data quality rules and transformation in SQL or Python. We believe Spark Declarative Pipelines on Databricks is the right fit for teams that want ingestion and transformation defined, monitored and governed in one place.

What are Snowflake Dynamic Tables?

Snowflake's documentation defines a dynamic table as an object that "materializes the results of a SELECT query and keeps them up to date." You create one with CREATE OR ALTER DYNAMIC TABLE, giving a TARGET_LAG, a WAREHOUSE and a REFRESH_MODE. Each refresh is applied atomically, so readers never see a partial refresh. Refresh modes are INCREMENTAL, FULL, AUTO, ADAPTIVE and CUSTOM_INCREMENTAL. Snowflake documents a minimum target lag, and it states that dynamic tables don't support workloads that need fresher data than that or strictly guaranteed refresh timing.

What is Spark Declarative Pipelines?

Spark Declarative Pipelines on Databricks provides a declarative framework for batch and streaming pipelines in SQL and Python. Its core objects are flows, streaming tables, materialized views and sinks. A streaming table is a Delta table with extra support for incremental processing, and a materialized view caches query results and refreshes incrementally when it can.

Spark Declarative Pipelines vs Snowflake Dynamic Tables: how they compare

Capability	Spark Declarative Pipelines on Databricks	Snowflake Dynamic Tables
Unit of definition	A pipeline whose source code declares streaming tables, materialized views, views, flows and sinks	One dynamic table per CREATE DYNAMIC TABLE statement with a SELECT query
Languages	SQL and Python (from pyspark import pipelines as dp)	A SQL SELECT query; the Snowpark Python API can also create one from a DataFrame with DataFrame.create_or_replace_dynamic_table
Dependencies	The pipeline runs flows in the correct order with maximum parallelism and retries transient failures	Snowflake infers the dependency graph from the queries and refreshes in dependency order
Incremental processing	Streaming tables process each input row once; materialized views refresh incrementally when possible	INCREMENTAL, FULL, AUTO, ADAPTIVE or CUSTOM_INCREMENTAL refresh modes
Freshness control	Triggered mode (refresh, then stop), continuous mode (process data as it arrives), or real-time mode on a continuous pipeline's update flow	A target lag per table, or DOWNSTREAM on intermediate tables
Ingestion	Streaming tables read from Auto Loader and message buses such as Apache Kafka, Azure Event Hubs and Google Pub/Sub	Reads base tables; Snowflake's loading docs describe landing raw data with Snowpipe, then transforming it with dynamic tables
Compute	Serverless compute (Databricks recommends it for new pipelines) or classic compute	The virtual warehouse named in the definition runs each refresh
Refresh consistency	Materialized views are correct at the time of each update, even when data arrives late or out of order	Each refresh is applied atomically, and downstream tables see a consistent snapshot of their inputs
External scheduling	Run as a task in Lakeflow Jobs	SCHEDULER = DISABLE hands refreshes to manual control or an external tool

How Databricks approaches declarative pipelines

Auto Loader feeds streaming tables from cloud storage without you managing a schema location or checkpoint, because the pipeline manages those settings.
Change data is a first-class case. The AUTO CDC APIs compute slowly changing dimensions Type 1 and Type 2 from a change data feed, and AUTO CDC FROM SNAPSHOT derives changes when only snapshots are available. Data quality rules live in the same definitions: expectations are SQL Boolean constraints on a streaming table, materialized view or view, and they can drop invalid records or fail the update while recording data quality metrics.
Pipeline mode is independent of table type, so streaming tables and materialized views can run in either triggered or continuous mode. The core programming model is part of open-source Apache Spark, as the Spark Declarative Pipelines programming guide shows.

FAQs

Is Spark Declarative Pipelines only for batch?

No. SDP supports streaming tables and materialized views, and on Databricks both can run in triggered or continuous mode.

Can I write pipeline logic in Python?

Yes, on both. SDP supports Python and SQL, and its Python API is from pyspark import pipelines as dp. In Snowflake, the Snowpark Python API can create a dynamic table from a DataFrame, and Snowflake's guidance is that logic expressible as a SQL SELECT statement is a candidate for a dynamic table.

Can Spark Declarative Pipelines handle operational, low-latency streaming?

Yes. For operational workloads that need an immediate response, such as fraud detection, pipelines can use Spark Real-Time Mode (RTM), a Structured Streaming trigger type that complements the default micro-batch mode. The pipeline runs in continuous mode with real-time mode enabled and an update flow that writes to a sink, on classic or serverless compute.