DEV Community: Vivek Kumar

Funnel Analysis in SQL: Find Exactly Where Users Drop Off

Vivek Kumar — Fri, 14 Aug 2026 17:30:09 +0000

Your product has a signup funnel. Someone visits the pricing page, starts a trial, invites a teammate, and (hopefully) subscribes. Your CEO asks a simple-sounding question in standup: "Where are we losing people?"

If your answer is a shrug and a promise to "pull some numbers," this article is for you. Funnel analysis is one of the highest-leverage things you can do with the data you already have — and you don't need a dedicated product analytics tool to do it. You need an events table and a handful of SQL patterns.

The catch is that funnels are deceptively easy to get wrong. Count the steps naively and you'll produce numbers that look precise and are quietly inflated by 30%. Let's build a funnel the right way, and see exactly which mistakes to avoid.

The data we're working with

Assume a single wide events table — the shape most product analytics setups converge on:

CREATE TABLE events (
  id          BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  event_name  TEXT NOT NULL,   -- 'viewed_pricing', 'started_trial', ...
  created_at  TIMESTAMPTZ NOT NULL
);

Our funnel has four steps:

Step	event_name
1. Viewed pricing	`viewed_pricing`
2. Started trial	`started_trial`
3. Invited a teammate	`invited_teammate`
4. Subscribed	`subscribed`

The naive version (and why it lies)

The tempting first attempt: count distinct users for each event, independently.

SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'viewed_pricing')   AS viewed,
  COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'started_trial')    AS trial,
  COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'invited_teammate') AS invited,
  COUNT(DISTINCT user_id) FILTER (WHERE event_name = 'subscribed')       AS subscribed
FROM events;

This runs fast and gives you four numbers. It's also wrong for a funnel. It counts anyone who ever fired each event, in any order, at any time. A user who subscribed in January and viewed the pricing page again in June counts toward both "viewed" and "subscribed" — even though their subscribe never followed this pricing view. As Fivetran's team puts it, calculating each step independently and lumping them together mixes historical actions from different time periods into one funnel and artificially inflates conversion.

A funnel is fundamentally about order: step 3 only counts if it happened after the same user's step 2.

The right way: ordered, per-user steps

The reliable pattern is to figure out, for each user, the furthest step they reached in sequence. Window functions make this clean. First, stamp each user's first timestamp for each step:

WITH step_times AS (
  SELECT
    user_id,
    MIN(created_at) FILTER (WHERE event_name = 'viewed_pricing')   AS t1,
    MIN(created_at) FILTER (WHERE event_name = 'started_trial')    AS t2,
    MIN(created_at) FILTER (WHERE event_name = 'invited_teammate') AS t3,
    MIN(created_at) FILTER (WHERE event_name = 'subscribed')       AS t4
  FROM events
  GROUP BY user_id
)
SELECT * FROM step_times;

Now a user "reached step 3" only if t1 <= t2 <= t3 — each step's first occurrence comes after the previous one. We translate that into a single step_reached number:

WITH step_times AS (
  SELECT
    user_id,
    MIN(created_at) FILTER (WHERE event_name = 'viewed_pricing')   AS t1,
    MIN(created_at) FILTER (WHERE event_name = 'started_trial')    AS t2,
    MIN(created_at) FILTER (WHERE event_name = 'invited_teammate') AS t3,
    MIN(created_at) FILTER (WHERE event_name = 'subscribed')       AS t4
  FROM events
  GROUP BY user_id
),
progress AS (
  SELECT
    user_id,
    CASE
      WHEN t4 IS NOT NULL AND t4 >= t3 AND t3 >= t2 AND t2 >= t1 THEN 4
      WHEN t3 IS NOT NULL AND t3 >= t2 AND t2 >= t1             THEN 3
      WHEN t2 IS NOT NULL AND t2 >= t1                          THEN 2
      WHEN t1 IS NOT NULL                                       THEN 1
      ELSE 0
    END AS step_reached
  FROM step_times
)
SELECT
  COUNT(*) FILTER (WHERE step_reached >= 1) AS viewed,
  COUNT(*) FILTER (WHERE step_reached >= 2) AS trial,
  COUNT(*) FILTER (WHERE step_reached >= 3) AS invited,
  COUNT(*) FILTER (WHERE step_reached >= 4) AS subscribed
FROM progress;

Because we use >= on step_reached, each stage is a proper subset of the one before it. The funnel can only ever go down — which is what a funnel is supposed to do.

Turning counts into a conversion table

Raw counts are hard to read. What people actually want is the drop-off rate at each step. Unpivot the stages into rows and compute conversion against both the previous step and the top of funnel:

WITH counts AS (
  -- the progress CTE from above, aggregated
  SELECT
    COUNT(*) FILTER (WHERE step_reached >= 1) AS s1,
    COUNT(*) FILTER (WHERE step_reached >= 2) AS s2,
    COUNT(*) FILTER (WHERE step_reached >= 3) AS s3,
    COUNT(*) FILTER (WHERE step_reached >= 4) AS s4
  FROM progress
),
stages AS (
  SELECT 1 AS step, 'Viewed pricing'  AS label, s1 AS reached, s1 AS prev FROM counts
  UNION ALL SELECT 2, 'Started trial',    s2, s1 FROM counts
  UNION ALL SELECT 3, 'Invited teammate', s3, s2 FROM counts
  UNION ALL SELECT 4, 'Subscribed',       s4, s3 FROM counts
)
SELECT
  label,
  reached,
  ROUND(100.0 * reached / MAX(reached) OVER (), 1)     AS pct_of_top,
  ROUND(100.0 * reached / NULLIF(prev, 0), 1)          AS pct_of_prev
FROM stages
ORDER BY step;

That produces the report your CEO actually wanted:

Stage	Users	% of top	% of previous
Viewed pricing	10,000	100.0%	—
Started trial	4,200	42.0%	42.0%
Invited teammate	1,500	15.0%	35.7%
Subscribed	1,050	10.5%	70.0%

Now the story is obvious. The single biggest leak isn't the final subscribe step (70% convert once they invite someone) — it's the jump from viewing pricing to starting a trial. That's where a product team should spend its energy.

Add a time window (this is the realistic version)

"Subscribed at some point after viewing pricing" is a weak definition. Someone who viewed pricing in 2024 and subscribed in 2026 didn't really move through this funnel. Real attribution almost always has a window — 30 minutes for a session, 7 days for a marketing funnel, 14 days for a trial.

You enforce it by requiring each step to fall within N days of the funnel's start (t1):

CASE
  WHEN t4 >= t3 AND t3 >= t2 AND t2 >= t1
       AND t4 <= t1 + INTERVAL '14 days' THEN 4
  WHEN t3 >= t2 AND t2 >= t1
       AND t3 <= t1 + INTERVAL '14 days' THEN 3
  WHEN t2 >= t1
       AND t2 <= t1 + INTERVAL '14 days' THEN 2
  WHEN t1 IS NOT NULL                    THEN 1
  ELSE 0
END AS step_reached

Expect your conversion numbers to drop when you add the window. That's not the query breaking — it's the previous version having been too generous.

Common mistakes and gotchas

Counting events instead of users. Use COUNT(DISTINCT user_id), not COUNT(*). One enthusiastic user viewing the pricing page 40 times should count once, or your top-of-funnel is meaningless.

Ignoring order. The independent-count version treats "subscribed then viewed pricing" the same as "viewed pricing then subscribed." Always anchor later steps to earlier timestamps.

Forgetting the time window. Without one, a funnel silently accumulates matches across a user's entire history and inflates conversion. Pick a window that reflects how the funnel is actually supposed to work.

Off-by-one in window frames. If you extend this to rolling windows, remember ROWS BETWEEN 7 PRECEDING AND CURRENT ROW spans 8 rows, not 7 — one of the most common bugs in window-function queries.

Mismatched grain. If a funnel should be per-session rather than per-user (a user can enter a funnel many times), partition by a session_id, not user_id. Choose your grain before you write a line of SQL.

Key takeaways

Funnel analysis lives or dies on two rules: count distinct users, and respect event order within a time window. The independent-count query is fast and wrong; the ordered step_reached pattern is only a few more lines and actually answers the question. Once you have step_reached, everything else — conversion tables, segmenting by plan or channel, comparing this month to last — is just a GROUP BY away. And because these are subsets of the previous step, the funnel behaves like a funnel instead of a pile of unrelated metrics.

The best part: this all runs against the database you already have. No new pipeline, no event-tracking migration — just SQL you can drop into a saved query and turn into a dashboard.

Your turn

How do you handle funnels where users can re-enter — per session, per day, or first-touch only? And what time window do you use for your signup funnel? Drop your approach (or your gnarliest funnel query) in the comments. If you'd rather not hand-write this every time, tools like Draxlr let you save funnel queries and turn them into shareable dashboards straight on top of your existing database.

Sources: Fivetran — Funnel Analysis and Conversion Metrics in SQL, Cube — SQL Queries for Funnel Analysis, Vikram Oberoi — Funnel analysis in SQL using window functions and range frames, Metabase Learn — How to do funnel analysis.

Self-Correcting Text-to-SQL: How Execution Feedback Loops Fix Broken Queries

Vivek Kumar — Fri, 14 Aug 2026 06:41:01 +0000

You wire up an LLM to your database, ask it "how much revenue did we make last month," and it hands back a beautiful SQL query. You run it. ERROR: column "total_amount" does not exist. The column is actually called amount_cents. The model guessed, and it guessed wrong.

This is the single biggest reason text-to-SQL demos look magical and text-to-SQL in production feels flaky. A model writing SQL from a natural language question is doing it blind — it never sees whether the query actually runs, returns rows, or returns nonsense. The fix that has quietly become the standard in 2025 research and production systems is deceptively simple: let the model run its own query, look at what happened, and try again. That loop is called execution feedback, and it's the difference between a party trick and a tool you can ship.

In this article you'll learn what an execution feedback loop actually looks like in code, why "self-correction" only works when it's grounded in real results, and the guardrails you need so a self-retrying AI doesn't melt your database or your latency budget.

Why single-shot text-to-SQL fails

A model generating SQL in one pass has three ways to be wrong, and only one of them is a syntax problem:

Failure type	Example	Does it error?
Syntax error	Missing comma, unbalanced parenthesis	Yes — loud
Schema error	Wrong column or table name	Yes — loud
Semantic error	Right syntax, wrong logic (e.g. summed `quantity` instead of `amount`)	No — silent

The loud errors are actually the good news. A database that rejects a query is handing you a precise, machine-readable description of what's wrong. The insight from systems like MAC-SQL, CHESS, and ReFoRCE is that this error message is far more useful as a correction signal than asking the model to "double-check your work" in the abstract. Research is consistent on this point: self-correction using execution results reliably improves accuracy, while self-correction without external feedback often doesn't help at all — and can even make the query worse as the model second-guesses a correct answer.

So the winning pattern isn't a smarter prompt. It's a feedback loop.

