Auditing a SaaS data stack: a diagnostic walkthrough

A VPVPA clear statement of the benefits your product delivers, the problems it solves and why customers should choose you over alternatives.View full definition → of Analytics at a mid-market SaaS company pulls up two dashboards before a board meeting. One says monthly active users grew 12%. The other, built off the same event stream, says 4%. Both have been "live" for months. Nobody caught it because nobody was auditing the pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.View full definition →, they were just consuming its output. This happens constantly, and it is why data audits are now a standing item in SaaS operating reviews.

This lesson walks through a typical SaaS data stack layer by layer, showing you what breaks, how to spot it, and what to check on a recurring basis.

The anatomy of a SaaS data stack

Most SaaS companies run a version of this pipeline:

1. Product event stream: instrumentation (via tools like Segment, Amplitude, or a custom SDK) capturing user actions (signup_completed, feature_used, subscription_upgraded).
2. Ingestion layer: pipes events into storage, often via a CDPCDPSoftware that unifies customer data from every source into one persistent profile that marketing, sales and service teams can act on.View full definition → (customer data platform) or streaming tool (Kafka, Fivetran).
3. Data warehouseData warehouseA central repository that consolidates data from many source systems into a structured, query-optimized store designed for analytics, reporting, and business intelligence.View full definition →: the analytical store (Snowflake, BigQuery, Databricks) where raw and transformed data lives.
4. Transformation layer: SQLSQLSales Qualified Lead: a prospect the sales team has validated as ready for direct outreach and a proposal, having passed clear qualification criteria.View full definition → modeling tools (dbt is the dominant one) that turn raw events into clean, business-ready tables.
5. BI layer: dashboards (Looker, Tableau, Mode) that business users actually see.

Each handoff between these layers is a place where trust erodes. Auditing means checking each seam, not just the final dashboard.

Layer 1: the event stream and schemaschemaA schema is the formal blueprint that defines how data is structured, named, typed, and related within a database, file, or message.View full definition → drift

Schema drift is when the structure of incoming data changes without warning, a field gets renamed, a data type flips from string to integer, a new event replaces an old one, and nothing downstream is told.

Example: engineering renames plan_type to subscription_tier during a refactor. The dashboard tracking upgrade conversion, built on plan_type, silently stops updating. It doesn't error out, it just freezes or returns nulls, and often nobody notices for weeks.

What to check:

• Are there schema tests running on ingestion (e.g., checking expected fields exist, types match)?
• Is there a data contractdata contractA formal agreement between the team that produces data and the teams that use it, defining structure, meaning, quality and who is accountable when it breaks.View full definition → between product/engineering and analytics, a documented agreement on event names, required properties, and change-notification process?
• When was the tracking plan (the master spec of what events should fire and what they contain) last reconciled against actual events in the warehouse?

A practical free resource for tracking plan discipline is Segment's tracking plan documentation, which is a reasonable template even if you don't use Segment.

Layer 2: ingestion and orphaned events

Orphaned events are records that arrive in the warehouse but can't be joined to anything meaningful, a feature_used event with no matching user_id, or a user ID that doesn't exist in the users table because the account was deleted or the ID format changed.

Orphaned events matter because they quietly deflate or inflate metrics. If 8% of your feature_used events can't be joined to a valid account (a plausible range cited in data-quality audits, treat as an estimate, actual rates vary widely by company), your feature adoption rate is wrong by roughly that margin, in whichever direction the orphaned data skews.

Quick check, worked example:

Say your warehouse logs 500,000 feature_used events in a month. A join against the dim_users table returns 460,000 matched rows.

orphan_rate = (500,000 - 460,000) / 500,000 = 8.0%

An 8% orphan rate is a common threshold where teams start investigating (some set alerting at 2 to 5%, tighter for billing-critical events). Below roughly 1 to 2%, it's often background noise from timing lags (a user acts right before account deletion propagates). Above that, it usually signals a broken join key or an integration bug.

What to check:

• What percentage of core events fail to join to a valid entity (user, account, subscription)?
• Is there a dead-letter queue (a holding area for events that fail processing) that someone actually reviews?

