Skip to main content

Declarative vs Imperative Pipelines

When designing data engineering pipelines in Snowflake, you must choose a design paradigm. Historically, data engineers relied entirely on Imperative programming. With the introduction of Dynamic Tables, Snowflake now strongly supports Declarative programming.

Understanding the difference between these two paradigms is critical for designing modern, low-maintenance data architectures.

Topics Covered​

  • What is an Imperative Pipeline?
  • What is a Declarative Pipeline?
  • Maintenance & Failure Recovery Differences
  • When to Use Which Paradigm

1. Imperative Pipelines (Streams + Tasks)​

Imperative pipelines define exactly HOW data moves step-by-step.

In an imperative approach, you are the orchestrator. You must define the source, track the changes, write the logic to apply those changes (MERGE), and schedule when that logic runs.

The Imperative Approach (Code Example)​

If we want a table summarizing daily sales by store location in India, an imperative pipeline requires three distinct objects:

-- 1. Track the changes (The Stream)
CREATE OR REPLACE STREAM RAW_SALES_STREAM ON TABLE RAW_SALES;

-- 2. Define exactly HOW to apply changes (The Task + MERGE)
CREATE OR REPLACE TASK UPDATE_SALES_SUMMARY
WAREHOUSE = COMPUTE_WH
SCHEDULE = '5 MINUTE'
AS
MERGE INTO SALES_SUMMARY target
USING (
SELECT STORE_LOCATION, SUM(AMOUNT) AS SALES
FROM RAW_SALES_STREAM
GROUP BY STORE_LOCATION
) source
ON target.STORE_LOCATION = source.STORE_LOCATION
WHEN MATCHED THEN UPDATE SET target.TOTAL_SALES = target.TOTAL_SALES + source.SALES
WHEN NOT MATCHED THEN INSERT (STORE_LOCATION, TOTAL_SALES) VALUES (source.STORE_LOCATION, source.SALES);

-- 3. You must remember to turn the task on!
ALTER TASK UPDATE_SALES_SUMMARY RESUME;

2. Declarative Pipelines (Dynamic Tables)​

Declarative pipelines define WHAT the final result should look like, and Snowflake figures out how to build and maintain it.

In a declarative approach, you provide the final SQL SELECT statement. Snowflake analyzes the statement, creates a hidden background process to track changes in the base tables, and incrementally updates the target table for you.

The Declarative Approach (Code Example)​

Achieving the exact same result using a Declarative Dynamic Table requires only one simple statement:

-- Declarative: "Make this table look exactly like the output of this query, updated every 5 minutes."
CREATE OR REPLACE DYNAMIC TABLE SALES_SUMMARY_DT
TARGET_LAG = '5 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT STORE_LOCATION, SUM(AMOUNT) AS TOTAL_SALES
FROM RAW_SALES
GROUP BY STORE_LOCATION;

(To verify who owns this Dynamic Table and has privileges to alter it, check your current context by running SELECT CURRENT_USER(); and SELECT CURRENT_ROLE();)

Maintenance & Failure Recovery Differences​

The real power of Declarative pipelines is revealed when things go wrong.

Failure in an Imperative Pipeline​

Imagine the UPDATE_SALES_SUMMARY task fails because the warehouse was suspended or the network timed out.

  • The Stream offset is not consumed.
  • The next time the task runs, it must process double the data.
  • If a junior engineer accidentally runs a SELECT * FROM RAW_SALES_STREAM and consumes the stream outside of the task, the data is permanently lost from the pipeline! The dashboard is now inaccurate, and you must manually rebuild the table.

Failure in a Declarative Pipeline​

Imagine the SALES_SUMMARY_DT refresh fails.

  • Snowflake's automated engine marks the table as LAGGING.
  • On the next scheduled interval, Snowflake automatically retries the refresh.
  • Because Dynamic Tables don't rely on exposed Streams, human error cannot "consume" the offset. The pipeline is self-healing.

When to Use Which Paradigm​

When Declarative Pipelines Shine​

You should use Dynamic Tables (Declarative) for 90% of standard data warehousing workloads:

  • Multi-hop transformation chains (Medallion architectures: Bronze -> Silver -> Gold).
  • Materializing heavy BI reporting views.
  • Any standard SQL joins, aggregations, and window functions.

When Imperative Pipelines are Required​

You must fall back to Streams + Tasks (Imperative) when:

  • Complex Conditional Logic: e.g., "If the STATUS is 'DELETED', execute a Stored Procedure to email the customer."
  • Side Effects: Calling External Functions or External Network APIs (like sending an SMS or hitting a REST endpoint).
  • Custom Merge Patterns: Such as tracking complex Slowly Changing Dimension (SCD) Type 2 validity dates (VALID_FROM, VALID_TO) that cannot be expressed purely via a declarative SELECT statement.

Summary​

  • Imperative (Streams + Tasks): You manage the state, the schedule, and the MERGE logic. High maintenance, but highly flexible for complex operations.
  • Declarative (Dynamic Tables): Snowflake manages the state and incremental updates based on a simple SELECT query. Self-healing and low maintenance.