Design: data access

Design: data access#

Note

A design document, not a user guide. It records how data.load() grows from “a name that resolves to a parquet file” into “a name that resolves to a location, which may be a database table”, and why ChartBook stays a thin layer over the database rather than becoming one.

Status: built for DuckDB on 2026-10-13 (step 5 in Design: start here, “Step 5, as built”; GitHub issue 9): [sources], table, query, data.sql(), data.ref(), data.connect(), the typed location in the index, and the worked example. Where what was built departs from this page, the overview says so and this page keeps its text with a dated note. Companion to the asset graph, whose rules and “not doing” list apply here.

Revised 2026-10-12: chartbook.data is now the front door for dataframes only. Everything in this document is about dataframes, so nothing here changed in substance; the wording about references and about the index was brought up to date.

The problem#

Today a dataframe is a file. data.load() resolves a name to a parquet path and calls pl.scan_parquet. The manifest has exactly one storage concept, path.

Organizations keep their data in Snowflake, Databricks, Postgres or a DuckDB file, and they already have tools that move it (dbt, Airflow, cron). ChartBook must not become another one. What it can do is let the same name point at a table instead of a file, so that:

  • data.load("acme/rates", "treasury_rates") keeps working when the parquet moves into a warehouse;

  • a user who wants control can write SQL, referring to dataframes by their ChartBook names, and have the warehouse run it;

  • the catalog page for a table-backed dataframe looks the same as for a file-backed one.

The rule#

A name resolves to a location. The backend runs the query. Arrow comes back. Polars wraps it.

ChartBook never parses, plans, rewrites or transpiles SQL. It knows where a name lives and which dialect lives there, and it substitutes identifiers. This is dbt’s source() and ref(): the resolver returns an identifier, and the SQL is the user’s.

Manifest#

A pipeline may declare named sources. A dataframe then points at exactly one of path, table or query.

[sources.warehouse]
backend = "snowflake"
url = "snowflake://acct/RESEARCH_DB"        # the public part only; no credentials, ever

[sources.local]
backend = "duckdb"
url = "duckdb:///./_data/local.duckdb"

[dataframes.treasury_rates]
name = "Treasury Rates"
source = "warehouse"
table = "RATES.TREASURY_RATES"               # a table or view that already exists

[dataframes.term_premium]
name = "Term Premium"
source = "warehouse"
query = "select date, dgs10 - dgs2 as tp from RATES.TREASURY_RATES"   # a view ChartBook defines

[dataframes.yields_local]
path = "./_data/yields.parquet"              # unchanged; the built-in parquet backend, no source needed

