Near-Real-Time CSV Ingestion with Malformed Files
Adventure Works receives 180 CSV files per hour, about 7 MB each, in an OCI Object Storage bucket. Each file has 20,000 to 40,000 order lines; duplicate order IDs occur when upstream retries. The business wants data visible within 15 minutes and must keep malformed rows for audit without failing the whole batch. The target is a 4-ECPU Autonomous Transaction Processing instance with a 300 GB partitioned target table. The current approach uses SQL*Loader from a jump host and is failing at peak. The team proposes an external table over the bucket and a nightly MERGE. Traffic is bursty: 60 files can arrive in five minutes. They ask for a near-real-time ingestion pattern with acceptable cost and how to handle bad files.
1. Evaluate the external-table-plus-nightly-MERGE proposal against the 15-minute requirement and recommend an ingestion pattern.
Read the worked answer
The proposal fails the visibility requirement because a nightly MERGE cannot make data visible within 15 minutes. An external table over the whole bucket also re-lists and potentially re-reads many objects on every query, which is wasteful when only new files matter. Instead, run a scheduled ingestion job every few minutes that lists the bucket, identifies objects not yet processed using a watermark or control table, and loads only those files with DBMS_CLOUD.COPY_DATA into a staging table. Then MERGE the staging rows into the partitioned target, deduplicating on order ID and using the partition key to limit the merge scope. Track processed object names and ETags so retries are idempotent. Size the job for the burst: 60 files in five minutes is about 420 MB, which is manageable if the job batches file lists and does not start overlapping runs. The judgment is that external tables are useful for ad hoc access, but controlled staging plus MERGE fits the latency and cost requirement better.
2. How should malformed rows and duplicate order IDs be handled without failing the entire batch?
Read the worked answer
Separate row-level validation from file-level acceptance. Load each file into a staging table with a permissive reject limit so one bad row does not abort the batch, then write rejected rows and the source object name to a quarantine table for audit. Validate required columns, data types, and order-ID format in staging, and route failures to the same quarantine path. Deduplicate with a MERGE or an analytic ranking that keeps the latest version by ingestion timestamp, so upstream retries do not create duplicates. Record file-level status as loaded, partially loaded, or quarantined, and alert on quarantine growth rather than silently continuing. If a file is structurally corrupt, mark it failed and do not retry indefinitely; require operator action. The key judgment is that the pipeline must be idempotent and auditable: reprocessing the same object should not duplicate data, and a bad file should be visible without stopping good files.
3. What scheduling, partitioning, and monitoring choices would keep this pipeline reliable and cost-effective on a 4-ECPU instance?
Read the worked answer
Schedule the ingestion job at a short interval that meets the 15-minute visibility target, but add a guard that prevents overlapping runs; a job every five minutes with a single active instance is reasonable. Partition the target by load date or order date and make the MERGE use the partition key so it does not scan all 300 GB. Keep staging tables small by truncating or exchanging partitions after each successful merge. Monitor objects processed per run, rows merged, rejects, elapsed time, and lag from object creation to visibility. Alert on runs that exceed the interval or on growing unprocessed-object counts during the 60-file burst. Watch DB time and I/O on the 4-ECPU instance; if COPY_DATA and MERGE overlap with daytime order traffic, move more work to a low-priority consumer group or adjust the schedule. The judgment is that reliability comes from idempotent watermarks, partition pruning, and overlap control, not from a larger instance.