The core loop

Here's the whole idea in pseudocode. Generate, execute, and if execution fails, feed the error back and regenerate — up to a cap.

def text_to_sql(question, schema, max_attempts=3):
    error = None
    sql = None
    for attempt in range(max_attempts):
        sql = llm_generate_sql(
            question=question,
            schema=schema,
            previous_sql=sql,        # None on first pass
            previous_error=error,    # None on first pass
        )
        ok, result_or_error = safe_execute(sql)
        if ok:
            return sql, result_or_error
        error = result_or_error      # feed this back next iteration
    raise RuntimeError(f"Failed after {max_attempts} attempts: {error}")

The magic is entirely in what you put in the prompt on the second pass. Instead of the original question alone, the model now sees its own failed attempt and the exact database error:

The previous query failed. Fix it.

Question: How much revenue did we make last month?

Your previous SQL:
  SELECT SUM(total_amount) FROM orders
  WHERE created_at >= date_trunc('month', now() - interval '1 month');

Database error:
  column "total_amount" does not exist
  HINT: Perhaps you meant to reference the column "orders.amount_cents".

Rewrite the query using only columns that exist in the schema below.

Postgres literally suggests the right column name in its HINT. A model handed that hint fixes the query on the next pass almost every time:

SELECT SUM(amount_cents) / 100.0 AS revenue_dollars
FROM orders
WHERE created_at >= date_trunc('month', now() - interval '1 month')
  AND created_at <  date_trunc('month', now());

That's the entire mechanism. One retry loop turns a brittle guesser into something that converges on a working query.

Catching silent errors: empty results and sanity checks

Syntax and schema errors are self-announcing. Semantic errors are the dangerous ones — the query runs fine and returns a confident, wrong number. You can catch a useful subset of these by treating suspicious results as a form of error worth feeding back.

The most valuable signal is an empty result set. If a user asks "which customers churned last month" and the query returns zero rows, that's rarely correct — it usually means a bad join or an over-strict filter. Feed that back too:

ok, rows = safe_execute(sql)
if ok and len(rows) == 0:
    error = ("Query executed but returned 0 rows. "
             "This is likely a bad JOIN or an overly strict WHERE clause. "
             "Re-examine the filters and join conditions.")
    ok = False  # trigger another repair pass

You can layer on cheap sanity checks as additional feedback signals:

A revenue query that returns a negative total
A count that exceeds the known row count of the table
A per-user aggregate where one user_id appears twice (a fan-out join bug)

None of these are proof of a wrong answer, but as feedback prompts they nudge the model to reconsider before a human ever sees the result.

Guardrails: making a retrying AI safe

The moment you let a model execute SQL — and re-execute it several times — you've built something that needs seatbelts. Four are non-negotiable.

1. Read-only, always. Run every generated query through a connection whose database role has SELECT-only privileges. Don't rely on the LLM to avoid DROP; enforce it at the database layer where it can't be prompted away.

-- One-time setup: a role the AI connects as
CREATE ROLE ai_readonly LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE app TO ai_readonly;
GRANT USAGE ON SCHEMA public TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_readonly;
-- No INSERT, UPDATE, DELETE, DROP — ever.

2. A keyword denylist as a second layer. Before executing, reject any query containing INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or GRANT. Belt and suspenders — the read-only role is the real defense, but a static check catches problems earlier and cheaper.

3. Statement timeouts and row limits. A retry loop can generate an accidental cross join that scans billions of rows. Cap it:

SET statement_timeout = '5s';
-- and wrap the model's query:
SELECT * FROM ( /* model SQL */ ) sub LIMIT 1000;

4. A hard cap on retries. This is the one people forget, and it bites twice. First, latency: each retry adds roughly 1.5–3 seconds of model plus execution time, so an uncapped loop can leave a user staring at a spinner for 15 seconds. Second, over-correction — models sometimes make a correct query worse on later passes, second-guessing themselves into a mangled answer. Three attempts is a sane default. If it hasn't worked by then, fail loudly and log the transcript.

Common mistakes

Feeding back the wrong thing. The database error string is gold — pass it through verbatim, including hints. Teams that summarize or truncate the error ("the query didn't work") throw away the exact signal that makes the loop work.

Retrying forever. No cap means unbounded latency and cost. Always set max_attempts.

Trusting self-critique alone. Asking the model "are you sure this SQL is correct?" without executing it is theater. The improvement comes from real execution results, not introspection.

Logging nothing. You cannot debug or improve a loop you can't see. Log every attempt: the question, each generated query, each error, and the final outcome. Those transcripts are also the best training data you'll ever get for few-shot examples.

Skipping the human confirmation early on. In the first weeks of production, show the generated SQL to the user before running it. It builds trust and surfaces semantic errors that no automated check will catch.

Key takeaways

Single-shot text-to-SQL fails because the model writes queries blind. An execution feedback loop — generate, run, feed the error back, retry — grounds the model in reality and is the single highest-leverage upgrade you can make. Treat empty result sets and failed sanity checks as errors worth feeding back, not just syntax failures. And wrap the whole thing in guardrails: a read-only role, a keyword denylist, statement timeouts, row limits, and a hard retry cap. Do that, and text-to-SQL stops being a demo and starts being infrastructure.

The good news is you don't have to build all of this by hand. Tools like Draxlr handle AI-powered SQL generation, safe read-only execution, and turning the results into shareable dashboards — so you get the feedback loop and the guardrails without wiring them up yourself.

How are you handling AI-generated SQL in your stack today — full auto-execute, human-in-the-loop, or something in between? What's the worst query an LLM has handed you? Drop it in the comments; I collect these.

Sources: ReFoRCE: A Text-to-SQL Agent with Self-Refinement, Consensus Enforcement, and Column Exploration, RetrySQL: text-to-SQL training with retry data for self-correcting query generation, Bridging Natural Language and Databases: Best Practices for LLM-Generated SQL, LLM Guardrails: Best Practices for Deploying LLM Apps Securely (Datadog).

Time-Series Charts in SQL: Bucketing, Gap-Filling, and Time Zones That Don't Lie

Vivek Kumar — Thu, 13 Aug 2026 14:47:17 +0000

Almost every chart in a dashboard is a time series. Signups per day, revenue per week, active users per hour, API calls per minute. They all boil down to the same shape: group rows into time buckets, count or sum something, plot the result.

It sounds trivial. Then you ship it, and a customer emails: "Why does my chart show zero signups on Tuesday? We definitely had signups." Or worse, they don't email — they just quietly stop trusting the numbers, because Tuesday's bar is missing entirely and the line jumps straight from Monday to Wednesday as if nothing happened.

Time-series charts have three failure modes that bite almost everyone building customer-facing analytics: buckets that don't line up, gaps that get silently skipped, and time zones that shift the whole chart by a day. This post walks through each one with plain SQL you can drop into a dashboard query, using a realistic SaaS schema. By the end you'll be able to produce a chart series that's dense, correctly bucketed, and shows each customer their own local days.

The setup

Let's assume a multi-tenant SaaS app with a familiar events table:

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  workspace_id BIGINT NOT NULL,   -- the tenant
  user_id     BIGINT NOT NULL,
  event_type  TEXT NOT NULL,      -- 'signup', 'login', 'purchase', ...
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Note created_at is TIMESTAMPTZ (timestamp with time zone). Store everything in UTC and keep this type everywhere — it's the single most important decision for getting time-series charts right later. If you're storing naive TIMESTAMP columns, fix that first; the rest of this post assumes you know what instant each row actually happened at.

Step 1: Bucketing with date_trunc

The workhorse for grouping timestamps into intervals is date_trunc. It chops a timestamp down to the start of a given unit — hour, day, week, month:

SELECT
  date_trunc('day', created_at) AS bucket,
  count(*) AS signups
FROM events
WHERE workspace_id = 42
  AND event_type = 'signup'
  AND created_at >= now() - interval '7 days'
GROUP BY 1
ORDER BY 1;

That gives you one row per day that had at least one signup:

bucket	signups
2026-07-25 00:00:00+00	12
2026-07-26 00:00:00+00	8
2026-07-28 00:00:00+00	5

Look closely. July 27 is missing. Not because of a bug — there were simply no signups that day, so GROUP BY produced no row. Your charting library receives three points and draws a line straight from the 26th to the 28th. The dip to zero on the 27th vanishes, and the trend looks smoother and healthier than it actually was.

date_trunc handles the "line them up" problem. It does nothing about the "missing days" problem. For that you need a spine.

Step 2: Gap-filling with generate_series

The fix is to generate a complete, dense list of buckets and LEFT JOIN your aggregated data onto it. Postgres ships generate_series for exactly this:

SELECT
  spine.bucket,
  COALESCE(e.signups, 0) AS signups
FROM generate_series(
       date_trunc('day', now() - interval '6 days'),
       date_trunc('day', now()),
       interval '1 day'
     ) AS spine(bucket)
LEFT JOIN (
  SELECT date_trunc('day', created_at) AS bucket,
         count(*) AS signups
  FROM events
  WHERE workspace_id = 42
    AND event_type = 'signup'
    AND created_at >= now() - interval '7 days'
  GROUP BY 1
) e ON e.bucket = spine.bucket
ORDER BY spine.bucket;

Now every day in the range shows up, and the empty ones report zero:

bucket	signups
2026-07-25	12
2026-07-26	8
2026-07-27	0
2026-07-28	5

generate_series builds the calendar, the LEFT JOIN attaches whatever data exists, and COALESCE turns the NULL from days-with-no-match into a real zero. This pattern is portable, works on plain PostgreSQL with no extensions, and is the single technique that separates an honest line chart from a misleading one.

If you're on TimescaleDB you get time_bucket() and time_bucket_gapfill() which do bucketing and gap-filling in one call — and let you bucket by arbitrary intervals like 5 minutes or 4 hours, which date_trunc can't. But you don't need an extension to get correct charts. The generate_series + LEFT JOIN spine works everywhere.

Step 3: Time zones, or "whose Tuesday is it?"

Here's the one that silently corrupts numbers for months before anyone notices. date_trunc('day', created_at) truncates in UTC. If your customer is in Los Angeles, an event at 6pm Monday their time happened at 1am Tuesday UTC — so it lands in Tuesday's bucket, not Monday's. Every evening event gets shoved into the next day. Daily totals are wrong, and "which day is our busiest" points at the wrong day.

The fix is to convert to the customer's zone before truncating, using AT TIME ZONE:

SELECT
  date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS bucket,
  count(*) AS signups
FROM events
WHERE workspace_id = 42
GROUP BY 1
ORDER BY 1;

created_at AT TIME ZONE 'America/Los_Angeles' converts the UTC instant into local wall-clock time, so date_trunc('day', ...) now means "midnight-to-midnight in LA." That's the day boundary the customer actually experiences.

In a multi-tenant app, the zone isn't a constant — it's a property of the tenant. Join it in:

SELECT
  date_trunc('day', e.created_at AT TIME ZONE w.timezone) AS bucket,
  count(*) AS signups
