Datasources

Declare the warehouses your tables live in, pick a SQL dialect per datasource, and understand how tiers influence routing.

What are Datasources?

datasources.yml names every warehouse the project models. Each entry tells 0sql:

• Which adapter to emit SQL for (postgres, snowflake, bigquery, …). The adapter is the SQL dialect of the statement you get back.
• The tier (hot, warm, cold), which influences which datasource the planner routes a query to.
• Connection details (host, port, database, schema), carried as metadata for your application.

0sql never connects to your warehouse. It plans against the model and returns one SQL statement; your application runs it. See Managing Datasources.

File format

datasources.yml is a mapping of datasource key to settings. The key, lowercased, is the datasource uid that table files refer to in their datasource: line. This is the template zsql init writes:

# The warehouses this project's tables live in. One entry per datasource,
# keyed by a short id that table files refer to. Connection details that
# are secrets (password, tokens, keys) are stripped before deploy, and
# nothing here is ever used by 0sql.
#
# warehouse:
#   name: Warehouse
#   adapter: postgres            # duckdb | postgres | snowflake | bigquery | databricks | trino | mysql | sqlserver | ...
#   tier: hot                    # hot | warm | cold; the planner prefers hotter tiers
#   host: localhost
#   port: 5432
#   database: analytics
#   username: analyst
#   schema: public
#
# local:
#   name: Local DuckDB
#   adapter: duckdb
#   tier: hot
#   file: ./data/analytics.duckdb

Keys

The file is required, needs at least one entry, and refuses a repeated key.

Tier Configuration

0sql has three tiers. They matter when the same table or measure is modeled in more than one datasource: the planner prefers hotter tiers when choosing which datasource’s SQL to generate. See Semantic Routing.

hot

The datasource you want queries to land on first: the main warehouse, or a fast OLAP tier in front of it.

warehouse:
  adapter: postgres
  tier: hot
  # ... connection details

warm

A secondary datasource the planner falls back to when the hot tier cannot answer the whole query.

archive:
  adapter: postgres
  tier: warm
  # ... connection details

cold

Archive or slow-access data, used last. Typical for object-store engines holding history.

historical:
  adapter: athena
  tier: cold
  # ... connection details

Tier is one of three routing inputs. Table cost and partitions declared on tables are the other two: a partition says which slice of data a table holds, so the planner can route a filter on last month to the hot tier and a filter on 2019 to the cold one.

Secrets

Connection fields that are secrets never leave your machine:

• 0sql never connects to your warehouse, so no credential belongs in the project at all.
• zsql deploy strips password, private_key, personal_access_token, secret_access_key, access_key_id, oauth_client_secret, oauth_client_id and api_key from datasources.yml before it builds the archive, so a secret pasted into the file by mistake is still not deployed.
• .zsql and .git are never part of the archive.

Supported Adapters

athena, bigquery, clickhouse, databricks, druid, duckdb, mysql, postgres, redshift, snowflake, sqlite, sqlserver, trino. Each has a page under Adapters describing the dialect it emits.

Semantic model and data source scoping

Each table in your semantic model belongs to exactly one datasource:

• Relationships are defined within a datasource; cross-datasource joins are not supported.
• Universes and query plans are built per datasource, and every query resolves to exactly one datasource. The SQL you get back targets that one engine.
• Compound measures and automatic data blending operate inside a single datasource: you cannot build one measure that mixes fields from Snowflake and Postgres, for example.

What multiple datasources are for is routing, not blending. Define the same tables and measures in more than one datasource (a Snowflake warehouse of record and a ClickHouse hot tier, say), and the planner picks the datasource that can satisfy the whole query, preferring by partition, tier, and cost. See Semantic Routing. A project can also model unrelated domains in different warehouses, as long as no single query needs both.

The response tells you which datasource was chosen, so your application knows which connection to run the SQL on.

Development engines

DuckDB and SQLite are convenient for local development and tests: a file path, no server. The model is the same; only the adapter and the dialect of the generated SQL change. SQLite lacks some SQL that analytical queries lean on (window functions on older builds, date truncation), so treat it as a demo target and use a warehouse adapter for the real thing.

Best Practices

1. Use descriptive keys, for example warehouse, not db1. The key is what table files refer to
2. Set tiers to match the engine: hot for the fast tier, cold for object-store engines
3. Keep secrets out of datasources.yml; nothing in the project needs a working credential
4. Declare partitions on tables that hold a slice of the data, so routing has something to work with
5. Run zsql check after editing; an unsupported adapter or tier fails there, not at deploy

Next Steps

• Managing Datasources, the CLI side
• Adapters, what each dialect emits
• Semantic Routing, how the planner chooses a datasource
• Cost optimization