English

SQL and Dashboard Design That Earns Stakeholder Trust

The CEO opens the revenue dashboard on Monday morning. There are 14 charts. Three of them have the word "revenue" in the title. They show different numbers. He pings you on Slack: "wait, what does revenue mean here?" You spend 30 minutes explaining the difference between booked, recognized, and collected. He thanks you, closes the tab, and goes back to the Stripe export his finance lead sent him because at least that one number doesn't argue with itself.

That dashboard didn't fail because it had too many charts. It failed because two of those charts said different things and nobody in the room could defend either one.

The pain in BA work isn't volume. It's that every dashboard you ship is a promise (this is the number, you can act on it), and most BAs ship that promise without doing the work that makes it defendable. This piece is about that work. The SQL patterns, the layout choices, the dbt hygiene, and the deprecation discipline that turn a 14-chart kitchen sink into one number a stakeholder will bet a quarter's budget on.

Dashboard design — one decision per dashboard

The single most useful question to ask before you build anything: what action does this dashboard drive?

If the stakeholder can't finish the sentence "after looking at this, I will…" in plain English, you don't have a dashboard. You have a wallpaper. Kill it or split it.

One decision per dashboard. Not three. The ARR dashboard answers "are we on plan this quarter." The pipeline-coverage dashboard answers "do we have enough deals to hit next quarter." Those are different decisions, different audiences, different cadences. They don't belong on the same canvas just because they both involve sales.

Visual hierarchy: the 2-second rule

When the dashboard loads, the stakeholder's eye should land on the answer in under 2 seconds. That means:

  • Top-left: the headline number. Big, bold, with the comparison baked in ($4.2M ARR · +6% vs plan). Not "Revenue YTD" with a line chart underneath that you have to read.
  • Below the headline: 2-4 supporting cuts that explain why the headline is what it is. By segment, by region, by motion. Not 12 cuts. Four.
  • Drill-downs in tabs or click-through: anyone who wants the long tail can get there. Don't make the default view carry it.

If your dashboard's headline number is in chart 7 of 14, you've inverted the pyramid. Most BAs do this because they're afraid of leaving something out. Be more afraid of stakeholders not knowing what to look at.

Color discipline — stop building Skittles bags

Most BA dashboards are a Skittles bag. Eight categorical colors, three accent colors, a heatmap, two diverging palettes, and a stoplight all on one page. That's noise, not signal.

Pick a constraint and hold it:

  • One accent color for "good" (usually a brand green or blue).
  • One accent color for "alert" (usually red or orange).
  • Everything else is neutral grayscale.

If you find yourself reaching for a fourth color, the problem isn't that you need more colors. The problem is that you're trying to show too many things on one chart. Split the chart.

Annotations beat legends

A legend is something the stakeholder has to read, decode, and remember. An annotation is the chart talking to them.

A two-line caption next to a Q3 dip ("Q3 dip = pricing change shipped Aug 14, expected to recover by Q4") prevents 80% of the follow-up Slack messages. The other 20% are about something the dashboard wasn't built to answer, which is also useful information.

Make annotations a habit. Every chart with a non-obvious shape gets a one-sentence explanation in the chart itself, not in a separate doc nobody reads.

SQL practices that ship trust

The dashboard is the front door. The SQL underneath is the foundation. Stakeholders don't see the SQL, but they feel it every time the number shifts and you can't explain why.

CTEs over nested subqueries

Compare these two queries that do the same thing:

-- The nested-subquery version (don't do this)
SELECT customer_id,
       SUM(amount) AS revenue
FROM (
  SELECT *
  FROM orders
  WHERE status = 'paid'
    AND created_at >= '2026-01-01'
    AND customer_id IN (
      SELECT id FROM customers
      WHERE region = 'NA'
        AND tier IN ('enterprise', 'mid-market')
    )
) paid_orders
GROUP BY customer_id;

-- The CTE version (do this)
WITH na_target_customers AS (
  SELECT id
  FROM customers
  WHERE region = 'NA'
    AND tier IN ('enterprise', 'mid-market')
),

paid_orders_2026 AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'paid'
    AND created_at >= '2026-01-01'
)

