Tip: Select the Build SQL query editor icon next to SQL query and ask Celigo AI to generate a query. Enter a description of your query in plain English in the space provided, and Celigo AI will attempt to generate a query based on your prompt.
Before you begin
Set up a connection to Microsoft Fabric Warehouse.
Note: Fabric Warehouse imports support Mapper 2.0 only, for every query type.
Use optimized bulk load (recommended for larger imports)
Select Use optimized bulk load (recommended for larger imports) in Choose type. This import type is ideal for large data volumes. It aggregates data before loading, reducing the number of batch inserts and improving performance. integrator.io splits the data into fixed-size files, stages them in the lakehouse on your connection, and loads them into Fabric Warehouse with the COPY INTO command. By default, all files load together. To load each file as soon as it’s created, check Load data as available.
On a connection that can use it, new imports start with this query type selected. On any other connection, new imports start with Use batch insert SQL query selected. ⚠ VERIFY: Confirm the default depends on the feature flag.
Additionally, integrator.io automatically generates insert or merge queries based on the destination table and primary keys, allowing you to update existing data without a separate deduplication flow.
-
Destination table: Select the table that receives the data in either of these ways:
- By validated table name: Begin typing the table name in the Destination table field, and any table that already exists in your warehouse displays in a list filtered by the text you enter. Select the refresh icon to reload the list. integrator.io doesn’t create tables.
- By referencing a dynamic lookup with a handlebars expression that identifies the destination table, in the format
{{lookup.<lookup name>}}, for example{{lookup.FabricTableLookup}}.
-
Primary keys: Select the field or fields that uniquely identify each record in your table, so your data can be matched and merged in the destination table. If your table already has primary keys, they’re shown automatically. After you create the import, map the fields you selected.
- If you leave Primary keys blank, integrator.io creates an insert query. Insert without primary keys may lead to duplication.
- If you select values in Primary keys, integrator.io creates a merge query.
- Override merge/insert statement: Check this box to replace the default statement with your own load logic. Write it with handlebars in the SQL box or the Advanced Field Editor (AFE). Use transactions to prevent data duplication on failure.
- Load data as available: Check this box to load each file into the destination table as soon as it’s staged. If you leave the box unchecked, integrator.io stages all files first and loads them together after the export completes.
Staged files
integrator.io stages each run in its own folder in your lakehouse and deletes the folder after the load, whether the run succeeds or fails. If a run stops abnormally, the folder can be left behind. It’s safe to delete. Deleted OneLake data stays billable as soft-deleted data for seven days.
Override merge/insert statement
When you select Override merge/insert statement, enter a SQL query in SQL query. integrator.io first loads all imported records into a temporary staging table, then runs your query. Use these handlebars expressions:
-
{{import.rdbms.bulkLoad.preMergeTemporaryTable}}: the temporary staging table that holds your imported records. -
{{import.rdbms.bulkLoad.tableName}}: the name of your destination table.
If the query doesn’t include the temporary table expression, integrator.io shows a warning. Fabric Warehouse has no savepoints or distributed transactions, so a query with several statements can’t be partially rolled back. ⚠ VERIFY: Confirm handlebars names with the help text, which uses the shorter {{preMergeTemporaryTable}} and {{tableName}}.
When optimized bulk load is unavailable
Use optimized bulk load (recommended for larger imports) stays visible but is unavailable when the connection’s Staging location is None. A message under it tells you to modify your connection to add staging details. Edit the connection and set Staging location to OneLake. The query type becomes available without recreating the import. For the connection settings, see Set up a connection to Microsoft Fabric Warehouse.
An import installed from a template with optimized bulk load selected keeps that query type. If its connection has no staging location, the import can’t use optimized bulk load, and its first run fails with an error that names the connection.
Primary keys and duplicate records
Each primary key value must appear only once in a batch. Fabric Warehouse doesn’t enforce primary keys, so nothing else blocks duplicates. If a batch contains the same key twice, the import fails with an error that names the key columns and sample values. integrator.io doesn’t remove duplicates or choose which one wins. Remove duplicates before this step or in your Override merge/insert statement. The check doesn’t run when you select Override merge/insert statement, because you control how records are applied.
Fabric Warehouse uses a case-sensitive collation by default, so string primary keys are case-sensitive: Account and account are different rows. If you moved from a Microsoft SQL import, you may see new rows where you used to see updates.
Data limits
| Limit | Detail |
|---|---|
| Row size | Each varchar(max) value and each row can be at most 16 MB. A larger record fails with a record error. |
| Identity columns | An identity column can’t be mapped. If you map one, the import fails when it runs. Remove the column from the mapping. |
| Rows that can’t load | In optimized bulk load, one row that can’t load fails the whole load, not only that record. |
| Unsupported data types | Fabric Warehouse doesn’t support types such as nvarchar, datetime, datetimeoffset, money, tinyint, xml, and geography. Text loads as varchar with UTF-8 collation. |
| Column count | If a row has a different number of columns than the destination table, the load fails. The missing columns aren’t filled with nulls. |
| Concurrent loads | Fabric Warehouse allows one writer per table at a time. integrator.io loads files into the same table one after another, and retries with a delay if a conflict occurs. |
| File format | Files are loaded as CSV. Parquet isn’t supported. |
Use batch insert SQL query
The batch insert data option is helpful for moderate data volumes. integrator.io builds the insert query for you automatically for each batch of records. If you leave Batch size empty, integrator.io inserts each page of records in one statement and splits it automatically at about 6 MB. A statement over 16 MB fails, and the error asks you to lower the batch size or use optimized bulk load. To set the number of records in each statement, use the Batch size field in the Advanced section.
By using metadata, you can:
- Search for a specific table or column to add to your query.
- Use lookups to find data.
- Refresh your data to get new updates automatically.
- Use Mapper 2.0 to map imported records.
Note: You can use any schema and table name regardless of what you used in your connection, in your import’s Destination table search. Write the table name as table (the schema defaults to dbo), schema.table, or warehouse.schema.table, where the warehouse is the one on your connection. Put square brackets around names with special characters, for example [my schema].[my table].
Use SQL query once per record
Execute a SQL query once for each record. You can write your SQL command in the SQL query text field. Select Edit to the right of the text field to open the SQL query builder AFE.
Note: Microsoft advises against frequent small writes to a Fabric warehouse, so use optimized bulk load for large volumes.
Use SQL query once per page of records
Execute a SQL query once per page of data. You can write your SQL command in the SQL query text field. Select Edit to the right of the text field to open the SQL query builder AFE.
Use SQL query on first page only
Use this option if you want to run a SQL command once per flow run. The SQL will execute as soon as the first page of data reaches the flow step. This option should be used carefully to avoid race conditions in your flows.