FROM events e
JOIN workspaces w ON w.id = e.workspace_id
WHERE e.workspace_id = 42
GROUP BY 1
ORDER BY 1;

Store an IANA zone name like America/Los_Angeles per workspace (not a fixed -08:00 offset — offsets don't know about daylight saving). Then the same query renders correct local days for every customer, whether they're in California, Berlin, or Sydney. Combine this with the gap-filling spine from Step 2, generating that spine in the customer's zone too, and the chart is both dense and locally correct.

A complete, chart-ready query

Putting all three together — bucketed, gap-filled, and time-zone-aware for a single tenant:

WITH bounds AS (
  SELECT w.timezone AS tz
  FROM workspaces w WHERE w.id = 42
),
spine AS (
  SELECT generate_series(
    date_trunc('day', (now() AT TIME ZONE (SELECT tz FROM bounds)) - interval '29 days'),
    date_trunc('day', (now() AT TIME ZONE (SELECT tz FROM bounds))),
    interval '1 day'
  ) AS bucket
),
daily AS (
  SELECT date_trunc('day', e.created_at AT TIME ZONE (SELECT tz FROM bounds)) AS bucket,
         count(*) FILTER (WHERE e.event_type = 'signup')   AS signups,
         count(*) FILTER (WHERE e.event_type = 'purchase') AS purchases
  FROM events e
  WHERE e.workspace_id = 42
    AND e.created_at >= now() - interval '31 days'
  GROUP BY 1
)
SELECT s.bucket::date AS day,
       COALESCE(d.signups, 0)   AS signups,
       COALESCE(d.purchases, 0) AS purchases
FROM spine s
LEFT JOIN daily d ON d.bucket = s.bucket
ORDER BY s.bucket;

That's a 30-day, two-metric series with no gaps, bucketed on the customer's local calendar — exactly what a line or bar chart needs, ready to hand straight to the frontend.

Common mistakes and gotchas

Filtering by local time instead of UTC. Keep your WHERE created_at >= ... bound in UTC (that's what the index is on). Convert to local time only inside date_trunc for the grouping. Wrapping the column in AT TIME ZONE in the WHERE clause can also throw away your index and force a full scan.

Fixed offsets instead of IANA zones. AT TIME ZONE '-08:00' breaks twice a year when daylight saving flips. Always store and use named zones like Europe/London so the database applies DST for you.

Forgetting the range bounds match the spine. If your generate_series covers 30 days but the aggregate subquery only pulls 7, you'll get 23 days of zeros that look real. Make the spine and the data query cover the same window.

Gap-filling a metric where zero is wrong. COALESCE(..., 0) is right for counts and sums. But for a running balance or a gauge like "active subscriptions," a missing bucket should carry the last known value forward (last-observation-carried-forward), not drop to zero. Pick the fill strategy that matches what the metric means.

Bucketing by week without agreeing on the start day. date_trunc('week', ...) starts weeks on Monday in Postgres. If your product defines the week as starting Sunday, your chart and your customer's mental model disagree by a day. Decide, document it, and be consistent.

Not pre-aggregating for big tenants. Scanning millions of rows on every dashboard load is slow. Once the raw query is correct, roll it into a daily summary table or materialized view keyed by (workspace_id, day), and query that for the chart.

Key takeaways

Bucketing, gap-filling, and time zones are the three things that make time-series charts either trustworthy or quietly wrong. date_trunc lines rows up into intervals but leaves holes; a generate_series spine plus a LEFT JOIN and COALESCE fills those holes so a zero day reads as zero instead of vanishing; and AT TIME ZONE with a per-tenant IANA zone makes sure each customer sees their own days, not UTC's. Get all three right and the chart matches what the customer actually experienced — which is the whole point of putting analytics in front of them.

If you're wiring these queries into a real dashboard, tools like Draxlr let you turn SQL like this into embeddable charts without hand-rolling the frontend — handy once the query is correct and you just want it on a screen.

How do you handle gap-filling and per-customer time zones in your dashboards — spine joins, a time-series extension, or pre-aggregated rollups? Drop your approach in the comments; I'm always curious what patterns people land on at scale.

Sources: Tiger Data — Mind the Gap: SQL Functions for Time-Series Analysis, Tiger Data — Simplified time-series analytics: time_bucket(), PostgreSQL Documentation — Date/Time Functions and Operators, Neon Docs — Postgres date_trunc() function.

Cohort Retention Analysis in SQL: The Query That Tells You If Your Product Is Actually Sticky

Vivek Kumar — Thu, 13 Aug 2026 06:55:31 +0000

Your signup chart is going up and to the right. Revenue is growing. Everyone's happy. And yet something feels off — support tickets keep repeating, the same features get "discovered" by the same users every month, and nobody can quite say whether the people who joined in January are still around in June.

That gnawing feeling has a name, and it has a query. Cohort retention analysis is the single most honest report you can run against your database. It takes your users, groups them by when they joined, and tracks how many of each group keep coming back over time. A growing signup number can hide a leaky bucket. A retention cohort table cannot — it shows you, month by month, exactly how fast your bucket leaks.

In this article we'll build a full cohort retention query from scratch using plain SQL, look at the difference between the three kinds of retention people confuse constantly, and cover the one mistake that makes almost every hand-rolled retention report wrong. By the end you'll have a query you can point at your own users and events tables today.

The two tables you need

You don't need an analytics warehouse for this. Two tables most SaaS apps already have will do:

Table	Columns we care about
`users`	`id`, `created_at`
`events`	`user_id`, `occurred_at`, `event_name`

The events table is whatever signals "this user is getting value" — a login, a report run, a message sent, an API call. Pick the action that means someone is actually using the product, not just a passive ping. This choice matters more than any SQL trick below: if you count "opened a marketing email" as activity, your retention will look great and mean nothing.

Step 1: Define the cohort

A cohort is just a group of users bucketed by the period they joined. We'll use signup month. In PostgreSQL, date_trunc snaps a timestamp down to the start of a period, which makes the bucketing trivial:

-- Each user's cohort = the month they signed up
SELECT
  id AS user_id,
  date_trunc('month', created_at)::date AS cohort_month
FROM users;

That gives every user a cohort_month like 2026-01-01. Everyone who signed up in January shares the same anchor, and that anchor never changes for them. This CTE is the foundation of everything else — and, importantly, the source of your denominator. Hold that thought.

Step 2: Find each user's activity months

Next we bucket every activity event into the month it happened:

SELECT DISTINCT
  user_id,
  date_trunc('month', occurred_at)::date AS activity_month
FROM events;

DISTINCT matters here. A power user might fire 400 events in March, but for retention we only care whether they were active that month, not how many times. Skip the DISTINCT and your later counts will double-count active users and blow past 100% retention — a classic "that can't be right" moment.

Step 3: Join them and measure the gap

Now we connect each cohort to its activity and compute how many months after signup each activity happened. This is where the shape of the report appears:

WITH cohort AS (
  SELECT id AS user_id,
         date_trunc('month', created_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT user_id,
         date_trunc('month', occurred_at)::date AS activity_month
  FROM events
)
SELECT
  c.cohort_month,
  -- whole months between signup and the activity
  (EXTRACT(YEAR  FROM age(a.activity_month, c.cohort_month)) * 12
 + EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)))::int AS month_number,
  COUNT(DISTINCT a.user_id) AS active_users
FROM cohort c
LEFT JOIN activity a
  ON a.user_id = c.user_id
 AND a.activity_month >= c.cohort_month
GROUP BY 1, 2
ORDER BY 1, 2;

month_number = 0 is the signup month, 1 is the month after, and so on. The result is a long, tidy table: one row per (cohort, month offset) with a count of how many users were active.

Step 4: Turn it into the triangle

Retention tables are usually shown as a triangle — cohorts down the side, month offsets across the top. FILTER (or CASE) pivots the long output into columns:

WITH cohort AS (
  SELECT id AS user_id,
         date_trunc('month', created_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT user_id,
         date_trunc('month', occurred_at)::date AS activity_month
  FROM events
),
joined AS (
  SELECT c.cohort_month, c.user_id,
    (EXTRACT(YEAR  FROM age(a.activity_month, c.cohort_month)) * 12
   + EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)))::int AS month_number
  FROM cohort c
  LEFT JOIN activity a
    ON a.user_id = c.user_id
   AND a.activity_month >= c.cohort_month
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id)                                        AS cohort_size,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 1)        AS m1,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 2)        AS m2,
  COUNT(DISTINCT user_id) FILTER (WHERE month_number = 3)        AS m3
FROM joined
GROUP BY cohort_month
ORDER BY cohort_month;

Divide each mN by cohort_size and you get retention percentages. A real result looks like this:

Cohort	Size	Month 1	Month 2	Month 3
2026-01	820	44%	31%	27%
2026-02	910	47%	34%	—
2026-03	1,050	52%	—	—

Read it two ways. Down a column tells you whether newer cohorts retain better than older ones — here Month 1 climbs from 44% to 52%, so something you shipped is working. Across a row tells you the shape of the decay: a curve that flattens (27% → 27% → 27%) means you've found a loyal core; a curve that keeps sliding toward zero means you have no floor, and that's an existential problem.

Bounded vs. rolling vs. classic retention

People say "retention" and mean three different things. Getting this wrong makes two teams argue about numbers that were never measuring the same thing.

Type	Question it answers	SQL condition
Classic (period) retention	Were they active in month N?	`month_number = N`
Bounded / Day-N retention	Were they active on exactly day N?	`activity_date = cohort_date + N`
Rolling retention	Were they active on or after month N?	`month_number >= N`

The query above is classic period retention, which is the right default for most SaaS products. Bounded (=) is stricter and used for habit-forming apps where daily use is the goal. Rolling (>=) is the most forgiving — it counts a user as retained at Month 3 if they showed up any time from Month 3 onward, which is useful for infrequent-but-valuable products like a tax tool or an annual-review app. There's no universally correct one; there's only the one that matches how your product is meant to be used. Just be sure everyone in the room is looking at the same definition.

The mistakes that quietly ruin retention reports

The denominator bug (the big one). This is the mistake in the majority of hand-written retention queries, and it always inflates your numbers. If you use INNER JOIN instead of LEFT JOIN, or add a WHERE clause on the activity, users who never came back silently disappear from the cohort entirely. Now you're computing "of the users who returned, how many returned?" — which is close to 100% by construction. The denominator must always be the full cohort, including the people who churned. Keep the LEFT JOIN, and never filter the cohort CTE by activity.

Sanity check it independently:

-- cohort_size here must equal the size in your report
SELECT date_trunc('month', created_at)::date AS cohort_month,
       COUNT(DISTINCT id) AS cohort_size
FROM users
GROUP BY 1 ORDER BY 1;

If those numbers don't match your report's cohort_size, your join is dropping churned users.

