Skip to main content

SQL Server to Snowflake: CDC via Apache Iceberg

SQL Server to Snowflake comes down to one choice. Copy the rows into tables Snowflake owns, or write them once as Apache Iceberg tables that Snowflake and every other engine can read. This guide does the second.

SQL Server's own change data capture (CDC) feature fills change tables from the transaction log. OLake Go reads those change tables and commits the rows as Iceberg tables into a catalog Snowflake reads. With Snowflake's own Horizon catalog, the default on this page, the files sit on Snowflake-managed storage.

Nothing else is in the path: no Openflow, no Kafka, no Debezium. With AWS Glue, the files sit in your S3 bucket and Snowflake reads them through a catalog integration.

By Shubham Satish BaldavaUpdated October 30, 2026

Key takeaways

  • OLake Go replicates SQL Server into Snowflake as Iceberg tables: a full historical snapshot first, then CDC from the change tables SQL Server already maintains, with no Kafka or Debezium in between.
  • Through the Horizon catalog the tables land as Snowflake-managed Iceberg tables. You query them in Snowsight right away, with no CREATE ICEBERG TABLE, no external volume and no refresh.
  • The same tables, written once by OLake Go, are also queryable from DuckDB and Spark through the same catalog. In the run on this page all three engines returned identical rows.

The short answer. To replicate SQL Server to Snowflake with change data capture (CDC), enable CDC on the database and tables. Run OLake Go with a read-only login as the source and the Snowflake Horizon catalog as the Iceberg destination.

The first sync copies each table in full (the historical snapshot); every later sync replays inserts, updates and deletes from the change tables. Snowflake, DuckDB and Spark then query the same Iceberg tables, with no second copy.

How SQL Server CDC reaches Snowflake here​

Two things happen in order, and OLake Go does both on its own. The first sync records the current log sequence number (LSN). Then it reads each table in parallel chunks and writes Parquet data files: that is the historical load.

Every later sync reads the change tables from the saved LSN and replays the inserts, updates and deletes since. Nothing written during the snapshot is lost, and there is no separate backfill job.

Each sync ends with one commit to Horizon. OLake Go asks Horizon for short-lived storage credentials, uploads the data and delete files, then posts the new snapshot to the catalog. Snowflake sees it the moment the commit lands; DuckDB and Spark see it on their next catalog load.

One thing to keep an eye on: the change tables are SQL Server's replay window, and the Agent is what fills them. The cleanup job drops change rows older than three days by default, so a job paused longer than that needs a fresh snapshot. A stopped Agent means no new change rows at all, with no error on the SQL Server side.

What you need​

  • SQL Server 2016 SP1 or later on Developer, Standard or Enterprise edition, with CDC available and the SQL Server Agent running. The tables on this page ran on SQL Server 2022 Developer in Docker. Hosted SQL Server on Amazon Relational Database Service (RDS), Azure SQL Managed Instance or Cloud SQL works the same way; step 1 names each setup procedure.
  • A Snowflake account where you can run the one-time setup as ACCOUNTADMIN. Snowflake-managed storage means no cloud bucket or IAM role on your side.
  • OLake UI from the Docker Compose install with the OLake Go MSSQL source. The run on this page used v1.1.0.
  • Docker, and about 30 minutes.

Step 1: Prepare SQL Server for CDC​

Most readers point OLake Go at tables they already have. If you want to follow along with the exact data from this page, create these two tables first:

The two sample tables used on this page
Sample schema, database shop
CREATE TABLE dbo.customers (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(100), email NVARCHAR(200), city NVARCHAR(100), tier NVARCHAR(20),
  created_at DATETIME2 DEFAULT SYSUTCDATETIME(),
  updated_at DATETIME2 DEFAULT SYSUTCDATETIME());

CREATE TABLE dbo.orders (
  id INT IDENTITY(1,1) PRIMARY KEY,
  customer_id INT REFERENCES dbo.customers(id),
  product NVARCHAR(100), amount DECIMAL(10,2), status NVARCHAR(20),
  created_at DATETIME2 DEFAULT SYSUTCDATETIME(),
  updated_at DATETIME2 DEFAULT SYSUTCDATETIME());

-- Then insert whatever you like. This page started with 8 customers and 40 orders.

