How to connect Accredo to Snowflake

Once your business is running on Accredo plus Shopify plus HubSpot plus a payroll provider, the questions you want to ask cross all of them: “What’s gross margin by branch by month?”, “Which sales reps have the worst payment-terms outcomes?”, “How does this quarter compare to the same quarter last year on cohort retention?”

Excel can’t answer those reliably. Accredo’s built-in reporting can’t either. You want a data warehouse, and Snowflake is the right answer for most NZ businesses operating at this size.

This is what the Rapido Snowflake pipeline actually looks like — not a marketing diagram, the real thing we run for customers today.

Architecture: the medallion pattern

The pipeline uses the standard medallion architecture. Three layers, each with a clear job:

Bronze — raw VARIANT storage. Every record from Accredo as it landed, every version preserved. Audit trail by default.
Silver — cleansed, deduplicated, typed entity tables. One row per current entity, with the latest version selected via QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC) = 1.
Gold — a proper star schema. Dimensions and facts, ready for analytics queries.
Analytics — pre-aggregated views over Gold. KPIs, year-over-year comparisons, rankings — directly consumable by Power BI.

Bronze → Silver → Gold are all wired together with Snowflake Dynamic Tables. You set a refresh target on Gold (default TARGET_LAG = '1 day'), Snowflake works out the rest of the dependency chain automatically — no Airflow, no dbt scheduler.

What lands in Bronze

The Python sync layer reads from Accredo’s Web Service API — OData v4 returning JSON — and writes to 21 Bronze tables, one per source entity. Headline ones:

AR_CUSTOMER_RAW — debtors
AP_CREDITOR_RAW — creditors / suppliers
IC_PRODUCT_RAW — products
IC_TRANSACTION_RAW — every inventory movement
OE_ORDER_RAW — sales orders (header + lines)
PO_ORDER_RAW — purchase orders
IN_INVOICE_RAW — invoices
…plus 14 more covering periods, locations, branches, sales people, sales areas, stock groups, departments, GL journals, etc.

Each row is a single API response, stored as VARIANT. Nothing is dropped. Every change a record has ever had is queryable in Bronze.

Silver: cleansed entities

Silver flattens the VARIANT into typed columns and selects the latest version per entity. 21 Silver tables mirror Bronze 1:1, but readable:

CUSTOMER
PRODUCT
INVOICE + INVOICE_LINE
SALES_ORDER + ORDER_LINE
PURCHASE_ORDER + PURCHASE_ORDER_LINE
INVENTORY_TRANSACTION
…

Silver tables are Snowflake Dynamic Tables with TARGET_LAG = DOWNSTREAM, which means they refresh whenever Gold needs them — no scheduling to maintain.

Gold: star schema for analytics

Gold is the layer your BI team actually queries. Ten dimensions, four facts.

Dimensions:

DIM_CUSTOMER — customer code, name, category, sales person, branch
DIM_PRODUCT — product code, description, stock group, sell price
DIM_PERIOD — Accredo periods so reports tie back cleanly to GL
DIM_CREDITOR — suppliers
DIM_SALES_PERSON — sales rep
DIM_SALES_AREA — sales territory
DIM_STOCK_GROUP — product grouping
DIM_LOCATION — physical locations
DIM_BRANCH — Saturn branches
DIM_DEPARTMENT — Saturn departments

Facts:

FACT_INVOICE_LINE — every invoice line, with calculated EXCLUSIVE_AMOUNT and GROSS_MARGIN
FACT_ORDER_LINE — every sales-order line
FACT_PURCHASE_ORDER_LINE — every PO line
FACT_INVENTORY_TXN — every stock movement, by product and location

That’s enough to answer almost every question a finance or ops team has, without writing complex joins.

Analytics: views ready for Power BI

The Analytics layer sits on top of Gold as pre-aggregated views — KPIs, YoY comparisons, ranking queries. Most customers point Power BI directly at these views and get useful dashboards in an afternoon, not a quarter.

Other tools that consume the same Gold layer:

Looker, Mode, Hex, Tableau — all speak Snowflake natively
dbt — for further modelling beyond what Gold gives you
AI-on-warehouse tools like Fabi or chat-to-warehouse interfaces

Power BI is the most common because most NZ businesses already pay for it as part of Microsoft 365.

What’s involved on your side

Three things:

A Snowflake account. Not necessarily a big one — a Standard edition trial is enough to see this working with your real data. Production usually runs on Standard or Enterprise depending on data residency and concurrency needs.
Read access to your Accredo Web Service API. The pipeline uses MongoDB-managed tokens for rotation; we wire that up.
A target Snowflake database. We default to RAPIDO_ERP with separate schemas for Bronze, Silver, Gold, Analytics — easy to govern with role-based access.

The pipeline itself runs as a Python service we operate. You don’t need to maintain it; you consume Gold and Analytics.

What changes for finance

Three things, all positive:

Month-end becomes faster. Reconciliations run while you sleep. Variances surface as exceptions, not “let me re-export Accredo to Excel.”
The CFO’s quarterly questions become standing dashboards. Once you’ve answered “gross margin by branch” once, it lives in a Power BI dashboard forever.
You unlock the rest of your stack. Tools that don’t have Accredo connectors but speak Snowflake — and that’s most modern BI/AI tooling — suddenly have access to your ERP data.

Cost note

Snowflake bills for storage (cheap) and compute (variable). The Rapido side is fixed. The Snowflake side depends on refresh cadence, query volume, and warehouse size — we’ll scope it on a 30-minute call against your specific shape.

Get started

The first step is a 30-minute discovery call. We connect to your Accredo, agree which entities to publish, and scope refresh cadence and target warehouse. Most customers see Bronze populating within a day and full Gold within a week. Book the call.