If you've ever watched a full table reload consume your Snowflake credits like a hungry monster, you know why incremental loading isn't just a nice-to-have—it's essential. Loading only the data that's changed since your last run can reduce processing time by 90% or more, slash compute costs, and make your pipelines far more maintainable.

But here's the thing: incremental loading isn't a one-size-fits-all solution. The right strategy depends on your source system, data characteristics, and business requirements. Let's explore the most effective approaches for Snowflake, with real-world patterns you can implement today.

Why Incremental Loading Matters

Before diving into the how, let's establish the why. Full table reloads might seem simpler—just truncate and reload everything—but this approach falls apart quickly as data volumes grow. Consider a customer table with 50 million records where only 10,000 change daily. A full reload processes 5,000 times more data than necessary.

The impacts are tangible:

Incremental loading addresses all of these issues, but it requires thoughtful implementation.

Strategy 1: Timestamp-Based Incremental Loading

This is the most common pattern, and for good reason—it's straightforward and works with most source systems. The concept is simple: track when you last loaded data, then fetch only records modified since that timestamp.

Basic Implementation

Here's a typical pattern using a watermark table to track load times:

-- Create a watermark table to track last load times
CREATE TABLE etl_watermarks (
  table_name VARCHAR,
  last_loaded_timestamp TIMESTAMP_NTZ,
  PRIMARY KEY (table_name)
);

-- Your incremental load query
INSERT INTO target_table
SELECT *
FROM source_table
WHERE modified_date > (
  SELECT last_loaded_timestamp 
  FROM etl_watermarks 
  WHERE table_name = 'source_table'
)
AND modified_date <= CURRENT_TIMESTAMP();

-- Update the watermark
MERGE INTO etl_watermarks w
USING (SELECT 'source_table' as table_name, CURRENT_TIMESTAMP() as ts) s
ON w.table_name = s.table_name
WHEN MATCHED THEN UPDATE SET last_loaded_timestamp = s.ts
WHEN NOT MATCHED THEN INSERT (table_name, last_loaded_timestamp) VALUES (s.table_name, s.ts);

The key here is the modified_date column in your source. This requires your source system to maintain accurate timestamps for updates—not always a given.

Important Considerations

Handle late-arriving data: I always recommend adding a lookback window. Instead of loading from the exact last watermark, subtract a buffer (say, 1 hour) to catch records that might have been committed slightly out of order:

WHERE modified_date > DATEADD(hour, -1, 
  (SELECT last_loaded_timestamp FROM etl_watermarks WHERE table_name = 'source_table')
)

Deal with updates correctly: For updates to existing records, you'll need a MERGE statement rather than INSERT to handle upserts properly. More on this in a moment.

Strategy 2: Change Data Capture (CDC) with Streams

Snowflake Streams are a game-changer for incremental loading within Snowflake. A Stream creates a change table that records all DML changes (inserts, updates, deletes) to a source table. This is true CDC without needing external tools.

How Streams Work

-- Create a stream on your source table
CREATE STREAM customer_stream ON TABLE customer_raw;

-- Query the stream to see changes
SELECT 
  customer_id,
  customer_name,
  email,
  METADATA$ACTION as dml_action,
  METADATA$ISUPDATE as is_update
FROM customer_stream;

The magic is in those metadata columns. METADATA$ACTION tells you whether a row was inserted or deleted, and METADATA$ISUPDATE flags updates (which appear as both a delete and insert).

Processing Stream Data

Here's a pattern for processing stream data into a target table:

MERGE INTO customer_target t
USING customer_stream s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' AND s.METADATA$ISUPDATE = FALSE
  THEN DELETE
WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' AND s.METADATA$ISUPDATE = TRUE
  THEN UPDATE SET 
    t.customer_name = s.customer_name,
    t.email = s.email,
    t.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT'
  THEN INSERT (customer_id, customer_name, email, created_at)
  VALUES (s.customer_id, s.customer_name, s.email, CURRENT_TIMESTAMP());

Once you consume a stream (any DML operation that uses it), the stream automatically advances its offset. This eliminates the need for manual watermark management.

My take: If your data is already in Snowflake and you need robust incremental processing, Streams are the best approach. They're efficient, transactionally consistent, and eliminate complex tracking logic.

Strategy 3: MERGE Operations for Upsert Patterns

The MERGE statement is your Swiss Army knife for incremental loading. It handles inserts, updates, and even deletes in a single atomic operation—crucial when you're dealing with changing dimension data.

A Comprehensive MERGE Pattern

MERGE INTO product_dimension pd
USING (
  SELECT 
    product_id,
    product_name,
    category,
    price,
    modified_date
  FROM product_staging
  WHERE modified_date > (SELECT MAX(last_updated) FROM product_dimension)
) ps
ON pd.product_id = ps.product_id
WHEN MATCHED AND ps.modified_date > pd.last_updated THEN
  UPDATE SET
    pd.product_name = ps.product_name,
    pd.category = ps.category,
    pd.price = ps.price,
    pd.last_updated = ps.modified_date
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, category, price, last_updated)
  VALUES (ps.product_id, ps.product_name, ps.category, ps.price, ps.modified_date);

Notice the extra condition in the MATCHED clause: ps.modified_date > pd.last_updated. This prevents unnecessary updates when running the same data through multiple times—a common scenario during pipeline retries.

Strategy 4: Incremental Loading from External Sources

When loading from external sources (S3, Azure Blob, etc.), Snowflake's metadata columns help track what you've already loaded.

-- Create a tracking table for loaded files
CREATE TABLE loaded_files (
  file_name VARCHAR,
  load_timestamp TIMESTAMP_NTZ,
  row_count NUMBER
);

-- Load only new files
COPY INTO customer_raw
FROM @my_s3_stage/customers/
FILE_FORMAT = (TYPE = 'PARQUET')
FILES = (
  SELECT DISTINCT metadata$filename
  FROM @my_s3_stage/customers/
  WHERE metadata$filename NOT IN (SELECT file_name FROM loaded_files)
);

-- Track loaded files
INSERT INTO loaded_files
SELECT 
  metadata$filename,
  CURRENT_TIMESTAMP(),
  COUNT(*)
FROM customer_raw
WHERE load_timestamp = CURRENT_TIMESTAMP()
GROUP BY metadata$filename;

Best Practices and Gotchas

Use clustering keys for large tables: When your target table grows large, clustering on your incremental load key (like modified_date) dramatically improves MERGE performance. Snowflake can prune partitions it doesn't need to scan.

ALTER TABLE large_fact_table CLUSTER BY (modified_date);

Batch your incremental loads: Don't load every single record individually. Batch changes into reasonable chunks (every 15 minutes, hourly, etc.) to balance freshness with efficiency.

Monitor your Stream lag: Streams have a data retention period (default 14 days). If you don't consume a stream within that window, you'll lose data. Set up monitoring alerts.

Test your idempotency: Your incremental loads should be idempotent—running them multiple times with the same data should produce the same result. This is critical for pipeline reliability.

Choosing the Right Strategy

Here's my decision framework:

Conclusion

Incremental loading in Snowflake isn't just about writing the right SQL—it's about understanding your data's characteristics and choosing patterns that match. Start with timestamp-based approaches for simplicity, leverage Streams when working within Snowflake's ecosystem, and always measure your performance gains.

The initial investment in setting up incremental loading pays dividends quickly. I've seen teams reduce their ETL costs by 80% and improve data freshness from daily to near real-time by implementing these patterns. Your future self (and your CFO) will thank you.