Skip to main content

SQL Server to ClickHouse: CDC via Apache Iceberg

SQL Server to ClickHouse usually means Debezium through Kafka into the ClickHouse Kafka engine, a nightly bcp export, or a managed extract, load and transform (ELT) tool, and tables that only ClickHouse can read. This guide takes a different route.

SQL Server's own change data capture (CDC) feature fills change tables from the transaction log. OLake Go reads them and commits the rows as Apache Iceberg tables into an Apache Polaris catalog, and ClickHouse attaches that catalog as a database.

Polaris is an open source Iceberg REST (Representational State Transfer) catalog you run yourself, with the files in your own bucket. Nothing else is in the path: no Kafka, no Debezium, no ClickHouse Cloud. DuckDB and Trino read the same tables through the same endpoint.

By Shubham Satish BaldavaUpdated October 30, 2026

Key takeaways

  • OLake Go replicates SQL Server into Iceberg tables in Polaris: a full historical snapshot first, then CDC from the change tables SQL Server already maintains, with no Kafka or Debezium in between.
  • ClickHouse attaches the whole Polaris catalog as one database with DataLakeCatalog, reads the files with the credentials Polaris vends, and sees every change the moment a sync commits.
  • The same tables, written once by OLake Go, are also queryable from DuckDB and Trino through the same catalog. In the run on this page all three engines returned identical rows.

The short answer. To replicate SQL Server to ClickHouse with change data capture (CDC), enable CDC on the database and tables. Run OLake Go with a read-only login as the source and an Apache Polaris 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. ClickHouse, DuckDB and Trino then query the same Iceberg tables, with no second copy.

How SQL Server CDC reaches ClickHouse 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 Polaris. OLake Go asks Polaris for short-lived storage credentials, uploads the data and delete files, then posts the new snapshot to the catalog. ClickHouse, DuckDB and Trino see it on their next query; none of them keeps a copy.

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.
  • An Apache Polaris server and a bucket, both reachable from OLake Go and from ClickHouse. The run on this page used Polaris 1.8.0 and MinIO in Docker; on AWS, use an S3 bucket and an IAM role instead.
  • ClickHouse 26.1 or later, where the DataLakeCatalog engine reads REST catalogs. The run on this page used the 26.8 LTS release in Docker.
  • 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 40 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 Polaris catalog​

ClickHouse reads Iceberg tables through a catalog. Apache Polaris is the one this page uses: an open source REST catalog that you run next to your bucket.

OLake Go writes into it, and ClickHouse, DuckDB and Trino read from it with the credentials Polaris hands out. The Iceberg files sit in your own S3 or MinIO bucket; Polaris holds only the pointers and the permissions.

CatalogOLake Go writesClickHouse readsVerified on this page
Apache PolarisYesYes: DataLakeCatalog with catalog_type restYes, end to end

Apache Polaris is an open source Iceberg REST catalog. It keeps the table metadata pointers, hands out short-lived storage credentials to engines, and applies its own role-based access control. OLake Go writes to it through the standard Iceberg REST API, and ClickHouse, Trino, DuckDB and Spark read from the same endpoint. The files live in an S3 bucket or an S3-compatible store that you own.

Three things need to exist before OLake Go can write: a Polaris server, a catalog inside it that points at your bucket, and a principal (a client id and secret) with the right to manage that catalog. The run on this page used Polaris 1.8.0 and MinIO in Docker; on AWS, swap the MinIO endpoint for a plain S3 bucket and an IAM role.

docker-compose.yml (Polaris and MinIO, for a local test)
services:
  minio:
    image: minio/minio
    hostname: lake-minio
    command: server /data --console-address ":9001"
    environment: { MINIO_ROOT_USER: minioadmin, MINIO_ROOT_PASSWORD: minioadmin }
    ports: ["9000:9000", "9001:9001"]
  polaris:
    image: apache/polaris:latest
    depends_on: [minio]
    ports: ["8181:8181", "8182:8182"]
    environment:
      AWS_REGION: us-east-1
      AWS_ACCESS_KEY_ID: minioadmin
      AWS_SECRET_ACCESS_KEY: minioadmin
      POLARIS_BOOTSTRAP_CREDENTIALS: POLARIS,root,<root-secret>
      polaris.realm-context.realms: POLARIS

