Building a Real-Time KPI Dashboard Without a Full Data Warehouse

The conventional path to a real-time KPI dashboard runs through a modern data stack: Fivetran for ingestion, Snowflake for warehousing, dbt for transformation, Looker for visualization. Total cost: $30,000–$120,000 per year in tooling, plus significant engineering time. For most middle-market operators, this is not a justified investment for what amounts to "show me today's revenue, margin, and unit economics."

The Lightweight Alternative

A watermark-based incremental query pattern against an existing transactional database (PostgreSQL, MySQL) produces the same result for a fraction of the cost:

import psycopg2
from datetime import datetime

def get_incremental_kpis(last_watermark: datetime):
    conn = psycopg2.connect(DATABASE_URL)
    cur  = conn.cursor()
    cur.execute("""
        SELECT
            DATE_TRUNC('day', created_at)   AS day,
            SUM(revenue)                    AS total_revenue,
            SUM(gross_profit)               AS gross_profit,
            COUNT(DISTINCT customer_id)     AS unique_customers,
            AVG(order_value)                AS aov
        FROM orders
        WHERE created_at > %s
        GROUP BY 1
        ORDER BY 1
    """, (last_watermark,))
    return cur.fetchall()

This query runs on a schedule (every 15 minutes is sufficient for most operational dashboards), appends only new rows to a lightweight state store, and feeds a frontend dashboard that renders in the browser. No warehouse, no ETL tool, no transformation layer needed for straightforward operational metrics.

The stateful compute layer maintains running aggregates in memory, updating incrementally rather than recomputing from scratch on each poll. This keeps query load minimal even on tables with millions of rows.

Read the full article with complete architecture and frontend integration →