# Sources

A source tells sqldash how to reach a database. It goes under the `source:` key of a
dashboard or of `metrics.yaml`, and it is always a flat list of fields. There are no
nested auth blocks and no driver-specific sub-schemas, so every engine is configured
the same way.

```yaml
source:
  type: snowflake
  account: acme-prod
  warehouse: WH
  database: ANALYTICS
  schema: PUBLIC
  username: ${env:SNOWFLAKE_USER}
  authentication: externalbrowser
```

Never write a password or token into the file. Reference it with `${env:VAR}` or keep
it in a local profile, as described in [Setup](/docs/setup/). `sqldash lint` warns about
a plaintext secret, and `--strict` turns that warning into a failure for CI. It also
flags unknown fields with a did-you-mean hint and tells you which extra to install when
a driver is missing.

## Snowflake

```yaml
source:
  type: snowflake
  account: acme-prod
  warehouse: WH
  database: ANALYTICS
  schema: PUBLIC
  role: ANALYST
  username: ${env:SNOWFLAKE_USER}
  authentication: externalbrowser
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `snowflake` |
| `account` | yes | Your account identifier, such as `acme-prod` or `xy12345.us-east-1`. |
| `warehouse` |  | The virtual warehouse queries run on. |
| `database` |  | The default database. |
| `schema` |  | The default schema. |
| `role` |  | The role to use after signing in. |
| `username` |  | The user to sign in as. Usually `${env:VAR}` or a profile. |
| `authentication` |  | `externalbrowser`, `password`, `pat`, or `keypair`. |
| `password` |  | Used with `authentication: password`. |
| `token` |  | A programmatic access token, used with `authentication: pat`. |
| `private_key_path` |  | Path to a private key file, used with `authentication: keypair`. |
| `private_key_passphrase` |  | The key's passphrase, if it has one. |
| `connect_args` |  | Extra settings passed straight to the Snowflake driver. |

`externalbrowser` opens your SSO login in a browser and is the default when no secret is
set. Install the `snowflake` extra.

### A role turns secondary roles off

Snowflake users get `DEFAULT_SECONDARY_ROLES = ALL` unless an admin changed it, and while
secondary roles are active every role granted to the user adds its privileges. Left that
way, `role: REPORTING_READER` would narrow nothing, and a table only your owner role can
read would still be readable through the source.

So when a source sets `role:`, or you pick a role in the query workspace, sqldash runs
`USE SECONDARY ROLES NONE` on every connection right after `USE ROLE`, and the source
gets exactly that role's grants. A source with no role keeps your defaults. If the data
access really comes from secondary roles, such as a primary role that only grants the
warehouse, keep them on explicitly.

```yaml
source: {type: snowflake, account: acme-prod, role: ANALYST, secondary_roles: true}
```

`secondary_roles: false` turns them off even without a `role:`.

### Pick a role, database, and warehouse

The query workspace behind [**+ Explore**](/docs/explore/) has a **Sources** section in
its sidebar. For a Snowflake connection it shows three pickers, and each choice applies
to the current query tab only.

- **Primary role** lists the roles your user can switch to, with their comments, and
  notes which secondary roles are active and that picking a role turns them off.
- **Database** lists the databases the role can see and sets where unqualified table
  names resolve. The schema browser follows it. A table in another database is still
  reachable as `DATABASE.SCHEMA.TABLE`.
- **Warehouse** lists the warehouses the role can see, with their sizes, and sets where
  the tab's queries run.

Snowflake keeps the session's warehouse and database across `USE ROLE` but only uses
them while the new role has a privilege on them, so switching to a role that lacks one
would quietly leave the session with no warehouse or no database. sqldash checks after
every switch. When the role cannot use the configured warehouse, it tries up to three
warehouses the role can see, keeps the first that works, and says so under the picker,
as in "ANALYST cannot use warehouse WH, so queries run on REPORTING_WH." The database
falls back the same way, trying the account's own databases before shared and
application ones. When nothing the role can see works, the picker says so and asks you
to pick another role.

A tile added from that tab keeps the choice. See [Explore](/docs/explore/) for how it is
written into the dashboard.

Other engines get a **Role** picker where they have session roles, which covers
Postgres, MySQL 8.0.19 and newer, MariaDB, Trino, and ClickHouse 24.4 and newer over its
HTTP driver. SQL Server and Redshift apply all granted roles together, so the picker
lists them without offering a switch. DuckDB, SQLite, BigQuery, Athena, and Databricks
have no session roles, and the sidebar names what governs access instead, such as
Google Cloud IAM or Unity Catalog grants. The database and warehouse pickers are
Snowflake only.

## BigQuery

```yaml
source:
  type: bigquery
  project: acme-analytics
  database: warehouse
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `bigquery` |
| `project` | yes | The Google Cloud project that runs and bills the queries. |
| `database` |  | The default dataset. |
| `options` |  | Extra connection settings, such as `credentials_path` for a service account key. |

sqldash signs in with Google Application Default Credentials, so run `gcloud auth application-default login` once,
or point at a service account key with `options: {credentials_path: /path/to/key.json}`.
Install the `bigquery` extra.

## Databricks

```yaml
source:
  type: databricks
  host: dbc-1234.cloud.databricks.com
  http_path: /sql/1.0/warehouses/abc123
  catalog: main
  schema: analytics
  token: ${env:DATABRICKS_TOKEN}
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `databricks` |
| `host` | yes | The workspace host, without `https://`. |
| `http_path` | yes | The SQL warehouse's HTTP path. |
| `token` | yes | A personal access token. Use `${env:VAR}` or a profile. |
| `catalog` |  | The default Unity Catalog catalog. |
| `schema` |  | The default schema. |
| `port` |  | Defaults to 443. |

Copy the host and HTTP path from the SQL warehouse's connection details page in
Databricks. Install the `databricks` extra.

## Redshift

```yaml
source:
  type: redshift
  host: acme.abc123.us-east-1.redshift.amazonaws.com
  port: 5439
  database: analytics
  username: ${env:REDSHIFT_USER}
  password: ${env:REDSHIFT_PASSWORD}
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `redshift` |
| `host` | yes | The cluster or workgroup endpoint. |
| `port` |  | Usually 5439. |
| `database` | yes | The database to connect to. |
| `username` | yes | The database user. |
| `password` | yes | The user's password. |

Install the `redshift` extra.

## Athena

```yaml
source:
  type: athena
  host: us-east-1
  schema: analytics
  options: {s3_staging_dir: "s3://acme-athena-results/"}
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `athena` |
| `host` | yes | The AWS region, such as `us-east-1`, or a full Athena endpoint. |
| `schema` | yes | The Athena database to query. |
| `options` | yes | Must include `s3_staging_dir`, the S3 location for query results. |
| `username` |  | An AWS access key id. Leave it out to use your default AWS credentials. |
| `password` |  | The matching secret access key. |

Install the `athena` extra.

## Postgres, MySQL, and MariaDB

```yaml
source:
  type: postgres
  host: db.internal
  port: 5432
  database: analytics
  username: ${env:PGUSER}
  password: ${env:PGPASSWORD}
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `postgres`, `mysql`, or `mariadb` |
| `host` | yes | The database host. |
| `port` |  | Defaults to the engine's usual port. |
| `database` | yes | The database to connect to. |
| `username` | yes | The database user. |
| `password` |  | The user's password. |
| `options` |  | Extra URL settings, such as `sslmode: require` for Postgres. |

Use `type: mysql` or `type: mariadb` with the same fields. Install the `postgres` or
`mysql` extra.

## SQL Server

```yaml
source:
  type: mssql
  host: sql.internal
  port: 1433
  database: analytics
  username: ${env:MSSQL_USER}
  password: ${env:MSSQL_PASSWORD}
  options: {driver: "ODBC Driver 18 for SQL Server"}
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `mssql` |
| `host` | yes | The server host. |
| `port` |  | Usually 1433. |
| `database` | yes | The database to connect to. |
| `username` | yes | The database user. |
| `password` |  | The user's password. |
| `options` | yes | Must include `driver`, the name of the installed ODBC driver. |

SQL Server connects through `pyodbc`, so install `pyodbc` alongside sqldash along with
an ODBC driver for SQL Server.

