> ## Documentation Index
> Fetch the complete documentation index at: https://oxy.tech/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Apps

> Configuration-based dashboards for visualizing SQL data

# Data Apps

A data app is a configuration-based visualization tool that allows you to create interactive dashboards from SQL queries without writing complex frontend code.

## What are Data Apps?

Data apps in Oxygen are defined using YAML configuration files (`.app.yml`) that specify both the data processing tasks and how to visualize the results.

> **ℹ️ Info**
>
> Data apps are designed to make it simple to transform SQL query results into meaningful visualizations with minimal effort. The `.app.yml` files abstract away the complexity of building dashboards, allowing you to focus on the data and insights rather than visualization code.

## Data App Components

In a data app YAML file, you need to specify the following components:

| Component   | Description                                     | Required |
| ----------- | ----------------------------------------------- | -------- |
| name        | Unique identifier for the data app              | Required |
| description | Brief explanation of the app's purpose          | Optional |
| tasks       | SQL queries that prepare data for visualization | Required |
| display     | Visualization components to render the data     | Required |

### Tasks

The `tasks` section defines SQL queries that will extract and transform data from your databases. Each task has the following structure:

| Field      | Description                                               | Required                            |
| ---------- | --------------------------------------------------------- | ----------------------------------- |
| name       | Identifier for the task (referenced in display)           | Required                            |
| type       | The task type (currently only `execute_sql` is supported) | Required                            |
| database   | Database connection to use (defined in config.yml)        | Required                            |
| sql\_query | Inline SQL query to execute                               | Required if sql\_file not provided  |
| sql\_file  | Path to SQL file to execute                               | Required if sql\_query not provided |
| variables  | Key-value pairs for variable substitution in queries      | Optional                            |
| cache      | Cache configuration for query results                     | Optional                            |
| export     | Configuration for exporting query results                 | Optional                            |

> **⚠️ Warning**
>
> Make sure your tasks are named uniquely as these names are referenced in the display section.

### Display

The `display` section defines how to visualize the data produced by your tasks. Several visualization types are supported:

#### Markdown

Markdown blocks can be used to add formatted text, headers, and documentation:

```yaml theme={null}
- type: markdown
  content: |
    # Dashboard Title
    Additional formatted text here
```

#### Controls

Controls add interactive filter widgets to your app. When the user changes a control value, every task that references that control re-runs automatically and all charts and tables update.

Controls are defined inline in the `display` list using `type: control`:

```yaml theme={null}
display:
  - type: control
    name: region
    control_type: select
    label: Region
    options:
      - All
      - North
      - South
    default: "All"

  - type: control
    name: start_date
    control_type: date
    label: From Date
    default: "2024-01-01"

  - type: control
    name: holidays_only
    control_type: toggle
    label: Holidays Only
    default: false
```

| Field         | Description                                                                       | Required                                |
| ------------- | --------------------------------------------------------------------------------- | --------------------------------------- |
| name          | Identifier used to reference the control value in SQL via `{{ controls.<name> }}` | Required                                |
| control\_type | Widget type: `select`, `date`, or `toggle`                                        | Required                                |
| label         | Human-readable label shown above the widget                                       | Optional                                |
| default       | Initial value when the app loads                                                  | Optional                                |
| options       | Option list for `select` controls — static strings or Jinja expressions           | Required for `select` (or use `source`) |
| source        | Task name whose first column provides options dynamically for `select` controls   | Optional (alternative to `options`)     |

##### Control types

**`select`** — a dropdown menu. Populate options from a static list, from Jinja expressions, or from a task query:

```yaml theme={null}
# Static options
- type: control
  name: region
  control_type: select
  label: Region
  options:
    - All
    - North
    - South
  default: "All"

# Jinja-templated options — evaluated at startup so the list stays current
- type: control
  name: year
  control_type: select
  label: Year
  options:
    - "All"
    - "{{ now(fmt='%Y') }}" # current year, e.g. "2026"
    - "{{ now(fmt='%Y') | int - 1 }}" # previous year, e.g. "2025"
    - "{{ now(fmt='%Y') | int - 2 }}" # two years ago, e.g. "2024"
  default: "All"

# Dynamic options from a task
- type: control
  name: store
  control_type: select
  label: Store
  source: store_list # task name; its first column becomes the option list
  default: "All"
```

**`date`** — a date picker that injects a `YYYY-MM-DD` string. The default can be a static date or a Jinja expression:

```yaml theme={null}
- type: control
  name: start_date
  control_type: date
  label: From Date
  default: "{{ now(fmt='%Y-%m-%d') }}" # today's date at startup
```

**`toggle`** — a boolean on/off switch:

```yaml theme={null}
- type: control
  name: holidays_only
  control_type: toggle
  label: Holidays Only
  default: false
```

##### Using control values in SQL

Reference control values in your task SQL using Jinja syntax:

```yaml theme={null}
tasks:
  - name: sales
    type: execute_sql
    sql_query: |
      SELECT *
      FROM sales
      WHERE ({{ controls.region | sqlquote }} = 'All' OR region = {{ controls.region | sqlquote }})
        AND sale_date >= {{ controls.start_date | sqlquote }}
        {% if controls.holidays_only %}AND period = 'Holiday'{% endif %}
    database: local
```

> **ℹ️ Info**
>
> Controls must be placed **before** the charts and tables they affect in the `display` list. The controls bar is rendered at the top of the page and stays visible as you scroll.

#### Bar Chart

```yaml theme={null}
- type: bar_chart
  title: Revenue by Category
  data: revenue_by_category # task name
  x: category # column for x-axis
  y: total_revenue # column for y-axis
```

#### Line Chart

```yaml theme={null}
- type: line_chart
  title: Sales Trend Over Time
  data: sales_trend
  x: month
  y: avg_sales
  x_axis_label: Month
  y_axis_label: Average Weekly Sales ($)
  series: indicator # optional: column name for multi-series lines
```

#### Pie Chart

```yaml theme={null}
- type: pie_chart
  title: Revenue by Region
  data: revenue_by_region
  name: region # column for slice labels
  value: total_revenue # column for slice values
```

#### Table

```yaml theme={null}
- type: table
  title: Filtered Sales Detail
  data: raw_sales # task name
```

#### Row (multi-column layout)

Use `row` to arrange multiple charts side by side:

```yaml theme={null}
- type: row
  columns: 2
  children:
    - type: bar_chart
      title: Revenue by Category
      data: revenue_by_category
      x: category
      y: total_revenue

    - type: pie_chart
      title: Revenue by Region
      data: revenue_by_region
      name: region
      value: total_revenue
```

## Complete example

```yaml theme={null}
name: sales_dashboard
description: Interactive sales dashboard with region and date filters.

tasks:
  - name: store_options
    type: execute_sql
    sql_query: |
      SELECT 'All' AS store
      UNION ALL
      SELECT DISTINCT store FROM 'sales.csv'
      ORDER BY store
    database: local

  - name: sales_by_category
    type: execute_sql
    sql_query: |
      SELECT category, SUM(revenue) AS total_revenue
      FROM 'sales.csv'
      WHERE ({{ controls.store | sqlquote }} = 'All' OR store = {{ controls.store | sqlquote }})
        AND sale_date >= {{ controls.start_date | sqlquote }}
      GROUP BY category
    database: local

display:
  - type: control
    name: store
    control_type: select
    label: Store
    source: store_options
    default: "All"

  - type: control
    name: start_date
    control_type: date
    label: From Date
    default: "2024-01-01"

  - type: markdown
    content: |
      # Sales Dashboard
      Use the controls above to filter by store and date.

  - type: bar_chart
    title: Revenue by Category
    data: sales_by_category
    x: category
    y: total_revenue
```