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

# Data Source Types & Databases

> Data Sources are the datasets Building BI draws from; every metric, calculation, and column originates from one. The Data Sources interface is opened from ...

Use [Data sources](/products/building-bi/data-sources) to add and manage data sources. Data Sources are the datasets Building BI draws from; every metric, calculation, and column originates from one. The Data Sources interface is opened from the bottom of the left navigation panel.

<Warning>
  Global scope
  Modifications in the Data Sources interface are **global** — they affect all dashboards across the entire portfolio, not a single building. Never delete a data source unless certain; deletion instantly breaks any dashboard relying on it.
</Warning>

<Note>
  Name clash
  See [Data Sources](/products/building-bi/data-sources) for the name distinction with Cloud BMS API integrations.
</Note>

## Source types

| Type     | Description                                      | Editable?                                                                 |
| -------- | ------------------------------------------------ | ------------------------------------------------------------------------- |
| Physical | Direct data pulls from the backend.              | Not directly editable as a table.                                         |
| Virtual  | SQL-based views built on top of physical tables. | Editable via the ellipsis (⋮) on the far-right column of the table's row. |

<Note>
  Virtual tables recompute on every query. Use them for light, real-time reshapes. Prefer physical tables when historical trending or heavy aggregation must stay fast.
</Note>

## Database types

The database type determines the SQL syntax required for custom logic. Three databases are used, each suited to a data type:

| Database   | Best for                                                            | Virtual Tables?                                |
| ---------- | ------------------------------------------------------------------- | ---------------------------------------------- |
| ClickHouse | Real-time sensor data                                               | Yes — additional virtual tables can be created |
| PostgreSQL | High-dimension (most columns) non-sensor data                       | Yes — additional virtual tables can be created |
| BigQuery   | Near-real-time backup; internal module tables (FTT, FDD, OSS, etc.) | **No** — virtual tables cannot be created      |

Calculated columns and metrics do not support window functions or aggregations of aggregations. Virtual table SQL can. See [Calculated fields](/products/building-bi/reference/calculated-fields).

## Data Sources table columns

The Data Sources list page shows the following per row:

| Column        | Description                                                                                    |
| ------------- | ---------------------------------------------------------------------------------------------- |
| Type          | Physical or Virtual.                                                                           |
| Database Type | ClickHouse, PostgreSQL, or BigQuery — indicates required SQL syntax.                           |
| Management    | ⭐ favorite frequently used tables; ⋮ menu to edit alias/description or (for virtual) edit SQL. |

## Adding a new data source

Creating a new data source is uncommon; first try to solve problems with existing sources. Required fields differ by type:

| Field          | Physical                                                         | Virtual                                               |
| -------------- | ---------------------------------------------------------------- | ----------------------------------------------------- |
| Database Type  | Required                                                         | Required                                              |
| Database Name  | Required (ask Support/Data Analyst, or copy from another client) | Required                                              |
| Schema         | Required (ask Support/Data Analyst, or copy from another client) | Required                                              |
| Table Name     | Required                                                         | Required                                              |
| Display Name   | Required                                                         | Required                                              |
| Description    | Required                                                         | Required                                              |
| SQL Expression | —                                                                | Required (syntax depends on PostgreSQL vs ClickHouse) |

## Navigating a single data source

Clicking a data source name opens its **Table Schema**, exposing four building-block types: **Columns** (native), **Calculated** (single-row logic), **Metrics** (multi-row aggregations), and **URLs**. See [Calculated Fields (Columns, Metrics & URLs)](/products/building-bi/reference/calculated-fields).

| Action                   | Description                                                                                                                                                   |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Sync Datasource          | Pulls in the latest columns if the core KODE table changed.                                                                                                   |
| + Add Column             | Creates calculated columns, metrics, or URLs.                                                                                                                 |
| Check Usage              | (⋮ menu) Identifies which dashboards use a column / whether a column is Unused.                                                                               |
| Optimization check       | (⋮ menu) Checks whether a virtual table's SQL is optimized.                                                                                                   |
| Edit Data Source Details | (⋮ menu) Edit basic parameters and the virtual table SQL.                                                                                                     |
| Samples tab              | Shows the \~20 most recent rows to verify structure and returned data. An `i` icon in a column header marks a calculated column; select it to open the query. |

## Optimization (virtual sources)

Virtual sources that are not optimized can load slowly, error on large datasets, and cause similar performance issues. KODE OS offers AI-generated optimization suggestions (filters and the table to apply them to) shown beneath the current SQL Expression when editing details. Sources that are not optimized also surface a "Not Optimized" warning and appear flagged in the Setup-tab Data Source dropdown.

## Key sensor data sources

| Data Source                  | Type     | Description                                                                                                                                                    |
| ---------------------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Point Time Series Real-Time  | Physical | Core sensor table; every reading is a new row; source of truth for building readings. ClickHouse-based.                                                        |
| Point Time Series Last Value | Virtual  | Returns only the most recent recorded value per sensor. Values may not share a timestamp. Useful for comparing current states across misaligned polling rates. |

See [Table Structures & Polling Types](/products/building-bi/reference/table-structures) and [Point Time Series Column Reference](/products/building-bi/data-sources#column-reference).

## See also

* The row-level structure of these tables: [Table Structures & Polling Types](/products/building-bi/reference/table-structures)
* Every column in the sensor table: [Point Time Series Column Reference](/products/building-bi/data-sources#column-reference)
* Building custom logic on a source: [Calculated Fields (Columns, Metrics & URLs)](/products/building-bi/reference/calculated-fields)
* Task guide: [Data sources](/products/building-bi/data-sources)
* Physical vs virtual sources: [Physical vs virtual data sources](/products/building-bi/concepts/physical-vs-virtual-data-sources)

## Next steps

<CardGroup cols={2}>
  <Card title="Building BI overview" icon="library" href="/products/building-bi/overview" arrow={true}>
    Browse concept guides and reference pages for Building BI.
  </Card>

  <Card title="Dashboards" icon="layout-dashboard" href="/products/building-bi/dashboards" arrow={true}>
    Create and configure interactive dashboards with charts and widgets.
  </Card>
</CardGroup>

## Related reading

<CardGroup cols={2}>
  <Card title="Physical vs virtual data sources" icon="database" href="/products/building-bi/concepts/physical-vs-virtual-data-sources" arrow={true}>
    Compare direct tables with derived virtual sources.
  </Card>

  <Card title="Key data sources" icon="activity" href="/products/building-bi/data-sources#key-data-sources" arrow={true}>
    Choose Real-Time or Last Value Point Time Series sources.
  </Card>

  <Card title="Database types" icon="server" href="/products/building-bi/data-sources#database-types" arrow={true}>
    Understand ClickHouse, PostgreSQL, and BigQuery trade-offs.
  </Card>

  <Card title="Column reference" icon="table" href="/products/building-bi/data-sources#column-reference" arrow={true}>
    Look up column names for calculations and filters.
  </Card>
</CardGroup>