Partial cohorts. This month's cohort hasn't lived a full month yet, so its later columns look artificially low. Don't panic over a 12% Month-1 for the current cohort — it's incomplete, not collapsing. Either exclude cohorts that haven't matured or grey them out in the dashboard.

Timezone and week boundaries. date_trunc uses whatever timezone your timestamps are stored in. If created_at is UTC but your users are in California, a signup at 9pm Pacific on Jan 31 lands in February's cohort. Normalize to a single timezone (created_at AT TIME ZONE 'America/Los_Angeles') before truncating, and pick one week-start convention if you cohort by week.

Choosing a vanity activity event. Worth repeating: if your "activity" is anything a user can trigger without engaging (a background sync, an email open), your retention curve is measuring your cron jobs, not your product.

Key takeaways

Cohort retention is the report that can't be faked by a good growth month. Group users by signup period, count distinct active users per later period, and always divide by the full cohort — churned users included. Use LEFT JOIN, DISTINCT your activity, exclude immature cohorts, and agree on whether you mean classic, bounded, or rolling retention before anyone reads a number off the chart. Get those right and you'll have a table that tells you the truth about whether people actually stick around.

The query itself is 30 lines. The hard part is running it regularly and putting it somewhere your whole team sees it — which is exactly the point where a raw SQL query needs to become a living dashboard. If you're already piping these cohorts into a chart or embedding them in your product, I'd love to hear how you've set it up.

How do you define an "active" user for your retention math — and has changing that definition ever completely rewritten your curve? Drop your approach in the comments.

Text-to-SQL at Scale: Stop Feeding Your LLM the Whole Database

Vivek Kumar — Wed, 12 Aug 2026 07:07:19 +0000

Text-to-SQL at Scale: Stop Feeding Your LLM the Whole Database

The first text-to-SQL demo you build always works. You have five tables — users, orders, products, subscriptions, events — you paste the whole schema into the prompt, and the LLM writes flawless queries. Ship it.

Then you point the same system at your real production database. Two hundred tables. Columns named flg_actv_ind and usr_ref_id_2. Three tables that all look like they hold "orders." Suddenly the model joins the wrong tables, invents a column that doesn't exist, or times out because the schema alone is 40,000 tokens before the user even asks a question.

This is the wall every team hits when moving text-to-SQL from demo to production. And here's the surprising part: study after study finds that generating SQL is not the hard part — picking the right tables is. Schema-linking errors (selecting the wrong tables or columns) account for roughly 20% of all text-to-SQL failures in major benchmarks. This post is about how to get that 20% back.

The core problem: schema linking

"Schema linking" is the step where the system figures out which tables and columns a question actually needs, before writing any SQL. Ask "How much revenue did we make from annual plans last month?" and the relevant subset might be just subscriptions, plans, and invoices — three tables out of two hundred.

Get this wrong and everything downstream fails. Miss a needed column and the query is incomplete. Include ten irrelevant tables and you drown the model in noise, so it joins orders to the wrong customers table. As one benchmark analysis put it, an incomplete or incorrect schema makes generating a correct query nearly impossible — the LLM can't recover from bad context.

So the naive approach — "just give the model everything" — fails for two reasons at once:

Problem	What happens
Token cost / limits	A 300-table schema can blow past the context window, or cost a fortune per query, before the user's question is even added.
Noise and distraction	Even when it fits, irrelevant tables confuse the model. More schema means more chances to link the wrong `status` column or pick the wrong `orders` table.

The fix is to stop treating the schema as a fixed prompt prefix and start treating it as something you retrieve on demand — the same idea behind RAG (retrieval-augmented generation).

Pattern 1: Retrieve the relevant schema with embeddings

Instead of dumping all tables into the prompt, index them and fetch only the ones that match the question.

The setup: for each table (and often each column), write a short natural-language description and embed it into a vector store. At query time, embed the user's question and pull back the top-k most similar schema elements. Those — and only those — go into the prompt.

Question: "revenue from annual plans last month"
        │
        ▼  embed + vector search over table descriptions
Top matches:
   subscriptions  (0.89)  — customer plan enrollments, billing cycle
   plans          (0.86)  — plan tiers, price, interval (monthly/annual)
   invoices       (0.81)  — issued charges, amount, paid_at
        │
        ▼  only these 3 tables go into the LLM prompt

The descriptions matter enormously. A raw column name like flg_actv_ind will never match "active users" semantically. But if you index the description "boolean flag, whether the subscription is currently active," it will. This is why teams that succeed at text-to-SQL invest in a data dictionary — the embeddings are only as good as the words you feed them.

Your prompt goes from this:

[ 300 tables, 40,000 tokens of DDL ]
Question: revenue from annual plans last month

to this:

-- Retrieved schema (only what's relevant):
CREATE TABLE subscriptions (
  id            bigint PRIMARY KEY,
  customer_id   bigint,
  plan_id       bigint,
  status        text,          -- 'active','canceled','past_due'
  started_at    timestamptz
);
CREATE TABLE plans (
  id       bigint PRIMARY KEY,
  name     text,
  interval text,               -- 'month' | 'year'
  price_cents integer
);
CREATE TABLE invoices (
  id         bigint PRIMARY KEY,
  subscription_id bigint,
  amount_cents    integer,
  paid_at         timestamptz
);
-- Question: revenue from annual plans last month

Now the model has a clean, focused context and reliably produces:

SELECT SUM(i.amount_cents) / 100.0 AS revenue
FROM invoices i
JOIN subscriptions s ON s.id = i.subscription_id
JOIN plans p        ON p.id = s.plan_id
WHERE p.interval = 'year'
  AND i.paid_at >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
  AND i.paid_at <  date_trunc('month', CURRENT_DATE);

Pattern 2: Bring the foreign keys along

Here's a mistake that bites people the first week: they retrieve tables by semantic similarity but forget the relationships. The question mentions "revenue" and "plans," so retrieval returns invoices and plans — but not subscriptions, the join table that connects them. The model now has no path between the two tables and either hallucinates a join or fails.

The fix is a graph-expansion step. After semantic retrieval, walk the foreign-key graph one hop out and pull in any bridging tables:

retrieved:  invoices, plans
FK graph:   invoices → subscriptions → plans
add:        subscriptions  (bridges the two)

Always include the foreign-key definitions in the DDL you pass to the model. Relationships are the single most valuable piece of context for correct joins, and they're cheap to include.

Pattern 3: Add a few real values for tricky columns

The model often has to guess what a status column contains. Does "canceled" mean status = 'canceled', 'cancelled' (British spelling), or 0? It can't know from the schema alone.

Feeding a handful of sample distinct values per low-cardinality column removes the guesswork:

-- Sample values:
--   subscriptions.status: 'active', 'canceled', 'past_due', 'trialing'
--   plans.interval: 'month', 'year'

This one trick eliminates a whole class of "the query ran but returned zero rows" bugs, where the SQL is syntactically perfect but filters on a string that never appears in the data.

Common mistakes and gotchas

Gotcha	Why it hurts	Fix
Indexing cryptic column names, not descriptions	`usr_ref_id_2` matches nothing semantically	Embed human-written descriptions from a data dictionary
Top-k too small	You drop a needed table and the query is impossible	Tune k on real questions; err on including bridge tables
Top-k too large	Noise returns; the model links the wrong table	Rerank, and cap at what the question realistically needs
Ignoring foreign keys	Model can't find a join path	Expand one hop along the FK graph after retrieval
No sample values	Correct SQL, zero rows (wrong filter literal)	Include distinct values for low-cardinality columns
Duplicate-looking tables	Three tables named like "orders"; model picks the stale one	Descriptions must disambiguate: "legacy, do not use" vs "current"

One more subtlety worth knowing: the newest, strongest reasoning models are surprisingly good at handling large schemas directly, and some research now questions whether aggressive schema pruning is always necessary. But that's a bet on token budget and latency. For most teams shipping to production today — where every query costs money and users expect answers in under two seconds — retrieving a focused schema is still the pragmatic default. Retrieval also gives you a debuggable artifact: when a query is wrong, you can inspect exactly which tables were fed in and see whether the failure was retrieval or generation.

Key takeaways

The hard part of text-to-SQL at scale isn't writing SQL — it's picking the right tables. Don't paste your whole schema into the prompt; it's expensive, it overflows context, and the noise makes the model worse. Instead, treat the schema as a retrievable resource: embed human-readable descriptions of your tables and columns, fetch the top matches for each question, expand along foreign keys so join paths stay intact, and sprinkle in sample values for ambiguous columns. Together these turn a flaky demo into something you can actually put in front of users.

If you're building this, the unglamorous prerequisite is a good data dictionary. The LLM can only retrieve what you've described well.

Your turn

Are you building a natural-language query layer over a big database? What's been your biggest headache — wrong joins, hallucinated columns, or latency? And how are you handling schema selection: full dump, embeddings, or hand-curated table groups? Drop your approach in the comments — I'm collecting patterns that hold up in production.

Your AI Writes Correct SQL and Still Gets the Wrong Answer. Here's Why.

Vivek Kumar — Tue, 11 Aug 2026 04:34:37 +0000

You wire up an AI SQL assistant, type "what was our revenue last month," and get back a clean, valid query. It runs. It returns a number. Everyone nods.

Then someone in finance runs the same question through the accounting system and gets a different number. Now you're in a meeting arguing about which one is real.

Here's the uncomfortable truth about text-to-SQL: the SQL is almost never the hard part. Modern models write syntactically correct SQL the vast majority of the time. The failures that actually hurt you aren't syntax errors — they're queries that run perfectly and quietly return the wrong answer. And no amount of "use a better model" fixes them, because the model was never given the one thing it needed: your definitions.

This post is about why that happens and how a semantic layer — a place where each metric is defined exactly once — turns an unreliable guessing machine into something you can actually trust.

The problem: your business terms are ambiguous, and the database doesn't know it

Ask three teams what an "active user" is and you'll get three answers. To product, it's a user with a login event in the last 30 days. To billing, it's a user with a live subscription. To growth, it's someone who hit a core action this week.

Your warehouse doesn't encode any of that. It has an events table, a users table, and a subscriptions table. When an LLM sees "active users," it picks one plausible interpretation, writes valid SQL for it, and hands you a number. The number looks reasonable, so nobody questions it — until it disagrees with every other system in the company.

This is what makes text-to-SQL dangerous rather than merely imperfect. As the team at Omni put it, the queries that fail don't throw an error — they return wrong data. A syntax error you catch immediately. A silently wrong active_user count you ship to a board deck.

Consider what the model actually has to guess:

Business term	Possible SQL meaning A	Possible SQL meaning B
Active user	login event in last 30 days	subscription status = 'active'
Revenue	SUM(orders.amount)	SUM(orders.amount) minus refunds
Churn	cancellations this month	MRR lost / MRR at start of month
New customer	first order this month	first paid subscription this month

The model isn't wrong for picking column A. It's wrong because there is no way for it to know you meant column B. That knowledge lives in people's heads and in scattered SQL snippets, not in the schema.

Why "just prompt it better" only gets you so far

The common first fix is to stuff definitions into the prompt: "active user = a user with at least one login event in the last 30 days." This genuinely helps — including business glossaries in context is one of the most effective mitigations you can apply today.

But prompt-based definitions don't scale:

Every question re-derives the metric from a fuzzy English sentence, so you get subtle drift between runs.
Nobody updates the prompt when the definition changes, so the glossary rots.
Complex metrics (net revenue retention, cohort churn) don't fit in a sentence — they need real SQL logic.
You have no guarantee the model used the definition rather than ignoring it.

Prompting treats the symptom. The disease is that your metric definitions aren't a first-class, queryable object anywhere in your stack.

The fix: define each metric exactly once

A semantic layer is a centralized catalog that sits between your warehouse and whatever queries it — a BI tool, a dashboard, or an AI assistant. Instead of every analyst (and every LLM prompt) re-deriving "monthly revenue," you define it one time, as explicit SQL, and everything references that definition.

Here's the shift in concrete terms. Without a semantic layer, your AI generates the whole query from scratch:

-- AI free-writing against raw tables. Which interpretation did it pick?
SELECT COUNT(DISTINCT user_id)
FROM events
WHERE event_name = 'login'
  AND created_at >= NOW() - INTERVAL '30 days';

With a semantic layer, the metric is pre-defined and the AI's job shrinks to picking the right metric and dimensions — not inventing the SQL. A definition might look like this (dbt/MetricFlow-style YAML):

metrics:
  - name: active_users
    label: "Active Users"
    description: "Distinct users with a login event in the last 30 days"
    type: simple
    sql: user_id
    agg: count_distinct
    filters:
      - "event_name = 'login'"
      - "created_at >= dateadd(day, -30, current_date)"
    dimensions: [plan, country, signup_month]

Now when someone asks "active users by plan last quarter," the AI doesn't guess the join or the filter. It selects the active_users metric and the plan dimension, and the semantic layer compiles the guaranteed-correct SQL. As dbt Labs describes it: if the model picks the right metric and dimensions, the query is correct by construction — it can't produce a bad aggregation or a wrong join.

You can express richer metrics the same way. Net revenue as explicit, reviewed logic:

-- Definition behind the "net_revenue" metric, written once, reviewed by humans
SELECT
    date_trunc('month', o.order_date) AS month,
    SUM(o.amount) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
GROUP BY 1;

Every question about "revenue" now resolves to this, whether it comes from a human, a dashboard, or an AI prompt. One source of truth, not one interpretation per query.

What this does to accuracy (the numbers are dramatic)

This isn't a marginal polish. Recent benchmarking tells a stark story. On raw, unnormalized tables, text-to-SQL systems have historically landed around 32.7% end-to-end accuracy. Point the same models at a properly modeled semantic layer and accuracy jumps to roughly 72–100% depending on question type, with enterprise deployments commonly reporting 85–95%.

The other benefit is subtler but just as important: when a question can't be answered from the defined metrics, a semantic layer can say so instead of fabricating a plausible query. "I don't have a metric for that" is infinitely safer than a confident wrong number.

Common mistakes and gotchas

Treating the semantic layer as documentation. A glossary in Notion doesn't help the AI. The definition has to be a live, queryable object — SQL the engine actually compiles — not prose a human has to remember to copy into a prompt.

Modeling everything on day one. You don't need 300 metrics. Start with the 10–15 numbers that show up in exec meetings and cause arguments. Those are where silent wrongness costs you the most.

Forgetting synonyms. Users say "signups," "new accounts," "registrations," and "new users" for the same thing. Register synonyms so the AI maps natural language to the right metric instead of free-writing a fourth variant.

No ownership. A metric definition without an owner drifts. When finance changes how churn is calculated, someone has to update the one definition — and because it's centralized, that update propagates everywhere instead of leaving twelve stale copies.

Skipping row-level security. A semantic layer is a great place to enforce tenant isolation, but only if you wire it in. Don't assume defining a metric also scopes it to the right customer's data.

Key takeaways

With modern models, correct syntax is the easy part. Correct meaning is the hard part, and it's where text-to-SQL silently fails.
Ambiguous business terms — active user, revenue, churn — have no single meaning in your schema, so the AI guesses, and the guess looks right.
A semantic layer defines each metric once as explicit SQL, shrinking the AI's job from "write the whole query" to "pick the right metric and dimensions."
This moves accuracy from roughly a third of questions to the 85–95% range in real deployments — and lets the system admit when it doesn't know.
Start small, assign owners, register synonyms, and treat definitions as code, not documentation.

If you're bolting an AI assistant onto your database, the highest-leverage thing you can build isn't a better prompt — it's a place where "revenue" means exactly one thing.

How are you handling this? Are you defining metrics in dbt, a dedicated semantic layer, or still shipping definitions inside prompts? Drop your approach in the comments — I'd love to hear what's actually holding up in production, and what tools you reach for to expose these metrics to non-technical teammates.

Sources: dbt: Semantic Layer vs. Text-to-SQL 2026 benchmark, Omni: Why text-to-SQL fails, Wren AI: Why the semantic layer is essential for reliable text-to-SQL, dbt Labs: How the dbt Semantic Layer works with MetricFlow.

Why Your Embedded Dashboards Are Slow (And the SQL Patterns That Fix Them)

Vivek Kumar — Mon, 10 Aug 2026 12:43:01 +0000

You shipped a customer-facing analytics page. It looked great in the demo with 500 rows of seed data. Then a real customer with 4 million events logged in, opened the dashboard, and watched a spinner for 22 seconds before their browser tab quietly gave up.

Here's the uncomfortable truth: when an embedded dashboard is slow, it is almost never the chart library, the network, or React. It's the SQL. You are asking your database to scan, join, and aggregate millions of rows every single time someone opens a report — and most of those rows haven't changed since the last time you did it.

This post walks through why that happens and the concrete SQL patterns that fix it, roughly in the order you should reach for them. We'll use plausible SaaS tables — users, orders, events, subscriptions — and show the query, the problem, and the fix.

The core problem: you're recomputing everything, every time

A typical dashboard tile runs something like this:

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*)                      AS order_count,
  SUM(amount_cents) / 100.0     AS revenue
