Databases - nao Labs

Connect your databases to give your agent access to table schemas, descriptions, and sample rows.

Supported Databases

The current nao CLI supports these database types in nao_config.yaml:

Athena
BigQuery
ClickHouse
Databricks
DuckDB
Fabric
MSSQL
MySQL
Postgres
Redshift
Snowflake
StarRocks
Trino

Common Parameters

Every database entry supports these shared fields:

databases:
  - name: warehouse_prod
    type: snowflake
    include:
      - analytics.*
    exclude:
      - analytics.tmp_*
    exclude_columns:
      - "*._peerdb_*"
      - "*.updated_at"
    templates:
      - columns
      - query_history
      - preview

name: Friendly connection name
type: One of athena, bigquery, clickhouse, databricks, duckdb, fabric, mssql, mysql, postgres, redshift, snowflake, starrocks, trino
include: Optional glob patterns for schema.table values to include
exclude: Optional glob patterns for schema.table values to exclude
exclude_columns: Optional glob patterns for schema.table.column values to hide (see below)
templates: Optional list of rendered context files

Excluding columns

include and exclude filter at the table level. To drop individual columns from everything the agent sees (column lists, previews, and profiling), use exclude_columns. Patterns are glob-matched against the fully-qualified schema.table.column name:

databases:
  - name: warehouse_prod
    type: snowflake
    exclude_columns:
      - "*._peerdb_*"        # replication metadata on every table
      - "analytics.orders.internal_notes"  # one specific column
      - "*.pii_*"            # any column prefixed pii_

Use it to keep sensitive or noisy columns (PII, internal bookkeeping, replication metadata) out of the agent’s context entirely. An empty or omitted list hides nothing.

The templates field used to be called accessors. The old key still works (nao will read it and migrate automatically), but new configs should use templates.

Templates

These are the built-in templates nao can render per table:

columns (default; schema plus the table description, row count, and partitioning metadata)
preview (default; sample rows)
query_history (default; AI-friendly per-table usage context built from your warehouse’s query history)
profiling (optional; column-level statistics)
ai_summary (optional; AI-generated table summary)

If you omit templates, nao renders columns, query_history, and preview by default. The table description, row count, partitioning, and clustering metadata all live in columns.md. profiling.md repeats the clustering columns on a short Clustering: line alongside its statistics.

nao init scaffolds a smaller set than the config-file default: columns and preview, plus ai_summary when you configure an LLM. It writes profiling and query_history as commented-out options you can uncomment later.

Migrating from how_to_use / description. The how_to_use template was renamed to query_history, and the standalone description template was removed (its content moved into columns.md). Both old names still load: nao migrates them automatically and prints a FutureWarning asking you to rename how_to_use to query_history (and drop description) in nao_config.yaml. Existing how_to_use.md and description.md files already written to disk are not removed automatically yet (tracked in getnao/nao#1193), so delete them by hand for now.

`query_history`

query_history produces a single markdown file per table built from your warehouse’s query history:

How often the table is queried
The tables it is most often joined with
The most frequently run queries against it

Table metadata (description, row count, and partitioning) is not in this file: it now lives in columns.md. To enable query-history-based context, set query_history_days on the database (defaults to 0, meaning no history is fetched):

databases:
  - name: warehouse_prod
    type: snowflake
    query_history_days: 30
    templates:
      - columns
      - query_history
      - preview

Query history is supported on BigQuery, Snowflake, Databricks, Postgres, and Redshift. On warehouses without history support, query_history reports that no history was found; the table metadata is always available in columns.md.

Customizing query history

Two optional fields let you control which queries feed into the query_history analysis: query_history_sql overrides the built-in history query for a database. The SQL must return a query_text column. Use the {days} placeholder to inject the configured query_history_days value:

databases:
  - name: warehouse_prod
    type: snowflake
    query_history_days: 30
    query_history_sql: |
      SELECT regexp_replace(query_text, '-- Looker.*$', '', 1, 0, 'm') AS query_text
      FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
      WHERE start_time >= DATEADD(day, -{days}, CURRENT_TIMESTAMP())
        AND execution_status = 'SUCCESS'
        AND query_type = 'SELECT'
      LIMIT 10000

This is useful when you need to strip BI tool comments (e.g. Looker slugs) before grouping, or to query a custom history table. query_history_exclude_patterns filters out noise after fetching. Each entry is a case-insensitive regex. Any query whose text matches at least one pattern is dropped before analysis:

databases:
  - name: warehouse_prod
    type: snowflake
    query_history_days: 30
    query_history_exclude_patterns:
      - 'SYSTEM\$'
      - '^SELECT CURRENT_SESSION\(\)'
      - '^SHOW '

When patterns are active, the console output reports how many queries were excluded so you can iterate on the list.

`profiling`

Use the optional profiling config block to control profiling refresh behavior:

databases:
  - name: warehouse_prod
    type: snowflake
    templates:
      - columns
      - preview
      - profiling
    profiling:
      refresh_policy: interval   # always (default), interval, or once
      interval_days: 7           # used only when refresh_policy: interval

refresh_policy: When to recompute profiling. One of always (default, every sync), interval (every interval_days days), or once (only when the file is missing).
interval_days: Refresh interval in days when refresh_policy: interval (default 7, minimum 1).

Profiling works across all supported warehouses for primitive columns and for complex column types (array, struct, map, json, row, tuple, variant, object, super). For array columns, nao unpacks the values before computing distinct counts and top values; other complex types are stringified before profiling.

`ai_summary`

ai_summary is opt-in. To use it, add ai_summary to templates and configure llm.annotation_model in nao_config.yaml. When enabled, nao renders databases/ai_summary.md.j2 and calls prompt("...") inside that template to generate LLM-based summaries during nao sync. The summary bases its data-quality and distribution notes on the full-table profiling statistics (empty counts, unique counts, min/max, most common values), which nao computes once per table and shares with the summary. The preview rows are treated as a small, non-representative sample, so the summary never infers data quality from them; when no profiling statistics are available it stays silent about data quality. ai_summary takes the same refresh config as profiling:

databases:
  - name: warehouse_prod
    type: snowflake
    templates:
      - columns
      - preview
      - ai_summary
    ai_summary:
      refresh_policy: interval   # always (default), interval, or once
      interval_days: 7           # used only when refresh_policy: interval

refresh_policy: When to regenerate the summary. Defaults to always, so existing setups keep regenerating it on every sync.
interval_days: Refresh interval in days when refresh_policy: interval (default 7, minimum 1).

Database Parameters

Athena

databases:
  - name: athena_prod
    type: athena
    s3_staging_dir: s3://my-query-results/athena/
    region_name: us-east-1
    schema_name: analytics
    work_group: primary
    profile_name: default

Optional auth fields:

profile_name
aws_access_key_id
aws_secret_access_key
aws_session_token

BigQuery

databases:
  - name: bigquery_prod
    type: bigquery
    project_id: my-gcp-project
    dataset_id: analytics
    credentials_path: /path/to/service-account.json
    sso: false
    location: US
    max_query_size: 5
    partition_filters:
      events: "event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)"

You can authenticate with either:

credentials_path
credentials_json
sso: true for ADC / browser auth

Optional for partitioned tables:

partition_filters: map of table_name: SQL filter used for preview queries on tables that enforce require_partition_filter = TRUE

Limit query size

Use max_query_size (in GB) to cap how much data a single query can scan. When set, nao runs a BigQuery dry run before every SQL execution and rejects the query if the estimated bytes processed exceed the limit.

databases:
  - name: bigquery_prod
    type: bigquery
    project_id: my-gcp-project
    dataset_id: analytics
    max_query_size: 5  # GB

The check applies to every query the agent runs - chat, stories, evaluations, anything going through nao chat or nao test.
The limit is enforced before BigQuery scans the data, so blocked queries cost nothing.
Errors include the estimated bytes and the configured limit so the agent can suggest a tighter filter and retry.
Leave the field unset (or set it to 0) to disable the check.

When you create a BigQuery connection through nao init, the CLI prompts for a maximum query size as part of the interactive setup. The same field is available in the IDE under Settings -> Warehouse Connections for the cloud and IDE flows.

Partition vs clustering columns

For BigQuery tables, nao sync exposes partition columns and clustering columns as separate sections in each table’s context. The agent uses partition columns to enforce WHERE filters that prune scanned bytes, and clustering columns to recommend the right join keys and predicate ordering for performance. Both are detected automatically - no config required. Both partition and clustering columns are written to columns.md; profiling.md also repeats the clustering columns on a short Clustering: line. Columns hidden via exclude_columns are filtered out of the clustering list too.

Native column types

For BigQuery, generated context reports the native BigQuery type rather than the normalized internal type. This matters because DATETIME and TIMESTAMP are distinct in BigQuery, and documenting both as a generic timestamp led the agent to write invalid SQL (for example TIMESTAMP_SUB on a DATETIME column). columns.md, ai_summary.md, and profiling.md now show INT64, DATETIME, TIMESTAMP, and so on. If the INFORMATION_SCHEMA lookup fails, nao falls back to the normalized types. Separately, 64-bit integer columns are no longer reported as int32 in profiling on any warehouse. BigQuery INT64, Snowflake INTEGER / NUMBER(38,0), and BIGINT columns elsewhere are now profiled with their true width.

ClickHouse

databases:
  - name: clickhouse_prod
    type: clickhouse
    host: your-instance.clickhouse.cloud
    port: 8443
    database: analytics
    user: default
    password: "{{ env('CLICKHOUSE_PASSWORD') }}"
    secure: true
    templates:
      - columns
      - preview

ClickHouse table and index metadata (ORDER BY, PARTITION BY, projections, primary key) is rendered inside columns.md, so no separate template is needed for it. Connection protocol nao connects to ClickHouse over the HTTP interface by default. For deployments that only expose the native TCP protocol (ports 9000 / 9440), set protocol: native:

databases:
  - name: clickhouse_native
    type: clickhouse
    host: your-instance.example.com
    port: 9440
    database: analytics
    user: default
    password: "{{ env('CLICKHOUSE_PASSWORD') }}"
    secure: true
    protocol: native   # "http" (default) or "native"

protocol: http (the default) keeps the existing behavior, no change needed for current configs. When prompting with nao init, the default port flips automatically based on the protocol you pick (8123/8443 for HTTP, 9000/9440 for native). secure: true means HTTPS on protocol: http and TLS over TCP on protocol: native. On the native protocol you can add verify: false to skip TLS certificate verification (for example against a self-signed internal certificate):

databases:
  - name: clickhouse_native
    type: clickhouse
    host: clickhouse.internal
    port: 9440
    database: analytics
    user: default
    password: "{{ env('CLICKHOUSE_PASSWORD') }}"
    protocol: native
    secure: true
    verify: false

Two optional timeouts are passed through to the underlying client on both protocols: connect_timeout and send_receive_timeout, both in seconds.

Databricks

databases:
  - name: databricks_prod
    type: databricks
    server_hostname: adb-1234567890123456.7.azuredatabricks.net
    http_path: /sql/1.0/warehouses/abc123
    access_token: "{{ env('DATABRICKS_TOKEN') }}"
    catalog: main
    schema_name: analytics

Read-only default schema Some queries need to stage temporary data in Unity Catalog. If the schema nao connects to is read-only, those queries fail. Set temp_schema to a schema you have write access to:

databases:
  - name: databricks_prod
    type: databricks
    server_hostname: adb-1234567890123456.7.azuredatabricks.net
    http_path: /sql/1.0/warehouses/abc123
    access_token: "{{ env('DATABRICKS_TOKEN') }}"
    catalog: main
    schema_name: analytics      # read-only, still used for table discovery
    temp_schema: scratch        # writable, used for temporary storage

When temp_schema is set it becomes the connection schema in place of schema_name; schema_name still drives which schema is synced. nao init prompts for it during interactive setup. Leave it unset if your default schema is already writable.

DuckDB

databases:
  - name: duckdb_local
    type: duckdb
    path: ./jaffle_shop.duckdb

Fabric

databases:
  - name: fabric_prod
    type: fabric
    server: myworkspace.datawarehouse.fabric.microsoft.com
    database: analytics
    schema_name: dbo
    auth_method: azure_cli

Fabric supports these authentication modes:

sql_password (SQL username/password)
azure_cli (az login token)
azure_interactive (browser login)
azure_service_principal (client ID and secret)

MSSQL

databases:
  - name: mssql_prod
    type: mssql
    host: sqlserver.example.com
    port: 1433
    database: analytics
    user: "{{ env('MSSQL_USER') }}"
    password: "{{ env('MSSQL_PASSWORD') }}"
    driver: FreeTDS
    schema_name: dbo

MySQL

databases:
  - name: mysql_prod
    type: mysql
    host: mysql.example.com
    port: 3306
    database: analytics
    user: "{{ env('MYSQL_USER') }}"
    password: "{{ env('MYSQL_PASSWORD') }}"

Postgres

databases:
  - name: postgres_prod
    type: postgres
    host: postgres.example.com
    port: 5432
    database: analytics
    user: "{{ env('POSTGRES_USER') }}"
    password: "{{ env('POSTGRES_PASSWORD') }}"
    schema_name: public

Redshift

databases:
  - name: redshift_prod
    type: redshift
    host: cluster.region.redshift.amazonaws.com
    port: 5439
    database: analytics
    user: "{{ env('REDSHIFT_USER') }}"
    password: "{{ env('REDSHIFT_PASSWORD') }}"
    schema_name: public
    sslmode: require

Optional SSH tunnel:

databases:
  - name: redshift_prod
    type: redshift
    host: cluster.region.redshift.amazonaws.com
    port: 5439
    database: analytics
    user: "{{ env('REDSHIFT_USER') }}"
    password: "{{ env('REDSHIFT_PASSWORD') }}"
    ssh_tunnel:
      ssh_host: bastion.example.com
      ssh_port: 22
      ssh_username: ec2-user
      ssh_private_key_path: ~/.ssh/id_rsa
      ssh_private_key_passphrase: "{{ env('SSH_KEY_PASSPHRASE') }}"

Snowflake

databases:
  - name: snowflake_prod
    type: snowflake
    username: "{{ env('SNOWFLAKE_USER') }}"
    account_id: xy12345.us-east-1
    password: "{{ env('SNOWFLAKE_PASSWORD') }}"
    database: ANALYTICS
    warehouse: COMPUTE_WH
    schema_name: PUBLIC

Snowflake also supports:

private_key_path
passphrase
authenticator
token

For SSO, use:

databases:
  - name: snowflake_prod
    type: snowflake
    username: "{{ env('SNOWFLAKE_USER') }}"
    account_id: xy12345.us-east-1
    database: ANALYTICS
    warehouse: COMPUTE_WH
    authenticator: externalbrowser

For Programmatic Access Token (PAT) authentication:

databases:
  - name: snowflake_prod
    type: snowflake
    username: "{{ env('SNOWFLAKE_USER') }}"
    account_id: xy12345.us-east-1
    database: ANALYTICS
    warehouse: COMPUTE_WH
    authenticator: programmatic_access_token
    token: "{{ env('SNOWFLAKE_PAT') }}"

Snowflake semantic views

When the agent runs nao sync against Snowflake, it also imports any semantic views declared in your account. Each view (metrics, dimensions, relationships pulled from INFORMATION_SCHEMA.SEMANTIC_VIEWS) is written to:

databases/type=snowflake/database=<db>/schema=<schema>/semantic_view=<name>/definition.md

The agent reads these alongside table metadata, so business definitions you maintain in Snowflake flow into the context layer with no extra config. If your account has no semantic views (or the view feature isn’t available on your edition), nao sync skips the step silently. No new fields in nao_config.yaml.

StarRocks (`type: starrocks`)

databases:
  - name: starrocks_prod
    type: starrocks
    host: starrocks.example.com
    port: 9030
    database: analytics
    user: "{{ env('STARROCKS_USER') }}"
    password: "{{ env('STARROCKS_PASSWORD') }}"

StarRocks supports multi-catalog environments. To sync tables from multiple catalogs, add a catalogs list:

databases:
  - name: starrocks_prod
    type: starrocks
    host: starrocks.example.com
    port: 9030
    database: analytics
    user: "{{ env('STARROCKS_USER') }}"
    password: "{{ env('STARROCKS_PASSWORD') }}"
    catalogs:
      - default_catalog
      - iceberg_catalog

StarRocks uses a dedicated connector (not the MySQL connector) to avoid transaction-related errors with SHOW statements.

Trino

databases:
  - name: trino_prod
    type: trino
    host: trino.example.com
    port: 8080
    catalog: iceberg
    user: "{{ env('TRINO_USER') }}"
    password: "{{ env('TRINO_PASSWORD') }}"
    schema_name: analytics

HTTPS and TLS nao talks to Trino over plain HTTP by default. Set http_scheme: https for TLS-only coordinators (Starburst, Stackable, OPA-authorized OSS Trino). Password authentication requires it:

databases:
  - name: trino_prod
    type: trino
    host: trino.example.com
    port: 8443
    catalog: iceberg
    user: "{{ env('TRINO_USER') }}"
    password: "{{ env('TRINO_PASSWORD') }}"
    schema_name: analytics
    http_scheme: https
    verify: /etc/ssl/certs/internal-ca.pem

verify controls certificate validation and only applies when http_scheme: https:

true (default): verify against the system CA bundle
false: disable verification
a path: verify against a custom CA bundle, for an internal CA

JWT bearer authentication For coordinators behind Trino’s OAuth2/JWT authenticator, pass a bearer token instead of a password:

databases:
  - name: trino_prod
    type: trino
    host: trino.example.com
    port: 8443
    catalog: iceberg
    user: "{{ env('TRINO_USER') }}"
    schema_name: analytics
    jwt_token: "{{ env('TRINO_JWT') }}"

If your tokens are short-lived, point jwt_token_file at a file that an external refresher rewrites. nao re-reads the file on every connection, so rotation needs no config change:

databases:
  - name: trino_prod
    type: trino
    host: trino.example.com
    port: 8443
    catalog: iceberg
    user: "{{ env('TRINO_USER') }}"
    jwt_token_file: /var/run/trino/token

Precedence when several are set: jwt_token_file, then jwt_token, then password. A JWT always forces http_scheme: https, even if the config says http, so the bearer token is never sent in cleartext. If the token file is missing or empty, nao falls back to jwt_token and then to password auth. When syncing Trino tables, nao automatically imports table-level comments from system.metadata.table_comments and column-level comments from DESCRIBE. These comments appear in the generated context files alongside schema metadata, giving the agent richer descriptions without any extra configuration.

SQL dialect handling

When the agent generates SQL, nao auto-detects the warehouse dialect from the target database’s type and injects extra rules into the system prompt so the query uses the right syntax:

T-SQL (MSSQL, Fabric): use TOP N instead of LIMIT.
BigQuery: quote identifiers with backticks and use SAFE_DIVIDE(a, b) instead of a / b to avoid divide-by-zero errors.
MySQL: quote identifiers with backticks and use IFNULL instead of COALESCE for null handling.

PostgreSQL, Snowflake, Redshift, Databricks, and other standard SQL warehouses do not get extra dialect rules - the agent falls back to standard SQL. If a chat uses several databases of different types, the rules for each are scoped to queries targeting that database.

Synchronization

Once configured, sync your database schemas:

nao sync

This will:

Connect to each database
Extract schema information
Render the configured templates
Save the output in databases/

Context Files

After syncing, you’ll see a structure like:

databases/
└── type=bigquery/
    └── database=my-gcp-project/
        └── schema=analytics/
            └── table=dim_users/
                ├── annotations.md
                ├── columns.md
                ├── preview.md
                ├── query_history.md
                └── profiling.md

Generated vs manual files

Every file nao sync writes carries a YAML frontmatter header saying who owns it, so it is clear at a glance what will be overwritten on the next sync. Generated files (columns.md, preview.md, query_history.md, profiling.md, ai_summary.md, and Snowflake definition.md) start with:

---
type: generated
comment: This file is managed and generated by the agent, do not modify it!
---

Alongside them, nao sync creates one annotations.md per table folder for your own notes:

---
type: manual
comment: These are manual notes you want the agent to keep in mind, safe to edit.
---

annotations.md is created empty below the header, and never overwritten: subsequent syncs leave an existing file untouched. Use it for table-specific rules the agent should honor that your warehouse metadata does not carry, for example “this table double-counts refunds, join to fct_refunds to net them out”. For rules that apply across tables, use RULES.md instead. Example generated files: columns.md

---
type: generated
comment: This file is managed and generated by the agent, do not modify it!
---

# dim_users

**Dataset:** `prod_silver`

## Description

Registry of all users, one row per user.

## Table Metadata

| Property | Value |
|----------|-------|
| **Row Count** | 2,198 |

## Columns (5)

- user_id (INT64)
- email (STRING)
- username (STRING)
- created_at (TIMESTAMP)
- is_paying (BOOL)

The table description, row count, and partitioning metadata all live in columns.md. There is no separate description.md file anymore. preview.md

# dim_users - Preview

**Dataset:** `prod_silver`

## Rows (10)

- {"user_id": 101, "email": "user_101@example.com", "username": "user_101", "created_at": "2026-03-01 10:15:00+00:00", "is_paying": true}
- {"user_id": 102, "email": "user_102@example.com", "username": "user_102", "created_at": "2026-03-02 08:05:42+00:00", "is_paying": false}

profiling.md

# dim_users - Profiling

**Dataset:** `prod_silver`

**Computed at:** `2026-03-14T18:57:58.672988+00:00`

**Columns:** 12

**Clustering:** `country`, `created_at`

## Column Profiles (JSONL)

- {"column": "user_id", "type": "INT64", "total_count": 2198, "null_count": 0, "null_percentage": 0.0, "distinct_count": 2198}
- {"column": "is_paying", "type": "boolean", "total_count": 2198, "null_count": 0, "null_percentage": 0.0, "distinct_count": 2, "top_values": [{"value": false, "count": 698}, {"value": true, "count": 1500}]}
- {"column": "country", "type": "STRING", "total_count": 1598, "null_count": 600, "null_percentage": 27.29, "distinct_count": 56, "top_values": [{"value": "US", "count": 500}, {"value": "FR", "count": 300}, {"value": "ES", "count": 200}]}

The **Clustering:** line only appears when the warehouse reports clustering, sort key, or liquid clustering columns for the table.

Table Selection

Control which tables are synced with include and exclude. Use glob patterns on schema.table:

analytics.orders
analytics.*
*.orders
*_staging
test_*
*

If both are set, nao applies include first and then removes matches from exclude.

databases:
  - name: warehouse_prod
    type: snowflake
    include:
      - analytics.*
      - marts.fct_*
    exclude:
      - analytics.tmp_*
      - marts.fct_*_backup

Best Practices

Start with your core schemas only
Keep templates small if token usage matters
Use include and exclude to avoid temp, backup, and test tables

Context Engineering Principles

Learn how to find the optimal balance between comprehensiveness and efficiency

​Supported Databases

​Common Parameters

​Excluding columns

​Templates

​query_history

​Customizing query history

​profiling

​ai_summary

​Database Parameters

​Athena

​BigQuery

​Limit query size

​Partition vs clustering columns

​Native column types

​ClickHouse

​Databricks

​DuckDB

​Fabric

​MSSQL

​MySQL

​Postgres

​Redshift

​Snowflake

​Snowflake semantic views

​StarRocks (type: starrocks)

​Trino

​SQL dialect handling

​Synchronization

​Context Files

​Generated vs manual files

​Table Selection

​Best Practices

Context Engineering Principles

Supported Databases

Common Parameters

Excluding columns

Templates

`query_history`

Customizing query history

`profiling`

`ai_summary`

Database Parameters

Athena

BigQuery

Limit query size

Partition vs clustering columns

Native column types

ClickHouse

Databricks

DuckDB

Fabric

MSSQL

MySQL

Postgres

Redshift

Snowflake

Snowflake semantic views

StarRocks (`type: starrocks`)

Trino

SQL dialect handling

Synchronization

Context Files

Generated vs manual files

Table Selection

Best Practices