The Data Stack Teardown: What a Real Mid-Size SaaS Analytics Architecture Looks Like

A lot of “modern data stack” content is either too generic to be useful or too tool-obsessed to help anyone make a real architecture decision.

So here is a more honest version.

This is a teardown of the kind of analytics architecture I would actually recommend for a mid-size SaaS company with real growth pressure, low analytics maturity, and the usual mess underneath the dashboards.

Not a perfect architecture. Not the only architecture. But a practical one that can survive contact with the business.

The company profile behind this example is intentionally anonymized, but the operating pattern is real:

• roughly 200-400 employees
• venture-backed, growing, and under pressure to make faster decisions
• marketing, product, sales, and finance all using data differently
• conflicting numbers across systems
• one small data team trying to support everyone at once

The point is not “look at this beautiful stack.”

The point is to show what a usable architecture looks like when the real job is turning messy marketing and revenue data into decisions leaders trust.

The Situation We Were Designing For

This was not a greenfield build.

The team already had data moving around. That was part of the problem.

They had:

• application data in PostgreSQL
• billing data in Stripe
• CRM data in HubSpot
• product events in Segment
• marketing data from Google Ads, LinkedIn, and Meta
• customer support data in Intercom
• dashboards in Looker
• a handful of legacy scripts and spreadsheet-based workarounds doing silent “integration” work nobody wanted to admit existed

The business pressure sounded familiar:

• marketing wanted attribution and CAC views they could defend
• finance wanted revenue reporting that matched the board deck
• product wanted activation and retention reporting they could actually use
• the data team wanted to stop being blamed for every mismatch between systems

This is the kind of environment where a stack decision is not just a tooling decision. It is a trust design decision.

The Architecture in One View

Here is the high-level structure.

                    SOURCES
  ┌────────────┬─────────┬──────────┬──────────┬────────────┐
  │ Postgres   │ Stripe  │ HubSpot  │ Segment  │ Ad/CX Tools│
  │ App DB     │ Billing │ CRM      │ Events   │ Meta/LI/etc│
  └─────┬──────┴────┬────┴────┬─────┴────┬─────┴─────┬──────┘
        │            │         │          │           │
        └────────────┴─────────┴──────────┴───────────┘
                             │
                             ▼
                   INGESTION / LOAD LAYER
                 Fivetran + selective custom syncs
                             │
                             ▼
                      BIGQUERY WAREHOUSE
        raw_*  →  stg_*  →  int_*  →  mart_* / semantic outputs
                             │
                             ▼
                        dbt TRANSFORMS
      tests • docs • lineage • model ownership • CI validation
                             │
                ┌────────────┴────────────┐
                ▼                         ▼
         LOOKER DASHBOARDS         OPERATIONAL EXPORTS
      executive / revops / mktg     CRM fields, alerts,
        product / finance views      scored segments, QA

That is the simple version.

The more important version is this:

Raw data alone does not create trust.
Trust came from the layers between ingestion and the dashboard:
- naming conventions
- model ownership
- metric definitions
- tests
- explicit caveats
- a narrower set of marts than the team originally wanted

That last part matters. We intentionally left things out. That is one reason the architecture worked.

Why We Chose This Stack

Mid-Market Data Stack Layers Compared

Before getting into each layer, here is the side-by-side comparison that usually helps teams evaluate the stack more honestly:

That framing matters because stack sprawl usually turns into a trust problem before it turns into a tooling problem. Salesforce’s State of Data and Analytics (2nd Edition) says data leaders estimate 26% of their organization’s data is untrustworthy — which is why this comparison is really about operating discipline, not vendor preference.

Warehouse: BigQuery

For this company, BigQuery was the right answer.

Not because Snowflake is bad. Not because Databricks is wrong. Because the team needed the shortest path to a warehouse they would actually operate well.

BigQuery fit for a few reasons:

• the company was already comfortable on GCP
• the analytics workload was much heavier than the ML workload
• the team wanted low infrastructure overhead
• finance wanted a pricing model they could understand quickly
• the data team was strong in SQL but did not need Spark complexity for the near-term roadmap

If this had been a heavier ML platform bet, Databricks might have won. If multi-cloud politics had dominated, Snowflake might have won. For a mid-size SaaS team trying to get reporting, governance, and modeling under control fast, BigQuery was the most practical path.

Ingestion: Fivetran, with restraint

Fivetran handled most source ingestion.

That included:

• PostgreSQL replication for application data
• Stripe
• HubSpot
• Intercom
• selected ad-platform connectors

That was the right choice because the team did not need to spend the next quarter building and maintaining extraction code.

But this is where a lot of teams get sloppy. Fivetran was the pipe, not the architecture. We did not treat connector output as business-ready data. We treated it as landing-zone data.

For a couple of edge cases, we used lighter custom sync logic because the connector behavior was either too noisy or too expensive for the exact use case. That is normal. A good architecture is not ideological about fully managed versus custom. It is selective.

Transformation layer: dbt

