Seven-Hour File Load Against a 90-Minute SLA
A nightly mapping reads 400 gzipped CSV files (210 GB, 180 million rows) from Amazon S3, filters to active orders, looks up a 12-million-row CUSTOMER table, joins a 50-thousand-row REGION table, applies expressions, and loads Snowflake. The Secure Agent has 8 vCPUs and 32 GB RAM. The lookup cache for CUSTOMER spills to disk; JVM garbage collection is high. Files are read serially, and the target uses row-by-row inserts. Runtime is 7 hours against a 90-minute SLA. Only about 35% of source rows are active, and 60% of CUSTOMER rows are inactive. The customer table changes daily. The team can change mapping logic, partitioning, and load method, but cannot add another Secure Agent this quarter.
1. Rank the likely bottlenecks and propose a revised mapping flow with the biggest expected impact.
Read the worked answer
The biggest cost is not the 50-thousand-row REGION join; it is moving 210 GB through a row-by-row agent and building a 12-million-row lookup cache. The lookup spills, so probes may hit disk, and GC pressure slows every row. Serial file reads underuse 8 vCPUs, and row-by-row inserts add network round trips.
Revised flow, in priority order:
- Filter active orders immediately after the source and before the lookup. That cuts rows to roughly 63 million.
- Reduce the CUSTOMER lookup to active rows and only needed columns. If the lookup is relational, use a lookup override or filter to about 4.8 million rows.
- Use a persistent, shared lookup cache refreshed once per day, not rebuilt per partition, and confirm it fits in memory.
- Partition by file list across 4-8 partitions rather than by individual rows, keeping the cache shared. Validate memory before increasing partitions; otherwise each partition may copy the cache.
- Better still, load the filtered data into a Snowflake staging table and perform the CUSTOMER join and MERGE in Snowflake, avoiding the agent cache and row-by-row inserts.
This addresses volume, memory, and target round trips rather than just adding partitions.
2. Would you replace the lookup with a joiner or move the join to Snowflake? Explain the trade-offs.
Read the worked answer
A joiner is not automatically faster. It must sort both inputs unless you can guarantee sorted data, and sorting 63 million active rows plus 12 million customers in the agent adds disk I/O and memory pressure. A joiner can be partitioned, but the large side may still spill, and the current agent is already short on memory.
A lookup is better for a small, filtered reference set. Use lookup override, persistent/shared cache, and cache only active customers. That is the quickest fix. However, if the customer table is in Snowflake and the order data can be staged there, a SQL join and MERGE is the strongest option because it uses the warehouse's distributed compute and avoids agent memory limits.
Decision: use a filtered lookup for an interim fix; use Snowflake staging plus SQL join/MERGE for the SLA. Do not choose a joiner just to avoid cache tuning, because the sort cost can be worse than the lookup it replaces.
3. How would you validate that the redesigned flow has no data loss?
Read the worked answer
Build a control framework rather than comparing only final row counts. Capture source file count, source row count, active-row count after the early filter, lookup match and no-match counts, reject counts, and target insert/update counts. Reconcile these totals: active source rows should equal matched plus unmatched plus rejected, and target rows should equal the expected MERGE result.
Compare financial control totals such as sum of order amount and distinct order count by day. Run the old and new flows in parallel to separate target schemas for several days and compare hashes for a sample of order IDs. Test a lookup miss, an inactive customer, a duplicate order, and a malformed file. After cutover, monitor SLA, lookup cache spill, GC time, partition skew, and reject rate. Investigate any partition that processes far more files or rows than its peers.