OLake Go reads SQL Server changes from the change tables that SQL Server's own change data capture (CDC) feature fills from the transaction log. Four things must be in place before the first sync.

SettingValueWhy it matters
CDC on the databasesys.sp_cdc_enable_dbCreates the cdc schema and the capture jobs for the database.
CDC on each tablesys.sp_cdc_enable_table per tableCreates the capture instance whose change table OLake Go reads.
SQL Server AgentrunningThe capture job that copies log records into the change tables is an Agent job; without it nothing lands in cdc.*. On Amazon RDS and Azure SQL Managed Instance the agent is on by default.
A login for OLake GoSELECT on the table schema, on the cdc schema and on msdb.dbo.cdc_jobsThe snapshot reads the tables; the change stream reads the change tables; the source test reads the capture job definition. Without the msdb grant the test fails with no SELECT on msdb.dbo.cdc_jobs.
On the SQL Server database
-- 1. CDC on the database (Developer, Standard 2016 SP1+ or Enterprise edition)
USE shop;
EXEC sys.sp_cdc_enable_db;

-- 2. CDC on every table you sync. @role_name = NULL skips the gating role.
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'customers', @role_name = NULL;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'orders',    @role_name = NULL;

-- 3. A login for OLake Go
USE master;
CREATE LOGIN olake WITH PASSWORD = '<password>';
USE shop;
CREATE USER olake FOR LOGIN olake;
GRANT SELECT ON SCHEMA :: dbo TO olake;
GRANT SELECT ON SCHEMA :: cdc TO olake;
GRANT VIEW DATABASE STATE TO olake;

-- 4. OLake Go checks the capture job definition in msdb before each sync
USE msdb;
CREATE USER olake FOR LOGIN olake;
GRANT SELECT ON dbo.cdc_jobs TO olake;

Check the result before moving on:

sql
SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'shop';   -- 1
SELECT capture_instance FROM cdc.change_tables;                        -- dbo_customers, dbo_orders
EXEC msdb.dbo.sp_help_job @job_name = N'cdc.shop_capture';             -- the Agent job exists

Hosted SQL Server uses its own procedure to enable CDC on the database: EXEC msdb.dbo.rds_cdc_enable_db 'shop' on Amazon RDS, EXEC msdb.dbo.gcloudsql_cdc_enable_db 'shop' on Cloud SQL; the table step is the same. The full walk-through is the SQL Server connector page.

Change tables keep three days by default

The cleanup job removes change rows older than three days (sys.sp_cdc_change_job @job_type = 'cleanup' changes it). A job paused longer than that loses its position and needs a fresh snapshot. Lengthen retention before a planned pause.

Step 2: Set up the catalog in Snowflake​

Snowflake reads Iceberg tables from two kinds of catalog. Horizon is Snowflake's own: OLake Go writes straight into it, the tables are Snowflake-managed, and they appear in Snowsight the moment a sync commits.

AWS Glue is a catalog outside Snowflake, so the files stay in your S3 bucket. Snowflake reaches them through a catalog integration: a read-only link to that external catalog, refreshed after each sync.

Horizon is the default on this page. Glue is the right pick when Athena, Redshift or Spark on Amazon Elastic MapReduce (EMR) will read the same tables and you want the files in your own bucket.

CatalogOLake Go writesSnowflake readsVerified on this page
AWS GlueYesYes: catalog integration (read-only)Documented, not yet run by us
Snowflake HorizonYesYes: native: tables are Snowflake-managed Iceberg tablesYes, end to end

Snowflake Horizon is the catalog built into every Snowflake account. OLake Go talks to it through the Iceberg REST endpoint at https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog, and the tables it creates are Snowflake-managed Iceberg tables. Writing through this endpoint needs OLake Go 0.11.0 or later.

Run the following once in a Snowsight worksheet as ACCOUNTADMIN. It creates a database for the tables, a role OLake Go writes as, a service user that holds the token, and the authentication policy the token needs.

Snowflake, once per account
USE ROLE ACCOUNTADMIN;

