Seeing intermittent mismatches between our LOS and servicing data after the 2 a.m. ET nightly ETL; we’re getting 0.3–0.5% loan count deltas in downstream risk reports until the 05:15 reconciliation completes. Has anyone shifted to micro-batching or CDC-based replication to keep SLAs without sacrificing referential integrity across borrower, note, and collateral tables?
Set an as-of watermark at 02:00 ET; swap partitions so reports read one snapshot. Cut our ‘0.3–0.5%’ drift to about 0.05%.
Building on @t_clark1991: switch to CDC but publish a commit-scoped watermark (min replicated LSN/xid across borrower, note, and collateral) and have reports read only rows <= that marker — like a tiny “pencils down” per micro-batch — so you get near‑real‑time without orphaned joins. Are you on SQL Server CDC or Debezium?
But we added a 4‑minute write-quiesce at 2:00 ET; our 0.5% drift vanished. @t_clark1991, 3–5 min delayed replica?
And instead of chasing CDC timing, create a 02:00 ET loan_population snapshot (loan_ids + partition keys) and have risk jobs filter/join to that set until 05:15; everything else can stream in behind it. It cut our loan count wobble to zero because membership is frozen, like boarding with a printed manifest. @l_thomas99, could you tolerate a 60–90s build to materialize that set each night?
Quick win: take a transactionally consistent “last good cut” for reporting by creating a DB snapshot/clone just before the nightly load (snapshot isolation or a zero‑copy clone) and pin all risk jobs to it until reconciliation — it’s a photo, not a live stream. You’ll pay a bit of storage/IO and need a tiny manifest to route queries, but it avoids CDC timing games; does your stack support pg_export_snapshot or Snowflake clones?
We killed a similar drift by adding a small “watermark” ledger: each micro‑batch stamps borrower/note/collateral with a batch_id and records its high_watermark, and a reporting view only exposes rows up to the lowest watermark across the three, so joins stay consistent mid‑load. Small caveat: if one stream lags, the view holds the prior cut until it catches up. @alana_s do you already tag rows with a batch_id or commit_ts to anchor this?