Auditing a SaaS data stack: a diagnostic walkthrough

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.View full definition →:

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.View full definition → (customer data platformcustomer data platformA Customer Data Platform unifies customer data from all sources into persistent, actionable profiles that other systems can use.View full definition →) 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 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 → 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.

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:

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.View full definition → 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 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.View full definition → 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 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.View full definition → 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.View full definition → 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 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 → 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, 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 →, 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, 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 →, 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, 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.View full definition →).
• 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.