databricks-dashboard

Databricks AI/BI Dashboard Patterns — Alpura

Apply to every native Databricks Lakeview Dashboard. These run directly in the Databricks workspace — no Python app needed. Always read from Gold layer tables only.

Always load @databricks-dashboard-colors alongside this skill for the dashboard's dark/light theme tokens and visualization palette, set via the Lakeview Theme panel — not @ui-ux-patterns, which covers Python/Plotly Apps. Follow the 60-30-10 rule for colors.

For conversational / natural language interfaces on top of Gold tables, use Genie Spaces (managed via manage_genie MCP tool) instead of, or alongside, Lakeview Dashboards.

---

Rules

• Datasets are SQL queries — always Gold layer (prod.gold.* or system.*)
• Every filter widget must use a named parameter — no hardcoded WHERE clauses
• Counter tiles show ONE metric with a comparison period
• Use dashboard-level filters, not per-tile filters, for date range and dimension slices
• Markdown tiles for section headers — never skip them in multi-section dashboards
• All SQL in datasets must be readable by the service principal running the refresh
• Never JOIN more than 3 tables in a single dataset — pre-join in Gold if needed
• Schedule refreshes for non-interactive dashboards — never leave manual-only for ops

---

Dataset SQL Patterns

Parameterized Date Filter

-- Dataset: daily_sales
-- Parameters: start_date (date), end_date (date), region (string, default='All')
SELECT
    order_date,
    region,
    SUM(amount)        AS total_sales,
    COUNT(order_id)    AS order_count,
    AVG(amount)        AS avg_order_value,
    SUM(amount) / NULLIF(LAG(SUM(amount)) OVER (ORDER BY order_date), 0) - 1 AS wow_growth
FROM prod.gold.sales_daily
WHERE order_date BETWEEN :start_date AND :end_date
  AND (:region = 'All' OR region = :region)
GROUP BY order_date, region
ORDER BY order_date

KPI Comparison Dataset (Current vs Prior Period)

-- Dataset: kpi_summary
-- Computes current period metrics alongside prior period for delta display
WITH current_period AS (
    SELECT
        SUM(amount)     AS revenue,
        COUNT(order_id) AS orders,
        AVG(amount)     AS avg_order,
        COUNT(DISTINCT customer_id) AS unique_customers
    FROM prod.gold.sales_daily
    WHERE order_date BETWEEN :start_date AND :end_date
),
prior_period AS (
    SELECT
        SUM(amount)     AS revenue_prior,
        COUNT(order_id) AS orders_prior,
        AVG(amount)     AS avg_order_prior,
        COUNT(DISTINCT customer_id) AS customers_prior
    FROM prod.gold.sales_daily
    WHERE order_date BETWEEN
        DATEADD(day, -DATEDIFF(day, :start_date, :end_date) - 1, :start_date)
        AND DATEADD(day, -1, :start_date)
)
SELECT
    c.*,
    p.*,
    ROUND((c.revenue - p.revenue_prior) / NULLIF(p.revenue_prior, 0) * 100, 1) AS revenue_pct,
    ROUND((c.orders  - p.orders_prior)  / NULLIF(p.orders_prior,  0) * 100, 1) AS orders_pct
FROM current_period c, prior_period p