Nightly Sync Exceeds Its Window After a Source Schema Change
A retail customer runs a nightly Boomi process that pulls 1.2 million changed customer rows from an on-premise Oracle database into Salesforce and a Snowflake warehouse. The window is 02:00–05:00, and the run historically finishes in 2h40m. Three weeks ago a new nullable loyalty_tier column appeared in Oracle, and the process now fails validation on 400–900 rows per night, retries the whole batch twice, and finishes at 05:50. Rows land in Salesforce but the Snowflake load is skipped because the process aborts before the second branch. The source team will not remove the column or guarantee its population. Downstream analysts need the warehouse current by 06:00.
1. Diagnose why the schema change caused a window overrun and the warehouse gap, and propose a change that restores the 06:00 freshness commitment without asking the source team for anything.
Read the worked answer
The overrun is not caused by the new column itself but by how the process reacts to it. Validation fails on a subset of rows, the process treats that as a batch-level failure, and the retry policy re-runs the entire 1.2M-row extract. Two full retries roughly triple the extract and transform time, which is what pushes 2h40m past 05:00. The warehouse gap is a separate design flaw: the Salesforce and Snowflake branches are sequenced so that a failure in the first branch aborts the process before the second branch executes.
Fix the reaction, not the source. First, add a default or null-safe mapping for loyalty_tier in the profile so unpopulated values pass validation and are carried as a known placeholder. Second, replace whole-batch retry with document-level error handling: route failing documents to a dead-letter store and continue, so one bad row costs one row, not a full re-extract. Third, make the two targets independent - separate branches or separate processes with their own schedules - so a Salesforce failure cannot suppress the Snowflake load. Finally, cap retries and alert on dead-letter volume rather than retrying blindly. With retries removed from the happy path, the run returns to roughly the original duration and the warehouse is current well before 06:00.
2. The analysts want to know whether rows with a missing `loyalty_tier` are safe to load. What would you tell them, and what would you instrument?
Read the worked answer
Tell them the rows are safe to load but the value is not trustworthy yet, and those are different questions. The rows are complete on every other attribute, so excluding them would understate customer counts. The tier field should be loaded as an explicit UNKNOWN member rather than null-imputed to a real tier, because silently defaulting to a tier would corrupt segmentation and any tier-based reporting.
Instrument three things. A per-run count of rows arriving with a missing tier, written to a control table so the trend is visible. A reconciliation check comparing source row count for the change window against rows landed in each target, alerting on variance above a small threshold. And a freshness timestamp per target so the 06:00 commitment is measured, not assumed. Once the source team does populate the column, the missing-tier count should fall toward zero, which gives you an objective signal that the workaround can be retired.