FROM orders
WHERE tenant_id = 42
  AND created_at >= now() - interval '90 days'
GROUP BY 1
ORDER BY 1;

For a customer with millions of orders, that means a full scan of the last 90 days of data on every page load, for every customer, refiring whenever someone changes a filter. The result — daily revenue for the last three months — barely changes minute to minute, but you pay the full computation cost again and again.

The performance ladder below goes from cheapest fix to most involved. Most teams can stop after step 2.

Step 1: Index for the filters your dashboards actually use

Before anything fancy, make sure the database isn't scanning the whole table to answer a filtered query. Embedded dashboards almost always filter by tenant and a time range, so index for exactly that:

CREATE INDEX idx_orders_tenant_created
  ON orders (tenant_id, created_at);

Now the query above can jump straight to tenant 42's recent rows instead of reading everyone's history. Confirm it's working with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders
WHERE tenant_id = 42 AND created_at >= now() - interval '90 days';

If you see Seq Scan on a large table in the output, your index isn't being used. If you see an Index Scan or Bitmap Index Scan, you're on the right track. This one change alone often turns a multi-second query into a sub-100ms one.

Step 2: Pre-aggregate with a materialized view

Indexing helps the database find rows fast. But if the dashboard genuinely needs to aggregate millions of rows, the fastest possible aggregation is the one you did earlier and saved. That's a materialized view: a stored snapshot of a query's results that dashboards read directly.

CREATE MATERIALIZED VIEW daily_order_stats AS
SELECT
  tenant_id,
  date_trunc('day', created_at) AS day,
  COUNT(*)                      AS order_count,
  SUM(amount_cents)             AS revenue_cents
FROM orders
GROUP BY tenant_id, date_trunc('day', created_at);

-- required for concurrent refresh (more on that below)
CREATE UNIQUE INDEX idx_daily_order_stats
  ON daily_order_stats (tenant_id, day);

Your dashboard tile now reads from a table that already has one row per tenant per day:

SELECT day, order_count, revenue_cents / 100.0 AS revenue
FROM daily_order_stats
WHERE tenant_id = 42
  AND day >= now() - interval '90 days'
ORDER BY day;

This is the pattern behind the "28 seconds to 180 milliseconds" numbers people quote — you're reading a few hundred pre-summarized rows instead of scanning millions. The catch: the data is only as fresh as your last refresh.

-- rebuilds the snapshot; blocks reads unless you use CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_order_stats;

CONCURRENTLY lets dashboards keep reading during the refresh, but it requires that unique index and is slower and more resource-hungry than a plain refresh. Schedule it with a cron job or pg_cron every few minutes or hourly, depending on how fresh the numbers need to be. If your users can tolerate data being a few minutes behind — and for most analytics, they can — this is the single highest-leverage change you can make.

Step 3: Roll up incrementally when refreshes get expensive

Materialized views have a sharp edge: plain PostgreSQL rebuilds the entire result on every refresh. There's no built-in incremental mode. If you have three years of history but only today's rows changed, you're recomputing three years of totals to capture one day of new data. As the table grows, the refresh itself becomes the bottleneck.

The fix is a rollup table you update incrementally with an upsert, touching only the days that changed:

CREATE TABLE order_rollups (
  tenant_id   BIGINT,
  day         DATE,
  order_count BIGINT,
  revenue_cents BIGINT,
  PRIMARY KEY (tenant_id, day)
);

-- run every N minutes; only recomputes recent days
INSERT INTO order_rollups (tenant_id, day, order_count, revenue_cents)
SELECT
  tenant_id,
  date_trunc('day', created_at)::date,
  COUNT(*),
  SUM(amount_cents)
FROM orders
WHERE created_at >= current_date - 1     -- yesterday + today only
GROUP BY 1, 2
ON CONFLICT (tenant_id, day)
DO UPDATE SET
  order_count   = EXCLUDED.order_count,
  revenue_cents = EXCLUDED.revenue_cents;

Now the cost of keeping the dashboard fresh scales with how much data changed, not with how much data you have. Rollup tables are more code than a materialized view, so reach for them only when refresh time becomes a real problem.

Step 4: Cache the final result for repeated views

Even a fast query is wasteful if 200 users on the same team load the identical "last 30 days" tile within a minute. Put a cache in front of the query — Redis or Memcached — keyed by the query's inputs, with a short TTL:

cache_key = "dash:orders:tenant=42:range=30d"
value     = <serialized rows>, TTL = 300s

On a cache hit you skip the database entirely. Invalidate on a time-to-live for simple cases, or on write events (a new order arrives) when you need tighter freshness. Caching is the cheapest layer conceptually, but put it last — caching a query that's fundamentally doing a full-table scan just hides the problem until the cache misses.

Common mistakes and gotchas

