Near Real-Time Dashboard Patterns
When business users ask for "real-time dashboards," they usually mean they want to see data refreshed continuously on a screen without having to click "reload." In the context of a Cloud Data Warehouse like Snowflake, we architect for Near Real-Time (latency of seconds to minutes), which satisfies 99% of analytical dashboard use cases.
Topics Covered
- Near Real-Time vs Real-Time: Setting Expectations
- Dynamic Tables for Dashboard Feeds
- Materialized Views for Aggregations
- BI Tool Refresh Strategies
Near Real-Time vs Real-Time
- True Real-Time (Sub-second): Used for operational systems, like instantly blocking a fraudulent credit card swipe. Requires OLTP databases (like Redis or DynamoDB) or stream processing engines (like Flink).
- Near Real-Time (1 second to 5 minutes): Used for analytical dashboards, like a retail store manager tracking hourly sales. Best served by Snowflake Streaming + Dynamic Tables.
Hands-On Demonstration: Building a Live Dashboard Feed
We will simulate a streaming data source of retail sales, and build a near real-time aggregation layer using Dynamic Tables that a BI tool (like Tableau or PowerBI) can query efficiently.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_DASHBOARD_DB;
USE DATABASE BEEMS_DASHBOARD_DB;
CREATE SCHEMA IF NOT EXISTS RETAIL_ANALYTICS;
USE SCHEMA RETAIL_ANALYTICS;
2. The Streaming Source Table
Imagine this table is being fed continuously by the Snowpipe Streaming API from point-of-sale systems across India.
-- Raw Streaming Table
CREATE OR REPLACE TABLE RETAIL_ANALYTICS.RAW_POS_SALES (
TRANSACTION_ID STRING,
STORE_LOCATION STRING,
CASHIER_NAME STRING,
SALE_AMOUNT NUMBER(10,2),
TRANSACTION_TIMESTAMP TIMESTAMP_NTZ
);
3. Dynamic Tables for Dashboard Feeds
Dashboards should never query raw streaming tables directly. Querying billions of raw rows every 10 seconds will skyrocket your Snowflake compute costs. Instead, we use Dynamic Tables to incrementally aggregate the data.
We will configure the Dynamic Table with a TARGET_LAG of 1 minute. This means Snowflake will automatically update the aggregations every 60 seconds with only the newest data.
-- Create an aggregated Dynamic Table for the Dashboard
CREATE OR REPLACE DYNAMIC TABLE RETAIL_ANALYTICS.DASHBOARD_STORE_SALES
TARGET_LAG = '1 minute'
WAREHOUSE = COMPUTE_WH
AS
SELECT
STORE_LOCATION,
COUNT(TRANSACTION_ID) AS TOTAL_TRANSACTIONS,
SUM(SALE_AMOUNT) AS TOTAL_REVENUE,
MAX(TRANSACTION_TIMESTAMP) AS LAST_SALE_TIME
FROM RETAIL_ANALYTICS.RAW_POS_SALES
GROUP BY STORE_LOCATION;
4. Simulating the Data Flow
Let's insert some mock data, simulating live transactions processed by cashiers across India.
-- Insert Batch 1 (Simulating time T=0)
INSERT INTO RETAIL_ANALYTICS.RAW_POS_SALES VALUES
('TXN-001', 'Mumbai - Andheri', 'Aditi Sharma', 450.50, CURRENT_TIMESTAMP()),
('TXN-002', 'Bangalore - Indiranagar', 'Rahul Dravid', 1200.00, CURRENT_TIMESTAMP()),
('TXN-003', 'Chennai - T Nagar', 'Karthik Natarajan', 300.00, CURRENT_TIMESTAMP());
-- Wait 60 seconds... or manually trigger the Dynamic Table to refresh for this lab
ALTER DYNAMIC TABLE RETAIL_ANALYTICS.DASHBOARD_STORE_SALES REFRESH;
-- The BI Dashboard runs this query (Highly efficient and fast)
SELECT * FROM RETAIL_ANALYTICS.DASHBOARD_STORE_SALES;
If we insert more data, the Dynamic Table will incrementally incorporate it on its next schedule without recalculating the old data.
-- Insert Batch 2 (Simulating time T+1)
INSERT INTO RETAIL_ANALYTICS.RAW_POS_SALES VALUES
('TXN-004', 'Mumbai - Andheri', 'Aditi Sharma', 800.00, CURRENT_TIMESTAMP()),
('TXN-005', 'Kolkata - Park Street', 'Priya Banerjee', 550.25, CURRENT_TIMESTAMP());
-- Manually trigger refresh for the lab (Normally, the 1-minute target lag handles this)
ALTER DYNAMIC TABLE RETAIL_ANALYTICS.DASHBOARD_STORE_SALES REFRESH;
-- Notice Mumbai's total revenue increased!
SELECT * FROM RETAIL_ANALYTICS.DASHBOARD_STORE_SALES;
Materialized Views vs Dynamic Tables
You can also use Materialized Views for dashboard aggregations.
- Materialized Views are updated synchronously. They are slightly more complex to manage and have strict limitations (e.g., no JOINs allowed).
- Dynamic Tables are updated asynchronously based on a target lag. They allow complex SQL (JOINs, Window Functions) and are generally the preferred method for modern near real-time pipelines in Snowflake.
BI Tool Refresh Strategies
To make the dashboard feel real-time:
- Use Direct Query / Live Connection mode in your BI tool (e.g., Tableau Live Connection), bypassing the BI tool's internal extract cache.
- Set the dashboard's auto-refresh interval to match your Dynamic Table's
TARGET_LAG(e.g., auto-refresh the browser extension every 60 seconds). - Always include a "Last Updated" timestamp on the dashboard (like the
LAST_SALE_TIMEcolumn in our example) so users know exactly how fresh the data is.
(Reminder: When deploying this to production, ensure the BI Tool connects using a dedicated service account, not your personal user account. To see your current user, execute SELECT CURRENT_USER();)
Summary
- Near Real-Time (minutes) is standard for analytics; True Real-Time (sub-second) is for operational systems.
- Never query raw streaming tables from a live dashboard.
- Use Dynamic Tables to create an efficient, incrementally updated aggregation layer.
- Configure your BI tool to use a Live Connection with an auto-refresh timer matching your target lag.