Designing KPI Dashboards That Data Engineers Will Actually Maintain

There is a particular kind of data engineering dread that sets in when a dashboard request comes in from a senior stakeholder with a screenshot from a competitor’s annual report and the words “can we do something like this?” The dashboard gets built — a visual masterpiece with twenty KPIs, eight chart types, four drill-through pages, and a colour gradient that required three hours of work. Three months later, it breaks because a column was renamed in the upstream table and nobody knows which of the forty-two DAX formulas or Athena queries feeds which visual.

KPI dashboards that data engineers will actually maintain are designed for sustainability from the start. This guide covers the technical and organisational patterns that separate dashboards that last from dashboards that become technical debt.

Define Metrics Before Building Anything

The most common mistake in KPI dashboard projects is starting with the visual design and working backwards to the data. By the time you discover that “active customers” means three different things to the sales team, the marketing team, and the finance team, you have already built three different query paths into your dashboard.

Establish a metric definition document before any SQL is written. For each KPI on the dashboard, document:

• Business definition: In plain language, what does this metric measure and what business question does it answer?
• Technical definition: The exact SQL or dbt model that produces it, including filters, aggregation logic, and date handling.
• Owner: Who is accountable for the accuracy of this metric?
• Data source: Which table(s) and which date range?
• Update frequency: How often is the underlying data refreshed, and what is the acceptable staleness?
• Known limitations: What does this metric not capture? What edge cases exist?

This document becomes your metric catalogue. When a stakeholder asks why the revenue figure on the dashboard differs from the revenue figure in the finance system, the metric catalogue is the first place you look — and ideally, it already explains the reconciliation.

For organisations with multiple BI tools and data consumers, storing this catalogue in a formal system (Atlan, DataHub, or Amazon DataZone) rather than a spreadsheet is worth the investment.

The Single-Source-of-Truth Architecture

KPI dashboards that are hard to maintain typically have their metric definitions embedded in multiple places: a SQL query in the SPICE dataset, a calculated field in the BI tool, and a transformation step in the ETL job. When the business logic changes, you must find and update all three — and if you miss one, the dashboard silently shows stale or incorrect numbers.

The sustainable architecture centralises metric definitions at one layer and treats everything else as a consumption interface:

Raw Data (S3/Redshift)
    ↓
Transformation Layer (dbt models)
    ↓ ← Define ALL business logic here
Curated Metrics Tables (in Athena/Redshift)
    ↓
BI Tool (QuickSight/Tableau/Power BI)
    ↓ ← Presentation only; minimal calculation here
Dashboard

In this architecture, the dbt transformation layer owns all business logic. The BI tool is a presentation layer that reads pre-computed metric tables. Calculated fields in the BI tool are limited to display formatting (percentages, currency symbols, conditional colours) — not business logic.

A dbt model for a dashboard KPI:

-- models/marts/kpi/fct_monthly_revenue_by_region.sql
{{
  config(
    materialized='table',
    schema='kpi',
    tags=['daily', 'dashboard-kpis'],
    meta={
      'owner': 'data-platform@myorg.com',
      'dashboard': 'Executive Monthly KPIs',
      'sla_hours': 4
    }
  )
}}

WITH orders AS (
    SELECT
        DATE_TRUNC('month', order_date) AS revenue_month,
        country_code,
        customer_segment,
        -- Net revenue: order amount minus refunds, excluding cancelled orders
        SUM(order_amount) - COALESCE(SUM(refund_amount), 0) AS net_revenue,
        COUNT(DISTINCT order_id) AS order_count,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM {{ ref('fct_orders') }}
    WHERE order_status NOT IN ('CANCELLED', 'PENDING')
    GROUP BY 1, 2, 3
)

SELECT
    revenue_month,
    country_code,
    customer_segment,
    net_revenue,
    order_count,
    unique_customers,
    -- Pre-compute period-over-period for the dashboard
    LAG(net_revenue, 12) OVER (
        PARTITION BY country_code, customer_segment
        ORDER BY revenue_month
    ) AS net_revenue_prior_year,
    net_revenue / NULLIF(LAG(net_revenue, 12) OVER (
        PARTITION BY country_code, customer_segment
        ORDER BY revenue_month
    ), 0) - 1 AS yoy_growth_rate
FROM orders

The meta block documents who owns this model and which dashboard it serves — critical when you need to trace the impact of a schema change downstream.

Dashboard Layout Principles for Maintainability

Visual design decisions affect maintainability as well as aesthetics. Some specific patterns that reduce future engineering effort:

Filter at the top, KPIs second, trends third, details last. This is the standard “F-pattern” reading flow for BI dashboards, but it also reflects the maintenance hierarchy: filters and KPI scorecards break most often (they are most sensitive to data quality issues), and their position at the top means problems are visible immediately rather than buried in a detail table.

One KPI per calculation path. Avoid dashboards where the same KPI appears in multiple places calculated differently. If Total Revenue appears in a KPI tile, a trend chart, and a regional breakdown table, all three should reference the same underlying metric field — not three separate calculations.