-- A small warehouse for your own queries, and the database OLake Go writes into.
CREATE WAREHOUSE IF NOT EXISTS OLAKE_WH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
CREATE DATABASE IF NOT EXISTS OLAKE_DEMO;
ALTER DATABASE OLAKE_DEMO SET CATALOG = 'SNOWFLAKE';
ALTER DATABASE OLAKE_DEMO SET EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED';

-- The role OLake Go uses. It needs to create schemas; tables are created inside them.
CREATE ROLE IF NOT EXISTS OLAKE_ROLE;
GRANT USAGE, CREATE SCHEMA ON DATABASE OLAKE_DEMO TO ROLE OLAKE_ROLE;
GRANT USAGE ON WAREHOUSE OLAKE_WH TO ROLE OLAKE_ROLE;
GRANT ROLE OLAKE_ROLE TO ROLE SYSADMIN;

-- A service user for OLake Go. It holds the programmatic access token.
CREATE USER IF NOT EXISTS OLAKE_SVC TYPE = SERVICE DEFAULT_ROLE = OLAKE_ROLE;
GRANT ROLE OLAKE_ROLE TO USER OLAKE_SVC;

-- Snowflake issues a token to a service user only under a network policy, unless an
-- authentication policy waives that. This one waives it. Any schema can hold the policy.
USE SCHEMA OLAKE_DEMO.PUBLIC;
CREATE AUTHENTICATION POLICY IF NOT EXISTS olake_pat_policy
  PAT_POLICY = (NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED);
ALTER USER OLAKE_SVC SET AUTHENTICATION POLICY olake_pat_policy;

-- The two values OLake Go asks for.
SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS account_identifier,
       CURRENT_REGION() AS region;

-- The token. Snowflake shows the secret once; copy it straight into OLake Go.
ALTER USER OLAKE_SVC ADD PROGRAMMATIC ACCESS TOKEN OLAKE_PAT
  ROLE_RESTRICTION = 'OLAKE_ROLE' DAYS_TO_EXPIRY = 30;

What each block is for:

  • CATALOG = 'SNOWFLAKE' and EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED' on the database make every table OLake Go creates a Snowflake-managed Iceberg table on Snowflake's own storage. No S3 bucket, IAM role or external volume on your side. To keep the files in your own bucket, create an external volume and name it here instead.
  • The authentication policy is the step people miss. Without it, the ADD PROGRAMMATIC ACCESS TOKEN line fails with Network Policy is required when creating a programmatic access token for user OLAKE_SVC. In production, attach a network policy that allows OLake Go's egress IP instead of waiving the check.
  • ROLE_RESTRICTION pins the token to OLAKE_ROLE, so a leaked token cannot act as ACCOUNTADMIN. Tokens expire after DAYS_TO_EXPIRY (15 by default, 365 at most). Rotate it before then or the sync fails with an authentication error.

You can check the token outside OLake Go by exchanging it for a session token, the same call OLake Go makes on every sync:

Token check
curl -s -X POST "https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog/v1/oauth/tokens" \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  --data-urlencode grant_type=client_credentials \
  --data-urlencode scope=session:role:OLAKE_ROLE \
  --data-urlencode client_secret=<your PAT>

A good token returns a JSON object with an access_token. The other authentication methods (key-pair JWT, External OAuth, workload identity federation) are on the Horizon catalog page.

Step 3: Connect SQL Server to OLake Go​

  1. In the OLake UI, open Sources and click Create Source. Pick MSSQL as the connector, give the source a name and keep the OLake Go version on the latest release.
  2. Under Change data capture (CDC), leave Enable CDC on. Turn on Manage Capture Instance if you want OLake Go to create a new capture instance by itself after a schema change (it then needs db_owner); leave it off to manage capture instances by hand.
  3. Under Connection details, fill in the host, port, database name, username and password. Keep SSL off for a local test; on hosted SQL Server use the provider's certificate.
  4. Click Set up source. OLake Go tests the connection, then checks that CDC is on for the database and that the tables have capture instances, and shows the current and recommended value for anything that fails.

Here is the source form from the run on this page. SQL Server ran in another container, so the host is host.docker.internal; the login is the one from step 1, with VIEW DATABASE STATE and SELECT on dbo and cdc.

OLake UI Edit source form for MSSQL on OLake Go v1.1.0 with Enable CDC on, host host.docker.internal, port 1433, database shop, user olake and SSL disabled
The SQL Server source in OLake UI: Enable CDC on, with the login from step 1.

