Question 1

Why move data from a data warehouse into SQL Server?

Accepted Answer

Usually because the consuming layer is already there. Teams with established stored procedures, scheduled reports, or a business intelligence tool pointed at SQL Server would rather receive modelled results into that environment than move every consumer to a new one. Landing a Snowflake model into SQL Server means the work of modelling happens once in the data warehouse while the tools people already use keep reading from where they always have.

Question 2

How is the load performed?

Accepted Answer

Bulk table replication stages rows into a scratch table and then merges into the destination, which keeps the visible table consistent while a load is running. Model syncs write directly instead, batching inserts up to 250 rows and issuing one update statement per row. If a batch of inserts fails, it is retried row by row so the error is reported against the specific records rather than the whole run.

Question 3

What happens when the model changes shape?

Accepted Answer

Added and removed fields are migrated automatically, and existing destination types are kept where they are still compatible. Where a type is not compatible, a schema migration is planned rather than the sync quietly writing the wrong thing. Values that cannot be coerced into the destination type surface as record level errors, which is the behaviour you want, since it points at the specific rows rather than failing the entire execution.

Question 4

How are existing rows matched?

Accepted Answer

Through an identity field you nominate on the Snowflake model and map to a destination column, with updates matching on equality. A declared primary key on the SQL Server table is not required, though the field should be unique or a single source row may update several destination rows. Bulk replication behaves differently and does use a primary key to merge, including composite keys, falling back to recreating the table when none is defined.

Connect Snowflake with Microsoft SQL Server

Connect Snowflake and Microsoft SQL Server with Polytomic

Staged then merged

Schema migration handled

Reaches private instances

Trusted by businesses worldwide

Step-by-step integration guide

Connect Snowflake in Polytomic

Connect Microsoft SQL Server in Polytomic

Create a sync between Snowflake and Microsoft SQL Server

Map fields and enable the sync

Frequently asked questions

The sync and ETL platform that does it all

Powerful data sync

Advanced analytics

Enterprise security

Easy setup

Ready to connect Snowflake with Microsoft SQL Server?