Separate business context from technical implementation. Dashboard titles, axis labels, and tooltip text should use business language (“Monthly New Customer Revenue”) not technical identifiers (“SUM(net_order_amount) WHERE customer_cohort_flag = 1”). The technical definition lives in the metric catalogue; the dashboard communicates business meaning.

Use colour deliberately and sparingly. Traffic-light colours (red/amber/green) for KPI status indicators are effective because they carry universal meaning. Using a different colour for every region, product line, and time period simultaneously produces visual noise that makes the dashboard harder to read and easier to misconfigure.

Data Freshness Indicators: A Required Engineering Investment

One of the most common sources of stakeholder distrust in dashboards is uncertainty about when the data was last updated. A dashboard showing yesterday’s revenue figures when the viewer expects today’s erodes confidence — even if the figures are technically correct for the data available.

Every production KPI dashboard should display data freshness prominently. Implement this through a _metadata table that your ETL pipeline writes after each successful run:

-- Written by dbt post-hook or Glue job completion
INSERT INTO metadata.pipeline_runs (
    pipeline_name,
    run_timestamp,
    records_processed,
    min_event_date,
    max_event_date,
    status
) VALUES (
    'daily_revenue_pipeline',
    CURRENT_TIMESTAMP,
    :records_processed,
    :min_date,
    :max_date,
    'SUCCESS'
)

In QuickSight, surface this as a KPI tile at the top of every dashboard page:

Data last updated: [max_event_date from pipeline_runs]
Pipeline status: [status indicator]

When the pipeline fails, this tile shows an amber or red indicator and the last successful run timestamp. Stakeholders can self-diagnose data freshness issues without filing a ticket.

Parameterised Dashboards vs. Embedded Filters

A common request is to build a “one dashboard for all regions” that each regional manager sees filtered to their own data. There are two implementation approaches with different maintenance implications:

Row-level security (RLS): The dashboard is a single published version. Access control determines what each user sees. Zero duplication of dashboard code. Changes to the layout happen once. This is the right approach when the dashboard is the same for all users — only the data changes.

Parameters/controls: The dashboard has a filter selector that users interact with. All users see all filter options. Appropriate when users legitimately need to compare across regions.

Dashboard duplication: Create separate dashboards for each region, each with a hardcoded filter. This is the maintenance anti-pattern. Twenty regional dashboards require twenty updates every time a KPI definition changes.

Implement RLS for regional dashboards — it is more work to set up but dramatically less work to maintain. Our QuickSight Guide covers the technical implementation of QuickSight Row-Level Security in detail.

Testing and Validation Before Deployment

Dashboard changes should go through a review process analogous to code review. Before publishing a dashboard change to production:

1. Data validation: Does the total on the dashboard match the total from a trusted source (finance system, previous report)? Spot-check five specific values by running the underlying query manually.

2. Cross-filter testing: Apply every filter combination on the dashboard and verify that all KPIs update coherently. A common bug is a KPI tile that ignores page-level filters.

3. Edge case testing: What happens when a filter selection returns zero rows? Does the dashboard show zero cleanly, or does it show an error or divide-by-zero?

4. Mobile rendering: If stakeholders view dashboards on mobile devices, verify that the layout renders acceptably. QuickSight’s auto-responsive layout helps, but complex multi-column layouts often require a separate mobile layout.

5. Load performance: Open the dashboard in an incognito window (to avoid cached results) and measure time-to-first-render. For SPICE dashboards, this should be under 3 seconds. For direct query dashboards, under 10 seconds is a reasonable threshold.

Maintenance Planning: Ownership and SLAs

Every KPI dashboard should have a documented owner, a data SLA, and an incident response path. Without this:

• Nobody is accountable when the dashboard breaks
• Stakeholders do not know who to contact
• The data team gets ad-hoc requests at all hours with no triage process

A minimal dashboard maintenance agreement:

• Owner: The data engineer or analytics engineer responsible for this dashboard
• Data SLA: Data is updated by 8am UTC daily. If data is not updated by 9am, an alert fires.
• Incident escalation: Alert → Slack channel #data-ops → PagerDuty on-call if not acknowledged within 30 minutes
• Stakeholder contact: The business stakeholder who owns the dashboard and is the first point of contact for metric interpretation questions

Document this in your dashboard description and in your internal runbook system. See our Athena SQL Best Practices post for the query-level optimisations that keep dashboard queries fast after they go live.

Conclusion

KPI dashboards that data engineers will actually maintain are designed around single-source metric definitions, a clear separation between business logic and presentation, robust data freshness indicators, and formal ownership with documented SLAs. The extra investment in architecture and process at the start pays back every month the dashboard is in production.

The best dashboard is not the most visually impressive one — it is the one that correctly answers business questions, stays working when the data changes, and can be updated without a major engineering effort. Infra IT Consulting helps organisations design and build BI infrastructure that meets these standards. Contact us to discuss your KPI dashboard requirements.