Our cloud data warehouse bill is growing 20% month-over-month. We have five years of historical data, but 90% of our queries only touch the last 12 months. What is the best way to "offload" or archive this older data to cheaper storage (like S3 or Cold Storage) while still making it available for occasional year-over-year audits? I don't want to completely delete it, but I can't justify the high cost of keeping it in "Hot" storage.
3 answers
Do you have a clear "Data Retention Policy" signed off by legal, or are you just archiving everything indefinitely "just in case"?
We implemented "Partition Pruning" and strict query limits. If a query doesn't include a WHERE date > ... clause, it gets automatically rejected by the system.
Query limits saved us! We also added a warning in our BI tool that pops up if a user tries to pull more than 2 years of data at once.
The most effective strategy is "Multi-Temperature Data Management." You should partition your largest tables by date (e.g., Year/Month). For data older than 12 months, move it to a "Cold" storage tier like Amazon S3 or Google Cloud Storage in a compressed format like Parquet. In your warehouse, you can then use "External Tables" (like Snowflake External Tables or BigQuery External Data Sources) to point to those files. This way, the data is removed from your expensive "active" storage, but users can still query it using standard SQL if they absolutely need to. Performance will be slower for the archived data, but since it's only for occasional audits, the massive cost savings on storage and "Always-On" compute will easily justify the trade-off.
Thomas, you hit the nail on the head. We don't have a formal policy, so the default has been "Keep everything forever." I’m trying to convince the legal team that after 7 years, we should be able to purge the data entirely. Until then, I need a technical solution to hide the cost. If I use External Tables, how do I prevent accidental "Full Table Scans" that might try to pull in the archived data and spike our compute costs? I want to make sure that a rogue analyst doesn't run a SELECT * and inadvertently trigger a massive scan across five years of S3 files.