How a Mid-Size Retailer Cut Inventory Costs by $2.3M After Moving to Snowflake

The Challenge

This retailer runs 12 physical stores and an e-commerce platform that does about $45M in annual revenue. Their inventory system sat on a PostgreSQL 11 database that was originally designed in 2018 for maybe a tenth of the transaction volume they're processing now. Nobody had touched the schema since.

The nightly batch job - a set of 23 interdependent SQL scripts triggered by cron - took over 6 hours to complete. That meant every morning, the stock levels visible to store managers were already stale by the time they opened their laptops. Overstock on slow-moving SKUs was costing roughly $280K/month in warehousing fees because nobody could see the real demand signal until it was too late.

Their data team of 3 people spent most of their time writing custom SQL patches and babysitting ETL scripts that broke every time the POS system pushed a schema change. Actual analysis? Maybe 20% of their week, on a good week. Forecasting was done in Excel spreadsheets that one senior analyst maintained - and when she went on vacation, nobody else could update them.

What We Built

We migrated the entire PostgreSQL warehouse to Snowflake over 8 weeks. The first thing we did was set up zero-copy cloning for dev and test environments - this alone saved the team 2-3 days per sprint that they'd been spending on environment provisioning and data refresh. The production database was on a Snowflake MEDIUM warehouse; dev and test clones cost essentially nothing until someone actually queried them.

We replaced those 23 cron-triggered SQL scripts with Talend ETL pipelines that sync POS transactions and e-commerce orders every 15 minutes. The key design decision: we built the pipelines to be idempotent with merge-based upserts so that if a job fails midway, you just re-run it. No more 3am pages because a batch job died at step 17 of 23 and someone needs to figure out the recovery sequence.

For forecasting, we built a demand prediction model in Python that reads directly from Snowflake via the Snowpark connector. It replaced the Excel-based forecasting entirely. The model runs daily, writes predictions back to a FORECASTS schema in Snowflake, and those predictions feed directly into the Power BI dashboards. We also configured row-level security in Power BI so regional managers only see data for their stores - no more accidentally sharing competitor-region numbers in meetings.

Results

Tech Stack

What We Learned

• Zero-copy cloning is a bigger deal than it sounds. The team was spending 2-3 days per sprint just provisioning test environments. On Snowflake, a clone of a 400GB database takes about 10 seconds and costs nothing until you query it. That alone changed how they work.
• 15-minute sync beats nightly batch for retail, but don't go real-time unless you need it. We considered streaming with Snowpipe, but the business didn't need sub-minute latency. The 15-minute Talend sync hit the sweet spot between freshness and complexity. Real-time would've tripled the maintenance burden for no measurable business gain.
• The forecasting model was only as good as the data feeding it. Our first model iteration had 84% accuracy. It jumped to 97% after we fixed a data quality issue where returned items weren't being subtracted from sales counts in the POS sync. Garbage in, garbage out - even with a solid model.