Moving Retail Inventory Off PostgreSQL: Idempotent Syncs, Clones and Forecasts on Snowflake

Last updated: October 2026

Retail inventory data has a short shelf life. Stock levels that are six hours old at opening time lead to the wrong reorders, and the cost shows up as overstock on slow-moving lines and empty shelves on fast ones.

This article describes the platform we built for a retailer with 12 stores and an online shop turning over about $45 million a year, covered in our retail migration case study. Its inventory system ran on a PostgreSQL 11 database designed in 2018 for a fraction of current volumes, and overstock on slow-moving SKUs was costing about $280,000 a month in warehousing fees. Project details come from that engagement. The SQL and Python are simplified illustrations written for this article.

Why the nightly batch kept failing

The nightly job was 23 interdependent SQL scripts triggered by cron, and it took more than six hours. A failure partway through, at step 17 of 23 for example, meant someone had to work out the recovery sequence, and store managers opened their laptops to stale stock levels either way. The data team of three spent most of its time patching scripts that broke whenever the point-of-sale system changed its schema, and perhaps 20% of its week on analysis.

The design change that mattered most was idempotency. We replaced the scripts with Talend pipelines that sync point-of-sale transactions and e-commerce orders every 15 minutes, and every load uses a merge-based upsert. If a job fails halfway, you run it again, and the result is the same as if it had succeeded the first time.

Idempotent upserts with MERGE

A MERGE is only safely repeatable if its source has at most one row per key. Snowflake's documentation explains why. If one target row matches several source rows and would be updated with different values, the MERGE is nondeterministic, and with the default ERROR_ON_NONDETERMINISTIC_MERGE = TRUE it fails with an error. If a duplicated source row has no match in the target, the INSERT branch runs once per copy and creates duplicates. De-duplicating the source inside the MERGE removes both problems.

MERGE INTO inventory.sales_lines AS t
USING (
    SELECT store_id, txn_id, line_no, sku_id, quantity, net_amount, updated_at
    FROM staging.pos_sales_lines
    WHERE updated_at >= DATEADD(hour, -2, :last_loaded_at)   -- overlap on purpose
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY store_id, txn_id, line_no ORDER BY updated_at DESC) = 1
) AS s
  ON  t.store_id = s.store_id
  AND t.txn_id   = s.txn_id
  AND t.line_no  = s.line_no
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
    sku_id     = s.sku_id,
    quantity   = s.quantity,
    net_amount = s.net_amount,
    updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT
    (store_id, txn_id, line_no, sku_id, quantity, net_amount, updated_at)
    VALUES (s.store_id, s.txn_id, s.line_no, s.sku_id, s.quantity, s.net_amount, s.updated_at);

Because the MERGE is idempotent, each run can deliberately re-read a window that overlaps the previous one, here two hours before the last successful load. Late-arriving rows are picked up, and rows that were already loaded produce no change. The high-water mark itself lives in a small control table that the orchestrator updates only after the MERGE commits; if you load inside Snowflake rather than from an external tool, a stream on the staging table is Snowflake's native way to track what has already been processed.

Zero-copy clones for development and testing

Before the migration, the team spent two to three days every sprint provisioning and refreshing test environments. On Snowflake, a clone of the 400 GB database took about ten seconds. A new clone shares all of the source's micro-partitions and uses no additional storage until data in it changes, at which point only the changed micro-partitions are billed.

-- A full-size development copy, as of now.
CREATE OR REPLACE DATABASE retail_dev CLONE retail_prod;

-- Or as of one hour ago, skipping tables whose Time Travel
-- retention does not reach back that far.
CREATE OR REPLACE DATABASE retail_dev CLONE retail_prod
  AT (OFFSET => -3600)
  IGNORE TABLES WITH INSUFFICIENT DATA RETENTION;

-- Tasks in a clone start suspended; resume only the ones you need.
ALTER TASK retail_dev.ops.refresh_forecasts RESUME;

Clones behave differently from their source in ways that catch teams out:

• Grants. When you clone a database or schema, each child object keeps the privileges granted on its source, but the cloned container itself does not inherit the grants on the source container. For a single table, COPY GRANTS copies explicit grants except ownership.
• Load history. Clones carry no load history, so files that were already loaded into the source can load again if a COPY or pipe runs in the clone.
• Stages, pipes and external tables. Internal named stages are cloned only with INCLUDE INTERNAL STAGES, pipes that reference internal stages are not cloned, and external tables are never cloned with a database or schema.
• Streams and tasks. Unconsumed stream records are not accessible in the clone, and tasks start suspended. Automatic Clustering also starts suspended on cloned tables.

Production ran on a Medium warehouse, and the clones cost nothing in storage until someone changed data in them.

Forecasting next to the data

Forecasting had lived in Excel workbooks that one senior analyst maintained. We replaced them with a demand model in Python that reads from Snowflake through Snowpark, runs daily, and writes its predictions back to a FORECASTS schema, which feeds the Power BI dashboards directly.

from snowflake.snowpark.functions import col, current_date, dateadd, lit

history = (session.table("analytics.daily_sku_net_units")
           .filter(col("sale_date") >= dateadd("day", lit(-730), current_date())))

pdf = history.to_pandas()              # use to_pandas_batches() for very large results
forecast_pdf = build_forecast(pdf)     # your forecasting code

session.write_pandas(forecast_pdf, "SKU_DEMAND", schema="FORECASTS",
                     auto_create_table=True, overwrite=True)

For teams that would rather not maintain their own model, Snowflake's built-in forecasting function, generally available since December 2023, trains one model per series in SQL. It needs at least 12 rows per series.

CREATE SNOWFLAKE.ML.FORECAST forecasts.sku_demand_model(
    INPUT_DATA        => TABLE(analytics.v_daily_sku_net_units),
    SERIES_COLNAME    => 'sku_id',
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME    => 'net_units'
);

SELECT *
FROM TABLE(forecasts.sku_demand_model!FORECAST(FORECASTING_PERIODS => 28));

Whichever route you take, the inputs matter more than the algorithm. Our first model reached 84% accuracy. It rose to 97% after we fixed a data quality problem in the point-of-sale sync: returned items were not being subtracted from sales counts. That is why the examples above forecast net units, sales minus returns, and not gross sales.

Regional managers see only their stores

We configured row-level security in Power BI so that regional managers see only data for their own stores. Roles and their DAX filters are defined in Power BI Desktop under Modeling and Manage roles, and members are assigned in the Power BI service on the semantic model's Security page. A dynamic rule keeps it to one role for every region.

-- Filter on the StoreManagers table, related to Stores
[ManagerEmail] = USERPRINCIPALNAME()

Two details are worth knowing before relying on it. Power BI RLS restricts only users with Viewer permission; workspace Admins, Members and Contributors see all the data. And with DirectQuery, the security in the data source applies, so a Snowflake row access policy, available on Enterprise Edition and above, can enforce the same rule in Snowflake when users connect with single sign-on.

Why 15 minutes and not real time

We considered streaming with Snowpipe, but the business did not need sub-minute latency. The 15-minute sync balanced freshness against complexity, and in our assessment real-time ingestion would have tripled the maintenance burden with no measurable business gain.

The migration took eight weeks. Batch processing went from more than six hours to 18 minutes, forecast accuracy rose to 97% from the 72% of the Excel process, out-of-stock events fell by 40%, and inventory costs fell by $2.3 million a year.