Layer 3: the warehouse and duplicate or missing records

Two common failure modes at this layer:

• Duplication: retry logic in an APIAPIApplication Programming Interface: a standardised interface that lets applications communicate and exchange data without knowing each other's internal workings.View full definition → integration re-sends the same event, inflating counts. A Stripe webhook retried three times can triple-count a subscription_created event if there's no idempotency key (a unique identifier ensuring the same event isn't processed twice).
• Late-arriving data: usage events from a mobile SDK that batches and syncs only when the app reopens, causing yesterday's dashboard to look artificially low and then "revise upward" days later.

What to check:

• Row counts over time for key tables: sudden spikes or drops warrant investigation.
• Are there dbt tests for uniqueness and not-null constraints on primary keys?

A minimal dbt test, checking that subscription_id is unique and never null:

models:
  - name: fct_subscriptions
    columns:
      - name: subscription_id
        tests:
          - unique
          - not_null

This is a five-minute fix that prevents a class of silent duplication bugs from ever reaching a dashboard.

Layer 4: BI and stale dashboards

Stale dashboards are dashboards that still run, still look fine, but reflect a broken or outdated data model. Causes include: an upstream table stopped refreshing, a filter was hardcoded to an old date range, or the underlying metric definition changed but the dashboard wasn't rebuilt.

A 2023 industry survey by Monte Carlo (a data observability vendor) found that data teams at typical organizations spend a significant share of their time firefighting data quality issues rather than proactive analysis; treat exact percentages as vendor-reported estimates, but the direction is well corroborated across the data engineering community.

What to check:

• Dashboard owner and last-verified date: if a dashboard has no named owner, assume it's unmaintained.
• Freshness metadata: does the BI tool show when underlying data last refreshed?
• Metric definition consistency: is "active user" defined once, centrally (ideally in a metrics layer like dbt's semantic layer or a tool like Cube), or redefined ad hoc in every dashboard?

Building the recurring audit checklist

An audit isn't a one-time cleanup, it's a cadence. A workable quarterly checklist:

Event stream

• Reconcile tracking plan against live events in the warehouse.
• Confirm schema change notifications are actually reaching analytics, not just engineering Slack channels.

Ingestion

• Calculate orphan rate for top 5 business-critical events.
• Check dead-letter queue volume trend.

Warehouse

• Run uniqueness/not-null tests on primary keys of core fact tables (subscriptions, invoices, usage).
• Spot-check row count trends for unexplained spikes.

BI layer

• Audit dashboard ownership: every dashboard needs a named owner or gets archived.
• Verify top 10 executive-facing dashboards against source-of-truth SQL queries.
• Confirm metric definitions live in one central place, not duplicated across tools.

Governance

• Document who can change schemas, who approves new events, who's notified of breaking changes.

This aligns closely with what the data observability field calls the "five pillars" (freshness, distribution, volume, schema, lineage), a framework popularized by Monte Carlo and now widely referenced across the industry; see this accessible overview from dbt Labs on data testing.

🎬 [VIDEO: "Data Observability Explained" - youtube.com/@MonteCarloData - a concise walkthrough of the freshness, volume, schema, distribution, and lineage framework applied to real pipelines]

Key Takeaways

• Schema drift, orphaned events, and stale dashboards are the three most common trust-killers in SaaS data stacks; each corresponds to a different layer (event stream, ingestion, BI).
• Orphan rate is a quantifiable, trackable metric: calculate it as (total events − joinable events) / total events, and set an alert threshold (commonly 2 to 5%, higher for exploratory events, lower for billing-critical ones).
• Every dashboard needs an owner and a last-verified date; unowned dashboards are the default source of stale, contradictory metrics.
• Testing belongs upstream, not just in BI: uniqueness and not-null tests in the transformation layer (via dbt or equivalent) catch duplication before it reaches a board deck.
• Audits are a cadence, not a cleanup: build a quarterly checklist covering event stream, ingestion, warehouse, BI, and governance, and assign explicit ownership for each layer.