Auditing a SaaS data stack: a diagnostic walkthrough

# 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.Voir la définition complète → 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.Voir la définition complète →, 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 pipelinepipelineAll active sales opportunities across the stages of the sales process, together with their combined potential value and probability of closing.Voir la définition complète →:

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 CDPCDPA Customer Data Platform unifies customer data from all sources into persistent, actionable profiles that other systems can use.Voir la définition complète → (customer data platformcustomer data platformA Customer Data Platform unifies customer data from all sources into persistent, actionable profiles that other systems can use.Voir la définition complète →) 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.Voir la définition complète →: 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.Voir la définition complète → 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.Voir la définition complète → 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 schemaschemaA schema is the formal blueprint that defines how data is structured, named, typed, and related within a database, file, or message.Voir la définition complète → tests running on ingestion (e.g., checking expected fields exist, types match)?
• Is there a data contract 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.Voir la définition complète → 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: BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.Voir la définition complète → 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 observabilitydata observabilityCapacité à comprendre, surveiller et diagnostiquer l'état de santé des données tout au long du pipeline, anticiper les incidents avant qu'ils n'impactent les décisions. vendor) found that data teams at typical organizations spend a significant share of their time firefighting data qualitydata qualityThe degree to which data is fit for purpose: accurate, complete, consistent, timely, valid and unique. Poor quality data undermines analytics, reporting and AI.Voir la définition complète → 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 metadatametadataDonnées sur les données, informations décrivant le contexte, la structure, la provenance et les caractéristiques d'un asset de données (auteur, date, format, source, définition).: does the BIBITechnologies and processes that turn raw data into actionable insights via reporting, dashboards and analysis, so teams can decide based on facts rather than intuition.Voir la définition complète → 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 schemaschemaA schema is the formal blueprint that defines how data is structured, named, typed, and related within a database, file, or message.Voir la définition complète → 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