Architectural Pattern: Incremental Data Export from Snowflake to Databricks

Let’s say we need to migrate data from Snowflake to another Data Warehouse or Lakehouse platform for any number of business or technical reasons. Rather than debating why such a migration might be required, let’s focus on how it can be done efficiently.

In this article, I’ll walk through a practical architectural pattern that enables incremental data movement from Snowflake to downstream platforms with minimal overhead. The solution leverages three Snowflake features: Streams + Tasks + Unload Commands

Prerequisite:

• Snowflake Stream
• Snowflake Task
• Snowflake Data Unloading

Generic Architecture:

As shown in above architecture, first we will be creating a Stream on top of the source table to track Changed Data Capture(CDC)—

CREATE OR REPLACE STREAM <stream_name>
ON TABLE <source_table>;

Then we will be having a Task which will run in some scheduled interval to ingest the data in Datalake like s3 or Azure Blob Storage —

COPY INTO @<external_stage>/<target_folder>/
FROM (
  SELECT *,
  CURRENT_TIMESTAMP() AS ingestion_timestamp
  FROM <stream_name>
)
FILE_FORMAT = (TYPE = PARQUET)
OVERWRITE = TRUE
HEADER = TRUE;

Note: As COPY INTO is a DML operation, executing the above command will not only unload the CDC data into the Data Lake but will also consume the records from the Snowflake Stream(i.e. Stream will be empty after each successful execution of the copy operation). So, there is no need to worry whether the same data will be unloaded again in the next scheduled run—the Stream will only expose the new changes that have occurred since the last consumption.

Extending the architecture:

Let’s say any changes happening in a Snowflake table need to be reflected in a Databricks Delta Table. Instead of performing full data loads every time, we can leverage the following architecture pattern to capture and propagate only the incremental changes —

1. A Snowflake Stream continuously tracks INSERT, UPDATE, and DELETE operations on the source table.
2. A scheduled Snowflake Task periodically reads the pending changes from the Stream and unloads them to a Data Lake in Parquet format.
3. In Databricks, an S3-backed Volume can be configured as the landing zone. A Workflow with a ***file-arrival trigger*** can monitor this Volume and automatically start the ingestion process whenever new files arrive.
4. Within the workflow, using ***Databricks Autoloader ***we can read only the newly arrived files, while maintaining checkpoints and schema information.
5. Finally, a ***MERGE operation*** applies the CDC records to the target Delta Table, ensuring that changes from Snowflake are reflected in Databricks with minimal latency.

Conclusion:

Sometimes the simplest solutions are the most effective. By combining Snowflake Streams, Tasks, and file-based data exchange through a Data Lake, we can build a robust incremental migration framework without introducing additional CDC tools or operational complexity.

The pattern is scalable, cloud-native, and can be easily adapted to synchronize Snowflake with Databricks or virtually any modern Warehouse/Lakehouse platform.

This is all for this blog, for more such interesting and practical contents, follow me on ***YouTube, [LinkedIn](https://www.linkedin.com/in/satadru-mukherjee-a237b41a5/)*, and Medium**!