Set up source runs the connection test and the CDC checks before anything is saved: CDC on the database, a capture instance per table, a readable capture job. When one fails, the dialog names the setting and what to run.

The full field reference, including SSL modes and SSH tunnels, is on the SQL Server connector page.

Step 4: Configure the Iceberg destination​

  1. Open Destinations and click Create Destination.
  2. Pick Apache Iceberg as the connector and Horizon as the catalog type.
  3. Choose Programmatic Access Token (PAT) as the authentication type and fill in the fields below.
  4. Click Create. OLake Go calls the Horizon endpoint, exchanges the token and saves the destination.
OLake UI destination form for the Snowflake Horizon catalog with the PAT authentication fields

Horizon Configuration Parameters

ParameterSample ValueDescription
Authentication Type
required		Programmatic Access Token (PAT)Authentication method OLake Go uses for Horizon Catalog REST API requests. Set to match your workspace's authentication configuration.
Horizon Iceberg REST Catalog URI
required		https://<account_identifier>.snowflakecomputing.com/polaris/api/catalogSnowflake Horizon Catalog REST API endpoint.
Snowflake Database
required		SNOWThe name of the database in Snowflake.
Session Role Scope
required		session:role:<HORIZON_ROLE>Informs Snowflake which role to use for this Horizon Catalog REST session, which controls privileges for catalog and table operations. Space-separated if providing more than one.
Programmatic Access Token
required		ver:1-hint:...Snowflake programmatic access token (PAT).
Cloud Storage Region
required		AWS_AP_SOUTH_1Snowflake's account region used to route requests to the correct storage endpoint. Run SELECT CURRENT_REGION(); to know the region
Catalog NamehorizonName of the Iceberg catalog OLake Go registers tables under. Defaults to olake_iceberg if left empty.
Enable Arrow WritesfalseWrites data and delete files using Apache Arrow based writer and registers them in Iceberg.
Catalog Name Supported for v0.3.5 and above
For the catalog name, OLake Go only supports lowercase letters and underscores. Spaces and special characters are not supported.

Step 5: Create the job and run the first sync​

  1. Open Jobs and click Create Job. Give it a name and a schedule. Every Day at a fixed hour, or a cron expression, both work; the schedule is how often the change stream is drained, not how often a full copy runs.
  2. Select the source and the destination you created.
  3. Under Advanced Settings, set Target Query Engines to Snowflake, plus DuckDB and Apache Spark if they will read the tables too. OLake Go then resolves an Iceberg delete format every one of those engines can read, so you never pick one by hand.
  4. Click Next. OLake Go discovers the tables and opens the Streams page.
  5. Tick the tables to replicate. For each one, set the sync mode to Full Refresh + CDC and the ingestion mode to Upsert. Leave the Upsert Type as OLake Go resolved it (Positional Deletes in the run on this page).
  6. Click Create Job, then open the job's menu and choose Sync Now for the first run.

Three target query engines were set on this job. That is the only format decision you make: OLake Go works out which Iceberg delete format all three can read and sets it on every stream.

OLake UI Streams Selection page with the dbo schema, customers and orders selected, sync mode Full Refresh + CDC, ingestion mode Upsert and upsert type Positional Deletes on Iceberg format version V2
Each stream: Full Refresh + CDC, Upsert, and the upsert type OLake Go resolved (Positional Deletes in this run). The Iceberg DB name at the top is editable.
OLake UI Job Logs and History for sql_server_to_snowflake showing two completed syncs, the first in 37 seconds and the second in 18 seconds
Job history: the 37 s historical snapshot, then the 18 s change-only sync.

On this page the first run copied 8 customers and 40 orders (48 records, 3.05 KB) and finished in 37 seconds, container start included. OLake Go names the destination schema after the job, the source database and the schema, so sql_server_to_snowflake on database shop produced sql_server_to_snowflake_shop_dbo. The Iceberg DB field at the top of the Streams page changes it before the first sync.

Step 6: Query the tables in Snowflake​

There is no registration step. The schema and tables OLake Go committed through Horizon are already Snowflake-managed Iceberg tables in the database you configured. One thing to know before the first query: OLake Go creates them with quoted, lowercase names, so Snowflake needs the double quotes.

