Create a sync to Microsoft Fabric Warehouse

Configure Microsoft Fabric Warehouse as your Celigo sync destination and let the sync manage the warehouse side for you: it creates the destination schema and tables 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 Fabric Warehouse, what the first run creates, how schema changes and ingestion modes work, and the Celigo-to-Fabric Warehouse data type mapping.

Prerequisites: A Microsoft Fabric Warehouse connection with a staging location selected (see Set up a connection to Microsoft Fabric Warehouse). The sync lists only connections that have a staging location, and a sync can’t be saved with a connection whose staging location is None. The connection needs a lakehouse for staging, and its service principal needs the Contributor role in that workspace. The sync also needs permission to create and change tables, in addition to the permissions for loading and merging data: CREATE SCHEMA (not needed for the dbo schema), CREATE TABLE, and ALTER TABLE. The connection test doesn’t check these, so a missing permission shows up as a failed run. For the full list of permissions, see Set up a connection to Microsoft Fabric Warehouse.

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 Fabric Warehouse as your destination

1. Select Microsoft Fabric Warehouse 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. The default is dbo. A schema name can be up to 128 characters, can contain only letters, numbers, and underscores, and keeps the case you enter. The wizard rejects a name that breaks these rules before you can 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 warehouse 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

On the first run, the sync creates the destination schema and table if they don’t already exist, 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.
• Primary key columns are created as NOT NULL with a non-enforced primary key. A column defined as an IDENTITY column can’t be used as a primary key — the run fails before loading and names the column.
• Column names longer than 128 characters are shortened to fit. If two source fields shorten to the same name, the run stops before any table is created and names both fields so you can rename one at the source or exclude it from the sync.
• Column and table names that match a T-SQL reserved word get a trailing underscore, so you can use them freely in your source data (see Reserved words). Names keep their case, and characters that Fabric Warehouse doesn’t allow in a name are replaced with an underscore.

If the schema or a table can’t be created, the run fails with an error that says which step failed and why.

Ingestion modes and primary keys

Whether rows are updated, appended, or replaced is set per object or export in the source step, not on the destination (see Create a sync from an application’s objects).

• 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.

Note: Fabric Warehouse doesn’t enforce primary keys. If a merge batch contains the same key value more than once, the run fails with an error that names the key columns instead of a raw Fabric error. The sync doesn’t remove duplicates or choose which record is kept.

Fabric Warehouse uses a case-sensitive binary collation (Latin1_General_100_BIN2_UTF8), so string merge keys are case-sensitive: Account and account are different rows. If you moved from a Microsoft SQL destination, you may see new rows where you used to see updates.

How schema changes are applied in Microsoft Fabric Warehouse

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 Fabric Warehouse:

• 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 in place; existing values are untouched.
• A column changes to an incompatible type — The sync replaces the column with a new one of the right type, without losing data or leaving an extra column behind.
• A column would shrink or lose precision — The change is rejected before anything is altered, and the run fails with an error naming the column, its current type, and the attempted type. Change the source field back, or exclude the field from the sync.

Data type mapping

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

A few mappings depend on the source field:

• Integers — A field that the source declares as SHORT or SMALLINT is stored as SMALLINT. An integer that Celigo infers from sample values is always stored as BIGINT.
• Decimals — The column uses the source field’s precision and scale. A field that declares no precision becomes DECIMAL(38,s).
• Text — The column uses the source field’s declared length, up to 8000, which is the most Fabric Warehouse allows for a sized text column. A field that declares a longer length, such as a NetSuite field declared as 32767, becomes VARCHAR(MAX). A field with no declared length also becomes VARCHAR(MAX). Merge key columns are different: a key with no declared length becomes VARCHAR(256), and a key that declares a length over 8000 becomes VARCHAR(8000), not VARCHAR(MAX). Fabric Warehouse doesn’t support NVARCHAR, so text is stored as VARCHAR with UTF-8 collation. VARCHAR(MAX) holds up to 16 MB, so a row larger than 16 MB fails to load.
• Binary — BINARY and BYTES fields are stored as VARBINARY. A field with no declared length, or one declared longer than 8000, becomes VARBINARY(MAX).
• Interval — INTERVAL fields are stored as VARCHAR(256).
• Time zones — TIME and DATETIME2 columns don’t store a time zone offset.
• JSON — Arrays, objects, and variants load as JSON text into VARCHAR(MAX) columns.
• Unsupported Fabric types — Fabric Warehouse doesn’t support types such as DATETIMEOFFSET, NVARCHAR, MONEY, TINYINT, XML, and GEOGRAPHY, so the sync maps them to the closest supported type above.
• Value that doesn’t fit — If a source value can’t be converted to the column type, for example text sent to a BIGINT column, the load fails with an error that names the column and the value. No rows are skipped.
• Existing tables — Tables created before these defaults keep their narrower types until they are recreated.

Microsoft Fabric Warehouse reserved words

Celigo sync automatically manages Fabric Warehouse (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 adds a trailing underscore to the destination name instead of quoting it, so a field named user becomes user_. Names keep their case and can be up to 128 characters, and characters that Fabric Warehouse doesn’t allow in a name are replaced with an underscore.

Limitations

• Fabric Lakehouse tables, SQL database in Fabric, and Parquet format aren’t supported as destinations.
• Connections that use an on-premise agent aren’t supported.
• Fabric Warehouse can’t be used as a data ingestion source.

Learn more

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