Polaris stores its own state in memory with this setup, so it starts empty every time; a production deployment points it at Postgres. Once it is up, create the bucket, the catalog and the principal through the management API. root is the bootstrap principal; the olake principal is the one OLake Go and the engines use.

Polaris, once
P=http://localhost:8181

# 1. A bucket for the tables (MinIO; on AWS create it in the console or with aws s3 mb)
aws --endpoint-url http://localhost:9000 s3 mb s3://lakehouse

# 2. A root token
TOKEN=$(curl -s $P/api/catalog/v1/oauth/tokens -H 'Polaris-Realm: POLARIS' \
  -d grant_type=client_credentials -d client_id=root -d client_secret=<root-secret> \
  -d scope=PRINCIPAL_ROLE:ALL | jq -r .access_token)
H=(-H "Authorization: Bearer $TOKEN" -H 'Polaris-Realm: POLARIS' -H 'Content-Type: application/json')

# 3. The catalog: where the files go, and how Polaris and the engines reach the store
curl -s "${H[@]}" $P/api/management/v1/catalogs -d '{"catalog": {
  "name": "olake", "type": "INTERNAL", "readOnly": false,
  "properties": {"default-base-location": "s3://lakehouse/olake"},
  "storageConfigInfo": {"storageType": "S3", "allowedLocations": ["s3://lakehouse/olake"],
    "endpoint": "http://<minio-host>:9000/", "endpointInternal": "http://lake-minio:9000",
    "pathStyleAccess": true, "region": "us-east-1"}}}'

# 4. A principal for OLake Go and the engines. The response holds clientId and clientSecret: keep them.
curl -s "${H[@]}" $P/api/management/v1/principals -d '{"principal": {"name": "olake"}}'

# 5. Roles and the grant that lets the principal create namespaces and tables in the catalog
curl -s "${H[@]}" $P/api/management/v1/principal-roles -d '{"principalRole": {"name": "olake_role"}}'
curl -s "${H[@]}" $P/api/management/v1/catalogs/olake/catalog-roles -d '{"catalogRole": {"name": "olake_catalog_role"}}'
curl -s -X PUT "${H[@]}" $P/api/management/v1/principals/olake/principal-roles -d '{"principalRole": {"name": "olake_role"}}'
curl -s -X PUT "${H[@]}" $P/api/management/v1/principal-roles/olake_role/catalog-roles/olake -d '{"catalogRole": {"name": "olake_catalog_role"}}'
curl -s -X PUT "${H[@]}" $P/api/management/v1/catalogs/olake/catalog-roles/olake_catalog_role/grants -d '{"type": "catalog", "privilege": "CATALOG_MANAGE_CONTENT"}'

What each block is for:

  • endpoint is the store address Polaris hands to engines along with vended credentials; endpointInternal is the address Polaris itself uses. On AWS S3 leave both out. On MinIO both are required, or Polaris tries to reach AWS and fails. Keep the trailing slash on endpoint: ClickHouse 26.8 and 26.10 build the object path from it, and without the slash every read fails with a 404 (the fix is explained under troubleshooting on this page).
  • Polaris vends credentials through the store's STS endpoint. MinIO exposes one, so nothing else is needed. For a store without STS, add "stsUnavailable": true and give Polaris static keys to hand out.
  • CATALOG_MANAGE_CONTENT lets the principal create namespaces and tables and read and write their data. Scope it to a namespace instead when several teams share the catalog.
  • The principal's secret is shown once, in the response to step 4. It is what goes into OLake Go as <client_id>:<client_secret>.
Hostnames with underscores break Polaris

Polaris is a Java service, and Java's URI parser rejects hostnames that contain an underscore. A MinIO container named lab_minio makes every table creation fail with host must not be null or Expected scheme-specific part at index 5: http:. Use a hyphen in the hostname (lake-minio), as above.

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