Snowsight
-- What OLake Go created (schema = <job name>_<source database>[_<source schema>])
SHOW ICEBERG TABLES IN SCHEMA OLAKE_DEMO."sql_server_to_snowflake_shop_dbo";

-- Quoted identifiers: the names are lowercase, as they were in the source
SELECT "id", "name", "city", "tier"
FROM OLAKE_DEMO."sql_server_to_snowflake_shop_dbo"."customers"
ORDER BY "id";

SHOW ICEBERG TABLES reports catalog_name = SNOWFLAKE, iceberg_table_type = MANAGED and external_volume_name = SNOWFLAKE_MANAGED: Snowflake treats them as its own managed Iceberg tables, not as external tables that need a refresh.

OLake Go adds four columns to every table: _olake_id (a hash of the primary key), _op_type, _olake_timestamp and _cdc_timestamp. _op_type is r for a row from the first snapshot, c for an insert, u for an update and d for a delete. Filter them out of dashboards, or use them to see when a row last changed.

Snowsight query on OLAKE_DEMO.sql_server_to_snowflake_shop_dbo.customers returning 10 rows with id, name, city and tier
The customers table in Snowsight after the change sync: 10 rows on an X-Small warehouse.

Step 7: Change rows in SQL Server and sync again​

To see the change path work, the run on this page made 12 changes in SQL Server after the first sync:

Changes made in SQL Server between the two syncs
INSERT INTO dbo.customers (name, email, city, tier) VALUES
  (N'Maya Patel', 'maya@example.com', N'Pune', 'silver'),
  (N'Lucas Silva', 'lucas@example.com', N'São Paulo', 'bronze');
INSERT INTO dbo.orders (customer_id, product, amount, status) VALUES
  (9, 'Laptop', 1299.00, 'placed'), (9, 'Mouse', 25.50, 'placed'),
  (10, 'Monitor', 349.99, 'placed'), (3, 'Keyboard', 89.00, 'placed'), (7, 'Webcam', 79.90, 'placed');
UPDATE dbo.customers SET tier = 'gold',       updated_at = SYSUTCDATETIME() WHERE name = N'Liam Carter';
UPDATE dbo.customers SET city = N'Abu Dhabi', updated_at = SYSUTCDATETIME() WHERE name = N'Zara Ali';
UPDATE dbo.orders    SET status = 'delivered', updated_at = SYSUTCDATETIME() WHERE id IN (1, 4, 7);

The second sync read 12 records (855 bytes) from the change tables and finished in 18 seconds end to end. For customers that meant one new data file with 4 rows: 2 inserts plus the new versions of 2 updated rows.

One small delete file retires the 2 old versions. OLake Go's commit report in the sync log shows it directly: addedDataFiles=1, addedRecords=4, addedPositionalDeleteFiles=1, addedPositionalDeletes=2, and orders the same shape with 8 rows and 3 deletes.

OLake UI sync log filtered to the Iceberg commit report, showing addedDataFiles 1, addedPositionalDeleteFiles 1, addedRecords and addedPositionalDeletes for the change-only sync
The change-only sync in the OLake UI log: one data file and one positional delete file per table.

Snowflake shows the result with no refresh. Liam Carter is gold, Zara Ali moved to Abu Dhabi, and the two new customers are there.

The _op_type column shows how each row arrived: r for the snapshot, c for the inserts and u for the updates. The Agent capture job runs every few seconds, so a change made right before a sync starts can land in the next one instead.

Schema changes. A column added in SQL Server is not part of the existing capture instance. Create a new capture instance for the table, or let OLake Go do it with Manage Capture Instance on the source. OLake Go then adds the column to the Iceberg table on the next sync, and Snowflake shows it.

Widening changes are applied; narrowing or incompatible type changes fail the sync rather than corrupt the table. Full rules are on the schema evolution page.

Query the same tables from DuckDB and Spark​

This is the point of writing Iceberg instead of loading Snowflake tables. Nothing is exported, copied or refreshed: both engines read the files OLake Go wrote, through the same Horizon endpoint, with credentials vended by Snowflake.