Mistake	What happens	Do this instead
Refreshing a materialized view without `CONCURRENTLY`	Dashboards block and show errors during every refresh	Add a unique index and use `REFRESH ... CONCURRENTLY`
Refreshing far more often than the data changes	Table and index bloat; refresh itself becomes the slow query	Match refresh frequency to real freshness needs; vacuum after refreshes
Full rebuilds on a huge history table	Refresh time grows without bound	Switch to an incremental rollup with `ON CONFLICT` upserts
Caching before indexing	First load and every cache miss is still painfully slow	Fix the query first, then cache
Trying to serve truly real-time data from a materialized view	Users see stale numbers and lose trust	Use materialized views for near-real-time/historical; query live tables (well-indexed) for the few tiles that must be current

The freshness trade-off deserves emphasis: materialized views and rollups are, by design, a little behind. That's perfect for "revenue over the last 90 days" and wrong for "orders in the last 60 seconds." Know which tiles need which, and don't pay for real-time where nobody needs it.

Key takeaways

Slow embedded dashboards are a data-access problem, not a front-end one. Work up the ladder: index for the tenant-plus-time-range filters your dashboards actually use; pre-aggregate with a materialized view so you read summarized rows instead of scanning raw ones; move to incremental rollup tables when full refreshes get expensive; and cache the finished result for repeated views. Most teams get the win they need from the first two steps, and each step buys you a little staleness in exchange for a lot of speed — a trade almost every analytics tile is happy to make.

Your turn

What's the slowest tile in your product right now, and which layer of this ladder haven't you tried yet? If you're building customer-facing analytics, tools like Draxlr let you build SQL dashboards, add pre-aggregated metrics, and embed them into your app without hand-rolling every one of these layers yourself. Drop a comment with the query pattern that finally fixed your slowest dashboard — I'd love to see the before-and-after numbers.

Sources: Metabase — SQL performance tuning, Stormatics — PostgreSQL Materialized Views, Citus Data — Materialized views vs. Rollup tables in Postgres, PostgreSQL docs — REFRESH MATERIALIZED VIEW.

How to Build a Self-Serve Reporting Tool Your Team Actually Uses

Vivek Kumar — Mon, 10 Aug 2026 06:40:11 +0000

It usually starts with one Slack message: "Hey, can you pull how many signups we got last week?" You write the query in thirty seconds, paste the number, and move on. Then it happens again. And again. Soon you're the human API for your own database, and every "quick pull" is a context switch that eats your afternoon.

The fix isn't hiring an analyst or buying a six-figure BI suite. It's building a self-serve reporting tool: a thin layer over your existing SQL database that lets non-engineers answer their own questions safely, without you in the loop and without anyone accidentally running a DELETE on production.

This post walks through how to actually build one — the read-only foundation, a schema for saved and parameterized queries, the SQL patterns that keep it fast and safe, and the governance mistakes that quietly erode trust in the numbers. By the end you'll have a blueprint you can ship in a sprint.

What "self-serve" really means

Self-serve doesn't mean handing everyone a raw SQL console and hoping for the best. It means giving people a curated, parameterized set of reports they can run, filter, and schedule on their own — grounded in queries you've already vetted.

There are three layers to get right:

Access — a read-only path to the data that can never mutate or lock up production.
A query catalog — vetted, reusable queries with typed parameters (date ranges, plan tiers, customer IDs).
Governance — one agreed definition per metric, so "active user" means the same thing everywhere.

Skip any one of these and the tool either becomes dangerous, useless, or — worst of all — plausible but wrong. Let's build all three.

Step 1: Give reporting its own read-only door

The single most important decision: reporting traffic should never touch your primary write database directly. A heavy GROUP BY over a year of events can lock rows and slow down real users. Point reporting at a read replica instead, and connect with a read-only role so a bad query can't do damage.

-- Create a role that can only read from the reporting schema
CREATE ROLE reporting_ro WITH LOGIN PASSWORD 'change_me';

GRANT CONNECT ON DATABASE analytics TO reporting_ro;
GRANT USAGE ON SCHEMA public TO reporting_ro;

-- Read-only on everything that exists now...
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_ro;

-- ...and everything created later
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT TO reporting_ro;

Two extra guardrails worth adding on that role:

-- Kill runaway queries after 30 seconds
ALTER ROLE reporting_ro SET statement_timeout = '30s';

-- Cap how much work a single query can do before it bails
ALTER ROLE reporting_ro SET work_mem = '64MB';

Now the worst thing a self-serve user can do is run a slow query that gets killed automatically. Managed databases (Azure SQL, RDS, Cloud SQL) all support read replicas and read-only routing, so you rarely have to build this plumbing yourself — you just point your reporting connection string at the replica endpoint.

Step 2: Model the report catalog

The heart of a self-serve tool is a catalog of saved queries with typed parameters. Store them in their own small schema so the tool can list, render, and run them.

