If your data gets into a messy state, you can reconcile it.

The reconciliation pattern is:

  1. Schedule a job on an interval (daily, hourly, etc).
  2. Query trusted data to identify records in a bad state.
  3. Publish repair events to a log.
  4. Let a log processor apply each repair idempotently.
Airflow schedules a job that uses Trino to find bad records in a data lake built on Ceph object storage with Apache Iceberg tables. The job publishes repair events to Kafka, and Flink applies each repair idempotently to MySQL. Debezium captures the changed rows from MySQL and publishes change events to Kafka. An indexer consumes the change events and writes them into the Iceberg tables, so the next scheduled run no longer finds the bad records.
The reconciliation loop

One way to apply this pattern is to have Airflow schedule a job that uses Trino to identify the bad records from your data lake (e.g., Ceph object storage with Apache Iceberg tables), then publish repair events into Kafka, and have an online processor like Flink reconcile the datastore (e.g., MySQL). Change data capture closes the loop: Debezium streams the repaired rows from the datastore through Kafka, and an indexer writes them back into the lake.

References