Create Dashboard

my-project/
  .bruin.yml                    # Database connections (required for queries)
  dashboards/
    my-dashboard.yml            # Any *.yml file = a dashboard
    another.yml
    explorer.dashboard.tsx      # Any *.dashboard.tsx file = a TSX dashboard
    dynamic-report.dashboard.tsx
    lib/
      kpi.tsx                   # Shared TSX helpers (not auto-discovered)
    queries/
      my_query.sql              # SQL files for TSX include() and dac query -f
  themes/
    corporate.yml               # Optional custom themes (token overrides)

default_environment: default
environments:
  default:
    connections:
      duckdb:
        - name: my_duckdb
          path: /absolute/path/to/data.db
          read_only: true       # recommended for DuckDB to avoid lock issues

      postgres:
        - name: my_postgres
          host: localhost
          port: 5432
          database: analytics
          username: user
          password: pass

      # Other connection types supported by bruin CLI

# Required
name: My Dashboard                    # Display name, also used as URL slug
rows: []                              # At least one row required

# Optional
description: A description            # Shown on the dashboard list page
connection: my_duckdb                 # Default connection for all queries

# Optional: interactive filters
filters: []

# Optional: named queries (reusable across widgets)
queries: {}

# Optional: declarative data source (enables metrics & dimensions)
source:
  table: my_schema.my_table
  date_column: created_at             # For automatic date range filtering
  date_format: "%Y%m%d"              # strftime format if date is stored as string
  connection: my_postgres             # Overrides dashboard-level connection

# Optional: reusable metric definitions
metrics: {}

# Optional: reusable dimension definitions
dimensions: {}

filters:
  # Select dropdown
  - name: region
    type: select
    default: "All"                     # Initial value
    multiple: false                    # true for multi-select
    options:
      values: ["All", "North America", "Europe", "APAC"]   # Static options
      # OR dynamic options from a query:
      # query: SELECT DISTINCT region FROM dim_regions ORDER BY region
      # connection: my_postgres        # Optional connection override

  # Date range picker (with preset default)
  - name: date_range
    type: date-range
    default: last_30_days              # Preset name OR explicit {start, end}
    # default:                         # Explicit dates also work:
    #   start: "2025-01-01"
    #   end: "2025-12-31"
    options:
      presets:                         # Optional: control which presets appear
        - last_7_days
        - last_30_days
        - last_90_days
        - this_month
        - this_year

  # Free text input
  - name: search
    type: text
    default: ""

  # Plain date input
  - name: as_of_date
    type: date
    default: "2025-01-01"

  # Plain numeric input
  - name: min_revenue
    type: number
    default: 1000

queries:
  total_revenue:
    sql: |
      SELECT SUM(amount) as value FROM sales
      WHERE created_at >= '{{ filters.date_range.start }}'

  revenue_by_month:
    sql: |
      SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
      FROM sales GROUP BY 1 ORDER BY 1
    connection: my_postgres                  # Optional connection override

source:
  table: my_schema.events                # REQUIRED: table name (supports Jinja)
  date_column: event_date                # Optional: enables automatic date range filtering
  date_format: "%Y%m%d"                  # Optional: strftime format if date is stored as string
  connection: my_postgres                # Optional: overrides dashboard-level connection

source:
  table: "`project.{% if filters.env == 'prod' %}prod_dataset{% else %}dev_dataset{% endif %}.events`"

metrics:
  page_views:
    aggregate: count                     # count, count_distinct, sum, avg, min, max
    # column not needed for count

  users:
    aggregate: count_distinct
    column: user_id                      # REQUIRED for all aggregates except count

  revenue:
    aggregate: sum
    column: amount

  high_value_orders:
    aggregate: count
    filter:                              # Optional: conditional aggregation
      status: completed
      amount_gt: 100                     # Generates: status = 'completed' AND amount_gt = '100'

metrics:
  pages_per_session:
    expression: page_views / sessions    # Arithmetic using other metric names

  conversion_rate:
    expression: conversions / visits * 100

