Situation
A US-based CPG company specializing in the Better-For-You segment operated on a fully remote setup with Finance, Accounting, and Commercial teams each tracking the 3FS, Revenue, Profitability, and Sales Actuals independently, in a heavily manual, Excel-anchored cycle that was producing different numbers depending on who ran the report.
The firm had no data warehouse. All reporting was done by manually pulling data from source systems, normalizing it in Excel, and building visuals by hand. Distributor flat files from UNFI and KeHE ran to hundreds of thousands of rows each, straining Excel and pushing it to tool limits. Business logic lived in cell formulas and manual steps: fragile, undocumented, and hard to maintain. There was no single source of truth, and the multi-week reporting lag meant Sales Actuals were reconciled long after decisions needed to be made.
Objective
The client needed to dismantle a manual, Excel-anchored reporting infrastructure and replace it with a centralized, automated platform. Four goals defined success:
- Eliminate data silos by replacing disparate source systems with a Cloud Data Warehouse as the single source of truth for Finance, Accounting, and Commercial teams.
- Automate end-to-end data pipelines to remove manual data pulls, normalization, and ad hoc reporting effort.
- Deliver four production-ready BI dashboards, covering 3FS (Financial Statements), Revenue & Sales, Inventory, and Profitability, each with a standardized KPI and metric glossary.
- Build for scale, ensuring the architecture could absorb new data sources and support growth without key-person dependencies.
Approach
We began with a diagnostic exercise to map the current technology environment (every source system, every manual step, and every downstream report) and design an optimal target architecture built for both present and future needs.
Automated ingestion with FivetranWe replaced manual data pulls with fully managed Fivetran connectors auto-loading NetSuite, GoCrisp, and Google Drive flat files into Snowflake on an incremental, 24-hour cadence. Every source (NetSuite, KeHE, UNFI, DOT Foods, Whole Foods, Vistar, Crisp, and others) now flows in automatically without human intervention.
Medallion Architecture on SnowflakeData lands into a Raw/Bronze layer and passes through dbt Cloud’s Cleanse/Silver and Aggregate/Gold layers in mirrored Dev and Prod environments. Every table is a version-controlled dbt model. Business rules, joins, and lineage live in SQL, not in cell formulas. SCD Type-2 and Type-4 logic tracks historical changes across the key dimensions the business needs for accurate financial reporting.
Sigma Computing BI layerWe built an AI-powered, spreadsheet-like BI layer on Sigma Computing that queries the Snowflake warehouse live, giving the FP&A team a familiar interface without the fragility of Excel. Four production dashboards were delivered: Financial Performance (3FS: P&L Summary, Balance Sheet, Cash Flow), Revenue & Sales, Profitability, and Sales Actuals, each with a built-in KPI/metric glossary to standardize definitions across functions.
Figure 1Five pain points of the existing state: no single source of truth, a ~3-week reporting lag, Excel at breaking point with hundreds-of-thousands-of-row distributor files, key-person dependency in cell formulas, and heavy manual reconciliation distorting monthly margin.
Figure 2Data source fragmentation (before state): a Sankey diagram showing how NetSuite, KeHE, UNFI, GoCrisp, POS/Scan data, Whole Foods, DOT Foods, and Vistar each flowed into separate Excel and Google Sheets files at multiple reporting layers, with no central consolidation point.
Figure 3Target architecture: four-stage pipeline, Fivetran (ingest) to Snowflake (store, Medallion) to dbt Cloud (transform, version-controlled SQL) to Sigma Computing (visualize, AI-powered self-service BI). Guiding principles: single source of truth, version-controlled SQL, self-service BI, and DEV/PROD governance.
Figure 4Full data architecture: source systems ingest via Fivetran into Snowflake’s Raw/Bronze layer, pass through dbt-managed Clean/Silver and Aggregate/Gold zones in mirrored Dev and Prod environments, and feed Consumer Data Marts serving four Sigma dashboards.
Figure 5Six impact metrics: 1 single source of truth; ~3 weeks to live (multi-week lag to near real-time); 4 production dashboards; 1.6M+ rows automated; ~3.94M/4.42M rows on the gold spine; 0 key-person formulas.
Figure 6End-to-end data flow (after state): all source systems consolidate through Staging, Raw, Cleansed, and Aggregate layers on Snowflake and dbt, feeding automated 3FS dashboards, Revenue, Open Orders, Inventory, Customer P&L, and Unified Sales Actuals in Sigma.
Impact
FP&A, Accounting, and Commercial teams now reference the same numbers. The mismatches between Sales Actuals and NetSuite that previously drove cross-functional disputes are resolved by the architecture, not by manual reconciliation. The monthly close process moved from a multi-week manual lag to a near-real-time cadence driven by the warehouse refresh.
Over 1.6 million rows of distributor and retailer depletion data that were previously breaking Excel now flow through automatically, governed end-to-end. The gold spine supports approximately 3.94M to 4.42M rows across Transaction Accounting and Sales Accounting Lines, sized to scale comfortably with the company’s growth without any re-platforming.
All business logic was moved out of cell formulas and into version-controlled SQL: maintainable, auditable, and no longer dependent on any single person. With a robust, scalable architecture and data management processes in place, integration of new systems is now seamless, enabling rapid growth and expansion without the fragility that constrained the business before.