Loading Data into Synapse with ADF

A dedicated SQL pool in Azure Synapse Analytics is a massively parallel database: one control node and many compute nodes, built to scan billions of rows. That design makes it very fast at reading data files in bulk and unimpressive at accepting rows one at a time through a single connection. Loading data into Synapse well means using the path the engine was built for, and the Azure Data Factory Copy activity gives you a choice of three.

The definitions below are written from Microsoft's documentation and have not been deployed.

Three load methods

The Synapse sink of the Copy activity loads data in one of three ways (Microsoft Learn: Azure Synapse Analytics connector):

Microsoft describes the first two as the fastest and most scalable way to load, and says of PolyBase that you will see a large gain in throughput over the default bulk insert. The two flags cannot both be true.

The reason is architectural. With the COPY statement or PolyBase, Data Factory is only the coordinator: it tells the warehouse where the files are, and the compute nodes load them. With bulk insert, every row travels through the integration runtime and a connection to the control node.

One consequence surprises people who try to tune it. When the COPY statement or PolyBase is used on the Azure integration runtime, the effective Data Integration Units are always 2, and changing the DIU setting has no effect, because loading from storage is powered by the Synapse engine. ADF performance tuning explains DIUs in general.

Direct or staged

Both bulk methods read from storage. That gives two cases.

Direct copy. The source is already in Azure Blob storage or Azure Data Lake Storage Gen2, in a format and with settings the loader accepts. The Copy activity invokes the load and the warehouse pulls the data from the source location.

Staged copy. The source is something else, a SQL Server, an API, a file format the loader cannot read. With staging enabled, the service converts the data to a compatible format, writes it to interim Blob or Data Lake Storage Gen2 that you nominate, invokes the load from there, and cleans up the temporary data (Microsoft Learn: performance features). The interim storage cannot be Azure Premium Storage, and the factory needs permission to delete from it.

Microsoft's overview of loading Synapse makes the same point from the other side: the staging feature is how you achieve high load speeds from all types of data stores (Microsoft Learn: load data into Azure Synapse Analytics).

The COPY statement

Direct copy criteria. The service checks these and fails the activity if they are not met:

• Source store: Azure Blob storage or Data Lake Storage Gen2, with one of the listed authentication types.
• Format: Parquet, ORC or delimited text.
• Delimited text settings: the row delimiter is set explicitly to a single character or to carriage return and line feed; the encoding is UTF-8 or UTF-16; the escape character equals the quote character; no lines are skipped.
• Source options not used: wildcard folder paths, wildcard file names other than a star, last-modified filters, prefix, partition discovery and additional columns. A folder source must be read recursively.

A direct load from Parquet:

{
  "name": "LoadSalesFromLake",
  "type": "Copy",
  "inputs": [ { "referenceName": "LakeSalesParquet", "type": "DatasetReference" } ],
  "outputs": [ { "referenceName": "SynapseStgFactSales", "type": "DatasetReference" } ],
  "typeProperties": {
    "source": {
      "type": "ParquetSource",
      "storeSettings": { "type": "AzureBlobFSReadSettings", "recursive": true }
    },
    "sink": {
      "type": "SqlDWSink",
      "allowCopyCommand": true,
      "copyCommandSettings": {
        "additionalOptions": { "MAXERRORS": "100" }
      },
      "preCopyScript": "TRUNCATE TABLE stg.FactSales"
    }
  }
}

The settings group accepts default values for target columns and additional options that are passed straight into the statement's WITH clause.

Staged. The same sink, with staging switched on at the activity level:

{
  "name": "LoadOrdersFromSqlServer",
  "type": "Copy",
  "inputs": [ { "referenceName": "OnPremOrdersTable", "type": "DatasetReference" } ],
  "outputs": [ { "referenceName": "SynapseStgOrders", "type": "DatasetReference" } ],
  "typeProperties": {
    "source": { "type": "SqlSource" },
    "sink": { "type": "SqlDWSink", "allowCopyCommand": true },
    "enableStaging": true,
    "stagingSettings": {
      "linkedServiceName": { "referenceName": "StagingLake", "type": "LinkedServiceReference" },
      "path": "adf-staging/orders"
    }
  }
}

PolyBase

PolyBase is the older bulk path, with the same direct and staged forms. What differs:

• Fallback. If a direct PolyBase copy's requirements are not met, the service falls back to bulk insert automatically. The copy succeeds, slowly, and nothing fails to tell you. The activity's output does record it, in a property named sqlDwPolyBase (Microsoft Learn: monitor copy activity). A direct COPY statement load that does not meet its criteria fails the activity instead.
• Reject settings. A reject value and type, a number or a percentage, set how many rows may be rejected before the load fails.
• Permission. The loading user needs CONTROL permission on the target database.
• Row size. PolyBase loads are limited to rows smaller than 1 MB and cannot load into VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) columns. For such data, Microsoft suggests splitting the table vertically or turning PolyBase off for that load.
• Empty strings and decimals. A known failure: empty values headed for a DECIMAL column fail with a conversion error unless the "use type default" setting is false.
• File names. With a folder source, PolyBase reads all subfolders and skips files whose names begin with an underscore or a period.

For new work the COPY statement is the simpler choice: fewer objects in the database, and its failure mode is loud.

Bulk insert

Bulk insert remains the default and has its uses: a small table, a quick test, or data that neither bulk loader can take. Its settings are the write batch size, which the service chooses by row size unless set, and a batch timeout that defaults to 30 minutes. For large volumes it is the wrong tool.

Other sink settings

Two cautions.

Automatic table creation is for convenience, not for production. The connector creates the table with round-robin distribution. A fact table in a dedicated pool is normally hash-distributed on a join key, and a large table created with the default will perform poorly in joins. Create the tables yourself with the distribution and index you intend.

Upsert is not the bulk path. It works by loading an interim table and then updating or inserting by key. For large changes, the common pattern is to load a staging table with the COPY statement and then run your own MERGE or insert-select in a stored procedure or Script activity. That keeps the heavy load on the fast path and the logic in SQL you control.

{
  "sink": {
    "type": "SqlDWSink",
    "writeBehavior": "Upsert",
    "upsertSettings": {
      "keys": [ "CustomerId" ],
      "interimSchemaName": "stg"
    }
  }
}

A loading pattern that holds up

1. Land files in the lake as Parquet, in dated folders. If the source is a database, a first Copy activity does this, partitioned where the table is large.
2. Load a staging table with the COPY statement, truncating it first with the pre-copy script. The step is then safe to repeat.
3. Transform inside the warehouse from staging to the final tables, with a stored procedure.
4. Record what was loaded: rows read, rows written, the folder, the run ID.

This is ELT: the warehouse does the transformation with its own compute. Incremental data loading covers choosing what to land in step 1.

Access and networking

• Authentication. The connector supports SQL authentication and Microsoft Entra authentication with a service principal or a managed identity.
• Firewall. With the Azure integration runtime, the logical SQL server needs the rule that allows Azure services. With a self-hosted runtime, its IP range must be allowed.
• Storage reached by the warehouse. In a direct or staged load the warehouse itself reads the files, so storage firewalls and identities have to permit that. Microsoft's notes cover storage behind virtual network service endpoints, which requires managed identity authentication.
• Serverless SQL pools can be a source; the bulk load methods described here are for loading tables in a dedicated pool.

From mapping data flows

A data flow writing to Synapse has an Enable staging setting in its sink, which Microsoft says should be set to true so that the service loads with the SQL COPY command in bulk (Microsoft Learn: sink performance). The staging location is set on the Data Flow activity.

Mistakes to avoid

• Leaving the sink on bulk insert for a large table because the pipeline "worked".
• Raising DIUs to speed up a COPY statement or PolyBase load.
• A silent PolyBase fallback. Check the activity output, and review the source settings against the direct copy criteria.
• Wildcards or filters on the source of a direct load. They disqualify it.
• Auto-created tables in production.
• Staging on a storage account the warehouse cannot reach.

For the warehouse itself, see Azure Synapse Analytics explained. The same idea, bulk loading from object storage, appears on AWS in loading data into Redshift. The activity is covered in the Copy activity guide.

Azure Data Factory and Azure Synapse are covered in the Data Engineering course.

Sources

• Copy and transform data in Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Load data into Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn
• Copy activity performance optimization features (Microsoft Learn) — Microsoft Learn
• Monitor copy activity (Microsoft Learn) — Microsoft Learn
• Sink performance and best practices in mapping data flow (Microsoft Learn) — Microsoft Learn