dimensions:
  daily:
    column: event_date
    type: date                           # "date" = chronological ORDER BY ASC

  country:
    column: geo.country                  # Dotted paths work (aliased as "country")

  event:
    column: event_name                   # No type = ORDER BY metric DESC (top-N)

rows:
  - widgets:
      - name: Widget A
        col: 8                # Takes 8/12 columns
        # ...
      - name: Widget B
        col: 4                # Takes 4/12 columns
        # ...

  - widgets:
      - name: Full Width
        col: 12               # Full width
        # ...

rows:
  - height: 480               # Tall row — charts fill the extra vertical space
    widgets:
      - name: Revenue Trend
        type: chart
        chart: area
        col: 8
        # ...
      - name: By Region
        type: chart
        chart: pie
        col: 4
        # ...

  - widgets:                  # No height → default
      - name: Recent Orders
        type: table
        col: 12
        # ...

- name: Page Views
  type: metric
  metric: page_views              # References a metric from the metrics: map
  value:
    field: value                  # Result column (auto-aliased to "value" for metric refs)
    type: number
    format: ",.0f"                # Optional: d3-format string
  col: 3

- name: Total Revenue
  type: metric
  query: total_revenue          # or sql:
  value:
    field: value                # REQUIRED: which result column to display
    type: number                # number | date | category
    format: "$,.2f"             # Optional: d3-format string (currency, percent, etc.)
  col: 3

- name: Total Orders
  type: metric
  sql: SELECT COUNT(*) as total FROM orders
  value: { field: total }
  col: 3

- name: Daily Traffic
  type: chart
  chart: area                       # line | bar | area (or any x/y chart type)
  dimension: daily                  # References a dimension from dimensions: map
  metrics: [page_views, users]      # References metrics from metrics: map
  col: 8

- name: Top Countries
  type: chart
  chart: bar
  dimension: country                # Non-date dimension = sorted by first metric DESC
  metrics: [users]
  limit: 8                          # Optional: limit number of results
  col: 4

- name: Pages/Session Trend
  type: chart
  chart: line
  dimension: daily
  metrics: [pages_per_session]      # Expression metrics work too — inlined as SQL
  col: 4

- name: Revenue Trend
  type: chart
  chart: line                   # line | bar | area
  sql: |
    SELECT month, revenue, revenue_last_year FROM monthly_data ORDER BY month
  x: { field: month, type: date, format: "%b %Y" }    # REQUIRED: x encoding
  y:                                                   # REQUIRED: y encoding (field may be a list)
    field: [revenue, revenue_last_year]
    format: "$,.0f"
    beginAtZero: true                                  # honest scale
    curve: straight                                    # chart-wide: no implied in-period movement
  series:                                              # widget-level per-series overrides, keyed by y-column
    revenue_last_year: { dash: dashed }                # dashed comparison line
  col: 8

- name: Sales by Region
  type: chart
  chart: bar
  stacked: true                 # bar only; REQUIRES color
  color: { field: region }      # one stacked series per region
  sql: |
    SELECT month, region, SUM(amount) AS revenue
    FROM sales GROUP BY 1, 2 ORDER BY 1, 2
  x: { field: month }
  y: { field: revenue }         # single column — color does the splitting
  col: 6

- name: Revenue by Region
  type: chart
  chart: pie
  sql: |
    SELECT region, SUM(amount) as total FROM sales GROUP BY 1
  label: region                 # REQUIRED: category column
  value: { field: total }                  # REQUIRED: numeric column
  col: 4

- name: Price vs Quantity
  type: chart
  chart: scatter
  sql: SELECT price, quantity FROM orders
  x: { field: price }
  y: { field: [quantity] }
  col: 6

- name: Sales Bubble
  type: chart
  chart: bubble
  sql: SELECT region, revenue, profit, order_count FROM summary
  x: { field: region }                     # X axis
  y: { field: [revenue] }                  # Y axis
  size: order_count             # REQUIRED: bubble size column
  col: 6

