Recommended approach to update reconciliation log tables during DB2-to-Azure SQL Hyperscale migration using ADF + Databricks

Question

Recommended approach to update reconciliation log tables during DB2-to-Azure SQL Hyperscale migration using ADF + Databricks

Janice Chi 160

We are migrating data from IBM DB2 to Azure SQL Hyperscale using a combination of Azure Data Factory (ADF) and Azure Databricks. As part of our reconciliation framework, we calculate row counts and hash values within Databricks after each major stage (Extract, Transform, Load).

Environment:

Source: IBM DB2 (on-prem)

Destination: Azure SQL Hyperscale

Processing: Azure Databricks (PySpark)

Orchestration: Azure Data Factory (V2)

We also maintain a specialized reconciliation log table in Azure SQL Hyperscale to store these metrics, which will be used for validation and audit purposes.

We are considering two approaches to update this log table:

Option A: Write directly to the log table from Databricks using JDBC after each operation.

Option B: Pass the computed values to ADF and have ADF call a stored procedure in Hyperscale to update the log table.

Question: Which of the above approaches is recommended by Microsoft for better robustness, operational maintainability, and failure recovery? Are there any known best practices or limitations in using stored procedures via ADF for logging reconciliation metadata?

Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-07T06:50:58.2466667+00:00

@Janice Chi We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-07T06:50:58.2466667+00:00

@Janice Chi We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Venkat Reddy Navari 3,625 Microsoft External Staff Moderator

Hi Janice Chi Great to see you're implementing a detailed reconciliation framework during your DB2 to Azure SQL Hyperscale migration. Both Option A (Databricks JDBC write) and Option B (ADF stored procedure call) are technically viable, but here's a breakdown based on robustness, maintainability, and failure recovery aligned with recommended Microsoft practices:

Recommended Approach: Option B – Use ADF to call stored procedure

Why Option B is preferred:

Operational Clarity & Separation of Concerns: Let Databricks focus purely on data transformation and reconciliation metric generation, and delegate logging/persistence to ADF. This simplifies error tracing and decouples concerns.
Better Failure Handling: ADF pipelines provide built-in retry policies, failure scopes, alerts, and granular activity-level monitoring when calling stored procedures.
Centralized Logging Logic: Logic inside stored procedures is versioned, centralized, and easier to audit or update without modifying code in Databricks notebooks.
Compliance and Audit-Readiness: Easier to enforce transactional boundaries (e.g., via BEGIN TRANSACTION) inside the SP for consistent audit logs.

When Option A may still be valid:

If extremely low-latency logging is needed or you're outside ADF's orchestration path (e.g., Databricks running independently).

In scenarios requiring complex or conditional metadata writes based on in-memory logic only available in Databricks.

Best Practices if using ADF + SP:

Use Execute Stored Procedure activity in ADF with clear parameters for stage, row counts, hash, timestamp, etc.

Ensure your stored proc handles idempotency (i.e., no duplicate inserts on retries).

Log failures and outputs of the SP execution for traceability.

Use custom status codes or logging tables for enhanced observability if needed.

Docs reference (for ADF stored procedure activity): Azure Data Factory – Execute stored procedure

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Janice Chi 160 Reputation points

2025-07-09T11:37:52.0933333+00:00

next will be real-time streaming CDC phase where:

~800 Kafka topics (CDC from DB2 via IBM CDC engine) are consumed via Databricks Structured Streaming.

Merged data is written directly from Databricks into Azure SQL Hyperscale main tables (no staging, no ADLS in this phase).

Hash-based reconciliation and retries are handled using commit-aware microbatches.

Questions:

In such a high-throughput, low-latency scenario, is it recommended to continue using ADF pipelines to orchestrate metadata and log updates (via stored procedures) in Hyperscale?

Or should Databricks directly update metadata (e.g., via JDBC calls to log batch status, recon metrics) to reduce orchestration latency and simplify retry handling?

What is Microsoft’s recommended pattern for updating run-level and batch-level metadata (e.g., run ID, recon status, partition range, offset range, failure reason) during continuous Structured Streaming?
Venkat Reddy Navari 3,625 Reputation points Microsoft External Staff Moderator

2025-07-09T19:58:55.5866667+00:00
Hi Janice Chi Real-Time Streaming Metadata Handling: Databricks vs ADF
Option 1: Databricks Direct JDBC (Preferred for Low-Latency)

Low-Latency: Writing metadata directly from Databricks to Azure SQL Hyperscale avoids orchestration delays from ADF, crucial for high-throughput scenarios.

Commit-Aware Microbatches: Databricks handles retries within the stream, ensuring idempotency and efficient logging.

Simplified Retry Logic: Databricks' fault tolerance (e.g., checkpointing) automatically recovers from transient failures, minimizing pipeline disruption.

Optimization: Ensure JDBC batch writes and connection pools are tuned to avoid locking issues in high-concurrency environments.

Option 2: ADF Orchestrating Stored Procedures (Use for Compliance & Centralized Logging)

Centralized Logging: ADF ensures audit compliance with stored procedures, providing transactional consistency (e.g., BEGIN TRANSACTION).

Advanced Monitoring: ADF’s built-in retry policies, failure handling, and alerts can be useful for complex workflows.

Latency Considerations: ADF introduces orchestration overhead, so it’s less suited for real-time, low-latency streaming scenarios.

Databricks Direct JDBC is the best choice for real-time, low-latency streaming. It reduces orchestration overhead, handles retries efficiently, and scales well for high-throughput scenarios.

ADF with Stored Procedures is ideal when you need centralized logging, compliance, and advanced error handling. But be aware that ADF can introduce latency, which might not be suitable for real-time requirements.

For streaming workloads, Databricks should handle both data processing and metadata logging. ADF can be used for centralized monitoring, compliance, and failure recovery when necessary.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Share via

Recommended approach to update reconciliation log tables during DB2-to-Azure SQL Hyperscale migration using ADF + Databricks

1 answer

Your answer