Azure Synapse Analytics从Azure SQL到Data Lake表增量复制：存储表名与Max ID是否可行？

Absolutely, this pattern is not just feasible—it’s a standard, recommended approach for managing incremental loads when replicating data from Azure SQL to Azure Data Lake Storage (ADLS) using Azure Synapse Analytics. Let me walk you through exactly how to implement it, including how to embed the source table name and max ID in your target data (or a dedicated tracking table) for future incremental syncs.

Core Approach #

The key idea is to:

1. Capture metadata (source table name and its current max ID) from your Azure SQL source upfront.
2. Replicate the source data to ADLS, attaching this metadata to each record (or storing it in a separate control table for tracking).
3. Use this metadata to filter only new/updated records in subsequent pipeline runs.

Step-by-Step Implementation #

1. Capture Source Metadata with a Lookup Activity #

First, use a Lookup activity in your Synapse Pipeline to fetch the max ID and table name from your Azure SQL source. For example, run this query:

SELECT 
  MAX(your_id_column) AS source_max_id,
  'YourSourceTableName' AS source_table_name
FROM YourAzureSQLSourceTable

This will return a single row with the metadata you need. Store these values in pipeline variables for later use in your pipeline.

2. Replicate Data to ADLS with Metadata #

Use a Copy Data activity to move data from Azure SQL to ADLS. To embed the metadata in your target table:

• In the Source tab, add a filter if you’re doing an incremental load (e.g., your_id_column > @pipeline().variables.previous_max_id—we’ll cover where this value comes from next).
• In the Mapping tab, switch to Derived Columns and add two new columns:
  • source_table_name: Set its value to @pipeline().variables.source_table_name (pulled from your Lookup activity).
  • source_max_id: Set its value to @pipeline().variables.source_max_id.
• Choose your target ADLS format (Parquet is highly recommended for performance and queryability) and specify the storage path.

3. Maintain an Incremental Tracking Table (For Future Loads) #

To make subsequent incremental loads seamless, create a dedicated tracking table in ADLS (e.g., a Parquet table mapped to a Synapse Serverless SQL table). This table will store:

• source_table_name
• last_synced_max_id
• sync_timestamp

After each successful Copy Data run:

• Use a Script activity or Copy Data activity to update this tracking table with the latest source_max_id from your pipeline variables.
• On your next pipeline run, first query this tracking table to get the last_synced_max_id for the source table, then use that value to filter the source SQL data (only fetch records where your_id_column > last_synced_max_id).

Key Considerations #

• Ensure your ID column is reliable: The your_id_column should be a monotonically increasing value (like an identity column, timestamp, or sequence) to guarantee you don’t miss any records during incremental syncs.
• Handle deletes/updates (if needed): If your source table has deletions or updates, this basic max ID pattern won’t capture those. For that, enable Change Data Capture (CDC) on your Azure SQL table and use Synapse’s CDC capabilities to replicate those changes.
• Performance optimizations: For large tables, use partitioned Parquet tables in ADLS to speed up querying and incremental loads. You can also use Synapse’s Serverless SQL Pools to query the ADLS data directly without loading it into a dedicated pool.

内容的提问来源于stack exchange，提问作者MAK