Nightly Oracle and File Ingestion Under a Shrinking Window
An insurer runs 60 on-premises jobs that drop CSV extracts onto an SFTP server plus 14 Oracle tables replicated to BigQuery. Nightly volume is 2.5 TB and everything must be queryable before the 07:00 claims stand-up. The link to Google Cloud is 1 Gbps and is shared with backup traffic that saturates it between 01:00 and 03:00. Nine of the Oracle tables have no trustworthy updated_at column and no primary key that is stable across updates. Today a failed extract is discovered when the 07:00 report is empty, and recovery means re-running the whole night, which takes 5 hours. Compliance requires that analytics reflect the source as of each night and that analysts never see a partially loaded table.
1. Design the incremental strategy for the nine Oracle tables that lack reliable timestamps and stable keys.
Read the worked answer
First, size the problem: 2.5 TB is the total. If those nine tables are ~200 GB, nightly full extract plus diff is cheap and boring. If they are 2 TB of the 2.5 TB, only change capture saves the SLA and it is worth escalating to the DBA team.
Preferred: log-based CDC. A Datastream stream from Oracle (LogMiner-based) does not care whether updated_at is accurate or whether a business key is stable - it replays redo and preserves the operation, including deletes that a timestamp-based pull would miss entirely. Prerequisites are the negotiation: supplemental logging enabled, a dedicated user with the required grants, and redo retention long enough to survive an outage. Confirm those before promising the design.
Fallback: full extract + hash diff. Extract the table, compute a row hash over all columns, MERGE on whatever composite key uniquely identifies a row, and delete rows whose hash disappeared. This requires a key that is at least unique, even if it is not stable across updates - if a row's key can change, you must diff into a staging table and reconcile by full-row comparison, which is expensive but only feasible at the small-table end of the size estimate.
Do not: rely on ORA_ROWSCN unless the table was created with row-level dependency - it is block-level by default and will silently under-report changes. Verify with the DBA rather than assume.
2. How do you guarantee analysts never see a partially loaded table, and that a re-run does not duplicate data?
Read the worked answer
Make every load land somewhere invisible, then publish in one atomic step.
Pattern: each run writes to stg_<table>__<run_id>. The publish step is a single MERGE (or, for full-refresh tables, a load with WRITE_TRUNCATE into the target partition) executed inside one BigQuery multi-statement transaction. Analysts hold grants on the target table only; the staging dataset is not in their access path. Nothing partially written is ever visible.
Idempotency: the MERGE is keyed on the natural key, so applying the same staging data twice is a no-op. Do not use plain INSERT or WRITE_APPEND anywhere in the path - that is what turns a retry into duplicates. Where a natural key genuinely does not exist, dedupe the staging table first with QUALIFY ROW_NUMBER() OVER (PARTITION BY <key> ORDER BY <ingest_ts> DESC) = 1 and refuse to publish if the dedupe removed an unexpected number of rows.
Freshness contract: write a load_audit row per table per night (source row count, staging row count, merged inserts/updates/deletes, run_id, status) and gate the 07:00 report on it. That converts 'the report is empty' from a discovery into an alarm, and gives the diff you need when a number looks wrong.
Recovery: because the unit of publish is a table-night, re-running is per-table, not per-night - minutes instead of five hours.
3. Is the 07:00 SLA achievable on the current link, and what do you change?
Read the worked answer
Do the arithmetic before the architecture. 2.5 TB is 20 Tb; at 1 Gbps that is ~20,000 seconds, about 5.6 hours at 100% utilization with zero contention, zero protocol overhead, and zero downstream load time. That is already at or past the deadline, and the 01:00-03:00 backup saturation removes two hours from the usable window. The link cannot carry the current volume, so tuning will not fix it.
Ranked actions:
- Compress and columnarize at the source. CSV to gzip or Parquet before transfer is routinely a 5-10x reduction on extract data. This alone can move 2.5 TB to well under 500 GB on the wire and is the cheapest win available.
- Cut the volume, don't move it. Nightly full extracts of tables that change by a few percent are the real cost. CDC or hash-diff (per the earlier task) reduces both bytes and downstream merge work.
- Get off the shared link. Dedicated Interconnect or a bandwidth reservation for the replication window, with backups scheduled outside it.
- Serialize sensibly. Run the largest tables first inside the window so a partial night still has the important tables loaded by 07:00, rather than 60 jobs competing for a saturated pipe.
The judgement: compression plus incremental extraction is an engineering fix you can ship this quarter; a second link is a procurement cycle. Do both, in that order, and measure actual achieved throughput rather than nominal link speed.