CREATE TABLE saved_reports (
  id            BIGSERIAL PRIMARY KEY,
  slug          TEXT UNIQUE NOT NULL,      -- 'weekly-signups'
  title         TEXT NOT NULL,             -- 'Weekly Signups by Plan'
  description   TEXT,
  sql_template  TEXT NOT NULL,             -- query with :placeholders
  owner_email   TEXT NOT NULL,
  created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE report_params (
  id          BIGSERIAL PRIMARY KEY,
  report_id   BIGINT REFERENCES saved_reports(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,               -- 'start_date'
  data_type   TEXT NOT NULL,               -- 'date' | 'int' | 'text'
  default_val TEXT
);

A saved report is just a SQL template plus a typed parameter list. Here's what a stored template looks like:

-- sql_template for 'weekly-signups'
SELECT
  date_trunc('week', u.created_at) AS week,
  s.plan_tier,
  COUNT(*) AS signups
FROM users u
JOIN subscriptions s ON s.user_id = u.id
WHERE u.created_at >= :start_date
  AND u.created_at <  :end_date
GROUP BY 1, 2
ORDER BY 1, 2;

Running weekly-signups with start_date = '2026-07-01' and end_date = '2026-07-15' returns something like:

week	plan_tier	signups
2026-06-29	free	412
2026-06-29	pro	58
2026-07-06	free	377
2026-07-06	pro	71

Your users never see the SQL. They see a title, a couple of dropdowns and date pickers, and a "Run" button. That's the whole magic of self-serve: the hard part is done once, by you, and reused forever.

Step 3: Bind parameters safely — never concatenate strings

This is where homegrown tools get dangerous. If you build the final query by gluing user input onto a string, you've just built a SQL injection vector into your own reporting layer. Always pass parameters through your driver's binding, never string interpolation.

# GOOD — parameters are bound by the driver, not concatenated
cursor.execute(
    report.sql_template.replace(":start_date", "%(start_date)s")
                       .replace(":end_date",   "%(end_date)s"),
    {"start_date": user_start, "end_date": user_end},
)

# BAD — one comment character away from disaster
query = f"... WHERE created_at >= '{user_start}'"  # never do this

Because the connection uses the reporting_ro role, even a query that slips through can only read. Defense in depth: safe binding and a powerless role.

Step 4: Enforce one definition per metric

Here's the mistake that kills trust faster than any bug: two reports that both claim to show "active users" and return different numbers. The moment a founder sees 1,204 in one dashboard and 1,187 in another, they stop believing all of them.

The fix is to define each metric once, in a database view, and have every report build on the view instead of re-deriving the logic.

CREATE VIEW active_users AS
SELECT DISTINCT user_id
FROM events
WHERE occurred_at >= now() - INTERVAL '28 days'
  AND event_name IN ('app_open', 'api_call');

Now any report that needs "active users" joins to active_users. Change the definition in one place and every report updates together. This is a lightweight version of what the analytics world calls a semantic layer — the practice of mapping business terms to governed, single-source definitions. You don't need a fancy product to start; a handful of well-named views gets you 80% of the value.

The industry consensus in 2026 is blunt about this: a natural-language or AI layer does not fix inconsistent metrics — it amplifies them. If your underlying definitions disagree, adding "ask a question in English" on top just produces confident, wrong answers faster. Get the definitions right first.

Common mistakes that quietly break your tool

Mistake	What goes wrong	Fix
Querying the primary DB	Reports lock rows and slow down real users	Point reporting at a read replica
String-concatenated params	SQL injection in your own tool	Bind parameters via the driver
Duplicated metric logic	Two reports, two different "truths"	One view per metric, reused everywhere
No query timeout	One heavy report ties up the replica	Set `statement_timeout` on the role
Defining everything up front	Months of modeling, nobody uses it	Ship 5 reports people asked for, iterate

That last one deserves emphasis. The most common failure mode of self-serve projects isn't technical — it's over-engineering. Teams try to model every possible metric before launch, spend a quarter on it, and ship something no one asked for. Start with the five queries you're already being pinged for on Slack. Turn those into saved reports. Add more only when someone asks twice.

Add scheduling once the basics work

Once your catalog exists, the highest-leverage feature to add next is scheduled delivery — email the "Weekly Signups" report to the founder every Monday at 8am so they never have to ask. A tiny report_schedules table plus a cron job that runs the query and sends the results covers the 90% case.

CREATE TABLE report_schedules (
  report_id    BIGINT REFERENCES saved_reports(id),
  cron         TEXT NOT NULL,          -- '0 8 * * 1'
  recipients   TEXT[] NOT NULL,        -- ['founder@acme.io']
  params       JSONB                   -- frozen parameter values
);

Now the number finds them, and the Slack pings stop for good.

Key takeaways

Building a self-serve reporting tool is far less work than it sounds, because the database does most of the heavy lifting. The blueprint: a read replica plus a read-only role for a safe foundation, a small catalog of saved queries with typed parameters so non-engineers can filter without writing SQL, bound parameters to stay injection-proof, and one view per metric so everyone shares the same numbers. Ship the five reports people already ask for, then let real usage tell you what to build next.

Do that, and you go from being the human query API to shipping a tool that quietly answers the questions before anyone has to ask.

Your turn

How does your team handle ad-hoc data requests today — a dedicated analyst, a shared query doc, a full BI platform, or something homegrown? And what's the one report your team asks for over and over? Drop it in the comments — I'm curious how many of us are secretly running the same "human API" job. If you've built something like this (or use a tool like Metabase, Lightdash, or Draxlr to skip the plumbing), share what worked and what you'd do differently.

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

Vivek Kumar — Sat, 08 Aug 2026 07:59:39 +0000

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 between events and users 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.canceled_on IS NULL OR s.canceled_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 to LEFT JOIN in staging views keeps your source-of-truth counts complete; switch to INNER 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.

Why AI Keeps Inventing Columns That Don't Exist (and How to Stop It)

Vivek Kumar — Fri, 31 Jul 2026 06:50:03 +0000

You ask an AI assistant for "total revenue by customer last month," it hands back a clean-looking query, you run it, and Postgres throws:

ERROR:  column "c.total_revenue" does not exist
LINE 3:   SUM(c.total_revenue) AS revenue

The query was confident. It was syntactically perfect. It was also referencing a column that has never existed in your schema. Welcome to the single most common failure mode of AI-generated SQL: the schema hallucination.

If you've spent any time using LLMs to write queries, you've hit this. The model invents a total_revenue column, joins to an accounts table you don't have, or assumes your orders table has a status column when yours calls it state. It's frustrating precisely because everything looks right. In this article I'll explain why this happens under the hood, and give you a concrete playbook to stop it — whether you're pasting schemas into ChatGPT or building a text-to-SQL feature into your own product.

Why AI hallucinates tables and columns

Large language models don't "know" your database. They generate SQL the same way they generate prose: by predicting the most statistically likely next token based on patterns seen during training. When you ask for revenue, the model has seen thousands of tutorials with a total_revenue column, so it confidently reaches for that name — even though your actual schema stores it as amount_cents on an invoices table.

There are a few distinct root causes worth naming, because each has a different fix:

Cause	What it looks like
No schema grounding	The model never saw your real tables, so it guesses common names
Training-data over-generalization	It reaches for "textbook" column names like `created_date` instead of your `created_at`
Underspecified questions	Vague prompts ("show me active users") invite the model to invent an `is_active` flag
Complex/ambiguous design	Multiple tables with similar columns confuse which one the model should use

The important insight: a hallucinated column is syntactically valid but semantically misaligned with your actual schema. The model isn't broken — it's doing exactly what it was designed to do with insufficient information. Which means the fix is almost always about feeding it better context, not about finding a smarter model.

Fix #1: Ground the model in your real schema

The number one cause of hallucinations is that the model is guessing at names it was never told. So tell it. Instead of asking "write me a query for revenue by customer," give it the actual DDL:

-- Paste this before your question
CREATE TABLE customers (
  id           BIGINT PRIMARY KEY,
  company_name TEXT,
  created_at   TIMESTAMPTZ
);

CREATE TABLE invoices (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT REFERENCES customers(id),
  amount_cents INTEGER,
  status       TEXT,          -- 'paid', 'open', 'void'
  issued_at    TIMESTAMPTZ
);

With that in context, the model now produces:

SELECT
  c.company_name,
  SUM(i.amount_cents) / 100.0 AS revenue_dollars
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.status = 'paid'
  AND i.issued_at >= date_trunc('month', now()) - interval '1 month'
  AND i.issued_at <  date_trunc('month', now())
GROUP BY c.company_name
ORDER BY revenue_dollars DESC;

Notice it used amount_cents and issued_at — your real column names — because you removed the guesswork. If you're building this into an app, you can pull the schema programmatically:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

Feed that output into your prompt as structured context and hallucination rates drop dramatically.

Fix #2: Add comments and metadata for ambiguous columns

Column names are often cryptic, and a model can't tell that state means order status versus a US state. Annotate them. Comments in the DDL you provide double as documentation the model reads:

CREATE TABLE orders (
  id        BIGINT PRIMARY KEY,
  state     TEXT,   -- order lifecycle: 'cart','placed','shipped','delivered','refunded'
  total     INTEGER,-- order total in cents, NOT dollars
  placed_at TIMESTAMPTZ
);

The -- order total in cents comment alone prevents the classic bug where the model forgets to divide by 100. For ambiguous business terms — what counts as an "active" customer, how "MRR" is calculated — spell out the definition. The model can't infer your business logic; it can only work with what you give it.

Fix #3: Use a semantic layer instead of raw tables

For a production text-to-SQL feature, exposing raw tables to an LLM is asking for trouble. A better pattern is a semantic layer: a curated set of views or defined metrics that map messy physical tables to clean business concepts. The model maps the user's intent onto this safe, well-named layer instead of navigating raw schema.

CREATE VIEW revenue_by_customer AS
SELECT
  c.id                     AS customer_id,
  c.company_name,
  date_trunc('month', i.issued_at) AS month,
  SUM(i.amount_cents) / 100.0      AS revenue
FROM invoices i
JOIN customers c ON c.id = i.customer_id
WHERE i.status = 'paid'
GROUP BY 1, 2, 3;

Now a question like "revenue for Acme last month" maps to a trivially correct query against revenue_by_customer. There are fewer tables, cleaner names, and no room to hallucinate a join. As a bonus, the view enforces your business rules (only paid invoices count) so the AI can't accidentally include voided ones.

Fix #4: Give the model examples (few-shot prompting)

Models are far more accurate when shown a couple of correct question-to-SQL pairs from your schema. This is called few-shot prompting, and research on approaches like DAIL-SQL shows curated examples meaningfully improve accuracy. Include two or three representative pairs in your prompt:

Q: How many orders did we ship yesterday?
A: SELECT COUNT(*) FROM orders
   WHERE state = 'shipped'
     AND placed_at::date = current_date - 1;

Q: Top 5 customers by revenue this year?
A: SELECT company_name, revenue
   FROM revenue_by_customer
   WHERE month >= date_trunc('year', now())
   ORDER BY revenue DESC LIMIT 5;

These examples teach the model your naming conventions, your preferred date handling, and which views to prefer — all without a single fine-tuning run.

Fix #5: Validate before you run

Even with perfect grounding, treat generated SQL as untrusted input. The cheapest safety net is to validate column and table references against the real schema before execution. In Postgres you can dry-run without touching data using EXPLAIN:

EXPLAIN SELECT c.company_name, SUM(i.total_revenue)  -- hallucinated column
FROM invoices i JOIN customers c ON c.id = i.customer_id
GROUP BY c.company_name;
-- ERROR: column i.total_revenue does not exist

If EXPLAIN fails, you've caught the hallucination without running anything. Feed the error message back to the model and let it self-correct — this validate-and-retry loop is one of the most effective mitigations in practice. Always run generated queries as a read-only role so a hallucinated DELETE or DROP can never do damage.

Common gotchas

Gotcha	How to avoid it
Stale schema in the prompt	Regenerate schema context on each run; don't hardcode it once
Silent wrong answers	A query can be valid SQL yet use the wrong column — spot-check results against known numbers
Cents vs. dollars	Document units in column comments; it's the most common "looks right, is wrong" bug
Ambiguous joins	Provide foreign keys in the DDL so the model doesn't invent join conditions
Over-trusting big models	A smarter model hallucinates less, but never zero — keep the validation layer

The trickiest failures aren't the ones that error out — those are easy. The dangerous ones are queries that run cleanly but quietly use the wrong table or forget a WHERE filter, returning a plausible-but-wrong number that ends up in a dashboard. That's why validation and result spot-checking matter as much as grounding.

Key takeaways

Schema hallucination happens because the model is pattern-matching against training data instead of your actual database. The fix is context, not a bigger model. Ground every prompt in your real DDL, annotate ambiguous columns with comments, expose a clean semantic layer of views rather than raw tables, show the model a few correct examples, and always validate generated SQL against the schema before running it as a read-only user. Do those five things and "column does not exist" mostly disappears from your life.

Have you built text-to-SQL into a product, or are you still copy-pasting schemas into a chat window? What's tripped you up the most — hallucinated columns, wrong joins, or the silent-wrong-answer problem? Drop your war stories in the comments. And if you've found a grounding or validation trick that works, I'd love to hear it.

Sources: Reducing Hallucinations in Text-to-SQL (Wren AI), Deep Dive into Text-to-SQL Hallucinations (DataFocus), Improving Text-to-SQL Accuracy with Schema-Aware Reasoning (Towards AI), Before Generation, Align it! (arXiv)

How to Safely Expose Database Insights to Non-Technical Users

Vivek Kumar — Thu, 30 Jul 2026 07:04:44 +0000

Every growing SaaS company hits the same wall. Your ops lead wants to know how many trials converted last week. Your support manager wants a breakdown of tickets by plan. Your CEO wants MRR by cohort. And all of those questions land in the same place: the one or two engineers who actually know the schema.

So you become a human query API. Someone Slacks you "can you pull X?", you context-switch, write a SELECT, paste a screenshot, and go back to what you were doing. Multiply that by five teammates and you've lost half a day to being a reporting middleman.

The obvious fix — "just give them database access" — is a trap. Non-technical users don't want a psql prompt, and even if they did, handing out raw credentials to production is how you end up with a DELETE that forgot its WHERE, or a support rep who can suddenly read every customer's payment token. This article walks through how to give non-technical people real, self-serve access to insights without giving them the keys to blow up your database.

Start with a read-only role, always

The single most important control is boring: a database role that can only read. Not "we trust them," not "they promised to be careful" — a role that is physically incapable of writing.

In PostgreSQL, you build it up from nothing:

-- 1. Create a login role for analytics
CREATE ROLE analytics_ro WITH LOGIN PASSWORD 'use-a-real-secret';

-- 2. Let it connect and see the schema, but nothing more
GRANT CONNECT ON DATABASE app_production TO analytics_ro;
GRANT USAGE ON SCHEMA public TO analytics_ro;

-- 3. Read-only on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_ro;

-- 4. And on tables you create in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO analytics_ro;

That last step trips up almost everyone. GRANT SELECT ON ALL TABLES only covers tables that exist right now. Ship a new invoices table next month and your analytics role can't see it. ALTER DEFAULT PRIVILEGES fixes that going forward.

A common gotcha worth calling out: PostgreSQL's permission layers stack, and a higher layer can quietly block a lower one. If analytics_ro has SELECT on a table but not USAGE on the schema that contains it, every query fails with a confusing permission error. Grant schema USAGE first, then table privileges.

Hide the columns that should never leave the database

Read-only stops writes, but it doesn't stop a support rep from running SELECT * FROM users and seeing password hashes, API keys, or PII they have no business seeing. Read-only is not the same as safe-to-read.

PostgreSQL supports column-level grants, so you can be surgical:

-- Revoke the broad grant on the sensitive table
REVOKE SELECT ON users FROM analytics_ro;

-- Re-grant only the safe columns
GRANT SELECT (id, email, plan, country, created_at)
  ON users TO analytics_ro;

Now a query touching password_hash or stripe_customer_id fails outright, while the useful columns stay available. The catch is that SELECT * will now error for this role — which is actually a feature, because it forces explicit column lists.

For anything more complex than "hide a few columns," reach for a view. Views are the cleanest way to hand non-technical users a curated, friendly shape of the data:

CREATE VIEW reporting.active_customers AS
SELECT
    u.id            AS customer_id,
    u.email,
    u.plan,
    s.status        AS subscription_status,
    s.mrr_cents / 100.0 AS mrr,
    u.created_at::date  AS signed_up_on
FROM users u
JOIN subscriptions s ON s.user_id = u.id
WHERE s.status = 'active';

GRANT SELECT ON reporting.active_customers TO analytics_ro;

The user queries active_customers and never touches the underlying tables, the joins, or the sensitive columns. You've turned a messy schema into a clean, self-documenting reporting surface.

Isolate each customer's data with row-level security

If your non-technical users are your customers (embedded analytics), read-only and column grants aren't enough — you need to guarantee customer A can never see customer B's rows. That's what row-level security (RLS) is for.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::int);

Your application sets the tenant per session:

SET app.current_tenant = '42';
-- From now on, every query on `orders` only returns tenant 42's rows,
-- even a bare SELECT * FROM orders.

The big win: isolation lives in the database, not in a WHERE clause your app might forget. One missing filter in application code has leaked entire customer tables before. With RLS, the guarantee holds even if the query is careless.

Give them an interface, not a SQL prompt

Locking down the data is half the job. The other half is that your ops lead is never going to write a window function. Non-technical users need a layer between them and SQL. There are three broad approaches:

Approach	How it works	Best for
Prebuilt dashboards	You write the queries once; they filter and view	Recurring KPIs everyone watches
Semantic layer	You define metrics (MRR, churn) as reusable building blocks; users drag & drop	Teams that ask varied but related questions
AI / text-to-SQL	Users type "revenue by plan last quarter"; AI generates the SQL	Open-ended, ad-hoc exploration

Text-to-SQL is the newest and most exciting of the three, but it changes your security model rather than replacing it. When an AI writes queries on behalf of a user, the read-only role and column grants become more important, not less — they're the guardrails that keep a hallucinated or over-broad query from doing damage. Point AI query tools at the restricted analytics_ro role and the curated reporting views, never at a superuser connection.

A quick sanity checklist

Before you hand any non-technical user (or AI agent) access to your data, run through this:

Check	Why it matters
Connection is read-only	No writes, ever — physically enforced by the role
Sensitive columns revoked or hidden behind views	PII, secrets, and hashes never surface in a query
Multi-tenant tables have RLS enabled	Customers can't see each other's rows
Query timeout set (e.g. `statement_timeout`)	A runaway `JOIN` can't melt production
Access points at a replica, not primary	Heavy reporting queries don't slow down your app

That fourth row deserves emphasis. A non-technical user doesn't know that a cross join over two big tables is expensive. Set a statement_timeout on the analytics role so a bad query cancels itself instead of pinning your CPU:

ALTER ROLE analytics_ro SET statement_timeout = '30s';

And if you can, run reporting against a read replica. That way even a perfectly legitimate but heavy dashboard refresh never competes with the queries your customers depend on.

Common mistakes to avoid

Giving out the app's database user. It's tempting to reuse the credentials your application already has. Don't — that role can write, and it can usually read everything. Always mint a dedicated, minimal role.

Relying on the frontend to hide data. If sensitive columns are filtered out only in your dashboard UI, anyone who inspects a network request or exports raw data can still reach them. Enforce restrictions at the database layer where they can't be bypassed.

Forgetting ALTER DEFAULT PRIVILEGES. New tables silently become invisible to your read-only role, and you'll waste an afternoon debugging "why can't they see the new report?"

Skipping the timeout. The first time a non-technical user accidentally runs an unbounded query, you'll wish you'd set one. Do it upfront.

Key takeaways

Safely exposing insights isn't about trust — it's about building a data access path where mistakes are structurally impossible. A dedicated read-only role stops writes. Column grants and reporting views hide what should stay hidden. Row-level security isolates tenants. A statement timeout and a read replica protect performance. And an interface — dashboards, a semantic layer, or AI text-to-SQL — meets non-technical users where they are.

Get those layers right and you stop being a human query API. Your teammates answer their own questions, your data stays protected, and you get your afternoons back.

How do you hand out data access at your company — read-only roles, a BI tool, an embedded dashboard, or something homegrown? Drop your setup (and your horror stories) in the comments. I'd love to hear what's worked and what's bitten you.

Build vs. Buy Embedded Analytics: A Decision Guide for SaaS Teams

Vivek Kumar — Tue, 28 Jul 2026 04:08:33 +0000

Every SaaS product eventually hits the same request: "Can we get a dashboard for our data?" Your customers want to see their own orders, usage, revenue, and trends — inside your app, not exported to a spreadsheet.

So you open a ticket, and the debate starts. Do we build the analytics ourselves with the database we already have, or do we buy an embedded analytics tool and drop it in? It sounds like a simple procurement question. It isn't. Pick wrong and you either sink two engineers into a reporting side-project for a year, or you pay a monthly bill for a tool that still needs three sprints of glue code before it's safe to ship.

This post gives you a decision framework grounded in real costs, real SQL, and the multi-tenant gotchas that turn a "quick dashboard feature" into a security incident. By the end you'll know which path fits your product — and why the honest answer for most teams is somewhere in the middle.

First, be honest about what you're building

The word "dashboard" hides a lot of complexity. Before you can decide build vs. buy, you need to know what your customers actually need. There's a big difference between these three things:

Level	What it means	Effort
Static reporting	A few fixed charts: revenue this month, active users, top products	Low
Interactive dashboards	Filters, date ranges, drill-downs, per-customer views	Medium
Self-serve analytics	Customers build their own queries, charts, and saved reports	High

Most teams say they want self-serve analytics and actually need interactive dashboards. Nail down the real requirement first, because it changes the math completely. Three fixed charts is a weekend of work on top of SQL you already know. A self-serve query builder is a product inside your product.

The core question: is analytics your differentiator?

Here's the filter that cuts through most of the debate:

Build what differentiates you. Buy everything else.

If analytics is your product — you're a monitoring tool, a financial platform, a data-heavy vertical SaaS where the charts are the reason people pay — then dashboards are core IP and you should probably build. Your competitive edge lives in how you model and present data, and you don't want that outsourced.

If analytics is a supporting feature — reporting that makes your main workflow stickier — buying almost always wins. You'll ship in weeks instead of quarters, and your engineers stay focused on the thing customers actually pay for.

The real cost comparison

Teams wildly underestimate the build side because they price the first version and forget the next three years. Here's the honest picture that industry cost breakdowns converge on:

	Build in-house	Buy a platform
Time to first dashboard	6–12 months	4–8 weeks
Year 1 cost	~$180k–$310k (engineering)	~$6k–$24k/yr + integration
3-year cost	~$370k–$630k	~$150k–$360k
Ongoing maintenance	15–25% of build cost / yr	Included in subscription

The number that surprises people is maintenance. A dashboard feature isn't "done" when it ships. Customers ask for new chart types, new filters, faster load times, CSV export, scheduled emails. Every one of those is a ticket that competes with your roadmap forever.

That said — buying isn't free of engineering either. A "cheap" $500/month tool that requires three sprints to wire up proper tenant isolation isn't actually cheap. Which brings us to the part that bites everyone.

The gotcha that turns a feature into an incident: multi-tenancy

If you build, this is where you'll spend your real time — not on charts, on making sure customer A never sees customer B's data. Most SaaS apps use a shared-schema model: one big table with a tenant_id column.

-- Every analytics table carries the tenant boundary
SELECT
    date_trunc('day', created_at) AS day,
    count(*)                      AS orders,
    sum(amount_cents) / 100.0     AS revenue
FROM orders
WHERE tenant_id = $current_tenant   -- this filter is load-bearing
  AND created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

That WHERE tenant_id = $current_tenant clause looks trivial. It is the single most important line in your entire analytics feature. Miss it on one query — one forgotten join, one aggregate that rolls up across tenants — and you've leaked data.

The dangerous version of this mistake is enforcing the boundary only in the UI. Filtering results in your frontend is not security. If the query returned other tenants' rows, they already crossed the wire. Enforce isolation server-side, in the query, every time.

The more robust pattern is Postgres Row-Level Security, so the database itself refuses to return the wrong rows even if a query forgets the filter:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::bigint);

-- Your app sets this per request, before running any query:
SET app.current_tenant = '4172';

Now a query that forgets WHERE tenant_id still can't leak — the policy scopes it automatically. This is exactly the kind of infrastructure a good embedded tool handles for you, and exactly what a "cheap" tool makes you build yourself.

Performance: the second thing you'll discover in month six

Tenant isolation and performance collide. The moment you add RLS or per-tenant filters, your query planner needs help. The fix is boring but non-negotiable: index the tenant boundary.

-- Composite index: tenant first, then the column you filter/sort by
CREATE INDEX idx_orders_tenant_created
    ON orders (tenant_id, created_at DESC);

With this in place, a per-tenant time-range query reads only that tenant's recent rows instead of scanning the whole table. Teams that skip this ship fine on day one with 10 customers, then watch dashboards crawl at 10,000. Verify it with EXPLAIN ANALYZE and make sure you see an index scan, not a sequential scan:

EXPLAIN ANALYZE
SELECT count(*), sum(amount_cents)
FROM orders
WHERE tenant_id = 4172
  AND created_at >= now() - interval '90 days';
-- Look for: "Index Scan using idx_orders_tenant_created"

For heavy aggregations that customers hit constantly, pre-compute with a materialized view and refresh on a schedule rather than recomputing on every page load:

CREATE MATERIALIZED VIEW tenant_daily_revenue AS
SELECT tenant_id,
       date_trunc('day', created_at) AS day,
       sum(amount_cents) / 100.0     AS revenue,
       count(*)                      AS orders
FROM orders
GROUP BY tenant_id, date_trunc('day', created_at);

-- Refresh nightly (or hourly) instead of on every dashboard load
REFRESH MATERIALIZED VIEW CONCURRENTLY tenant_daily_revenue;

The hybrid path (why most teams end up here)

The build-vs-buy debate is usually a false binary. The pattern that increasingly wins in 2025–2026 is:

Buy the parts that are undifferentiated and dangerous to get wrong: the dashboard rendering, chart library, filter UI, and tenant-isolation plumbing.
Build the parts that are your edge: the SQL that models your domain, the metrics definitions, and any bespoke visualization your competitors don't have.

In practice that looks like keeping your carefully-tuned SQL and semantic layer in-house, and letting an embedded analytics tool handle the rendering and the multi-tenant security boundary. You get to ship in weeks, keep control of your metrics, and not become a dashboard-maintenance company.

Common mistakes to avoid

Buying on demo polish. The prettiest demo often hides weak multi-tenancy. Ask how the tool isolates tenant data before you fall in love with the charts.
Enforcing security in the UI. Frontend filtering is not access control. If the query returned the row, it's already leaked.
Pricing only the first version. Build costs are dominated by years two and three, not the launch. Budget for maintenance or don't build.
Skipping the tenant_id index. It works at 10 customers and dies at 10,000. Index the boundary from day one.
Confusing "we could build it" with "we should build it." You can build almost anything. The question is whether it's the best use of the only engineers you have.

Key takeaways

The build-vs-buy decision comes down to one honest question: is analytics your differentiator, or a supporting feature? If it's core IP, build it and own it. If it's a feature that makes your product stickier, buy the rendering and isolation, and spend your engineering budget on the SQL and metrics that are actually yours. And whichever path you pick, treat multi-tenant isolation as a database-level guarantee — not a UI filter — because that's the line between a nice feature and a headline you don't want.

What did your team choose, and what surprised you six months in? Did you build, buy, or land in the hybrid middle? Drop your experience — and the tools you're using — in the comments. I'd love to hear what held up under real load.