A mid-market company came to us after getting a quote from a large consulting firm: 18 months, $200,000+, and a team of twelve. They needed a centralized data warehouse that unified data from their ERP, CRM, marketing platforms, and transactional database. We delivered the whole thing in four weeks for a fraction of the cost.
The Starting Point
The company was running a PostgreSQL transactional database that had grown to over 100 million rows. It was never designed for analytical queries — it was an OLTP system doing double duty. Report generation was slow. Some queries took 15–20 minutes. The finance team ran their reports at 6 AM to avoid impacting production. Marketing had no access to data at all and was making decisions based on platform-specific dashboards that didn't connect to revenue.
Multiple departments needed analytics, but every team was working with a different slice of truth. There was no single source of record for key metrics like customer lifetime value, marketing attribution, or inventory turnover.
The Architecture
We built a three-layer deployment that cleanly separates concerns:
- Layer 1 — Ingestion. Automated pipelines pull data from PostgreSQL, HubSpot, Google Analytics, Shopify, and internal APIs on a 5-minute refresh cycle. Change-data-capture for the transactional database, full-sync for smaller sources.
- Layer 2 — Warehouse. Google BigQuery as the analytical engine. Denormalized fact tables, dimensional models, and pre-computed aggregates for the queries that matter most. BigQuery handles 100M+ rows without breaking a sweat — most analytical queries return in under 3 seconds.
- Layer 3 — Presentation. Custom dashboards tailored to each department. Finance sees margin and cash flow. Marketing sees attribution, CAC, and LTV. Operations sees inventory, fulfillment, and throughput. Everyone sees the same underlying numbers.
The Timeline
Week 1: Data audit and pipeline setup. We mapped every source system, identified key entities and relationships, and got ingestion running into BigQuery. By Friday, raw data was flowing.
Week 2:Warehouse modeling. Built the dimensional model, created fact and dimension tables, wrote the transformation logic. Handled the messy parts — deduplication, timezone normalization, currency conversion, null handling in legacy records.
Week 3:Dashboard development. Department-specific views built to each team's actual questions, not generic templates. Iterative feedback with stakeholders — adjust a metric here, add a drill-down there.
Week 4: QA, validation, and handoff. Cross-referenced dashboard numbers against known financial reports. Trained end users. Deployed monitoring and alerting for pipeline failures.
Why the Big Firm Quoted 18 Months
Large consulting firms optimize for billable hours, not delivery speed. An 18-month project with a team of twelve generates significantly more revenue than a 4-week engagement with one or two senior engineers. The incentive structure is misaligned with the client's interest.
The other factor is technology choice. Many firms default to enterprise-grade platforms (Informatica, Snowflake with a managed services layer, Talend) that add complexity and cost without proportional benefit for mid-market companies. BigQuery with custom pipelines is simpler, cheaper, and more than capable for datasets in the hundreds of millions of rows.
The Result
Total cost: $12,500 CAD for the build, plus roughly $150/month in BigQuery and infrastructure costs. The company went from having no centralized analytics to having every department working off the same data, refreshed every five minutes, with sub-3-second query times.
The software they now own is also an intangible asset on their balance sheet and qualifies for SR&ED tax credits — making the effective cost even lower.
Want to discuss this? Get in touch →
Related: Case study: outcomes & financial picture → · Apps, APIs & More → · Dashboards & KPI Reporting →