Token check
curl -s http://localhost:8181/api/catalog/v1/oauth/tokens -H 'Polaris-Realm: POLARIS' \
  -d grant_type=client_credentials -d client_id=<client_id> -d client_secret=<client_secret> \
  -d scope=PRINCIPAL_ROLE:ALL

A good principal returns a JSON object with an access_token. Running Polaris on Kubernetes, with Postgres behind it, or with an external identity provider is covered on the Polaris 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, cdc and msdb.dbo.cdc_jobs.

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 Polaris as the catalog type.
  3. Choose OAuth2 as the authentication type. REST Catalog URI is the Polaris catalog endpoint (http://<polaris-host>:8181/api/catalog), Polaris Catalog Name the catalog from the setup (olake), REST Auth URI the token endpoint, and REST Credential the principal's <client_id>:<client_secret>.
  4. Fill in the store: AWS Region, and for MinIO the S3 Endpoint with the AWS Access Key and AWS Secret Key. On AWS S3 leave the endpoint empty.
  5. Click Create. OLake Go exchanges the credentials for a token, creates a namespace and a test table through Polaris, writes to the bucket and drops the test table again.

Here is the destination form from the run on this page. Polaris and MinIO ran in other containers, so both addresses use host.docker.internal; the credential is the olake principal from step 2.

OLake UI Edit destination form for Apache Iceberg with catalog type Polaris, authentication type OAuth2, REST catalog URI http://host.docker.internal:8181/api/catalog, Polaris catalog name olake, S3 endpoint http://host.docker.internal:9000, REST scope PRINCIPAL_ROLE:ALL and region us-east-1
The Polaris destination in OLake UI: OAuth2 with the principal from step 2, and the MinIO endpoint for the files.

Polaris Configuration Parameters

ParameterSample ValueDescription
REST Catalog URI
required		http://<REST_ENDPOINT>:8181/api/catalogEndpoint URL for the Polaris REST catalog service.
Authentication Type
required		oauth2Authentication method OLake Go uses for Polaris REST catalog requests. Set to match your catalog's authentication configuration.
Polaris Catalog Name
required		<POLARIS_CATALOG_NAME>Name of the Polaris catalog to use, OLake Go passes this to the Polaris REST catalog as the warehouse identifier, so catalog operations are routed to that catalog and its pre-configured S3 storage and IAM settings.
REST Auth URI
required		http://<REST_ENDPOINT>:8181/api/catalog/v1/oauth/tokensURL of the REST catalog’s OAuth2 token endpoint. OLake Go uses this with REST Credential to request an access token during OAuth2 client-credentials authentication.
REST Credential
required		<olake_user_client_id>:<olake_user_client_secret>Specifies the client ID and secret for OAuth2, formatted as client_id:client_secret. Used with REST Auth URI when OLake Go requests an access token from the catalog’s auth service. Not needed when using REST Token or other auth types.
AWS Region
required		ap-south-1AWS Region where your S3 bucket exists. Required so OLake Go calls the correct regional S3 endpoint.
REST ScopePRINCIPAL_ROLE:ALLRequired for OAuth2 client-credentials auth. Scopes are space-separated. Used with REST Auth URI and REST Credential for OAuth2 client-credentials auth. Values are catalog-specific. Not needed when using REST Token or other auth types.
S3 Endpointhttp://<S3_ENDPOINT>:9000S3 API endpoint for writing Iceberg data files. Leave empty for AWS S3 but required for S3-compatible storage like MinIO.
AWS Access KeyXXXAWS access key ID for authentication. Optional if using IAM roles or instance profiles.

If using IAM:
Docker Compose: add the required IAM environment variables under x-envs in your compose file. See Service Environment Variables for setup details.
Kubernetes: set up pod IAM in Cloud Integration.
AWS Secret KeyXXXAWS secret access key for authentication. Optional if using IAM roles or instance profiles.

If using IAM:
Docker Compose: add the required IAM environment variables under x-envs in your compose file. See Service Environment Variables for setup details.
Kubernetes: set up pod IAM in Cloud Integration.
Enable Arrow WritesfalseWrites data and delete files using Apache Arrow based writer and registers them in Iceberg.
Catalog Nameolake_icebergName of the Iceberg catalog OLake Go registers tables under. Defaults to olake_iceberg if left empty.
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 ClickHouse, plus DuckDB and Trino 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 (Equality 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.

With ClickHouse in the list it chose equality deletes, which need no index between runs. Leave systranschemas unticked; troubleshooting entry 2 explains why.

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

On this page the first run copied 8 customers and 40 orders (48 records, 3.05 KB) and finished in 17 seconds, container start included. OLake Go names the destination namespace after the job, the source database and the schema, so sql_server_to_clickhouse on database shop produced sql_server_to_clickhouse_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 ClickHouse​

There is no registration step. ClickHouse attaches the whole Polaris catalog as one database with the DataLakeCatalog engine, lists every table OLake Go committed, and reads the files with the credentials Polaris vends. One thing to know before the first query: ClickHouse has one level of namespace, so the Iceberg namespace and table name go together inside backticks.

clickhouse-client
SET allow_database_iceberg = 1;

CREATE DATABASE polaris
ENGINE = DataLakeCatalog('http://<polaris-host>:8181/api/catalog')
SETTINGS
catalog_type = 'rest',
warehouse = 'olake',
catalog_credential = '<client_id>:<client_secret>',
oauth_server_uri = 'http://<polaris-host>:8181/api/catalog/v1/oauth/tokens',
auth_scope = 'PRINCIPAL_ROLE:ALL',
vended_credentials = true,
region = 'us-east-1';

SHOW TABLES FROM polaris;

-- What OLake Go created (namespace = <job name>_<source database>_<source schema>)
SELECT id, name, city, tier
FROM polaris.`sql_server_to_clickhouse_shop_dbo.customers`
ORDER BY id;

vended_credentials = true makes ClickHouse read the files with the short-lived keys Polaris hands out per table, so no store keys sit in ClickHouse. To use static keys instead, replace it with storage_endpoint, aws_access_key_id and aws_secret_access_key. The database is read-only: INSERT, ALTER and RENAME DATABASE return NOT_IMPLEMENTED.

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.

clickhouse-client querying polaris.sql_server_to_clickhouse_shop_dbo.customers through the DataLakeCatalog engine and returning 10 rows with id, name, city and tier
ClickHouse 26.8 reading the customers table through Polaris after the change sync: 10 rows, one per key.

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 9 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 equality delete file carries a delete for each of the 4 keys it wrote, inserts included, because an equality delete costs nothing when no older row matches.

OLake Go's commit report in the sync log shows it directly: addedDataFiles=1, addedRecords=4, addedEqualityDeleteFiles=1, addedEqualityDeletes=4. The orders table has the same shape with 8 rows and 8 deletes.

OLake UI sync log filtered to the Iceberg commit reports for customers and orders, showing addedDataFiles 1, addedEqualityDeleteFiles 1, addedRecords 4 and 8, and addedEqualityDeletes 4 and 8
The change-only sync in the OLake UI log: one data file and one equality delete file per table.

ClickHouse shows the result on the next query, with no refresh. Liam Carter is gold, Zara Ali moved to Abu Dhabi, and the two new customers are there. The two retired versions are gone, because ClickHouse applies the equality deletes while reading.

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 ClickHouse shows it the next time the table is opened.

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 Trino​

This is the point of writing Iceberg instead of loading ClickHouse tables. Nothing is exported, copied or refreshed: both engines read the files OLake Go wrote, through the same Polaris endpoint.

DuckDB attaches Polaris as an Iceberg REST catalog with the principal's client id and secret; DuckDB fetches the token itself. On a laptop, where the store address Polaris hands out may not resolve, add an S3 secret for the store and turn credential vending off:

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

CREATE SECRET polaris_secret (
TYPE iceberg,
CLIENT_ID '<client_id>', CLIENT_SECRET '<client_secret>',
OAUTH2_SERVER_URI 'http://<polaris-host>:8181/api/catalog/v1/oauth/tokens',
OAUTH2_SCOPE 'PRINCIPAL_ROLE:ALL'
);
-- MinIO, or any store whose address differs from the one Polaris vends
CREATE SECRET store_secret (
TYPE s3, KEY_ID '<access key>', SECRET '<secret key>',
ENDPOINT '<minio-host>:9000', URL_STYLE 'path', USE_SSL false, REGION 'us-east-1'
);

ATTACH 'olake' AS polaris (
TYPE iceberg,
ENDPOINT 'http://<polaris-host>:8181/api/catalog',
SECRET polaris_secret,
ACCESS_DELEGATION_MODE 'none'
);

SHOW ALL TABLES;
SELECT id, name, city, tier FROM polaris.sql_server_to_clickhouse_shop_dbo.customers ORDER BY id;

With Polaris on AWS S3, drop the S3 secret and the ACCESS_DELEGATION_MODE line: DuckDB then reads with the credentials Polaris vends. DuckDB reads equality and positional deletes, and deletion vectors from duckdb-iceberg 1.5.3 onward.

DuckDB 1.5.5 querying polaris.sql_server_to_clickhouse_shop_dbo.customers and returning the same 10 rows as ClickHouse
DuckDB 1.5.5 reading the same table through Polaris: the same 10 rows.

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

Data types: SQL Server to Iceberg to ClickHouse​

OLake Go maps SQL Server types to Iceberg types, and ClickHouse 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)ClickHouse showsNotes
tinyint, smallint, intintNullable(Int32)The IDENTITY keys on this page arrived as Int32.
bigintlongNullable(Int64)
decimal, numeric, money, smallmoney, floatdoubleNullable(Float64)OLake 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.
realfloatNullable(Float32)
bitbooleanNullable(Bool)
date, smalldatetime, datetime, datetime2, datetimeoffsettimestamptz (UTC)Nullable(DateTime64(6, 'UTC'))datetime2 has no zone in SQL Server; it is read as UTC. datetimeoffset is converted to UTC.
char, varchar, nchar, nvarchar, text, ntext, uniqueidentifier, xml, json, sql_variantstringNullable(String)A uniqueidentifier arrives as its 36-character text form. Parse json with JSONExtract.
binary, varbinary, image, rowversion, geometry, geography, hierarchyidstringNullable(String)Binary 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 ClickHouse side comes from the ClickHouse Iceberg table engine page (checked 2026-10-30) and from DESCRIBE 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 your bucket: you pay S3 or MinIO rates for the Parquet and delete files, and nothing to Polaris or ClickHouse for holding them.
  • Compute. OLake Go runs wherever you run Docker. ClickHouse spends CPU only when you query; a DataLakeCatalog query reads Parquet over the network with no MergeTree index, so load a hot slice into a MergeTree table when a dashboard needs sub-second answers.
  • Maintenance. Nothing compacts the tables for you. A CDC table that gets a new small file and delete file per sync wants compaction; OLake Fusion does this for the catalogs OLake Go writes to, and Spark's rewrite_data_files works through the same Polaris 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; shorten retention once the job runs reliably every hour.
  • Credential rotation. Polaris principals do not expire by default. Rotate the olake principal's secret on your own schedule with the management API; a rotated secret fails the next sync at the token exchange, before any data moves.

Troubleshooting​

1. The source test fails on the capture job​

required CDC prerequisites not met: cdc_capture_job ... no SELECT on msdb.dbo.cdc_jobs

OLake Go reads the capture job definition from msdb to pick a safe start position. Create a user for the login in msdb and grant SELECT ON dbo.cdc_jobs, as in step 1. The run on this page hit this before the grant was added.

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, along with any other table without a capture instance.

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 other cause is that the SQL Server Agent is not running, so the change tables never fill.

In Docker the agent is off unless the container starts with MSSQL_AGENT_ENABLED=true. EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent' reports Running. when it is up.

4. ClickHouse lists the tables but every query returns a 404​

Code: 499. DB::Exception: Failed to get object info for key olake/.../metadata/00003-....metadata.json in bucket lakehouse: No response body.. HTTP response code: 404

The object key is doubled. ClickHouse 26.8 and 26.10 join the table location onto the storage endpoint, then fail to strip it again when the endpoint has no trailing slash. Set the Polaris catalog's endpoint (and storage_endpoint in ClickHouse, if you use static keys) to a value ending in /, for example http://minio:9000/.

5. The destination test fails at the token exchange​

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

The client id or secret is wrong, the principal has no role that carries CATALOG_MANAGE_CONTENT on the catalog, or the Polaris Catalog Name is wrong. Run the token check from the catalog setup; it returns the same error Polaris gives OLake Go.

How this compares with other ways to move SQL Server data to ClickHouse
MethodFreshnessUpdates and deletesWhere the data landsWhat you run
Debezium, Kafka and the ClickHouse Kafka engineSecondsYes, with your own merge logicKafka topics, then ClickHouse tablesKafka, Kafka Connect, the Debezium SQL Server connector and a materialized view per table
ClickHouse ODBC table function against SQL ServerLive, per queryReads the current rowNothing is copied; every query hits SQL ServerAn ODBC driver and DSN on every ClickHouse server
Export (bcp or SSIS to files), then INSERT ... SELECT FROM s3(...)Hours to dailyNo; full reloads onlyNative ClickHouse tablesA script, a scheduler and a staging bucket
Managed ELT (Fivetran, Estuary, Airbyte Cloud)Minutes, by planVaries by toolClickHouse tables, a second copy you pay to move and storeNothing; priced per row, per GB or per connector
OLake Go to Iceberg on Polaris (this guide)CDC on a schedule down to one minuteYes, as Iceberg row-level deletesApache Iceberg tables that ClickHouse, DuckDB, Trino and Spark all readOne Docker Compose stack, open source; your bucket plus your own compute

Debezium through Kafka is the simpler choice when you need seconds of latency into MergeTree and already run Kafka. Writing Iceberg once avoids paying to move and store a second copy. That matters as soon as another engine needs the same rows, or you want to own the files and the catalog.

When not to use this​

  • Every query must hit MergeTree speed. A DataLakeCatalog query reads Parquet with no primary index. Keep the Iceberg tables as the source and load the hot slice into a MergeTree table on a schedule.
  • 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.
  • 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 ClickHouse without Kafka or ClickPipes?

Run OLake Go (open source, Apache 2.0) against a SQL Server database with change data capture enabled, and point it at an Apache Polaris 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 ClickHouse reads the result as Iceberg tables through its DataLakeCatalog engine. No Kafka, no Debezium and no ClickHouse Cloud account sit in the path.

Does ClickHouse read Iceberg tables with row-level deletes?

Yes. ClickHouse reads positional deletes, and equality deletes from 25.8. The run on this page used equality deletes, which OLake Go chose because ClickHouse, DuckDB and Trino all read them, and ClickHouse 26.8 returned one row per key after the updates. Deletion vectors (Iceberg v3) are not read by the 26.8 LTS release.

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, on the cdc schema and on msdb.dbo.cdc_jobs. Amazon RDS and Azure SQL Managed Instance expose the same feature through their own stored procedures.

Why Apache Polaris rather than a ClickHouse table?

A Polaris catalog is one open endpoint that ClickHouse, DuckDB, Trino and Spark all read, with its own access control and vended storage credentials. Loading into a ClickHouse MergeTree table is faster to query but makes ClickHouse the only reader, and a second engine means a second copy. Keep the Iceberg tables as the shared source and load hot slices into MergeTree when a dashboard needs it.

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

decimal, numeric, money and float columns become Iceberg double and show up in ClickHouse as Float64, so round at query time when you need exact decimal arithmetic. datetime, datetime2, smalldatetime and date become DateTime64 in UTC. nvarchar, varchar, uniqueidentifier, xml, json and the binary types arrive as String. bit becomes Bool; int and bigint map to Int32 and Int64.

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.

SQL Server to SnowflakeSame source, read from Snowflake.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 ClickHouseSame engine, replicated from Postgres.MySQL to ClickHouseSame engine, replicated from MySQL.MongoDB to ClickHouseSame engine, replicated from MongoDB.DB2 to ClickHouseSame engine, replicated from DB2.Kafka to ClickHouseSame engine, replicated from Kafka.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: