Connect SQL Server to Snowflake: Replication & 2-way Sync
Learn how to connect Snowflake and SQL Server easily and without coding using Skyvia - a no-code cloud data integration platform. Skyvia supports more than 200 connectors and requires nothing except a web browser. In this video tutorial we will show several data integration scenarios:
1. Replication of SQL Server data to Snowflake using ELT approach.
2. Snowflake data sync to SQL Server using ETL approach.
Get Skyvia for free: https://skyvia.com
Timestamps:
00:00 - Introduction
00:21- Create Snowflake and SQL Server connections
02:51 - Replicate (ELT) SQL Server data to Snowflake using Replication - wizard-based ELT tool
06:05 - Sync (ETL) Snowflake data to SQL Server using Import - wizard-based ETL tool
05:52 - Introduction to Data Flow - visual ETL data pipeline designer
👉 Do you want to learn more about Skyvia? Watch our overview video: https://www.youtube.com/watch?v=41JIlV1bP9c
âś… Check how Skyvia products can help your company in different scenarios: https://skyvia.com/solutions/
🔥 Solve a wide range of data-related tasks with our all-in-one cloud data platform! Get started for free https://app.skyvia.com/register
đź”” Subscribe to our channel and get dozens of Skyvia tutorials and useful videos: /@skyvia3342
đź“§ Do you have any questions or tutorial-related ideas? Feel free to leave a comment or send us an email to sales@skyvia.com
Closed Caption:
Hello, and welcome to the Skyvia tutorials.
In this video, we will check how to syncÂ
data between Snowflake and SQL Server.
This tutorial assumes that you already have a Skyvia account.
If you don't have it yet,  you can create it for free byÂ
following the link in the description.
Before you start building IntegrationÂ
between SQL Server and Snowflake,
create a connection to each of them.
To create a new connection to SQLÂ
Server, click "Create new" and select
"Connection.” Skyvia is compatible with aÂ
variety of databases and data warehouses,
including BigQuery, Redshift, MySQL,Â
Postgres, "Azure", and many others.
Choose "SQL Server."
There are two ways to create a connection to SQL Server:
directly or through the Agent.
Choose a direct connection if yourÂ
database is accessible over the Internet.
To set up a direct connection, enter theÂ
server's address, your user ID, password,
and select the database you want to connect to.
If your SQL Server is on a local computerÂ
or network, use the Agent connection.
Agent application will act as a secure layerÂ
between Skyvia and your database server.
To connect through the Agent, firstÂ
download the Agent app. Click “Create new”,
select "Agent", then download and install the app on your computer.
On this page, you’ll see your Agent’s key. CopyÂ
it to paste into the Agent app's UI once it’s
running, or download it and place it in the sameÂ
folder as the Agent app so the key is inserted
automatically. It’s also recommended to renameÂ
your Agent for easy identification in the future.
After downloading and installing the Agent app,Â
you’re ready to create a new connection.
Select "Agent Connection" mode at theÂ
top, choose your Agent from the dropdown,
and enter the server's IP address, user ID,Â
password, and the database to connect to.
Remember to keep the Agent app running while using this connection.
If the app doesn’t launch properly, right-click onÂ
it and select "Run as administrator."
To create a new connection to Snowflake,
click “Create New,” select “Connection,” andÂ
choose “Snowflake.”
Enter the Domain, User, Password, and Database.
The Warehouse, Schema and Role fields are optional, however they are required for Replication and Import integrations that we will cover shortly.
The last field is “Files storage for Bulk.”Â
Snowflake uses a fast data importing and
processing method where data is first loaded
into temporary file storage as
CSV files, and then processed by Snowflake’s computing power.
You can choose between Amazon S3 and AzureÂ
storage. If “None” is selected for the “Files storage for Bulk”,
you can still import data between SQL Server and Snowflake,
but Replication to Snowflake won’t beÂ
available, and data processes will be slower.
Once done with the connections, let's moveÂ
to Replication. Replication creates a copy of
your SQL server database on Snowflake’s data warehouse.
This can be useful, for example, if you want to offload data from SQL Server to Snowflake for advanced analytics.
This example will cover replicating aÂ
custom-created ERP. To replicate your data, click
"Create new" and choose “Replication”.
Select SQLÂ Server as source, and Snowflake as target.
Let’s start with Replication options.Â
If you want to track changes instead
of replicating the whole database everyÂ
run, leave "Incremental updates" enabled.
Select the "Update Schema" checkboxÂ
to apply data structure changes made
in the Source to the database schema in Target.
If incremental updates are enabled, youÂ
will need to choose an ingestion mode.
Select "New and Modified" to replicate both new and modified records.
Select "New" to only replicate new records,Â
ignoring updates to previously replicated records.
Select "Log-based" to use the native database change tracking
mechanism to locate new and modified records.
You can also choose to Create or Drop tables.
Now, select SQL server tables.
By default, all object fields are replicated.
If you want to replicate specific columns only or tuneÂ
the settings for some table – click edit.
In the task editor window, you can changeÂ
the object replication behavior.
To rename the target object, change the "Target name" field.
To define a replication behavior, select oneÂ
of three modes:
Standard mode is assigned to each object automatically
and uses the settings of your replication.
Re-sync mode forces the full replication of the object by
dropping and creating an object in Target during each Integration run.
Re-sync on demand is created for objectsÂ
that are part of your replication,
but are not required for each replication run.
With re-sync on demand, you can include the object
in the next replication run by resetting its LastSyncTime.
Ingestion mode defines how to track changes inÂ
Source during incremental replication.
Depending on the ingestion mode, Created ColumnÂ
and Modified Column fields will appear.
If you need to filter data, add conditions.
Once you're done, click "Save task."
Now, you can run or schedule the Integration.Â
Click "Schedule" to set it up for future runs.
You can set the integration to run every day or on specific weekdays,
every few hours or minutes, and achieve near real-time data integration.
Let's schedule Integration to run every day at 10 AM.
Rename your integration by editing the “Untitled” field.
Click “Create” to save your integration.
To start the replication process immediately, click the “Run” button.
After starting the Replication,Â
you can check the execution status
and its results on the monitor and logÂ
tabs, where you will see the number of
successfully replicated rows and errorÂ
messages in case something goes wrong.
Let's move to the next scenario:Â
syncing Snowflake data to SQL Server.
In this example, we will sync sales ordersÂ
from Snowflake to the SQL Server database using
Import - a wizard-based ETL tool that allowsÂ
users to integrate data in any direction.
Import is ideal for scenarios such as migrating data between platforms,
synchronizing data across systems, and performing ETL or reverse
ETL processes to move data into cloud applications or databases.
To create a new Import,
click "Create New" and select "Import".Â
In the Source Type, select "Data Source".
Select Snowflake as Source, and SQL Server as Target.
To define the data to load, its destination and how to map it,
click “Add new.” You can add any numberÂ
of tasks to your import integration.
On the source definition page, select theÂ
Source object to import data from.
You can also apply filters to your data. For example,
let's exclude one of the products. To do this, add the filter condition.
Click the next step button.
Select the target object and choose the operation.
Use "Insert" to add new records, "Update" toÂ
modify existing ones, and "Delete" to remove records.
The "Upsert" operation updates aÂ
record if it exists, or inserts a new one.
The next step is mapping. Skyvia triesÂ
to map fields automatically. However,
you can remap any field as you see fit.Â
There are four mapping types: column,
constant, lookup, and expression.
To use Upsert,you need to map the Primary key using Lookup.Â
Lookup mapping fetches a value for the mapped
Target field from a record in any Source orÂ
Target object based on specific conditions.
After mapping the fields, click "Save task".
Let’s review Import options.
You can enable "Preserve task order" toÂ
execute the tasks in the order they appear
in the task list. The "Nested Objects"Â
checkbox allows you to unwind fields,
store nested objects or arrays, and access orÂ
map the fields within these nested structures.
Enable the "Source values in error log"Â
checkbox to see the source data in the logs.
If everything is correct, change the name in the “Untitled” field
and click “Create” to save the integration.
Let's run this Import integration.
The monitor tab shows the detailsÂ
of the import to SQL Server.
You can also set up notifications about Integration errors.
Click on your user account icon and select account settings.
Choose notifications in the menu and selectÂ
which emails should receive a notification.
If your scheduled integration fails, you will receive an email.
For more complex scenarios with complicatedÂ
logic, advanced transformations, or several
connectors at once, Skyvia offers Data FlowÂ
– a visual ETL data pipeline designer.
In this example, we will walk through a data flow that imports orders into
different SQL Server tables, depending on the order quantity.
To create a data flow, click "Create new" and select "Data Flow."
This data flow imports orders from SnowflakeÂ
to different SQL Server tables based on the order quantity.
This flow has four components:Â source, condition, targets and variables.
The source component queries data from Snowflake.
The condition component checks whether the orderÂ
quantity is greater or equals five.
If true, the data is routed to the wholesaleÂ
table. If the quantity is less than 5,
the orders will be inserted into the order sales table.
You can add more conditions if needed.
The row count components track how manyÂ
rows were successfully imported into
the target tables and how many failed.
You can also count the number of rows
directed to each target by adding new values in the result section.
Thank you for watching this SQL Server and Snowflake integrations tutorial.
If you have any additional questions, feel free to dropÂ
us a comment below and we will help you.
To try Skyvia for free, use the link in the description.
See you in the next videos.
Video Length: 10:14
Uploaded By: Skyvia
Published: 2/17/2025
View Count: 5,789