Best practices for processing HVR CDC data in Databricks when handling out-of-order updates, delete

[krishgarikipati]

Hi Databricks Community,

I am working on a data lakehouse architecture on AWS, where HVR captures changes from OLTP databases such as Oracle and PostgreSQL, AWS S3 is used for storage, and Databricks handles data processing across Bronze, Silver, and Gold layers.

We need to maintain accurate downstream Delta tables while processing inserts, updates, and deletes from source systems.

I would like to understand the recommended architectural approach for the following scenarios:

1. Out-of-order events: If multiple updates for the same primary key arrive out of order, how can we ensure that an older event does not overwrite the latest record?

2. Duplicate events and retries: How should we handle duplicate CDC records or replayed files without creating inconsistent target data?

3. Delete propagation: What is the recommended approach to propagate source deletes from Bronze to Silver and Gold while retaining audit history where required?

4. Incremental processing: Would you recommend implementing custom Delta MERGE logic or using Lakeflow Declarative Pipelines AUTO CDC APIs for this type of architecture?

5. Performance: What are the best practices for handling high-volume CDC data, small files, and frequent MERGE operations on Delta tables stored in S3?

Our current architecture uses HVR for source ingestion and Databricks for downstream processing. I am particularly interested in a production-ready solution that balances data correctness, performance, maintainability, and cost.

If anyone has implemented a similar architecture, I would appreciate suggestions, design patterns, or code examples.

Thank you!

[emm022stone]

Hello, Use a source sequence number or commit timestamp to ensure only the latest change per primary key is applied. Deduplicate CDC records before merging, handle deletes with soft-delete flags or Delta Change Data Feed for audit requirements, and consider Lakeflow AUTO CDC for maintaining SCD Type 1 or Type 2 tables.

[Aravind_Reddy · Accepted solution]

Hi @krishgarikipati 

For this architecture, I'd keep Bronze as an immutable, replayable CDC layer and use Lakeflow AUTO CDC in Silver, provided HVR supplies reliable source sequencing metadata.

The key is to order changes using source transaction sequence or equivalent metadata rather than file arrival time, so late events don't overwrite newer values. I'd also define the delete strategy upfront: SCD Type 1 for current-state replication or SCD Type 2 when audit history is required.

For performance, I'd focus on incremental processing, controlling small-file growth, and monitoring MERGE duration and pipeline latency. AUTO CDC could simplify the sequencing and history-management logic compared with maintaining all of it in custom MERGE statements.

[Khasim_1 · Accepted solution]

Hi @krishgarikipati ,

1. Out-of-Order Events

• Problem: Ensuring older events don't overwrite newer state changes.
• Recommended Approach: Use windowing with sequence/timestamp ordering during the Silver layer transformation rather than blind updates.
• Implementation:
• Ensure HVR captures and passes source commit timestamps, sequence numbers, or Log Sequence Numbers (LSN) alongside the record.
• In your processing logic, use a Window function (e.g., ROW_NUMBER() OVER (PARTITION BY primary_key ORDER BY source_timestamp DESC, lsn DESC)) to rank incoming changes.
• Only apply the record with the highest rank/latest timestamp to the target Delta table.

1. Duplicate Events and Retries

• Problem: Preventing replayed files or duplicate CDC rows from creating data corruption.
• Recommended Approach: Enforce idempotent processing using Delta MERGE combined with deduplication staging.
• Implementation:
• Deduplicate the incoming batch in the Bronze-to-Silver transition by keeping only the latest event per primary key per micro-batch.
• Use Delta Lake's MERGE INTO statement with a unique match condition based on your primary key and sequence number.
• Ensure your pipeline tracks processed file names or commit metadata (e.g., via Auto Loader checkpointing) to prevent re-reading the same S3 files.

1. Delete Propagation

• Problem: Propagating deletes from Bronze to Silver/Gold while keeping audit history.
• Recommended Approach: Use Soft Deletes (Flag columns) or Time-Travel-enabled Delta operations, depending on audit requirements.
• Implementation:
• For Auditing / Slowly Changing Dimensions (SCD Type 2): Instead of physical deletion, mark the record as deleted by setting an is_active = false flag and populating an end_date timestamp.
• For Hard Deletes: Use MERGE INTO in the Silver layer, matching on the primary key where the incoming operation flag equals 'DELETE', and update the target record to mark it deleted or explicitly remove it if strict data minimization (GDPR) applies.

1. Incremental Processing: Custom MERGE vs. Lakeflow Declarative Pipelines (AUTO CDC APIs)

• Problem: Choosing between custom Delta code and native Databricks tools.
• Recommended Approach: Leverage Databricks Lakeflow / Delta Live Tables (DLT) AUTO CDC APIs (APPLY CHANGES INTO).
• Why:
• Custom MERGE code requires you to manually handle ordering, sequencing, deduplication, and error states, leading to high maintenance overhead.
• DLT's APPLY CHANGES INTO natively handles out-of-order data, SCD Type 1 and Type 2 updates, and delete tracking automatically out of the box, significantly reducing custom boilerplate code and improving pipeline reliability.

1. Performance at Scale (High-Volume CDC on S3)

• Problem: Mitigating small file issues, high-frequency MERGE overhead, and S3 throttling.
• Recommended Approach: Implement a multi-layered optimization strategy for Bronze, Silver, and Gold.
• Key Best Practices:
• Bronze Layer: Store raw HVR output efficiently as append-only Parquet/Delta files on S3. Avoid frequent small-file MERGE operations in Bronze.
• Liquid Clustering: Use Databricks Liquid Clustering on your Silver and Gold tables (partitioning by your primary keys or frequently joined columns) to optimize data layout without the pitfalls of over-partitioning.
• Maintenance Jobs: Schedule regular OPTIMIZE (compaction) and VACUUM commands (or enable Predictive Optimization) to clean up small files and manage S3 storage costs.
• Write Optimization: Enable spark.databricks.delta.optimizeWrite.enabled = true to automatically optimize file sizes during writes.

Thanks,

Khasim.

[ThiamLee]

Great discussion! I’d lean toward AUTO CDC for handling out-of-order events and deletes, with proper sequencing and deduplication to keep the Delta tables consistent. Performance tuning and audit history are also key for a reliable production setup.

[data_pulse]

Ingestion: Land HVR output in Bronze as append-only with Auto Loader checkpoints. Keep HVR’s operation-type and sequence/commit columns, since ordering and deletes depend on them.

Out-of-order events: Prefer AUTO CDC with sequence_by set to the HVR sequence/commit column. It handles late events and delete tombstones. Prefer Custom Merge if AUTO CDC doesn't fit.

Audit: Don’t rely on time travel, since VACUUM removes old versions. Use SCD Type 2 or the immutable Bronze layer.

Performance: Enable deletion vectors on Silver tables with frequent MERGEs. Use liquid clustering on commonly filtered or joined columns. Predictive Optimization only covers UC managed tables, so schedule OPTIMIZE and VACUUM for external ones.