Skip to content
TwinCommitDocs

Azure Synapse Analytics target

TwinCommit loads Azure Synapse Analytics dedicated SQL pools by staging each micro-batch as Parquet in Azure Data Lake Storage Gen2. It then runs COPY INTO a staging table and a MERGE into the target. The MERGE and the checkpoint update commit in one Synapse transaction, so delivery is exactly-once.

Supported: Synapse dedicated SQL pools (Gen2) · Microsoft Fabric Data Warehouse via the same COPY INTO + MERGE path. Synapse serverless SQL pools are read-only, so to query replicated data from serverless SQL use the Azure Data Lake Storage target with Delta Lake.

Configuration

Section titled “Configuration”
target:
  type: synapse
  server: acme-dw.sql.azuresynapse.net
  database: replica_dw
  schema: replica
  auth: service_principal          # or managed_identity, sql
  client_id: ${AZURE_CLIENT_ID}
  client_secret: ${AZURE_CLIENT_SECRET}
  tenant_id: ${AZURE_TENANT_ID}
  staging: abfss://staging@acmelake.dfs.core.windows.net/twincommit/
  merge_interval: 60s
  distribution: hash_primary_key   # or round_robin, replicate

How apply works

Section titled “How apply works”
  1. Changes are accumulated for up to merge_interval and written as Parquet to staging.
  2. COPY INTO loads the batch into a staging table, authenticated with the workspace managed identity.
  3. MERGE applies inserts, updates and deletes using the latest change per key. The checkpoint row is updated and the transaction commits.
  4. Staged files are deleted after the commit.

twincommit ddl -apply creates target tables as HASH-distributed on the primary key with a clustered columnstore index. Set distribution to change that.

Permissions

Section titled “Permissions”
CREATE USER [twincommit-sp] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_ddladmin', 'twincommit-sp';
EXEC sp_addrolemember 'db_datawriter', 'twincommit-sp';
EXEC sp_addrolemember 'db_datareader', 'twincommit-sp';
GRANT ADMINISTER DATABASE BULK OPERATIONS TO [twincommit-sp];

The identity used by COPY INTO needs Storage Blob Data Reader on the staging container. TwinCommit itself needs Storage Blob Data Contributor to write and delete staged files.