Terabytes of CSV/Parquet files land in Azure Blob every hour, and app teams want fresh rows in Azure SQL Database without babysitting SSIS, moving VMs, or bolting on brittle scripts. The cloud-native path is a single SQL statement you can run in parallel and automate: OPENROWSET (BULK) reading directly from Azure Blob.

Why this matters

• Zero servers to manage. Read directly from Blob into Azure SQL - no middle tier required.
• Security-first. Use Managed Identity instead of scattering SAS tokens.
• Scales with patterns. Wildcards for batch loads, partitioned folders for parallelism, idempotent MERGE patterns to keep data clean.
• Operationally sane. Built-in retries plus small transaction batches mean fewer overnight pages.

Architecture in a glance

1. Data lands in Blob by partition (e.g., container/events/dt=2025-11-21/part-*.parquet).
2. Azure SQL defines an EXTERNAL DATA SOURCE pointing to that container (auth via SAS or Managed Identity).
3. An INSERT...SELECT FROM OPENROWSET(BULK ...) ingests files using a wildcard pattern.
4. Loads run in parallel by partition (hour/day), with small batches for predictable logging and lock durations.
5. A small ingestion ledger table enforces idempotency and powers safe retries.

Authentication: SAS vs Managed Identity (MI)

Aspect	SAS Token	Managed Identity (Recommended)
Secret handling	You pass a long secret string	No secrets; Azure AD token requested at runtime
Rotation	Manual; tokens expire	Automatic token issuance
Scope	Can be right-sized via stored access policy	Scope via RBAC (Storage Blob Data Reader)
Risk	Easy to leak in code/scripts	Lower risk; no token to leak
Setup in Azure SQL	CREATE DATABASE SCOPED CREDENTIAL ... SECRET = 'sas'	IDENTITY = 'Managed Identity' (no secret)

If you can use MI, do. SAS is fine for quick starts but poor for long-lived production.

One-time setup (Azure SQL)

1) Create the credential

Managed Identity (best):

-- Azure SQL Database with a system-assigned managed identity enabled on the server
CREATE DATABASE SCOPED CREDENTIAL [BlobMI]
WITH IDENTITY = 'Managed Identity';

SAS token (fallback):

-- SAS must include at least r (read) permission on the container or path
CREATE DATABASE SCOPED CREDENTIAL [BlobSAS]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET   = 'sv=2025-01-01&ss=bf...&se=2025-12-31T23%3A59Z&sp=rl...'; -- no leading '?'

2) Create the external data source

CREATE EXTERNAL DATA SOURCE [BlobEvents]
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://<account>.blob.core.windows.net/<container>',
    CREDENTIAL = BlobMI -- or BlobSAS
);

Fast path: ingest CSV or Parquet with OPENROWSET

CSV example (header row, inferred schema)

INSERT INTO dbo.events_raw (event_id, ts_utc, user_id, payload)
SELECT
    TRY_CAST([event_id] AS BIGINT),
    TRY_CAST([ts_utc]   AS DATETIME2(3)),
    TRY_CAST([user_id]  AS INT),
    [payload]
FROM OPENROWSET(
    BULK 'events/dt=2025-11-21/*.csv',
    DATA_SOURCE = 'BlobEvents',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2
) WITH (
    [event_id] VARCHAR(40),
    [ts_utc]   VARCHAR(30),
    [user_id]  VARCHAR(20),
    [payload]  NVARCHAR(MAX)
) AS src;

Parquet example (schema-on-read)

INSERT INTO dbo.events_raw (event_id, ts_utc, user_id, payload)
SELECT
    event_id,
    ts_utc,
    user_id,
    payload
FROM OPENROWSET(
       BULK 'events/dt=2025-11-21/*.parquet',
       DATA_SOURCE = 'BlobEvents',
       FORMAT = 'PARQUET'
) AS src;

Use folder partitioning (dt=YYYY-MM-DD/) to isolate batches and run loads concurrently per partition.

Idempotency that actually works

Create a small ingestion ledger that records which file (or partition) has already been applied.

CREATE TABLE dbo.ingestion_ledger (
    batch_key        VARCHAR(200) NOT NULL,  -- e.g., 'dt=2025-11-21'
    first_seen_utc   DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    applied_utc      DATETIME2(3) NULL,
    checksum_sha256  CHAR(64) NULL,
    CONSTRAINT PK_ingestion_ledger PRIMARY KEY (batch_key)
);

The pattern:

1. Compute a batch_key (e.g., dt=2025-11-21).
2. If not present, insert a pending row.
3. Load with INSERT…SELECT from OPENROWSET.
4. MERGE into the final table using a natural/business key (or a composite of fields).
5. Mark the ledger row as applied.
6. On retries, the MERGE becomes a no-op, maintaining idempotency.