DuckDB attaches Horizon as an Iceberg REST catalog. It wants a bearer token rather than the raw PAT, so exchange the PAT first (the curl call from the catalog setup), then attach:

DuckDB 1.5.5
INSTALL iceberg; LOAD iceberg;
INSTALL httpfs;  LOAD httpfs;

CREATE SECRET horizon_secret (TYPE iceberg, TOKEN '<access_token from /v1/oauth/tokens>');

ATTACH 'OLAKE_DEMO' AS horizon (
TYPE iceberg,
ENDPOINT 'https://<account_identifier>.snowflakecomputing.com/polaris/api/catalog',
SECRET horizon_secret,
ACCESS_DELEGATION_MODE 'vended_credentials'
);

SHOW ALL TABLES;
SELECT id, name, city, tier FROM horizon.sql_server_to_snowflake_shop_dbo.customers ORDER BY id;

The session token lives about an hour; re-run the exchange and CREATE OR REPLACE SECRET when a query fails with an authorization error. DuckDB reads deletion vectors from duckdb-iceberg 1.5.3 onward, and only attaches REST catalogs backed by S3, S3 Tables or Google Cloud Storage, so pick an AWS- or GCP-hosted Snowflake account for this path.

DuckDB 1.5.5 returned the same 10 customers and the same orders roll-up as Snowflake in the run on this page. It read the current metadata file of each table through Horizon.

An orders roll-up matched SQL Server to the cent in all three engines: 16 delivered orders for 8659.68, 18 placed for 10522.26, 11 shipped for 6397.25.

Data types: SQL Server to Iceberg to Snowflake​

OLake Go maps SQL Server types to Iceberg types, and Snowflake maps Iceberg types to its own. Integers and strings pass through; numbers with a scale and the date types are the groups to know about.

SQL ServerIceberg (written by OLake Go)Snowflake showsNotes
tinyint, smallint, intintNUMBER(10,0)The IDENTITY keys on this page arrived as NUMBER(10,0).
bigintlongNUMBER(19,0)
decimal, numeric, money, smallmoney, floatdoubleFLOATOLake Go stores decimals as doubles. The orders.amount sums on this page matched SQL Server to the cent in every engine; round at query time when you need exact decimal arithmetic.
realfloatFLOAT
bitbooleanBOOLEAN
date, smalldatetime, datetime, datetime2, datetimeoffsettimestamptz (UTC)TIMESTAMP_LTZ(6)datetime2 has no zone in SQL Server; it is read as UTC, so set the session zone before comparing. datetimeoffset is converted to UTC.
char, varchar, nchar, nvarchar, text, ntext, uniqueidentifier, xml, json, sql_variantstringVARCHARA uniqueidentifier arrives as its 36-character text form. Parse json with PARSE_JSON().
binary, varbinary, image, rowversion, geometry, geography, hierarchyidstringVARCHARBinary and spatial values arrive as text; keep large binaries out of the sync.

SQL Server types in the table come from the SQL Server connector reference. The Snowflake side comes from Snowflake's Iceberg data types page (checked 2026-10-30) and from DESCRIBE TABLE on the tables of this run. OLake Go adds _cdc_start_lsn and _cdc_seqval on SQL Server tables, the log position each change came from.

Cost and maintenance​

  • Storage is Snowflake's with SNOWFLAKE_MANAGED: you pay Snowflake storage rates for the Parquet and delete files. Point the database at your own external volume to keep the files in your bucket instead.
  • Compute. OLake Go runs wherever you run Docker; it does not use a Snowflake warehouse to write. Snowflake compute is spent only when you query.
  • Maintenance. Snowflake expires old snapshots and compacts manifests on Snowflake-managed tables at no cost. Automatic data-file compaction is bundled only when Snowflake alone writes the table (Snowflake-managed Iceberg table maintenance). A CDC table that gets a new small file every minute will want compaction from the writer side; OLake Fusion does this for the catalogs OLake Go writes to, and Spark's rewrite_data_files works through the same Horizon endpoint.
  • Change table disk. Every captured change is a row in the cdc schema until the cleanup job removes it. On a busy table three days of changes is real space in the database; watch sys.dm_db_partition_stats for the cdc.*_CT tables and shorten retention once the job runs reliably every hour.
  • Token rotation. The programmatic access token expires (30 days in the setup above, 365 at most). Put the renewal on a calendar; an expired token fails the next sync at the catalog handshake, before any data moves.