Rules:

  • table and query require source. path forbids it. Declaring two of the three is an error.

  • url follows the SQLAlchemy URL convention (snowflake://, databricks://, postgresql://, duckdb:///). ChartBook does not use SQLAlchemy; the convention is what Polars, Ibis and Kedro already accept, so one string works everywhere.

  • Identifiers are three-level everywhere that matters (catalog.schema.table in Unity Catalog and DuckDB, database.schema.table in Snowflake and Postgres). table may give one, two or three parts; the backend fills the rest from url and quotes for its dialect.

Credentials never enter the repo#

The manifest names a source. The machine supplies the secret, in this order:

  1. environment variable CHARTBOOK_SOURCE_<NAME>_URL holding a complete URL;

  2. ~/.chartbook/sources.toml, a table per source name with either url or individual keys (user, password, token, warehouse, role) merged into the manifest URL;

  3. the manifest URL alone, which is enough for DuckDB files and for anything the backend’s own client can authenticate (SSO, key pair, ~/.snowflake/connections.toml).

This is dbt’s profiles.yml and Airflow’s conn_id: code names a handle, the environment resolves it. chartbook check verifies that every source resolves, not that it connects.

Python surface#

Four calls, from easiest to rawest. The first is unchanged.

from chartbook import data as cb

cb.load("acme/rates", "treasury_rates")          # -> pl.LazyFrame, as today
cb.sql("select date, rate from {acme/rates:treasury_rates} where date >= '2024-01-01'")
cb.ref("acme/rates:treasury_rates")              # -> 'RESEARCH_DB.RATES.TREASURY_RATES'
cb.connect("warehouse")                          # -> the raw duckdb / snowflake / databricks connection

  • load returns a pl.LazyFrame for every backend. For files, Iceberg and Delta it is truly lazy. For a warehouse it is Arrow fetched eagerly and wrapped, because Polars has no lazy database scan. The docstring says so. format="pandas" and format="polars_eager" keep working.

  • sql substitutes each {ref} with the backend’s identifier. If every reference lives in one source, the query runs there, in that dialect, with that engine’s pushdown. If references span sources, the query runs in DuckDB with each non-DuckDB reference registered as a view or temp table first, and the SQL is DuckDB’s dialect. That is the whole rule; there is no third case. (As built, 2026-10-13: one source per query, plus any files, which DuckDB reads alongside. Two sources are refused. With DuckDB the only engine the mixed case is two DuckDB files, and ATTACH through connect() is the honest answer until there is a second engine.)

  • ref exists so a user can paste the identifier into SQL they run with their own tooling.

  • connect hands back the client object. Anything ChartBook does not expose is reachable here.

The reference grammar is the one in Design: references: treasury_rates is local, fed_yield_curve:treasury_rates is another pipeline, ftsfr/fed_yield_curve:treasury_rates is fully scoped. Every function in chartbook.data takes dataframes only, so a reference that names another kind (chart/gdp, model/x) is refused, and a {ref} inside sql() must be a dataframe. Cross-pipeline references need the resolution chain (step 3 in Design: start here); the first implementation of this design is local-only. (As built: the chain existed first, so a cross-pipeline file dataframe works in sql() and ref(), as the pinned copy. A table or query of another pipeline was not pinned at first; since 2026-10-15 a snapshot writes a table or query in a DuckDB file out as parquet into the store, so it is pinned and read like a file.)

Because sql() puts dataframes’ columns into SQL that the user writes, column names should be SQL-compatible: letters, digits and underscores, starting with a letter. ChartBook recommends that for every dataframe; a readable name is metadata, given in a series table (Design: the index, “Series”).

Two words to keep apart: the kind of an asset (dataframe, chart, …) is part of its address. The kind of a location (file, table, query) is where a dataframe’s content is, and is what this document adds to.

Backends#

A backend is the thing behind a source: how to connect, how to quote, how to scan, how to run SQL, how to describe. A source is a configured instance of a backend, named in a manifest.

class Backend(Protocol):
    name: ClassVar[str]                                   # "parquet", "duckdb", "snowflake"
    def ref(self, loc: Location) -> str: ...              # quoted identifier in this dialect
    def scan(self, loc: Location) -> pl.LazyFrame: ...    # lazy where the engine allows
    def sql(self, query: str) -> pl.LazyFrame: ...        # run dialect-native SQL, Arrow -> Polars
    def connect(self): ...                                # raw handle
    def describe(self, loc: Location) -> Description: ... # columns, row count, last modified

  • Built in: parquet (what exists today) and duckdb. DuckDB is the local engine because its relation API returns a real pl.LazyFrame, it reads parquet, Iceberg and Delta, and it attaches Postgres, SQLite and Unity Catalog. It is also the fallback engine for mixed-source sql().

  • Plugins, later: snowflake and databricks using each vendor’s Arrow fetch, postgres via ADBC. Package naming chartbook-backend-<name>, entry-point group chartbook.backends, selected by name from backend =. Following the asset graph’s rule, the Protocol is extracted into a public contract only once the third implementation exists; duckdb is the second.

  • Optional, never core: cb.table(ref) returning an ibis.Table for users who want Ibis’s expression API. Ibis stays an extra. The public return type is Polars.

Backends are selected, not broadcast, so this is a registry keyed by name, not a pluggy hook. The plotting backends stay pluggy because many renderers legitimately answer the same call.

Index consequences#

The Dataframe record carries a typed location object, which for a file is { "kind": "file", "path", "resolved_path" }. (The index schema was revised on 2026-10-12 to have this from its first version, so that this design is an addition and not a migration.) A table-backed dataframe needs:

  • two more kinds of location: { "kind": "table", "source", "backend", "identifier" } and { "kind": "query", "source", "backend", "sql" };

  • columns populated from describe() rather than from reading parquet;

  • an honest hash. A warehouse table cannot be content-hashed cheaply. Record content_hash: null, hash the definition (identifier or query text plus the described schema), and take freshness from the warehouse’s own metadata (last_altered, or max(date_col)). The Build record lists the source as impure, exactly as it does for a FRED fetch. Hash what you can, record what you cannot.

Settle the exact field names of the table and query kinds when this is implemented. That a dataframe’s location is a typed object, not a bare path, is settled, and the index schema shows it.

Vocabulary#

Term

Means

Not

backend

an engine behind a source: parquet, duckdb, snowflake

“adapter” (dbt’s word for a large dialect shim), “connector” (extract-and-load)

source

a named, configured instance of a backend in a manifest

a credential

exporter

one-way emission of the graph to an orchestrator or standard

“integration”

plugin

a pip package that registers backends or exporters via entry points

a design concept; it is only how code arrives

Not doing#

  • No query planning, rewriting or transpiling. sql() substitutes identifiers and nothing else. If dialect translation is ever wanted, it is an optional, best-effort sqlglot pass, and it is not in this design.

  • No semantic layer. No measures, dimensions, joins or metric definitions in the manifest. A “series” stays a column of a dataframe, as the index schema has it. If a user wants a derived series, query is the tool.

  • No writes. ChartBook never creates, loads or alters a warehouse table. Pipelines write with their own tools; data.path() serves file outputs; table and query describe what already exists.

  • No credentials in any committed file. Including the catalog’s.

  • No Ibis as a return type or dependency of the core.

  • No DuckDB-WASM for data access in the browser. Chart pages embed series inline; anyone who wants to query gets sql() in Python. Cross-origin parquet in the browser is the problem this design avoids.

  • No per-backend plugin system before the third backend. Rule from the asset graph.

Phasing#

  1. Built (0.2.0). data.path() and data.load() call one internal resolver, chartbook.resolve.resolve(), that returns a typed Location. Only FileLocation exists. path() returns a Path for file locations and raises TypeError for anything else (resolve.require_file()); load() raises NotImplementedError. This is the seam; it adds no behaviour. To add a location kind: subclass Location, return it from the resolver, and teach load() to scan it.

  2. After the index. [sources], table, query; the duckdb backend; sql(), ref(), connect(); local references only; the index’s typed location. Worked example: the yield_curve example pipeline gains one query-backed dataframe over a DuckDB file. (Built 2026-10-13.)

  3. With P1 resolution. Cross-pipeline references in sql() and ref(). (Built with 2, for file dataframes.)

  4. On demand. snowflake and databricks as plugins, then extract the Protocol.

Prior art#

  • dbt source() / ref(): a name compiles to a qualified identifier spliced into the user’s SQL. The model for ref() and sql().

  • Kedro ibis.TableDataset: table_name plus connection: {backend: duckdb, ...}; one dataset type covers every SQL engine through a backend key. The model for [sources].

  • Airflow connections and dbt profiles: code names a handle; the environment resolves it. The model for credentials.

  • DuckDB relation.pl(lazy=True), ATTACH, read_parquet: the local engine.

  • Polars read_database is eager and there is no scan_database; why warehouse loads are honest about eagerness.

  • Evidence.dev sources: a connection file plus .sql files in the source’s dialect, each addressable by name from page-level SQL. The model for query.