Skip to main content

SQL Server to BigQuery: CDC via Apache Iceberg

SQL Server to BigQuery usually means a Datastream stream, a Kafka pipeline, or a nightly bcp export and bq load. This guide takes a different route. SQL Server's own change data capture (CDC) feature fills change tables from the transaction log, and OLake Go commits those rows as Apache Iceberg tables into a BigLake catalog, with the files in your own Cloud Storage bucket.

BigLake is Google Cloud's Iceberg representational state transfer (REST) catalog. BigQuery queries the tables through it with a four-part name, with no dataset to create and no load job, and DuckDB and Spark read the same tables.

By Shubham Satish BaldavaUpdated October 30, 2026

Key takeaways

  • OLake Go replicates SQL Server into Iceberg tables in a BigLake catalog: a full historical snapshot first, then CDC from the change tables SQL Server already maintains, with no Datastream or Kafka in between.
  • BigQuery reads the catalog as a data source. Every change a sync commits is visible on the next SELECT, with no refresh.
  • The same tables, written once by OLake Go, are also queryable from DuckDB and Spark. In the run on this page all three engines returned identical rows.

The short answer. To replicate SQL Server to BigQuery with change data capture (CDC), enable CDC on the database and tables. Run OLake Go with a read-only login as the source and a BigLake catalog with a Cloud Storage bucket as the Iceberg destination, with BigQuery as a target query engine. The first sync copies each table in full (the historical snapshot); every later sync replays inserts, updates and deletes from the change tables.

How SQL Server CDC reaches BigQuery 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 reads each table in parallel chunks and writes Parquet data files. Every later sync reads the change tables from the saved LSN and replays the inserts, updates and deletes since.

Each sync ends with one commit to BigLake. OLake Go uploads the data and delete files with the service account's own credentials, then updates the table's metadata pointer in the catalog. BigQuery, DuckDB and Spark see the new snapshot 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. Cloud SQL for SQL Server works the same way; step 1 names its setup procedure.
  • A Google Cloud project with the BigLake API enabled, a Cloud Storage bucket for the files, and a service account for OLake Go with the BigLake Editor role on the project and Storage Object User on the bucket. The run on this page used a bucket in us-central1.
  • BigQuery in the same project. Queries run in the bucket's region, and the account running them needs the BigQuery Job User role plus read access to the catalog.
  • OLake UI from the Docker Compose install with the OLake Go MSSQL source. The run on this page used v1.1.0.

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 BigLake catalog​

BigQuery reads Iceberg tables through a catalog. BigLake is the one this page uses: Google Cloud's Iceberg REST catalog, with one namespace per job and the files in your Cloud Storage bucket.

DuckDB and Spark read from the same catalog. To BigQuery it is a data source in the project, so a table OLake Go commits is queryable on the next statement.

CatalogOLake Go writesBigQuery readsVerified on this page
BigLake (Google Cloud Lakehouse)YesYes: native: namespaces map to BigQuery datasetsYes, end to end

BigLake is the Iceberg REST catalog built into Google Cloud's Lakehouse. OLake Go talks to it at https://biglake.googleapis.com/iceberg/v1/restcatalog, the table files land in a Cloud Storage bucket you own, and BigQuery reads the tables without any registration step. Writing to BigLake needs OLake Go 0.9.1 or later.

