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:
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?
Duplicate events and retries: How should we handle duplicate CDC records or replayed files without creating inconsistent target data?
Delete propagation: What is the recommended approach to propagate source deletes from Bronze to Silver and Gold while retaining audit history where required?
Incremental processing: Would you recommend implementing custom Delta MERGE logic or using Lakeflow Declarative Pipelines AUTO CDC APIs for this type of architecture?
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!
