Building an Automated Accounts Payable Automation Data Pipeline to Snowflake
Building an Automated Accounts Payable Automation Data Pipeline to Snowflake
Extraction and validation solve the "getting clean data" problem. But clean data sitting in a Python script's memory, or scattered across local JSON files, isn't useful to a finance team. It needs a permanent, queryable, governed home. In this post, we walk through how RUSA Analytics built the ETL layer of our Accounts Payable Automation pipeline — the piece that takes validated records and loads them into Snowflake as a single source of truth.
The Problem With Scattered Data
Before this pipeline existed, the natural failure mode for any automation project looked like this: extraction runs, validation runs, and the results get written to a folder full of CSV or JSON files. It works — for about a week. Then someone asks a simple question, like "how many invoices from this distributor were flagged as mismatches last month?", and answering it means manually opening a dozen files and stitching the numbers together by hand.
Scattered data, manual loading, and poor organization aren't really separate problems — they're the same problem wearing different clothes. The fix isn't a better spreadsheet. It's a proper data warehouse with an automated loading process behind it.
Why Snowflake
We chose Snowflake as the storage layer for a few concrete reasons that matter for this specific use case:
Separation of storage and compute, so loading large volumes of invoice data doesn't interfere with someone running a Power BI report at the same time.
Structured schema support, which fits naturally with the clean JSON output coming out of our extraction and validation agents.
Direct, native connectivity to Power BI, which matters because the whole point of this pipeline is to end in a live dashboard, not another static export.
Governed access, so finance, distributor management, and analytics teams can all query the same trusted tables without needing their own copies of the data.
The Pipeline Architecture
The ETL pipeline moves three distinct types of data into Snowflake:
Validated invoice records — the clean output of the extraction and validation agents, ready for reporting
Exception records — invoices flagged as MISMATCH or NOT_FOUND, along with the specific reason
Distributor master data — the reference dataset that validation checks against, kept current inside Snowflake itself
Keeping all three in the same governed warehouse means every downstream report — invoice KPIs, validation analysis, exception analysis, rebate analytics — pulls from the same consistent source, instead of three teams working off three different exports that inevitably drift out of sync.
Setting Up the Connection
Before loading anything, the pipeline connects to Snowflake from Python using the Snowflake connector, targeting a dedicated database and schema built specifically for this project — in our case, a database called APDL_POC, structured to hold staging tables, distributor master data, and enriched/validated invoice tables separately.
python
import snowflake.connector
conn = snowflake.connector.connect(
account='your_account_identifier',
user='your_username',
password='your_password',
warehouse='your_warehouse',
database='APDL_POC',
schema='PUBLIC'
)Separating staging tables from enriched tables matters more than it might seem at first. Staging tables hold raw, freshly-loaded data exactly as it arrived from the validation stage. Enriched tables hold the final, business-ready version — joined with distributor master data, tagged with validation status, ready for a BI tool to query directly. This separation means a failed or partial load never corrupts the "clean" tables that dashboards depend on.
Loading Distributor Invoice Data
Once connected, the pipeline runs a straightforward but carefully ordered process: load raw extracted-and-validated records into a staging table, then run a transformation step that enriches those records — joining in distributor master data, applying validation status, and formatting fields consistently — before writing the final result into the reporting tables that Power BI reads from.
Using the Database Explorer inside Snowflake, you can watch this structure directly: distinct tables for staged records, distributor master data, and enriched, validation-tagged invoice records, all living in the same governed database instead of being scattered across a filesystem.
Verifying the Load
An ETL pipeline that runs silently and never gets checked is a liability, not an asset. Part of building this pipeline properly meant writing verification queries that run right after each load — simple SQL checks that confirm row counts match expectations, that no distributor names came through as null, and that every record has a valid validation status attached to it.
sql
SELECT DISTRIBUTOR_NAME, VALIDATION_STATUS, COUNT(*) AS RECORD_COUNT
FROM APDL_POC.PUBLIC.ENRICHED_INVOICES
GROUP BY DISTRIBUTOR_NAME, VALIDATION_STATUS
ORDER BY DISTRIBUTOR_NAME;This kind of query does double duty — it's both a data quality check and, informally, a first look at exactly the kind of validation summary that later becomes a Power BI visual.
Why This Step Is the One Businesses Skip — And Shouldn't
It's common for teams building their first automation project to stop right after validation, treating "we extracted and checked the data" as the finish line. In practice, that's the point where a lot of automation projects quietly fail to deliver ongoing value — because without a proper warehouse and a repeatable, automated load process, every new batch of invoices requires someone to manually re-run scripts and re-export files. The pipeline becomes another manual process, just a slightly faster one.
Building the Snowflake loading layer properly — with staging tables, enrichment logic, and verification queries — is what turns a one-time script into a system that runs the same way, reliably, every single time new invoices come in.
What This Enables Downstream
With validated invoice data, exception records, and distributor master data all living in Snowflake, the pipeline is finally ready for its last stage: Power BI. Because Power BI connects directly to Snowflake, every KPI card, chart, and filter on the dashboard reflects the current state of the warehouse — not a snapshot from whenever someone last remembered to export a CSV.
This is the architectural principle that makes the whole system worth building: Snowflake serves as the single, governed source of truth for every downstream report, so finance and distributor teams are always looking at the same numbers, updated automatically as new invoices flow through the pipeline.
The Bigger Picture
A data pipeline isn't glamorous work — there are no dashboards to screenshot, no AI model to demo. But it's the piece that determines whether an automation project becomes a permanent system or a one-off proof of concept that quietly stops being maintained after a few months. Getting the ETL layer right — staging, enrichment, verification, all automated — is what makes everything upstream (extraction, validation) and everything downstream (Power BI reporting) actually sustainable.
Comments