SELECT po.customer_id,
       SUM(po.amount) AS revenue
FROM paid_orders_2026 po
JOIN na_target_customers c
  ON po.customer_id = c.id
GROUP BY po.customer_id;

Same result. The CTE version has names. A peer can read it in 30 seconds. The nested version is a puzzle. A query a peer can't read in 30 seconds is a query that will silently break in 6 months when somebody changes the customer tier definition and nobody notices because they couldn't find the filter.

CTEs cost you four extra lines and buy you a query that survives team turnover.

Single-source-of-truth dbt models

If revenue is calculated in four dashboards, it will diverge in four dashboards. Not "might." Will. Somebody will tweak one to exclude refunds. Somebody else will tweak another to include trial conversions. Six months later, four dashboards show four numbers and trust is gone.

Centralize the metric in a dbt model. One file. One definition. Then every dashboard, every Looker explore, every Hex notebook references that model and only that model. If finance wants to change how revenue is calculated, they change it once, and every downstream artifact updates.

This is not a nice-to-have. This is the difference between a BA team that scales and a BA team that spends Q4 every year reconciling numbers nobody believes.

Named tests on every metric model

dbt gives you four built-in tests that catch most silent drift:

version: 2

models:
  - name: fct_revenue_recognized
    description: "Recognized revenue per company GAAP policy. Owner: finance-data."
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: revenue_status
        tests:
          - accepted_values:
              values: ['recognized', 'deferred', 'refunded']

Run these on every PR. If somebody adds a new revenue_status value upstream and forgets to update the model, the test fails before the dashboard does. The first time a not_null test catches a bug in production data, you'll wonder how you ever shipped without it.

Comment the why, not the what

-- Bad: tells you what the code already says
WHERE status = 'paid'

-- Good: tells you why the rule exists
-- Finance excludes refunds processed >30d after order
-- per revenue policy v3 (signed by CFO 2025-Q4).
WHERE status = 'paid'
  AND NOT (status_changed_at > created_at + INTERVAL '30 days'
           AND status = 'refunded')

The bad comment is noise. The good comment is the only thing that will save the next BA who inherits this query in a year. Write the why. Especially for any business rule that isn't obvious from the column name.

The "two definitions of revenue" diagnostic

Here's the most useful trust-building move a BA can make in their first 90 days, and it doesn't involve writing a single dashboard.

Go to your finance lead. Ask them, on a call, "how do we calculate revenue?" Write down the answer.

Go to your sales lead. Ask the same question. Write down the answer.

They will give you two different answers.

Sales will tell you about booked ACV: closed deals, signed contracts, the number on the leaderboard. Finance will tell you about recognized revenue: collected cash net of refunds, deferred according to policy, the number that goes to the board. Both are correct. Both are real. They are answering different questions. And right now, somewhere in your company, a dashboard is showing one of them and calling it "revenue" without saying which one.

Document both. Name them clearly in the dbt model:

-- fct_revenue_finance.sql -- recognized per GAAP policy v3
-- fct_revenue_sales_booked.sql -- booked ACV at deal-close date

Expose both in your BI layer. Let the stakeholder pick the one that matches their question. When the CEO asks "how is revenue tracking," you ask back: "booked or recognized?" They will pause. They will think. They will give you the right answer, and from that moment on you are the person they trust because you made them feel competent instead of confused.

This diagnostic also tells you which org is sloppier with definitions, which is intel you'll use for the next two years.

Dashboard vs. one-off — when to build, when to screenshot

Not every question deserves a dashboard. The default in most BA orgs is "the answer to a question is a new dashboard," and that's how you end up with 200+ dashboards and no idea which 30 are actually used.

