Technical Case Study: How We Solved a 5.5GB Data Validation DuckDB OOM Error for Our AI Submission
How our team optimized DuckDB validation code to fix a 2GB memory crash and successfully package a 5.5GB LightGBM model submission without running out of RAM.
Hey everyone! As part of our preparation for the Amazon ML Challenge, our team (ManTech) recently built a machine learning pipeline using LightGBM for a massive business entity resolution challenge.
However, right at the finish line—while packaging our official submission files—we hit a major roadblock: a massive DuckDB OutOfMemoryException (OOM) error on Windows.
If you are working with large datasets, here is exactly how we optimized our validation code, bypassed the 2GB memory cap, and successfully assembled our 5.5GB submission package!
If you are working with large datasets, here is exactly how we optimized our validation code, bypassed the 2GB memory cap, and successfully assembled our 5.5GB submission package!
The Problem: The 5.5GB Bottleneck 📊
Our final submission required two massive tab-separated files:
• matching_results.tsv (1.7M+ rows, ~108 MB)
• candidate_pairs.tsv (1.7M+ rows, 5.54 GB of raw pair data)
Our final submission required two massive tab-separated files:
• matching_results.tsv (1.7M+ rows, ~108 MB)
• candidate_pairs.tsv (1.7M+ rows, 5.54 GB of raw pair data)
To ensure our submission was flawless, our validation script needed to check every single candidate ID against our full test targets.
In our v1 builder, the script attempted a global DISTINCT operation over hundreds of millions of candidate occurrences. Because DuckDB was constrained to a default 2GB memory limit, the system completely ran out of memory and crashed before the lookup could finish.
The Solution: How We Optimized to v2 🛠️:
Instead of just throwing more hardware at the problem, we refactored our Python and DuckDB validation logic to be highly memory-efficient:
Instead of just throwing more hardware at the problem, we refactored our Python and DuckDB validation logic to be highly memory-efficient:
- Swapped DISTINCT for Native Anti Joins: Instead of forcing DuckDB to calculate a massive distinct list of unique IDs across hundreds of millions of rows, we configured it to check every occurrence directly using a native anti join.
- Aggressive Memory Management: We rewrote the script to explicitly free up the compact Source 1 summaries from memory before triggering the massive lookup.
- Optimized Threading and Caps: We disabled insertion-order preservation in the validator, isolated the execution to one thread (--threads 1), and bumped the DuckDB cap to a safe 4GB limit.
The Results: Success! 🚀
By implementing these changes in our updated v2 assembler, the validator passed all integrity checks perfectly! It validated all 74 source/model/artifact files, passed the target ID audit, and successfully compressed the data into our final ManTech_submission.zip package.
By implementing these changes in our updated v2 assembler, the validator passed all integrity checks perfectly! It validated all 74 source/model/artifact files, passed the target ID audit, and successfully compressed the data into our final ManTech_submission.zip package.
Key Takeaways for Student Builders:
• Don't use DISTINCT on massive datasets if a direct streaming join or anti join can achieve the same result with a lower memory footprint.
• When writing validation scripts for AI/ML datasets, always read the archive back to verify CRC and SHA-256 hashes to prevent corruption.
• Keep an isolated, frozen model artifact alongside a clean requirements.txt to guarantee absolute reproducibility.
• Don't use DISTINCT on massive datasets if a direct streaming join or anti join can achieve the same result with a lower memory footprint.
• When writing validation scripts for AI/ML datasets, always read the archive back to verify CRC and SHA-256 hashes to prevent corruption.
• Keep an isolated, frozen model artifact alongside a clean requirements.txt to guarantee absolute reproducibility.
Have you ever run into memory limits while processing large datasets for your AI models? What are your favorite tricks for optimizing DuckDB or pandas? Let's discuss in the comments below! 👇
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article