Everything below happens in the Google Cloud console, in the project that will hold the tables.

  1. Enable the API. Open APIs and services, then Enable APIs and services, and enable the BigLake API. BigQuery and Cloud Storage are on by default in most projects.
  2. Create a bucket. In Cloud Storage, create a bucket in the region where BigQuery will run the queries (us-central1 on this page). The bucket name becomes the catalog name in BigQuery, so pick one you are happy to type in SQL.
  3. Create a service account for OLake Go. In IAM and Admin, Service Accounts, create one (for example olake-biglake). Then grant it two roles: BigLake Editor on the project (IAM, Grant access, the service account as principal) and Storage Object User on the bucket (the bucket's Permissions tab).
  4. Download a JSON key. On the service account's Keys tab, Add key, Create new key, JSON. The file downloads once; OLake Go asks for its contents.
Google Cloud console IAM Grant access panel with the OLake service account as principal and the BigLake Editor role selected
The project-level grant: the service account OLake Go runs as, with the BigLake Editor role.

There is no catalog to create. BigLake creates a single-bucket catalog named after the bucket the first time OLake Go calls the endpoint with gs://<bucket> as the catalog path, in the default end-user credential mode, where every client brings its own storage credentials. The catalog shows up under BigLake, Catalogs after the first connection test.

Two things to know before the first sync:

  • The service account's BigLake Editor role is what lets OLake Go create namespaces and commit tables. Storage Object User on the bucket is what lets it write the Parquet and metadata files. Missing either one fails the connection test with a 403 from biglake.googleapis.com or from storage.googleapis.com, which tells you which grant is missing.
  • Leave the catalog in end-user mode. OLake Go 1.1.0 does not send the X-Iceberg-Access-Delegation header, so a catalog switched to vended credentials rejects its commits (see troubleshooting). Readers that want short-lived tokens from the catalog can switch the mode later with gcloud beta biglake iceberg catalogs update <bucket> --credential-mode=vended-credentials, after setting rest_access_delegation in a CLI config.
Google Cloud console Create private key dialog for the OLake service account with the JSON key type selected
The JSON key for the service account. The console downloads it once; its contents go into the OLake Go destination form.

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 BigLake as the catalog type.
  3. Fill in the fields below. The catalog path is the bucket, with no folder after it. Paste the whole service account JSON file into the credentials field.
  4. Click Create. OLake Go calls the BigLake endpoint, creates the catalog if it is the first contact, and writes and drops a test table in the bucket.

Here is the destination form from the run on this page. It holds the BigLake endpoint, the bucket as the catalog path, the service account JSON and the project ID.

OLake UI Edit destination form for Apache Iceberg with catalog type Big Lake, REST catalog URI https://biglake.googleapis.com/iceberg/v1/restcatalog, BigLake catalog path gs://olake-x2y-lab-bq, authentication type GCP and the project ID oauth-361809
The BigLake destination in OLake UI: the endpoint, the bucket as catalog path, GCP authentication and the project ID.
FieldValue on this pageWhat it is
REST Catalog URIhttps://biglake.googleapis.com/iceberg/v1/restcatalogThe BigLake Iceberg REST endpoint. The same for every project and region.
BigLake Catalog Pathgs://<bucket>The bucket that holds the tables. The bucket name is also the catalog name BigQuery uses. Bucket root only; a folder after it is not supported.
Authentication TypeGCPGoogle OAuth through the service account. The only choice for BigLake.
GCP Service Account JSONthe key file contentsUsed for both the catalog calls and the Cloud Storage writes.
Catalog Nameolake_icebergA label OLake Go uses in its own logs. Lowercase letters and underscores only.
GCP Auth Scopeshttps://www.googleapis.com/auth/cloud-platformThe OAuth scope on the token. Leave the default.
GCP Project IDthe project IDRoutes and bills the catalog calls. Required for multi-bucket catalogs, harmless to set for a single bucket.
Enable Arrow WritesoffKeeps the default Java writer, which is what this page was tested with.

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

This job lists BigQuery, Spark and DuckDB as target query engines. OLake Go then offers only the delete formats all three read, and the run on this page used Positional Deletes.

  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 Google BigQuery, Apache Spark and DuckDB. 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.

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 Positional Deletes on Iceberg format version V2
Each stream: Full Refresh + CDC, Upsert, and Positional Deletes as the upsert type. The Iceberg DB name at the top is editable.
OLake UI Job Logs and History for sql_server_to_bigquery showing two completed syncs, the first in 36 seconds and the second in 33 seconds
Job history: the 36 s historical snapshot, then the 33 s change-only sync.

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

There is no registration step and no external table to declare. A BigLake catalog is a BigQuery data source in the same project: the catalog is the bucket name, the namespace OLake Go created is the dataset, and the tables are the tables. Address them with the four-part name in backticks, and run the query in the bucket's region.

BigQuery SQL editor (location: the bucket region)
SELECT id, name, city, tier
FROM `<project-id>.<bucket>.sql_server_to_bigquery_shop_dbo.customers`
ORDER BY id;

SELECT status, count(*) AS n, round(sum(amount), 2) AS total
FROM `<project-id>.<bucket>.sql_server_to_bigquery_shop_dbo.orders`
GROUP BY status ORDER BY status;

From a terminal, bq query --project_id=<project-id> --location=<region> --use_legacy_sql=false "<sql>" runs the same statements. Set the location to the bucket's region (us-central1 on this page); the default US multi-region returns Not found: Dataset for a catalog whose bucket is regional.

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.

BigQuery reads Iceberg v2 tables with positional and equality deletes, which is why the job on this page uses positional deletes. Deletion vectors on v3 tables are a preview feature in BigQuery with per-query limits, so keep the job off deletion vectors while BigQuery is a reader.

BigQuery SQL editor in the Google Cloud console running the customers query on oauth-361809.olake-x2y-lab-bq.sql_server_to_bigquery_shop_dbo.customers and returning 10 rows with id, name, city and tier
BigQuery reading the customers table through the BigLake catalog 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 33 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 positional delete file retires the 2 old versions, and orders has the same shape with 8 rows and 3 deletes.

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

BigQuery shows the result on the next query, with no refresh and no ALTER TABLE. 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 6 untouched snapshot rows, c for the 2 inserts and u for the 2 updates.

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 BigQuery shows it on the next query; full rules are on the schema evolution page.

Query the same tables from DuckDB and Spark​

Nothing is exported, copied or refreshed: both engines read the files OLake Go wrote, through the same BigLake catalog.

DuckDB attaches BigLake as an Iceberg REST catalog with a Google access token. The catalog on this page is in end-user credential mode, so DuckDB also needs its own Cloud Storage credentials to read the files: an HMAC key for the service account (Cloud Storage, Settings, Interoperability, Create a key for a service account).

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

-- gcloud auth print-access-token, or a token minted for the service account
CREATE SECRET biglake_catalog (
TYPE iceberg,
TOKEN '<access token>',
EXTRA_HTTP_HEADERS MAP {'x-goog-user-project': '<project-id>'}
);
-- the HMAC key pair for the bucket
CREATE SECRET biglake_files (TYPE gcs, KEY_ID '<hmac access id>', SECRET '<hmac secret>');

ATTACH 'gs://<bucket>' AS biglake (
TYPE iceberg,
ENDPOINT 'https://biglake.googleapis.com/iceberg/v1/restcatalog',
SECRET biglake_catalog
);

SHOW ALL TABLES;
SELECT id, name, city, tier FROM biglake.sql_server_to_bigquery_shop_dbo.customers ORDER BY id;

The access token lives an hour; CREATE OR REPLACE SECRET with a fresh one when a query fails with 401. If the catalog is in vended-credentials mode instead, drop the gcs secret and add ACCESS_DELEGATION_MODE 'vended_credentials' to the ATTACH: BigLake then hands DuckDB a short-lived storage token per table. DuckDB reads positional deletes, so the rows match BigQuery after every sync.

Google Cloud console Cloud Storage Interoperability settings with the Select a service account dialog, the OLake service account selected and the Create key button
The key for DuckDB: Cloud Storage, Settings, Interoperability, then a key for the same service account OLake Go uses.

DuckDB 1.5.5 returned the same 10 customers and the same orders roll-up as BigQuery in the run on this page. The data files came through the hash-based message authentication code (HMAC) key.

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

OLake Go maps SQL Server types to Iceberg types, and BigQuery maps Iceberg types to its own when it reads the catalog. 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)BigQuery showsNotes
tinyint, smallint, intintINT64The IDENTITY keys on this page arrived as INT64.
bigintlongINT64
decimal, numeric, money, smallmoney, floatdoubleFLOAT64OLake 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.
realfloatFLOAT64
bitbooleanBOOL
date, smalldatetime, datetime, datetime2, datetimeoffsettimestamptz (UTC)TIMESTAMPdatetime2 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_variantstringSTRINGA uniqueidentifier arrives as its 36-character text form.
binary, varbinary, image, rowversion, geometry, geography, hierarchyidstringSTRINGBinary 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 BigQuery side comes from the BigQuery Iceberg tables page (checked 2026-10-30) and from the table schemas 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 Cloud Storage rates for the Parquet and delete files. The BigLake catalog itself has no per-table charge.
  • Compute. OLake Go runs wherever you run Docker. BigQuery bills on-demand queries by bytes scanned, or by slot time on an edition; a query over the catalog reads the Parquet files in the bucket.
  • Maintenance. BigLake keeps every snapshot OLake Go commits in the table's metadata.json and rejects one above 1 MB, which a busy CDC table reaches after a few thousand syncs. Expire old snapshots and compact on a schedule with OLake Fusion or with Spark's expire_snapshots and rewrite_data_files procedures through the same catalog.
  • 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.
  • Key rotation. The service account key is what OLake Go writes with. Rotate it from the service account's Keys tab and paste the new JSON into the destination.

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.

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. EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent' reports Running. when it is up.