dbt was the center of gravity.

This was the right call for the usual reasons, but also for one reason people understate:

dbt creates visible decision logic.

That matters more than the tooling itself.

The shift was not just “we now transform in SQL.” It was:

• model logic in version control
• pull requests instead of silent dashboard edits
• tests attached to models
• documentation close to code
• lineage visible enough for non-authors to follow

For a small team, that is the difference between “the warehouse works” and “the warehouse can survive staff turnover.”

BI layer: Looker

Looker stayed in place.

That was intentional.

A lot of architecture projects create unnecessary churn by changing the reporting tool and the warehouse architecture at the same time. Unless the BI tool itself is the blocker, that is usually too much motion.

The real problem here was not Looker. It was that Looker was sitting on top of shaky logic, inconsistent source treatment, and too many half-defined business rules.

So we kept Looker and made the upstream layer trustworthy enough for Looker to stop carrying logic it should not own.

The Modeling Strategy

The dbt project followed a boring structure on purpose:

• raw_* tables landed from ingestion
• stg_* models cleaned and standardized source behavior
• int_* models handled cross-system logic and reusable joins
• mart_* models produced business-facing outputs

That sounds standard because it is.

Standard is good.

The most expensive modeling mistake mid-size teams make is premature cleverness. They build flexible abstractions for future use cases instead of clean models for current decisions.

We avoided that.

Core marts we actually built

We did not try to build a warehouse for every possible future question. We built marts for the decisions the business already needed.

The first set looked roughly like this:

• mart_revenue_daily
• mart_pipeline_weekly
• mart_marketing_channel_performance
• mart_product_activation_cohorts
• mart_customer_health
• mart_exec_kpi_snapshot

That coverage sounds obvious, but it was a strategic choice. The team originally wanted a larger semantic layer, more generalized entities, and broader self-serve outputs. We cut that down.

Why?

Because the job was not to model the universe. The job was to create a smaller set of trusted artifacts that would stop the number fights first.

A more useful design principle than “make it reusable”

The design principle we used was:

Model for repeated decisions, not hypothetical flexibility.

That meant asking:

• which decisions happen every week or every month?
• which metrics are repeatedly argued about?
• where is the current trust gap most expensive?
• which marts reduce the most reconciliation work fastest?

That is how you end up with a mart like mart_exec_kpi_snapshot before a dozen theoretically reusable dimensions nobody outside the data team will touch.

The Metric Governance Layer

This is the part a lot of “stack teardown” posts skip.

But it is the part that actually determines whether the stack becomes trusted.

The company’s real pain was not that data was unavailable. It was that core terms meant different things in different rooms.

The most dangerous words were the usual ones:

• revenue
• pipeline
• qualified lead
• activation
• churn
• CAC

So the architecture included a governance layer that was operational, not ceremonial.

What governance looked like in practice

For the key metrics, every definition had:

• a named business owner
• a named data owner
• the system of record
• explicit inclusions and exclusions
• a note on where the metric was decision-grade versus directional only
• the dbt model or Looker explore that implemented it

That sounds lightweight because it was.

It did not require a giant governance council. It required enough specificity that people could stop pretending two different definitions were “basically the same.”

A simple example:

Metric: Pipeline
Business owner: VP Sales
Data owner: Head of Data
System of record: HubSpot opportunity objects, reconciled to finance rules
Directional use: weekly GTM review
Board-grade use: only after finance reconciliation cut
Known caveat: self-serve dashboard excludes manual finance adjustments

That kind of note prevents a lot of fake certainty.

The Testing Strategy

The test layer was one of the highest-leverage parts of the build.

Not because tests are glamorous. Because tests catch the exact kind of failure that destroys confidence quietly.

The dbt project used a practical testing stack:

1. Schema tests on critical keys and relationships

Every core model shipped with at least:

• not_null
• unique
• relationship checks where they mattered
• accepted values on controlled dimensions

Nothing novel there. Still essential.

2. Business-rule assertions on the marts that leadership cared about

This mattered more than generic warehouse hygiene.

Examples:

• revenue should not go negative outside refund logic
• weekly pipeline should stay within sane variance thresholds unless flagged
• conversion-stage progressions should follow valid sequences
• activation counts should not exceed eligible account counts

These are the tests that catch “the dashboard still loads, but the logic is wrong.”

3. Freshness and source drift checks

The business does not care whether a source freshness test exists. It cares whether Monday’s review starts with stale numbers.

So freshness expectations were tied to use case:

• executive KPI marts had tighter freshness expectations
• less critical historical reporting could lag more gracefully
• marketing-source drift got explicit monitoring because connector behavior was noisy

4. CI for dbt changes

Every meaningful dbt change ran through CI checks before merge. That included model validation, selected tests, and enough discipline that people had to explain logic changes before they landed.

Again: not exciting. Very valuable.

The Actual Data Lineage That Mattered

The pretty lineage graph is not the point.

The point is whether someone can answer:

• where this KPI comes from
• what joins it depends on
• who owns the business meaning
• which layer to inspect when it breaks

