Scheduled sync reports success while CRM receives nothing for three days
ContactSync runs every 10 minutes. It queries an on-premises Oracle Database through a connectivity agent for rows where LAST_UPDATED > :watermark, maps them to a CRM REST API, then writes the new watermark to a lookup. After a database failover at 03:20 on Tuesday, the flow has reported 'Completed successfully' for three days while CRM has received no new contacts. The activity stream shows the database poll returning 0 rows on every run. The last successful run wrote watermark 2025-03-11T03:19:58Z. The integration uses basic tracking, so payloads are not retained, and no business identifier is defined. The agent was restarted during the failover and shows Running on a single host; a second host was decommissioned last month. Alerting is a daily email listing failed instances.
1. Diagnose the silent zero-row behaviour and list the evidence you would pull to confirm it.
Read the worked answer
Work from the watermark arithmetic: if every poll returns 0 rows and the flow still succeeds, either the predicate no longer matches or the stored watermark is ahead of the data. At 03:20 the database was failing over and the extract likely returned zero rows or faulted; if the watermark update step sits outside the for-each, or inside a scope configured to continue on fault, it still executed with a current timestamp. From that moment LAST_UPDATED > watermark matches nothing, and 'success' is genuine - OIC does not consider zero rows a fault.
Evidence: the current lookup value (lookups are versioned; check history if available), max(LAST_UPDATED) in the source, the per-run step metrics in the activity stream (rows read, not just overall status), and the position of the update step relative to the loop and fault handler. Basic tracking without payloads will not show query results - which is itself the gap to close.
2. What instrumentation and design changes prevent a repeat of a three-day silent gap?
Read the worked answer
First, make silent zero impossible in the flow: after the extract, raise a fault when the source count for the window is zero across N consecutive runs while the feed is expected to be active, and never advance the watermark on a zero-row or faulted run. Advance it only after every record in the window is confirmed at the target.
Second, publish observable signals per run - rows read, watermark value, run duration - to OCI Monitoring and alarm on rows equal to zero for three consecutive runs and on watermark staleness (now minus watermark beyond, say, 30 minutes during business hours). A daily failed-instance email cannot detect a flow that never fails.
Third, define a business identifier (contact id and watermark) so instances are searchable, and raise tracking for this integration to a level that retains payloads, accepting the storage cost. Add a Logging activity at INFO writing the predicate, row count and watermark to OCI Logging. Finally, restore agent high availability: one host means any restart severs database access.
3. How do you recover the three-day gap without creating duplicates, and what is the rollback point?
Read the worked answer
Recovery is a controlled replay. Query the source for rows with LAST_UPDATED between the last good watermark (2025-03-11T03:19:58Z) and now, in bounded windows, and push them through the flow with the watermark pinned or bypassed. Before running it, confirm the CRM write is an upsert keyed on the contact's external id; if it is a blind create, replay will duplicate three days of contacts and you must deduplicate first. Run a small window, verify counts against the source, then continue.
After the replay is verified, set the watermark to the last good value and let the next scheduled run re-read from there - or to the replay's high-water mark if the replay covered everything. Keep the previous integration version activatable as the rollback point, and confirm only one version is active. Expect a customer-visible gap: three days of contact sync should be communicated to the business owner before, not after, the catch-up completes.