BEGIN TRY
    SET XACT_ABORT ON;
    BEGIN TRAN;

    DECLARE @batch_key VARCHAR(200) = 'dt=2025-11-21';

    IF NOT EXISTS (SELECT 1 FROM dbo.ingestion_ledger WITH (UPDLOCK, HOLDLOCK) WHERE batch_key = @batch_key)
        INSERT dbo.ingestion_ledger(batch_key) VALUES (@batch_key);

    ;WITH src AS (
        SELECT * FROM OPENROWSET(
            BULK CONCAT('events/', @batch_key, '/*.parquet'),
            DATA_SOURCE = 'BlobEvents',
            FORMAT = 'PARQUET'
        ) AS f
    )
    MERGE dbo.events_final AS T
    USING (
        SELECT DISTINCT
            CAST(event_id AS BIGINT)   AS event_id,
            CAST(ts_utc AS DATETIME2)  AS ts_utc,
            CAST(user_id AS INT)       AS user_id,
            payload
        FROM src
    ) AS S
      ON T.event_id = S.event_id  -- your natural key
    WHEN MATCHED THEN
        UPDATE SET
            T.ts_utc  = S.ts_utc,
            T.user_id = S.user_id,
            T.payload = S.payload
    WHEN NOT MATCHED THEN
        INSERT (event_id, ts_utc, user_id, payload)
        VALUES (S.event_id, S.ts_utc, S.user_id, S.payload);

    UPDATE dbo.ingestion_ledger
      SET applied_utc = SYSUTCDATETIME()
    WHERE batch_key = @batch_key;

    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

Without tracking batch/file identity, retries will double-load data sooner or later.

Batching & parallelism (without wrecking the log)

Goals: predictable transaction sizes, less lock pressure, steady log growth.

• Partitioned folders (day/hour) → one transaction per partition.
• Wildcards + small partitions beat one huge wildcard.
• Rowgroup sizing: if converting to clustered columnstore, load in 100k - 1M row chunks to form healthy rowgroups.
• TABLOCK when inserting into a heap to help minimal logging (verify in your tier).
• MAXDOP is limited in Azure SQL; prefer multiple small sessions over one monster session.
• Service tier right-sizing: loads are I/O and log heavy; bump DTUs/vCores for the ingest window.

Example: loop by hour

DECLARE @d DATE = '2025-11-21';
DECLARE @h INT = 0;

WHILE @h < 24
BEGIN
    DECLARE @batch_key VARCHAR(200) = CONCAT('dt=', CONVERT(CHAR(10), @d, 23), '/hh=', FORMAT(@h, '00'));
    EXEC dbo.load_partition @batch_key;  -- wrap the MERGE pattern above in a procedure
    SET @h += 1;
END

Retries that won't make things worse

Wrap ingestion in a lightweight retry with exponential backoff and error-code filters (throttling, transient storage/network hiccups). Keep transaction boundaries tight.

CREATE OR ALTER PROCEDURE dbo.load_with_retry @batch_key VARCHAR(200)
AS
BEGIN
    DECLARE @attempt INT = 1, @max_attempts INT = 5, @delay_ms INT = 500;

    WHILE @attempt <= @max_attempts
    BEGIN
        BEGIN TRY
            EXEC dbo.load_partition @batch_key; -- your idempotent loader (MERGE+ledger)
            RETURN;
        END TRY
        BEGIN CATCH
            -- Filter on transient errors you actually want to retry (demo keeps it simple)
            IF @attempt = @max_attempts THROW;
            WAITFOR DELAY TIMEFROMPARTS(0,0,0,@delay_ms,3);
            SET @delay_ms = @delay_ms * 2;
            SET @attempt += 1;
        END CATCH
    END
END

Because the loader is idempotent, retries don't duplicate rows.

Operational guardrails

• Schema-on-read validation: use a WITH (...) schema in CSV to catch drift early (TRY_CAST + reject queue).
• Quarantine bad files: on conversion failures, write offending file names to a rejects table with error text.
• Compression matters: prefer Parquet. Less I/O, enforced schema, better end-to-end SLAs.
• File sizing: target 128-512 MB per file for balanced parallelism. Thousands of 10 KB files create crippling overhead.
• RBAC for MI: grant the SQL server's MI Storage Blob Data Reader on the container (least privilege).
• Observability: track load duration, rows read, rows inserted/updated, and size per batch in the ledger (add columns).
• Backfills: throttle concurrency during business hours; increase it off-peak. Maintain a backfill queue (table) to coordinate.

Common pitfalls (and how to dodge them)

• Leaking SAS tokens in scripts or views → prefer MI; if you must use SAS, keep it short-lived via stored access policy.
• One mega-transaction → split by partition to avoid lock timeouts and log-growth spikes.
• Blind INSERT → always MERGE or INSERT … WHERE NOT EXISTS keyed by a business id to ensure idempotency.
• Schema drift surprises → use explicit columns + TRY_CAST. Write rejects; don't silently coerce.
• Tiny files everywhere → consolidate upstream (Spark/ADF) to sane file sizes.
• No isolation of staging vs final → land into staging, then MERGE to final to keep queries stable.

Conclusion & takeaways

• OPENROWSET (BULK) + Managed Identity gives a secure, near-serverless ingest path from Blob to Azure SQL.
• Partition your data and batch by folder for controllable parallelism and stable operations.
• Idempotent MERGE + ledger makes retries safe and boring - which is the goal.
• Prefer Parquet, right-size files, and monitor with a few extra columns in the ledger.

Start with one partitioned day in a lower environment, wire up the ledger and retry wrapper, then scale by adding concurrent partitions. If you hit a ceiling, profile log I/O and reshape the batches.