How Can I Sync Data from Custom SQL Queries in SQL Server to Snowflake?

Question

I want to sync data from a custom SQL query in my SQL Server database to Snowflake using Fivetran. However, the Fivetran SQL Server connector does not support syncing results directly from ad hoc queries. What are my options?

Environment

• Connector: SQL Server
• Destination: Snowflake

Answer

We don't support syncing custom SQL query output directly using a SQL Server connection. We replicate supported database objects, such as tables and supported views.

To sync data based on custom SQL logic, use one of the following options:

Option 1: Sync tables and transform in Snowflake

Sync the required SQL Server tables to Snowflake, then use dbt or Snowflake SQL to recreate the query logic in Snowflake.

This option provides the most flexibility.

Option 2: Sync source-side views

Create a view in SQL Server that contains your custom query logic. Make sure all underlying source tables are also selected for sync.

Fivetran SQL Server connections replicate views with supported definitions. After the sync, use the replicated view and underlying tables to recreate the dataset in Snowflake. If your logic cannot be represented as a SQL Server view, use option 1 or option 3.

Option 3: Build a custom connector

Build a custom connector using the Fivetran Connector SDK if you must execute arbitrary or parameterized SQL queries during extraction.

This option requires engineering effort and is recommended only if the built-in options do not meet your requirements.