The critical path for the marketing and revenue reporting looked more like this:

HubSpot deals ───────┐
Stripe billing ──────┼──► stg_revenue_events ─► int_customer_revenue ─► mart_revenue_daily
Postgres accounts ───┘

Segment events ──────┐
Postgres users ──────┼──► stg_activation_events ─► int_account_activity ─► mart_product_activation_cohorts
HubSpot lifecycle ───┘

Ad platforms ────────┐
HubSpot campaigns ───┼──► stg_marketing_spend   ─► int_channel_attribution ─► mart_marketing_channel_performance
Postgres signups ────┘

That was enough lineage to make debugging and stakeholder conversations materially easier.

The goal was not maximum visual sophistication. It was faster diagnosis when someone said, “Why did this number move?”

What We Intentionally Left Out

This is where a lot of architecture writeups become less useful than they should be.

They show what got included and hide what got rejected.

But exclusion decisions are often where the maturity shows up.

We did not build a giant semantic layer on day one

The team could have spent months creating a more abstracted metrics layer. They were not ready.

The business still had too many unresolved definition questions for that to pay off early.

We did not centralize every possible operational use case immediately

Not every workflow needed to route through the warehouse in phase one. A few lower-value team-specific reports stayed messy for longer. That was fine.

You do not need universal cleanliness before you can create executive trust.

We did not over-engineer real-time

There was pressure to make parts of the stack “real-time.” For the actual decisions involved, near-real-time was enough. Daily or scheduled refreshes handled most use cases cleanly.

This saved complexity, cost, and debugging pain.

We did not treat AI readiness as a separate architecture track

The company had AI ambitions. Fine.

But the useful AI conversation was not “which LLM workflow should sit on top of the warehouse?” It was “which data products are stable enough that automation or copilots would not spread bad logic faster?”

That kept the architecture grounded.

What This Architecture Changed for the Business

The technical outputs mattered. The operating change mattered more.

The architecture reduced:

• recurring number fights in leadership reviews
• spreadsheet reconciliation work
• dashboard logic hidden in BI
• silent breakage from source changes
• dependency on the one person who “just knew how it worked”

It increased:

• confidence in recurring KPI reviews
• speed of answering routine business questions
• the data team’s ability to ship new models without destabilizing old ones
• clarity about where caveats still existed

That is what a good mid-size SaaS architecture is supposed to do. It should increase decision speed without pretending uncertainty has disappeared.

What We Would Do Differently Next Time

This is the most important section.

If a teardown only presents the architecture as inevitable, it is marketing. If it shows where the design still had trade-offs, it becomes useful.

1. We would force metric ownership even earlier

We handled governance well, but some definition questions stayed open longer than they should have. That created rework in the marts.

The next time, I would force named ownership on the most politically loaded metrics sooner — especially pipeline and revenue-adjacent numbers.

2. We would narrow the first dashboard set more aggressively

Even after reducing scope, we still let a couple of low-value dashboards survive too long. That created extra QA work and preserved confusion around which views were truly authoritative.

I would cut harder earlier.

3. We would document confidence levels more visibly

The team knew some outputs were directional and others were board-grade. But that distinction could have been surfaced even more clearly in the reporting layer itself.

A number is not trustworthy just because it is available. Context belongs as close to the output as possible.

4. We would treat ad-platform data as suspect by default

This sounds cynical. It is also practical.

Ad-platform connector data is valuable, but it is often where teams smuggle in false precision fastest. I would now make the directional-vs-authoritative distinction even sharper for marketing performance views until the full source chain is proven.

When This Kind of Stack Is a Fit

This architecture pattern is a fit when:

• you are a mid-size SaaS company with a small-to-mid-sized data team
• your biggest problem is trust, alignment, and execution speed
• your core stack leans GCP or AWS, not Azure-heavy enterprise tooling
• you want SQL-first, maintainable architecture more than flashy platform sprawl
• you need the warehouse to support marketing, revenue, and product decisions together

It is less of a fit when:

• the real need is heavy ML platform engineering first
• the team has no interest in adopting dbt discipline
• leadership still wants every team to keep its own metric logic indefinitely
• nobody is willing to name an owner for the core business definitions

Bottom Line

A real mid-size SaaS analytics architecture is usually less magical than the marketing version.

It is not just connectors, a warehouse, dbt, and dashboards. It is a set of decisions about what deserves to be trusted, what gets modeled first, what gets left out, and where caveats need to stay visible.

That is what makes the stack useful.

If your team has the tools but still cannot get to decisions leaders trust, the architecture problem is usually not just technical. It is sequencing, ownership, governance, and business translation all at once.

That is exactly the kind of work we do inside Data Foundation. And if your team is still getting vague asks from the business before the architecture conversation even starts, begin with Translate the Ask.

For a related implementation view, read Building a Modern Data Foundation with dbt, 5 dbt Implementation Mistakes That Kill Data Trust, or the B2B platform warehouse migration case study.