Bank Nightly Db2 Load Misses SLA and Duplicates on Rerun
Meridian Bank loads 1.2 TB per day from an on-premises Db2 ledger into Iceberg tables in watsonx.data. The source has inserts and updates; every row carries a last_updated timestamp and a ledger_id business key. The current job truncates the target and reloads it, takes seven hours, and must finish by 04:00. After a failed run, operators rerun the job and create duplicate ledger rows. The target now contains about 900,000 files averaging 2 MB. The Db2 team will provide only a read-only account and permits extracts between 22:00 and 04:00. The connection currently uses a shared administrator ID over a public endpoint, which audit has flagged. The bank wants daily freshness, rerun safety, and less object-storage overhead without changing the Db2 application. A Spark engine is available in watsonx.data, and the target is partitioned by business date.
1. Design the daily ingestion pattern that meets the four-hour window and is safe to rerun after failure.
Read the worked answer
Pattern: replace truncate-and-reload with a high-water-mark incremental load plus an idempotent merge. Persist the maximum processed last_updated and a run identifier in a control table. Extract only rows changed since the watermark into a staging Iceberg table, deduplicate by ledger_id keeping the latest timestamp, then MERGE into the target on ledger_id. If the engine's ingestion job cannot MERGE, stage and use an idempotent partition overwrite for the affected business dates. Rerunning the same watermark must produce the same target state, so operators never double-load. Window: the source read window is 22:00-04:00; start the extract at 22:00, keep the merge and compaction inside the same window. For the initial 1.2 TB seed, parallelize by key range or date and load into partitions rather than one transaction. Late data: allow a lookback of a few days and reprocess affected partitions. Validation: row-count reconciliation by business date, duplicate check on ledger_id, and a freshness check on the maximum source timestamp. Test a forced failure and rerun before cutover.
2. What connection and credential controls should be applied to the Db2 source, and why not simply reuse the administrator ID?
Read the worked answer
Least privilege: use a dedicated read-only service account with SELECT only on the required schemas and views, not a shared administrator. The administrator ID expands blast radius and makes audit attribution impossible. Secrets: store the credential in a secret manager and inject it at runtime; do not embed it in connection definitions or job parameters. Rotate it on a schedule and after staff changes. Network: connect over a private endpoint or private network path, enforce TLS, and remove the public endpoint. Restrict the source-side allowed hosts and connection count so ingestion cannot exhaust Db2 sessions. Separation: use different accounts for ingestion and analyst federation; the ingestion account should not be usable for ad-hoc queries. Monitoring: alert on failed logins, unusual extract volumes, and off-window access. These controls address audit's finding and reduce the chance that a compromised job credential can modify the ledger.
3. How would you reduce 900,000 small files without breaking concurrent readers or blowing the load window?
Read the worked answer
Compaction, not reload: run an Iceberg rewrite that bin-packs small files into 128-256 MB files, partition by partition. Do not rewrite the whole table nightly. Start with the partitions that dashboards read most and the most recent business dates. Concurrency: Iceberg snapshots let readers continue on the old snapshot while compaction commits a new one; avoid long-running locks. Schedule compaction after the merge and before the 04:00 SLA, or in a separate maintenance window if it threatens the load. Manifests: rewrite manifests and expire old snapshots so planning does not scan 900,000 entries. Measurement: track file count, average file size, manifest count, and query planning time before and after. Guardrail: prevent the source of small files by tuning the Spark write partition count and target file size; otherwise compaction becomes a permanent nightly tax. If compaction cannot fit the window, move it to a dedicated engine so it does not compete with ingestion.