4. The destination test fails on a catalog in vended-credentials mode​

failed to test destination: failed to create or get table: rpc error: code = Internal desc = Failed to process request: Malformed request: X-Iceberg-Access-Delegation header must be present and contain `vended-credentials` when credential mode is `CREDENTIAL_MODE_VENDED_CREDENTIALS`.

The catalog was created, or switched, with --credential-mode=vended-credentials, and OLake Go 1.1.0 does not send the header BigLake wants in that mode (olake#1369). Switch the catalog back with gcloud beta biglake iceberg catalogs update <bucket> --credential-mode=end-user, which is the default for a catalog OLake Go created itself. With the CLI, "rest_access_delegation": "vended-credentials" in destination.json makes the same test pass; the UI has no field for it yet.

5. BigQuery cannot find the dataset​

Not found: Dataset <project>:<bucket>.sql_server_to_bigquery_shop_dbo was not found in location US

The query ran in a location other than the bucket's region. Set the query location to the bucket's region in the editor's More, Query settings panel, or pass --location=<region> to bq.

How this compares with other ways to move SQL Server data to BigQuery
MethodFreshnessUpdates and deletesWhere the data landsWhat you run
Datastream for SQL ServerNear real time, by Google's description; CDC from the change tables or the transaction logYesNative BigQuery datasets; BigQuery is the only readerNothing to run; a connection profile and a stream in the console
Debezium, Kafka and a BigQuery sink connectorSecondsYes, with the sink's upsert settingsKafka topics, then BigQuery tablesKafka, Kafka Connect, the Debezium SQL Server connector and a sink
Export (bcp to CSV), then bq loadHours to dailyNo; full reloads onlyNative BigQuery tablesA script, a scheduler and a staging bucket
OLake Go to Iceberg on BigLake (this guide)CDC on a schedule down to one minuteYes, as Iceberg row-level deletesApache Iceberg tables that BigQuery, Spark, DuckDB, Trino and Flink all readOne Docker Compose stack, open source; your bucket plus your own compute

Datastream is the simpler choice when native BigQuery tables are the only goal. Writing Iceberg once avoids paying to move and store a second copy as soon as Spark, DuckDB or a notebook needs the same rows.

When not to use this​

  • Every query must hit native BigQuery speed. A catalog table is read from Parquet in the bucket on every query. Keep the Iceberg tables as the source and load the hot slice into a native table on a schedule.
  • The edition has no CDC. SQL Server Express and Web cannot enable change data capture. 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 BigQuery without Datastream?

Run OLake Go (open source, Apache 2.0) against a SQL Server database with change data capture enabled, and point it at a BigLake catalog with a Cloud Storage bucket. 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. BigQuery queries the result through the catalog with a four-part name. No Datastream stream, Kafka or load job is needed.

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.

Does BigQuery read Iceberg tables with row-level deletes?

Yes. BigQuery reads Iceberg v2 merge-on-read tables with positional and equality deletes. The run on this page used positional deletes and BigQuery returned one row per key after the updates.

Do I need to create a BigQuery dataset or an external table?

No. A BigLake catalog is a data source BigQuery reads directly: the bucket name is the catalog, the namespace OLake Go creates is the dataset, and the tables appear under it. The query uses the four-part name project.bucket.namespace.table, and there is no CREATE EXTERNAL TABLE and no metadata refresh.

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. Cloud SQL for SQL Server enables CDC with msdb.dbo.gcloudsql_cdc_enable_db.

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

decimal, numeric, money and float columns become Iceberg double and show up in BigQuery as FLOAT64, so round at query time when you need exact decimal arithmetic. datetime, datetime2, smalldatetime and date become TIMESTAMP in UTC. nvarchar, varchar, uniqueidentifier, xml and the binary types arrive as STRING. bit becomes BOOL; int and bigint map to INT64.

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