Troubleshooting​

1. The source test fails to log in​

failed to connect to MSSQL: failed to ping database: mssql: login error: Login failed for user 'olake'.

The login does not exist on the server, or the password is wrong. CREATE LOGIN runs in master, CREATE USER ... FOR LOGIN runs in the database; both are needed. The run on this page hit this because the login batch had not run, and the fix was the two statements from step 1.

2. The first sync fails on a table you did not create​

failed to run change stream: failed in pre cdc run for driver[mssql]: CDC is not enabled for stream dbo.systranschemas

Enabling CDC or replication on a database adds dbo.systranschemas to it, and discovery lists it next to your tables. It has no capture instance, so a job that selects every stream fails before the snapshot.

Untick it on the Streams page (or any other table without a capture instance), and keep the job to the tables from step 1. The run on this page hit this on its first attempt.

3. The source test passes but no changes arrive​

Two causes. The common one is timing: the capture job copies log records into the change tables every few seconds, so a sync started right after a write can read nothing and the next sync picks the rows up. The run on this page saw exactly that on one attempt.

The other cause is that the SQL Server Agent is not running, so the capture job never copies log records into the change tables. In Docker the agent is off unless the container starts with MSSQL_AGENT_ENABLED=true. On a VM, start the SQLSERVERAGENT service.

EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent' reports Running. when it is up. After that, SELECT count(*) FROM cdc.dbo_customers_CT starts growing a few seconds after the next insert.

4. The next sync cannot resume​

The CDC cleanup job removed the change rows between the saved position and now, because the job was paused longer than the retention (three days by default). Run a fresh full snapshot and raise retention with EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 10080 (minutes, here seven days).

5. Snowflake cannot find the table​

SQL compilation error:
Object 'OLAKE_DEMO.SQL_SERVER_TO_SNOWFLAKE_SHOP_DBO.CUSTOMERS' does not exist or not authorized.

Snowflake upper-cased an unquoted identifier. OLake Go creates the schema and tables with their lowercase names, so quote them: OLAKE_DEMO."sql_server_to_snowflake_shop_dbo"."customers".

6. The destination test fails at the catalog handshake​

OLake Go's destination test exchanges the token at /v1/oauth/tokens before anything else. Three causes cover nearly every failure.

The service user has no network or authentication policy, the scope does not match the token's ROLE_RESTRICTION, or the token expired. Run the curl exchange from the catalog setup; it returns the same error Snowflake gives OLake Go.

How this compares with other ways to move SQL Server data to Snowflake
MethodFreshnessUpdates and deletesWhere the data landsWhat you run
Snowflake Openflow connector for SQL ServerCDC through SQL Server change tracking or CDCYes, as soft deletes plus journal tablesNative Snowflake tables; Snowflake is the only readerAn Openflow deployment in Snowpark Container Services or your own AWS account, billed in Snowflake credits
Debezium, Kafka and the Snowflake Kafka connectorSecondsYes, with the connector's tombstone handlingKafka topics, then Snowflake tablesKafka, Kafka Connect, the Debezium SQL Server connector and a schema registry
Managed ELT (Fivetran, Estuary, Airbyte Cloud)Minutes, by planYesSnowflake tables, a second copy you pay to move and storeNothing; priced per row, per GB or per connector
Manual export (bcp or SSIS to files, then COPY INTO)Hours to dailyNo; full reloads onlyFiles you controlA script and a scheduler
OLake Go to Iceberg on Horizon (this guide)CDC on a schedule down to one minuteYes, as Iceberg row-level deletesApache Iceberg tables that Snowflake, DuckDB, Spark and Trino all readOne Docker Compose stack, open source; Snowflake storage plus your own compute

Openflow is the simpler choice when Snowflake is the only reader and you want Snowflake to run the pipeline. Writing Iceberg once avoids paying to move and store a second copy as soon as another engine needs the same rows, or you want to own the files.