The heuristic is simple. Will this question be asked again in 30 days?

  • Yes → dashboard. Worth the maintenance commitment.
  • No → SQL query, screenshot, drop it in Slack, done. Don't pollute the dashboard library with a board-prep one-off.

Every dashboard you build is a 6-month maintenance commitment. Source tables change. Definitions drift. Stakeholders move teams. You'll be asked to fix it. Decline accordingly.

The decline-the-dashboard script:

"Happy to pull this for you as a one-off. I'll send the chart in Slack by EOD. If this becomes a recurring question (you're asking again next month), let's revisit and I'll build it properly with documentation. Right now, building a dashboard for a one-time question would add maintenance overhead that doesn't pay back."

Send that to a stakeholder once and they'll respect you more, not less. Building everything is a tell that you don't value your own time, which means they won't either.

Version control and change-log discipline

All SQL lives in git. Yes, your Looker LookML. Yes, your Hex notebook (or copy the SQL into a repo). Yes, your dbt models, obviously. If a number on a dashboard can change, the change has to be reviewable.

Two rules that catch most disasters:

  1. Two-person review on metric changes. Any PR that touches a fct_* or dim_* model gets reviewed by one other analyst before merge. Not a manager. A peer who'll actually read the SQL. This catches the silent definition drift that destroys trust.

  2. Change-log card pinned to every dashboard. Top of the dashboard, always visible:

Change Log — ARR Tracker
2026-04-15: Switched revenue source from Stripe to NetSuite.
            Numbers may differ from prior screenshots by ~2%.
            Owner: camellia. PR: #1842.
2026-02-03: Added enterprise-tier breakout.
2025-11-20: Initial build.

When a stakeholder screenshots the dashboard in March and asks why it's different in May, the change log answers the question without you having to.

The 6-month deprecation review

Most BA orgs have 200+ dashboards and use 30. The other 170 are dead weight: confused new hires, made executives doubt the active ones, ate query credits in the warehouse. Cleaning is the work.

Set a calendar reminder every 6 months. Run a query against your BI tool's audit log:

-- Looker example
SELECT dashboard.title,
       dashboard.id,
       MAX(history.created_at) AS last_opened,
       COUNT(DISTINCT history.user_id) AS unique_viewers_180d
FROM history
JOIN dashboard ON history.dashboard_id = dashboard.id
WHERE history.created_at > CURRENT_DATE - INTERVAL '180 days'
GROUP BY 1, 2
ORDER BY last_opened ASC;

The output sorts your dashboards from most-stale to least-stale. Apply the rule:

  • Not opened in 90 days → archive. No discussion.
  • Opened by 1-2 people → DM them. "Still need this? If yes, I'll keep it. If no, archiving Friday." Most will say archive.
  • Opened by 5+ people regularly → keep, and make sure it's on your maintenance list.

Post the kill list in a public Slack channel before you archive. Gives stakeholders 48 hours to object. They almost never do.

A BA team that does this twice a year goes from 200 dashboards down to 40, and the 40 that remain are the ones executives actually look at. The signal-to-noise improvement is enormous, and you become known as the team that ships less, which sounds counterintuitive until you watch the trust meter move.

Closing — trust is a craft output

Trust isn't a personality trait. It's not about being charming in stakeholder meetings, or always saying yes, or being the helpful BA. Those things help, but they fade. What lasts is the craft.

The BA who can answer "where does this come from, who else uses it, when did it last change" in 10 seconds is the BA executives keep on the strategic projects. The BA who can't gets quietly demoted to "ad-hoc query monkey" and never recovers, no matter how friendly they are.

Build for one decision. Hold the color line. Centralize your metrics. Test them. Comment the why. Ask both definitions of revenue. Decline the dashboards that don't earn their keep. Pin the change log. Archive on cadence.

That's the whole job. Ship those moves consistently for two years and you won't have to ask for the seat at the strategy table. They'll save it for you.

Learn More