From Raw Tables to Business Insights: A SQL Workflow Every SaaS Team Can Use

You add a subscriptions table, an events table, a handful of orders . Six months later someone on your team asks "what's our MRR trend?" and three people write three different queries against the raw tables. Each one joins slightly differently, filters test accounts differently (or not at all), and produces a different number. Now there are three "correct" answers in three Slack threads, and nobody trusts the dashboard anymore. This isn't a tooling problem. It's a workflow problem. Querying raw production tables directly for reporting works fine for one query, one time. It falls apart the moment more than one person needs the same answer, or the same question gets asked twice a week. The fix is a layered SQL workflow - raw data, cleaned staging views, and business-level marts - that data teams have used for years and that fits perfectly on top of a single Postgres or MySQL database, no data warehouse required. Here's how to build it. Why querying raw tables directly breaks down Production tables are shaped for your application, not for reporting. A few concrete problems show up almost immediately: - Grain confusion. Does one row in orders mean one order, or one line item? Aggregate the wrong way and your revenue numbers are silently wrong. - Inconsistent filtering. Test accounts, soft-deleted rows, and internal team usage sneak into totals unless every single query remembers to exclude them. - Duplicated logic. "Active subscription" gets defined five different ways across five dashboards, because the definition lives inside each query instead of in one place. - Fragile joins. An INNER JOIN betweenevents andusers silently drops any event from a user missing profile data - nobody notices until the numbers don't add up. None of these are exotic problems. They're what happens when reporting logic has no home of its own. The three-layer workflow The pattern - often called medallion architecture in the data engineering world (bronze/silver/gold) - maps cleanly onto three layers you can build with nothing but SQL views: | Layer | Purpose | Example | |---|---|---| | Raw | Your application tables, untouched | subscriptions , events , users | | Staging | Cleaned, renamed, de-duplicated, type-cast | stg_subscriptions , stg_events | | Marts | Business-level aggregates, one definition per metric | mart_mrr_daily , mart_dau | You don't need dbt or a warehouse to do this - plain SQL views (or materialized views if refresh speed matters) are enough for most SaaS-scale databases. Step 1: Raw layer - leave it alone Don't transform anything here. This is your source of truth exactly as your application wrote it. If a bug corrupts staging, you can always rebuild from raw. Step 2: Staging - clean once, reuse everywhere Staging views do the boring, repetitive cleanup so nobody has to do it twice: CREATE OR REPLACE VIEW stg_subscriptions AS SELECT id AS subscription_id, customer_id, lower(trim(plan)) AS plan, status, amount_cents / 100.0 AS amount_usd, created_at::date AS started_on, canceled_at::date AS canceled_on FROM subscriptions WHERE customer_id NOT IN (SELECT id FROM test_accounts); CREATE OR REPLACE VIEW stg_events AS SELECT id AS event_id, user_id, event_name, occurred_at, occurred_at::date AS event_date FROM events WHERE user_id IS NOT NULL; Every dashboard and every teammate now queries stg_subscriptions , not subscriptions . Test accounts are already excluded. Amounts are already in dollars, not cents. Nobody re-derives this logic in five different places. Step 3: Marts - one definition per metric Marts answer specific business questions, built on top of staging: CREATE OR REPLACE VIEW mart_mrr_daily AS SELECT d.day, SUM(s.amount_usd) AS mrr FROM generate_series( (SELECT min(started_on) FROM stg_subscriptions), current_date, interval '1 day' ) AS d(day) LEFT JOIN stg_subscriptions s ON s.started_on d.day) AND s.status = 'active' GROUP BY d.day ORDER BY d.day; CREATE OR REPLACE VIEW mart_dau AS SELECT event_date, count(DISTINCT user_id) AS dau FROM stg_events GROUP BY event_date ORDER BY event_date; Now "MRR" and "DAU" have exactly one definition each, living in the database instead of scattered across BI tool configs and one-off scripts. Anyone - including an embedded dashboard or an AI query assistant - can hit mart_mrr_daily and get the same number every time. Declare the grain before you write a single aggregate Before building any mart, write down what one row represents. "One row per order" and "one row per order line item" are both reasonable grains for an orders -adjacent table, but summing revenue over the wrong one either doubles or under-counts it. A one-line comment above the view - -- grain: one row per calendar day per active subscription - costs nothing and saves the next person (often future you) from re-deriving your logic by trial and error. Automate the refresh Views recalculate on every query, which is fine until the underlying tables get large. Two common upgrades: - Materialized views refreshed on a schedule ( REFRESH MATERIALIZED VIEW mart_mrr_daily ) for marts that don't need to be second-fresh. - A small nightly job (cron, Airflow, or even a Postgres pg_cron job) that rebuilds marts after the day's data has settled. Either way, the mart layer becomes the stable interface everything downstream - dashboards, embedded analytics, scheduled reports - reads from. Common mistakes and gotchas - Building the dashboard before the model. It's tempting to point a chart straight at raw tables to move fast. The dashboard becomes the source of truth, its logic invisible and untested, and untangling it later costs far more time than modeling up front would have. - Mixing grains in one table. A kpi_metrics table with daily rows, weekly rollups, and monthly summaries jammed together is a bug generator - always split by grain or make the grain an explicit column. - Silent INNER JOIN drops. Defaulting toLEFT JOIN in staging views keeps your source-of-truth counts complete; switch toINNER JOIN deliberately, only when you actually want to filter. - Skipping staging "to save time." The cleanup work doesn't disappear - it just moves into every dashboard and script that touches raw data, duplicated N times instead of written once. - No ownership of metric definitions. If "active user" can mean three different things depending on who wrote the query, put the definition in a mart and make that the only place it's allowed to live. Key takeaways Raw tables are for your application, not your reports. A thin staging layer removes repetitive cleanup, and a mart layer gives every metric exactly one definition that dashboards, embedded analytics, and even AI-generated queries can rely on. None of this requires a data warehouse or a new platform - just views, a bit of discipline about grain, and a refresh strategy that matches how fresh your numbers actually need to be. What does your reporting stack look like today - do you model before you dashboard, or query raw tables directly and clean up the mess later? Drop your approach (or your favorite gotcha) in the comments. Top comments (0)