Guidance on Best Practices for Loading CDC-Merged Data from ADLS to Azure SQL Hyperscale

As part of our healthcare data modernization initiative, we are currently implementing the Catch-Up phase of a large-scale CDC pipeline. The architecture involves:

• All Insert/Update/Delete logic (CDC merge) being handled in the Databricks branch layer (ADLS Gen2)

The resulting merged Delta data is to be loaded into Azure SQL Hyperscale

The main Hyperscale table already contains full historical data from a previous one-time load (80TB)

We are reaching out to confirm Microsoft-recommended best practices for the following scenario:

Question:

Given that all CDC merge logic is completed in Databricks and the data in ADLS is already final and de-duplicated, what is the recommended and performance-optimal approach for loading merged Delta data from ADLS to Azure SQL Hyperscale?

Specifically:

Is it recommended to perform a direct batch insert (via Databricks JDBC) into Hyperscale main table without using an intermediate staging table in this case?

Are there size limits, concurrency concerns, or tuning guidelines we should follow when using Spark JDBC for large volumes (partitioned by Kafka offset windows)?

Can Microsoft suggest checkpointing or retry-safe patterns that are compatible with ADLS + Databricks → Hyperscale ingestion for CDC-based pipelines?

Should Hyperscale be prepared with any specific indexing or minimal constraints to ensure optimal write performance from Spark?

We are aiming for a pattern that is idempotent, retry-safe, and aligned with reconciliation — where control tables track offset window status per topic + partition.