Younes El HamriData engineering · MoroccoAvailable 5 days/week

Retail reporting every six hours, with 42% less Snowflake spend

Client
Retail client (anonymised)
Sector
Retail, 12 stores + e-commerce
Delivered
Delivered through Carré Agency
Period
2022–2024
Stack
Azure Data Factory, Databricks, Snowflake, dbt, Power BI, Parquet
Reporting refreshweeklyevery 6 hours
Core dbt modelsfull refreshincremental
Monthly Snowflake spend−42%

The code that changed

models/silver/stg_orders.sqla sketch of the change, not the client's code
− first version
{{ config(materialized='table') }}

select *
from {{ source('shopify', 'orders') }}
+ what replaced it
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

select *
from {{ source('shopify', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Full refresh every six hours, rewritten as an incremental merge: 18 min → under 90 s, and 42% off the monthly Snowflake bill.

Problem

Every Friday the team pulled CSV exports from 12 store POS systems, and the finance controller spent most of Monday stitching them together in Excel to get one report out on Tuesday. Management was making stock decisions on data that was three to seven days old.

What I did

Built an incremental ELT platform: Azure Data Factory pulls the Shopify API, the Postgres ERP and the daily store dumps into Parquet, Databricks flattens the nested Shopify JSON, and dbt models bronze, silver and gold layers in Snowflake for Power BI.

The bottleneck was a person, not a tool

Twelve stores exported CSVs, an e-commerce platform had its own API, and the ERP had a third shape again. None of that was hard. What made it slow was that one controller had to reconcile all of it by hand before anyone saw a number. So the business ran on data that was three to seven days old, and nobody trusted a figure enough to argue with it.

The build

Ingestion. Azure Data Factory pulls incrementally from the Shopify API, the Postgres ERP and the daily store dumps, landing everything as Parquet.

Shaping. Databricks flattens the nested Shopify JSON, which is the part that does not fit a relational model without a fight.

Modelling. dbt builds bronze, silver and gold in Snowflake, so the layer an analyst queries is not the layer the loaders write to.

Serving. Power BI reads gold views only. No report reaches around the model.

The mistake worth publishing

The first version reprocessed 12 million rows every six hours. It worked and the numbers were correct, but every run was a full refresh of data that had barely changed, and Snowflake billed for all of it.

Rewriting the core models as incremental loads took the pipeline from 18 minutes to under 90 seconds and cut the monthly Snowflake bill by 42%.

What I would tell another team

Put the warehouse cost dashboard up on day one, not in month three. On this project the cost graph showed the problem well before any runtime or quality metric did. And once a pipeline is embedded in a business, nobody thanks you for rewriting it later.