Create a sync to Microsoft Azure Synapse Analytics

Configure Microsoft Azure Synapse Analytics as your Celigo sync destination and let the sync manage the warehouse side for you: it creates the destination schema and tables in your dedicated SQL pool on the first run and keeps them in step with your source as columns are added or widened. This article covers the destination settings specific to Azure Synapse Analytics, what the first run creates, how schema changes are applied, and the Celigo-to-Azure Synapse Analytics data type mapping.

Prerequisites: An Azure Synapse Analytics connection that uses Azure service principal authentication, points to a dedicated SQL pool, has Storage account, Container, and Folder path set, and doesn’t use an on-premises agent (see Set up a connection to Microsoft Azure Synapse Analytics). That article also covers the SQL pool permissions the service principal needs. The service principal must also have the Storage Blob Data Contributor role on the container. The folder path can’t be the container root, and the container should be used only for Celigo staging, because Celigo deletes older folders within the folder path. The sync can’t be saved with a Database user connection or a serverless SQL pool.

Create a data ingestion sync

Follow the instructions in Create a data ingestion sync to create a sync and configure your source application.

Set up Microsoft Azure Synapse Analytics as your destination

1. Select Microsoft Azure Synapse Analytics as the destination that will ingest and store all source data from the sync.

2. Choose an established connection, or select Create connection to create a new one.

   Caution: Avoid sharing connections with flows scheduled at the same time, as running them simultaneously can reduce performance.

3. Name your destination schema — the sync creates a schema based on the objects, exports, and fields you selected when configuring the source application. Give the schema a meaningful name to help you identify the destination tables that correspond to it later. If the schema already exists in the database, the sync writes into it; if it doesn’t, the first run creates it. The default schema name is dbo.

   A schema name can be up to 128 characters. The sync rejects a longer name when you save.

   Note: This schema is for use only with this sync. Be careful when entering its name — you can’t change it after you save and run the sync.

This sync names your destination tables using the following structure: <database>.<schema>.<table>

• <database> — The Database name set on the connection.
• <schema> — The destination schema name.
• <table> — The specific object, export, or custom table name selected for sync.

What the first run creates

After you save, the sync shows a Ready to run state and no data has loaded yet. Select Run, or wait for the next scheduled run. On the first run, the sync creates whatever doesn’t already exist in Azure Synapse Analytics, so you don’t write any DDL:

• The destination schema, if it doesn’t exist.
• One table per object or export, with columns typed according to the data type mapping and with every system column. Tables use round-robin distribution and a clustered columnstore index.
• Column and table names that match a T-SQL reserved word get an underscore appended, and other names are made valid (see Reserved words).

During each run, the sync stages data in the container and folder path set on the connection, loads it into your tables, and then deletes that run’s staged files. If a run ends before it can clean up, a later load through the same folder path removes folders older than 24 hours. Staged files don’t expire on their own. If the service principal can’t delete them, they build up in the container until you remove them.

Note: Azure Synapse dedicated SQL pools don’t enforce primary keys. If a batch contains the same primary key more than once, the load fails, and the error names the key columns and sample values. The sync doesn’t remove duplicates or choose which record is kept.

How schema changes are applied in Azure Synapse Analytics

The sync’s schema drift policy decides whether a source change is applied to the destination (see Configure a schema drift policy). When it is, this is what happens in Azure Synapse Analytics:

• A column is added — The column is added to the destination table and loads from the next run.
• A text column grows — The column is widened to fit; existing values are untouched.
• A column changes to a compatible type — The sync replaces the column with a new one of the right type, without losing data.
• A column would shrink or lose data — The change is rejected before anything is altered, and the run fails with an error that names the column, its current type, and the attempted type.
• A column is removed at the source — Nothing changes in Azure Synapse Analytics; the column and its history remain, as for every destination.
• Two source fields shorten to the same name — If two names are the same after being shortened to 128 characters, the run fails and the error names both fields.
• A column defined as an IDENTITY column can’t be used as a primary key — the run fails before loading and names the column.

Merge, append, or replace

Whether rows are updated or only added is set per object or export in the source step, not on the destination (see Create a sync from an application’s objects). The ingestion mode decides how each run writes to the table:

• Merge — With a primary key set, rows that match on the key are updated and new rows are inserted.
• Append — Every run adds rows. Without a primary key, every run appends.
• Replace — Each run replaces the table contents, so only the latest data remains.

Data type mapping

The data types sent to or inferred by Celigo syncs are transferred to Azure Synapse Analytics as follows (see Understand how data types are inferred for what each Celigo data type represents).

A few mappings depend on the source field:

• Text — The column length comes from the source field’s length, up to 4000. A field with no length becomes NVARCHAR(256). Primary key and unique key columns are NVARCHAR(450). Azure Synapse dedicated SQL pools don’t support NVARCHAR(MAX).
• Decimals — The column uses the source field’s precision and scale, with precision capped at 38. A field with precision but no scale uses a scale of 4, and a field with neither becomes DECIMAL(18,4).
• JSON — Arrays, objects, and variants load as JSON text into NVARCHAR(4000) columns.
• Double from NetSuite JDBC — A DOUBLE field from a NetSuite JDBC source is created as NVARCHAR(4000) instead of FLOAT. Existing tables keep the NVARCHAR(4000) column.
• Time with time zone, geometry, and geography — Stored as NVARCHAR(4000); Azure Synapse Analytics’ native types for these aren’t used.
• Interval — Stored as NVARCHAR(256).
• System columns are created as _celigo_deleted (BIT), _celigo_extracted_at and _celigo_loaded_at (DATETIMEOFFSET), and _celigo_job_id and _celigo_id (NVARCHAR(255)). See Understand Celigo sync system columns.

Microsoft Azure Synapse Analytics reserved words

Celigo sync automatically manages Azure Synapse Analytics (T-SQL) reserved words when creating or updating destination tables to prevent naming conflicts. You can use these keywords freely in your source data — the sync appends an underscore to the destination identifier, so a table named ORDER appears as order_. Matching isn’t case-sensitive.

The sync also makes other names valid: it replaces characters other than letters, numbers, underscores, and dollar signs with underscores, adds a leading underscore to names that don’t start with a letter, and shortens names to 128 characters.

Learn more

• Celigo sync overview
• Understand how data types are inferred
• Create a data ingestion sync
• Troubleshoot sync run errors