Loading Data into Azure Synapse Analytics

The four main loading patterns

Before writing any code, match your scenario to the right pattern:

COPY INTO: bulk loading from ADLS

COPY INTO is the simplest and fastest way to load data from ADLS Gen2 or Azure Blob Storage into a dedicated SQL pool table. It requires no external data source or external file format objects — you pass the storage path and format options directly in the statement.

-- Step 1: Create a staging table in the dedicated SQL pool
-- Round-robin distribution is ideal for staging tables
CREATE TABLE dbo.stg_orders
(
    order_id        BIGINT,
    customer_id     INT,
    order_date      DATE,
    product_id      INT,
    quantity        INT,
    unit_price      DECIMAL(10, 2),
    order_total     DECIMAL(18, 2),
    status          NVARCHAR(50)
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    HEAP
);

-- Step 2: Load Parquet files from ADLS using COPY INTO
COPY INTO dbo.stg_orders
FROM 'https://mydatalake.dfs.core.windows.net/silver/orders/year=2025/month=01/*.parquet'
WITH
(
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (
        IDENTITY = 'Managed Identity'
    )
);

-- Check how many rows were loaded
SELECT COUNT(*) AS rows_loaded FROM dbo.stg_orders;

The IDENTITY = ‘Managed Identity’ credential tells Synapse to authenticate to ADLS using the workspace’s managed identity. This is the recommended approach — no storage keys or SAS tokens to manage.

Loading CSV files with COPY INTO

CSV loading requires more options than Parquet because you must describe the file structure:

-- Load a CSV file with specific formatting options
COPY INTO dbo.stg_customers
FROM 'https://mydatalake.dfs.core.windows.net/bronze/customers/customers_2025.csv'
WITH
(
    FILE_TYPE = 'CSV',
    FIRSTROW = 2,               -- skip the header row (row 1)
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0A',     -- Unix line endings (\n)
    ENCODING = 'UTF8',
    DATEFORMAT = 'ymd',         -- date format: 2025-01-31
    CREDENTIAL = (
        IDENTITY = 'Managed Identity'
    ),
    ERRORFILE = 'https://mydatalake.dfs.core.windows.net/errors/customers/',
    MAXERRORS = 100             -- allow up to 100 bad rows before failing
);

The ERRORFILE option tells Synapse to write rows that fail to parse to a separate location rather than failing the entire load. This is valuable for production pipelines where occasional bad records should not block the whole batch.

The staging table pattern

Never load data directly into your production tables. Always land data in a staging table first, validate it, then merge into production. This pattern gives you a safe rollback point and lets you run data quality checks before data becomes visible to end users.

-- After loading into stg_orders, validate and merge into the production table

-- Step 1: Check for data quality issues
SELECT
    'NULL order_id' AS issue, COUNT(*) AS row_count
    FROM dbo.stg_orders WHERE order_id IS NULL
UNION ALL
SELECT
    'Negative order_total', COUNT(*)
    FROM dbo.stg_orders WHERE order_total < 0
UNION ALL
SELECT
    'Future order date', COUNT(*)
    FROM dbo.stg_orders WHERE order_date > GETDATE();

-- Step 2: If checks pass, merge into the production fact table
INSERT INTO dbo.FactOrders (order_id, customer_id, order_date, product_id, quantity, unit_price, order_total)
SELECT
    order_id,
    customer_id,
    order_date,
    product_id,
    quantity,
    unit_price,
    order_total
FROM dbo.stg_orders
WHERE order_id IS NOT NULL
  AND order_total >= 0
  AND order_date <= GETDATE();

-- Step 3: Truncate the staging table for the next load
TRUNCATE TABLE dbo.stg_orders;

Loading with PySpark (recommended for transformations)

When you need to transform data before loading — joining multiple sources, deriving columns, deduplicating, or applying complex business logic — load with PySpark rather than COPY INTO. PySpark processes data in parallel across Spark cluster nodes and writes to ADLS in Delta or Parquet format, which the dedicated pool or serverless pool can then query.

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, when, current_timestamp, md5, concat_ws
from pyspark.sql.types import DecimalType
from delta.tables import DeltaTable

spark = SparkSession.builder.getOrCreate()

# Read raw CSV from the bronze layer
orders_raw = spark.read.csv(
    "abfss://bronze@mydatalake.dfs.core.windows.net/orders/2025/",
    header=True,
    inferSchema=True
)

# Read customer reference data
customers = spark.read.parquet(
    "abfss://silver@mydatalake.dfs.core.windows.net/customers/"
)

# Transform: join, clean, and derive columns
orders_clean = (
    orders_raw
    .join(customers, on="customer_id", how="inner")
    .withColumn("order_total_decimal", col("order_total").cast(DecimalType(18, 2)))
    .withColumn("is_high_value", when(col("order_total") >= 500, True).otherwise(False))
    .withColumn("row_hash", md5(concat_ws("|", col("order_id"), col("order_total"))))
    .withColumn("load_timestamp", current_timestamp())
    .filter(col("order_total") >= 0)
    .dropDuplicates(["order_id"])
)

# Write to silver layer as Delta (supports ACID updates)
orders_clean.write.format("delta") \
    .mode("append") \
    .partitionBy("order_year", "order_month") \
    .save("abfss://silver@mydatalake.dfs.core.windows.net/orders_cleaned/")

print(f"Loaded {orders_clean.count()} cleaned order rows to silver Delta table")

Writing to Delta format in ADLS means you get ACID transactions, schema enforcement, and time travel (the ability to query historical versions). The serverless SQL pool can query Delta tables natively using the OPENROWSET function with FORMAT = ‘DELTA’.

Loading with Synapse Pipelines

Synapse Pipelines is the built-in orchestration engine. Use it when you need to schedule loads, handle dependencies between jobs, load from non-Azure sources (Salesforce, SAP, REST APIs), or require retry logic and monitoring with minimal code.

A typical pipeline for nightly data loading has these activities:

1. Lookup — read the last successful load timestamp from a control table
2. Copy Activity — extract changed records from source (using the watermark timestamp), land in ADLS bronze
3. Notebook Activity — run a PySpark notebook to transform bronze to silver
4. Stored Procedure Activity — execute COPY INTO or the merge logic in the dedicated SQL pool
5. Update watermark — write the new load timestamp back to the control table

This is called the watermark pattern for incremental loads and is one of the most common data engineering patterns in Azure.

Performance tips for large loads

For dedicated SQL pool loads over 100 GB:

• Scale up the pool before loading. A DW1000c loads data roughly 10x faster than a DW100c because it has 10x more compute nodes. Scale up, load, scale back down. The cost difference for a 1-hour load is small compared to the time saved.
• Use multiple files. COPY INTO parallelises across files. A single 10 GB file loads slower than 100 files of 100 MB each because only one compute node can read a single file at a time.
• Load during off-peak hours. Loading competes with query workloads for DWU resources. Schedule large loads when the pool has low query concurrency — usually overnight.
• Disable statistics auto-update during large loads. Auto-updating statistics after every load can add significant time. Run UPDATE STATISTICS manually after all loads complete.