- name: Revenue vs Growth
  type: chart
  chart: combo
  sql: SELECT month, revenue, growth_pct FROM monthly
  x: { field: month }
  y: { field: [revenue, growth_pct] }
  lines: [growth_pct]           # Which y series render as lines (rest are bars)
  col: 8

- name: Order Distribution
  type: chart
  chart: histogram
  sql: SELECT amount FROM orders
  x: { field: amount }                     # REQUIRED: column to bin
  bins: 20                      # Optional: number of bins (default: 10)
  col: 6

- name: Amount by Status
  type: chart
  chart: boxplot
  sql: SELECT status, amount FROM orders
  x: { field: status }                     # Category column
  y: { field: [amount] }                   # Numeric column
  col: 6

- name: Conversion Funnel
  type: chart
  chart: funnel
  sql: SELECT stage, count FROM funnel_data ORDER BY count DESC
  label: stage                  # REQUIRED: category column
  value: { field: count }                  # REQUIRED: numeric column
  col: 6

- name: Flow Diagram
  type: chart
  chart: sankey
  sql: SELECT source_stage, target_stage, flow_count FROM flows
  source: source_stage          # REQUIRED: source node column
  target: target_stage          # REQUIRED: target node column
  value: { field: flow_count }             # REQUIRED: flow weight column
  col: 8

- name: Activity Heatmap
  type: chart
  chart: heatmap
  sql: SELECT day_of_week, hour, event_count FROM activity
  x: { field: hour }                       # REQUIRED: X axis column
  y: { field: [day_of_week] }              # REQUIRED: Y axis column (array with 1 element)
  value: { field: event_count }            # REQUIRED: intensity column
  showValues: true                         # optional: print each cell's value in the cell
  colorScale:                              # optional: replaces the default blue buckets
    backgroundColor: [red, white, green]   #   2+ colors, low -> high
    range: [-500, 0, 500]                  #   one anchor per color; omit for auto min/max
    unit: absolute                         #   absolute (default) | percent | percentile
  col: 8

- name: Daily Revenue
  type: chart
  chart: calendar
  sql: SELECT date, revenue FROM daily_sales
  x: { field: date }                       # REQUIRED: date column (YYYY-MM-DD)
  value: { field: revenue }                # REQUIRED: intensity column
  col: 12

- name: Revenue Sparkline
  type: chart
  chart: sparkline
  sql: SELECT month, revenue FROM monthly ORDER BY month
  x: { field: month }
  y: { field: [revenue] }
  col: 3

- name: P&L Waterfall
  type: chart
  chart: waterfall
  sql: |
    SELECT category, amount FROM pnl
    ORDER BY CASE category
      WHEN 'Revenue' THEN 1
      WHEN 'COGS' THEN 2
      WHEN 'OpEx' THEN 3
      WHEN 'Net' THEN 4
    END
  x: { field: category }
  y: { field: [amount] }
  col: 8

- name: Process Control
  type: chart
  chart: xmr
  sql: SELECT date, value, mean, ucl, lcl FROM process_data
  x: { field: date }
  y: { field: [value, mean] }              # First = data line, second = center line (dashed)
  yMin: lcl                     # Lower control limit (dashed)
  yMax: ucl                     # Upper control limit (dashed)
  col: 8

- name: H1 vs H2 Revenue
  type: chart
  chart: dumbbell
  sql: SELECT region, h1_revenue, h2_revenue FROM comparison
  x: { field: region }                     # Category column (vertical axis)
  y: { field: [h1_revenue, h2_revenue] }   # Two numeric columns (start and end points)
  col: 6

- name: Revenue vs Target
  type: chart
  chart: gauge
  sql: SELECT current_revenue, revenue_target FROM kpi
  value: { field: current_revenue }        # REQUIRED: current value column (first row)
  target: revenue_target        # Optional: target/max column (default 100)
  col: 3

- name: Revenue by Category
  type: chart
  chart: treemap
  sql: SELECT category, revenue FROM sales
  label: category               # REQUIRED: label column
  value: { field: revenue }                # REQUIRED: size column
  col: 6