When not to use this​

  • The edition has no CDC. SQL Server Express and Web cannot enable change data capture, and Azure SQL Database needs the S3 tier or above. Full Refresh and Full Refresh + Incremental on an updated_at column still work there.
  • Your Snowflake account is on Azure and DuckDB is a reader. DuckDB attaches REST catalogs backed by S3, S3 Tables and Google Cloud Storage only, so pick Spark or Trino as the second engine there.
  • Tables have no primary key. Inserts replicate; updates and deletes cannot be matched to a row and are not applied. Add a primary key or a unique index first.

FAQs​

How do I replicate SQL Server to Snowflake without Openflow or a Kafka pipeline?

Run OLake Go (open source, Apache 2.0) against a SQL Server database with change data capture enabled, and point it at Snowflake's Horizon catalog. The first sync copies the tables in full, every later sync reads only the rows SQL Server's capture job wrote to the change tables, and the tables appear in Snowflake as Snowflake-managed Iceberg tables. No Openflow, Kafka, Debezium or Snowflake connector sits in the path.

Does OLake Go copy the existing SQL Server data before it reads the change tables?

Yes, automatically. A job in Full Refresh + CDC mode records the current log sequence number, copies every selected table in parallel chunks, then reads the change tables from that position. Nothing written during the copy is lost, and there is no separate backfill job.

What does SQL Server need for OLake Go CDC?

Change data capture on the database and on each table, which needs Developer, Standard (2016 SP1 or later) or Enterprise edition, plus a running SQL Server Agent to fill the change tables. The OLake Go login needs SELECT on the table schema and on the cdc schema. Amazon RDS and Azure SQL Managed Instance expose the same feature through their own stored procedures.

Does SQL Server CDC with OLake Go work on Amazon RDS, Azure SQL or Cloud SQL?

Yes. On RDS for SQL Server, enable CDC with msdb.dbo.rds_cdc_enable_db; on Cloud SQL, with msdb.dbo.gcloudsql_cdc_enable_db; Azure SQL Managed Instance and SQL Server on a VM take the standard sys.sp_cdc_enable_db. Azure SQL Database also supports CDC on the S3 tier and above. The table step and the login are the same everywhere.

Which SQL Server data types change on the way to Snowflake?

decimal, numeric, money and float columns become Iceberg double and show up in Snowflake as FLOAT, so round at query time when you need exact decimal arithmetic. datetime, datetime2, smalldatetime and date become timestamps in UTC (Snowflake TIMESTAMP_LTZ). nvarchar, varchar, uniqueidentifier, xml, json and the binary types arrive as VARCHAR. bit becomes BOOLEAN; int and bigint map one to one.

How long does SQL Server keep change rows for OLake Go?

Three days by default. The CDC cleanup job removes change rows older than its retention setting, and a job paused for longer than that cannot resume and needs a fresh snapshot. Raise retention with sys.sp_cdc_change_job before a planned pause, and keep an eye on the size of the cdc schema.

Can I use AWS Glue instead of the Horizon catalog?

Yes. With Glue as the catalog the files stay in your S3 bucket, Athena, Redshift and EMR read them natively, and Snowflake reads them through a catalog integration as read-only tables you refresh after each sync. Horizon is the better default when Snowflake is the main reader, because the tables are Snowflake-managed and need no refresh.

SQL Server to ClickHouseSame source, read from ClickHouse.SQL Server to BigQuerySame source, read from BigQuery.SQL Server to Amazon RedshiftSame source, read from Amazon Redshift.SQL Server to DatabricksSame source, read from Databricks.SQL Server to Apache IcebergSame source, read from Apache Iceberg.Postgres to SnowflakeSame engine, replicated from Postgres.MySQL to SnowflakeSame engine, replicated from MySQL.MongoDB to SnowflakeSame engine, replicated from MongoDB.Oracle to SnowflakeSame engine, replicated from Oracle.DB2 to SnowflakeSame engine, replicated from DB2.MariaDB to SnowflakeSame engine, replicated from MariaDB.Kafka to SnowflakeSame engine, replicated from Kafka.S3 to SnowflakeSame engine, replicated from S3.SQL Server connector referenceEvery field, sync mode and troubleshooting note for the source.Query engine compatibilityWhich engines read which catalogs and delete formats.All integrationsEvery source and engine pair with a tested guide.

Deeper reading on this site: