Datasources
datasources.yml describes each warehouse a project’s tables live in, and two different programs read it.
The planner reads adapter, which picks the SQL dialect it emits, and tier, which says which copy of the data to prefer. Those two and the name are what a deployed model keeps; the server has no database driver in it and reads nothing else here.
The zsql command on your own machine reads the rest — host, port, database, credentials — to open a read-only connection for zsql catalog, zsql sample and zsql run. This page is the file and the two keys the planner reads; Connecting to a warehouse is the connection.
The file
The commented template zsql init writes, abbreviated — it carries a worked entry for Postgres, Redshift, Snowflake, Databricks, BigQuery, Athena and DuckDB:
# The warehouses this project's tables live in. One entry per datasource,
# keyed by a short id that table files refer to.
#
# The server never connects to your warehouse. It has no database driver
# in it at all: it takes a query spec and gives back SQL text, and nothing
# about your rows ever reaches it. The zsql command on this machine does
# connect, read-only, when you ask it to. Give it a credential with no
# write grant.
#
# The model needs `adapter` and `tier`, and those are the only keys the
# planner reads. Everything else here is for the zsql command and for
# your own tools.
#
# Secrets never leave this machine: .zsql under the same datasource key,
# or ZSQL_DS_<KEY>_<FIELD>.
#
# warehouse:
# name: Warehouse
# adapter: postgres # the dialect to generate
# tier: hot # hot | warm | cold; the planner prefers hotter tiers
# host: localhost # from here down: for the zsql command; the server ignores it
# port: 5432
# database: analytics
# username: analyst
# schema: public
# sslmode: verify-full # disable | prefer | require | verify-ca | verify-full
#
# local:
# name: Local DuckDB
# adapter: duckdb
# tier: hot
# file: ./data/analytics.duckdb
A filled-in file for the running TPC-DS example:
tpcds:
name: TPC-DS (Postgres)
adapter: postgres
tier: cold
host: localhost
username: tpcds_user
database: tpcds
The top level maps a key to its settings. The key (trimmed, lower-cased) is the datasource uid; table files refer to it with datasource: tpcds. The file is required, needs at least one entry, and refuses a repeated key.
| Key | Required | Meaning |
|---|---|---|
name | no | Display name. Defaults to the key. Printed by zsql sql as -- datasource: <name>. |
adapter | yes | One of the identifiers below. Selects the dialect. |
tier | no | hot, warm or cold. The planner prefers hotter tiers when several datasources can answer a query. |
description | no | Free text. |
host, port, database, username, schema, file | no | Connection metadata for your own application. Not validated, and never used by 0sql. |
Adapters
athena bigquery clickhouse databricks druid duckdb mysql
postgres redshift snowflake sqlite sqlserver trino
Any other string fails the deploy with Datasource errors: adapter '<x>' is not supported.
The adapter controls the SQL the planner emits for that datasource: identifier quoting (" for most, backticks for bigquery, clickhouse, databricks and mysql, [...] for sqlserver), date literals and truncation functions, which words are reserved and which functions count as aggregates in your expressions, and join support (mysql has no FULL JOIN; druid has no CTEs). There are no adapter-specific settings to write in the project; the adapter name is the whole configuration.
Tiers
tier ranks copies of the same data. When a measure is reachable on a hot datasource and a cold one, the planner routes to the hot one. Use it when a fast store holds a subset or an aggregate of what the warehouse holds. Routing rules are on Semantic routing.
Secrets
The planner never opens a connection, so the deployed model needs no credential at all. On zsql deploy, datasources.yml is shipped with these keys removed:
password private_key personal_access_token secret_access_key
access_key_id oauth_client_secret oauth_client_id api_key
Keep secrets out of the file anyway: they belong in the gitignored .zsql, or in ZSQL_DS_<KEY>_<FIELD>.
A working credential is needed for the commands that read the warehouse on this machine — zsql datasource test, zsql catalog, zsql sample and zsql run — and zsql datasource auth is what writes it. Give it no write grant. The connection keys, the TLS settings and the per-adapter credentials are on Connecting to a warehouse.