## Trino and ClickHouse

```yaml
source:
  type: trino
  host: trino.internal
  port: 8080
  database: hive
  username: ${env:TRINO_USER}
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `trino` or `clickhouse` |
| `host` | yes | The server host. |
| `port` |  | The server port. |
| `database` |  | For Trino the catalog, for ClickHouse the database. |
| `username` |  | The user to connect as. |
| `password` |  | The user's password, if the server requires one. |

Install the `trino` or `clickhouse` extra.

## DuckDB and SQLite

```yaml
source:
  type: duckdb
  database: analytics.duckdb
```

| field | required | what it is |
| --- | --- | --- |
| `type` | yes | `duckdb` or `sqlite` |
| `database` |  | A file path relative to the project, or `:memory:`, which is the default. |
| `attach_files` |  | DuckDB only. `true` turns local CSV and Parquet files into views. |
| `base_dir` |  | DuckDB only. The folder the source's files resolve against, instead of the dashboard's own folder. |
| `external_access` |  | DuckDB only. `true` lets SQL read files outside the project. Off by default. |

Neither needs an extra.

### Query local CSV and Parquet files

```yaml
source: {type: duckdb, attach_files: true}
```

With `attach_files: true`, every `.csv` and `.parquet` file next to the dashboard, and
in its `data/` folder, becomes a view named after the file. You can then query it
directly.

```sql
SELECT category, SUM(amount) FROM orders GROUP BY 1
```

That is how the demo works. Commit a small extract next to a dashboard and anyone who
clones the repo can reproduce it. Only local folders are scanned. Remote object
storage is not attached.

### DuckDB reads only the project

A warehouse source is bounded by the credential it connects with. A DuckDB source has no
credential, and anyone who can load a served dashboard can type SQL into the query
workspace, so sqldash confines a DuckDB source to two folders and everything under them,
the folder holding its `database:` file and the folder its files resolve against. That
second one is `base_dir` when the source sets it and the dashboard's own folder
otherwise. `read_csv`, `read_text`, `read_blob`, and `glob` outside them fail with a
permission error that names what the source is confined to.

If a project genuinely reads files from elsewhere, such as a shared drive or a `.duckdb`
file in another tree, say so on the source.

```yaml
source: {type: duckdb, database: app.duckdb, external_access: true}
```

That switch is not narrow. It hands every viewer of every dashboard on that source the
full file access of whoever runs the server, so prefer pointing `base_dir` at the folder
you want read.

## Any other database

If SQLAlchemy has a dialect for it, sqldash can connect with a raw connection URL.
Write the whole source as the URL.

```yaml
source: "trino://analyst@trino.internal:8080/hive"
```

Keep secrets out of the URL with `${env:VAR}`, as in
`"postgresql+psycopg://${env:PGUSER}:${env:PGPASSWORD}@db.internal/analytics"`.
Inside a map of several sources, give the URL its own field.

```yaml
source:
  warehouse: {type: snowflake, account: acme-prod, default: true}
  events: {url: "duckdb:///events.duckdb"}
```

## Several sources in one dashboard

A dashboard that reads two databases names each connection.

```yaml
source:
  warehouse:
    type: snowflake
    account: acme-prod
    default: true
  app_db:
    type: postgres
    host: db.internal
    database: app
```

A tile picks one with `source: app_db`. A tile that names none runs against the entry
marked `default: true`. A map with a single entry needs no mark, but with several
entries and no mark sqldash refuses to guess, because picking by file order would let
a reordering quietly repoint every tile.

`sources:` is the older spelling of this map. Files that use it still load, but new
files should use `source:`.

## Inspect and test

```bash
sqldash source list --json      # every source, credentials redacted
sqldash source test             # connect and SELECT 1 on each, report latency
sqldash source describe --json  # tables and columns, the raw material for metrics
```

## Who can see what

sqldash has no login of its own today. `sqldash serve` runs on your machine with your
credentials, so the warehouse's grants, masking, and audit apply to every query, for
every person. Anyone who can reach the served page can run SQL in the query workspace
with those credentials, which is why `serve` binds to `127.0.0.1` by default.