- name: Product Scorecard
  type: chart
  chart: radar
  sql: SELECT attribute, product_a, product_b FROM scorecard
  x: { field: attribute }                  # REQUIRED: axis category column
  y: { field: [product_a, product_b] }     # REQUIRED: one or more series to compare
  col: 6

- name: Daily Price
  type: chart
  chart: candlestick
  sql: SELECT date, open, high, low, close FROM ohlc ORDER BY date
  x: { field: date }                       # REQUIRED: time column
  open: open                    # REQUIRED
  high: high                    # REQUIRED
  low: low                      # REQUIRED
  close: close                  # REQUIRED
  col: 12

- name: Revenue with confidence interval
  type: chart
  chart: vega-lite
  sql: SELECT month, revenue, lower_ci, upper_ci FROM monthly_revenue ORDER BY month
  spec:
    data: { name: dac }
    encoding:
      x: { field: month, type: temporal }
    layer:
      - mark: { type: area, opacity: 0.14 }
        encoding:
          y: { field: lower_ci, type: quantitative }
          y2: { field: upper_ci }
      - mark: { type: line, strokeWidth: 2 }
        encoding:
          y: { field: revenue, type: quantitative }

- name: Recent Orders
  type: table
  sql: |
    SELECT id, customer_name, amount, status, created_at
    FROM orders ORDER BY created_at DESC LIMIT 25
  columns:                      # Optional: customize column display
    - name: customer_name       # Must match SQL column name
      label: Customer           # Display header
    - name: amount
      label: Amount
      format: currency          # "currency" adds $ prefix, "number" for locale formatting
      align: right              # left | center | right (aligns header + body cells)
    - name: created_at
      label: Date               # ISO dates auto-format to readable strings
  col: 12

data:
  columns: [account, trend]
  rows:
    - [Atlas, [["2026-09-24", 12400], ["2026-09-25", 13100]]]
columns:
  - { name: account }
  - name: trend
    type: sparkline
    x: { field: date, type: date, format: "%b %d" }
    y: { field: revenue, type: number, format: "$,.0f" }

name: Regions

rows:
  - widgets:
      - name: Regions
        type: table
        col: 12
        sql: SELECT revenue, growth, score, status, actual, target, bonus, health FROM regions
        columns:
          - name: revenue
            number: currency
            format:
              - { backgroundColor: [red, white, green] }                # gradient, auto min→max
          - name: growth
            number: number
            format:
              - { backgroundColor: [blue, white, amber], range: [-25, 0, 25], unit: absolute }   # fixed anchors; unit also percent/percentile
          - name: score
            number: number
            format:                                                     # conditions, first match wins
              - { if: greater_than_or_equal, value: 80, backgroundColor: green }
              - { if: is_between, value: [50, 79], backgroundColor: amber }
              - { if: less_than, value: 50, textColor: red, strikethrough: true }
          - name: status
            format:
              - { if: text_contains, value: urgent, backgroundColor: amber, bold: true }
              - { if: is_empty, backgroundColor: "#F3F4F6", italic: true }   # flat fill (string)
          - name: actual
            number: number
            format:                                                     # cross-column, same row
              - { if: greater_than, value: { column: target }, backgroundColor: green }
          - name: target
            hidden: true                                                # in the result for the rule above, not rendered
          - name: bonus
            number: currency
            like: score                                                 # mirror score's colors, keep own number
          - name: health
            number: number
            format:                                                     # a condition wins over the gradient base below
              - { if: is_equal_to, value: 0, backgroundColor: red, bold: true }
              - { backgroundColor: [red, white, green] }                # base, last (always matches)

- name: Retention cohort
  type: pivot_table
  sql: SELECT cohort, month_since, active_users FROM retention
  pivot:
    rows: [{ field: cohort }]
    columns: [{ field: month_since }]     # months since signup spread across
    values:
      # colour the active_users cells low→high so retention decay is visible
      - field: active_users
        summarize: sum
        format:
          - backgroundColor: ["#FECACA", "#FEF08A", "#BBF7D0"]   # gradient over the cells