Skip to content
tabledoc
Esc
↑↓navigate↵open⌘Jpreview
On this page

Specification (v1)

The files tabledoc reads, how it checks them, and what it returns to a program.

tabledoc defines how people write the description of a dataset (YAML and Markdown, or a dbt project’s yml), checks it, and reads it for other programs. It depends on no particular host; tabledoc load writes the description into a DuckLake catalog as ordinary tables of one schema (_tabledoc by default), tabledoc dump writes the files back from it, and tabledoc export --markdown writes it as Markdown files for AI assistants and people to read. The files the author writes are the source.

Commands

It exits with 1 when there are errors (with --strict, warnings too) and 2 when the files or the catalog cannot be read or written at all.

Files

Paths are relative to the folder that holds tabledoc.yml (the root).

tabledoc.yml              the dataset (the name is fixed)
README.md                 the dataset's overview (optional)
<schema>/<table>.yml      a table
<schema>/<table>.md       the table's guide (optional; or <schema>/<table>/README.md)
<schema>/README.md        the schema's guide (optional)

In a dbt project, tables are described in dbt’s yml and each guide is a .md with the same name next to the model’s .sql (dbt projects).

Folders whose name starts with ., and target/, dbt_packages/, node_modules/ and .venv/, are not read.

tabledoc.yml

The description of one dataset.

$schema: https://tabledoc.queria.io/schemas/dataset/v1.json
name: boj
title: Bank of Japan time-series statistics
description: Every statistic on the Bank of Japan's time-series data search site.
language: en
topics: [Bank of Japan, interest rates, prices]
homepage: https://github.com/queria-io/dataset-boj
update_frequency: irregular
licenses:
  - id: boj-copyright
    title: Bank of Japan copyright and disclaimer
    url: https://www.boj.or.jp/en/about/copyright.htm
sources:
  - title: Bank of Japan time-series data search site
    path: https://www.stat-search.boj.or.jp/
contributors:
  - title: Bank of Japan
    path: https://www.boj.or.jp/
    roles: [rightsHolder]
spatial_coverage: [JP]
schemas:
  - name: timeseries
    title: Time series
docs:
  - docs/methodology.md
queria:
  title_kana: ニチギンジケイレツトウケイ
Key Required Meaning
$schema yes The format and its version
name yes The dataset’s name: ^[a-z0-9](?:[a-z0-9._-]*[a-z0-9])?$
schema The schema tabledoc load writes the description into; default _tabledoc
title, description, topics, homepage, language, issued, update_frequency language is a BCP 47 tag, the language of the text written without one (languages); issued is YYYY-MM-DD
repository The URL of the repository of the dataset’s source code, such as https://github.com/queria-io/dataset-boj. tabledoc load takes Git’s origin when it is left out
licenses, sources, contributors, temporal_coverage, spatial_coverage Written in a table file, the value is that table’s alone
schemas name, title, description, guide (the path of the schema’s guide; default <name>/README.md when it is there), docs (the schema’s other documents)
docs Paths of the dataset’s other documents
queria Keys only Queria reads (keys for one reader)
annotations Keys for other readers, by the reader’s name

Table files

The description of one table. A table file is a YAML file whose $schema is https://tabledoc.queria.io/schemas/table/v1.json; YAML files without it (dbt’s schema.yml, say) are not read. The keys follow one entry of dbt’s models:; keys dbt does not have (title, primary_key, sources and so on) sit at the same level.

$schema: https://tabledoc.queria.io/schemas/table/v1.json
name: series
title: Series
description: |-
  Every series of every database on the search site. One row is one series.
primary_key: [db, series_code]
sources:
  - path: https://www.stat-search.boj.or.jp/
columns:
  - name: db
    title: Database
    description: The same as db in databases
    dimension: categorical
  - name: series_code
    title: Series code
  - name: last_update
    title: Last updated
    dimension: time
    granularity: day
Key Meaning
name The table’s name. Defaults to the file name
table <schema>.<table>. Wins over the file’s place (which table)
title, description, topics topics as a repository’s topics on GitHub. keywords, the earlier name, is read as topics. Queria reads them, so they are written as topics under queria:
primary_key The columns that identify a row
licenses, sources, temporal_coverage, spatial_coverage Only when they differ from the dataset’s
sql, depends_on For a table dbt does not build: the SQL it is made from and the tables it reads
docs Paths of the table’s other documents
columns name, title, description, asset, and the semantic keys entity, dimension, granularity
metrics How the table is aggregated (semantics)
queria, annotations Keys for one reader

Types, nullability and row counts are not written. The data store holds the structure.

asset: true says the column’s value is the path of a file that belongs to the row.

Which table

  • By default the place decides: a table file at <schema>/<table>.yml describes <schema>.<table>. A file directly under the root is in the main schema
  • table: <schema>.<table> wins over the place. A table file at any other depth has to say it
  • Two files describing the same table is an error
  • dbt does not read a table file inside models/: dbt ignores keys outside models:

Languages

Text people read can be written in more than one language. Write the value as a map from a BCP 47 tag to the text, or as the text alone, which is in the dataset’s language.

language: ja
title:
  ja: 日本銀行時系列統計
  en: Bank of Japan time-series statistics
topics:
  ja: [日本銀行, 金利]
  en: [Bank of Japan, interest rates]
columns:
  - name: db
    title: {ja: データベース, en: Database}
    description: databases の db と同じ
  • Any of tabledoc’s keys whose value is text, or a list of text, takes either form: title, description, topics, homepage, and the title, url and path of licenses, sources and contributors, say. A list becomes a map from a tag to a list
  • Values that point at something, or come from a fixed set, hold one value: name, table, schema, repository, primary_key, an entity’s name, language, $schema, issued, update_frequency, dimension, granularity, agg, expr, sql, depends_on, spdx, id, roles, spatial_coverage, temporal_coverage, and the paths of guides and docs. Written in other languages, they would no longer point at the same table, column or file
  • Keys under queria: and annotations: are the reader’s own
  • A tag is checked as BCP 47. A map with no text is an error
  • In a dbt project’s yml, description is the text alone, in language. dbt does not take a map there
  • tabledoc.read returns these values as maps always: the text alone becomes {<language>: text}, and {und: text} without language

Guides and documents

  • A table’s guide is the .md with the same name in the same folder as its table file (series.md for series.yml), or README.md in the folder of that name (series/README.md). It has no front matter
  • The dataset’s overview is README.md at the root. A schema’s guide is schemas[].guide, else <schema>/README.md
  • Other documents are listed in docs: the dataset’s in tabledoc.yml, a schema’s in schemas[].docs, a table’s in its table file. Paths are relative to the root
  • The files a Markdown document links to with a relative path (images, PDFs, other documents) are sent too. A link that leaves the root is an error; a link to a missing file is a warning; a link to a folder is left alone
  • Build and maintenance notes belong in CONTRIBUTING.md, not in docs

Paths in the dataset

In the dataset, each folder is one thing described: the root is the dataset, <schema>/ a schema, <schema>/<table>/ a table. The folder’s README.md describes it, and its other Markdown files are its documents. tabledoc places the files there, wherever the author keeps them:

Written Path in the dataset
README.md README.md
an entry of docs <file name>
a schema’s guide <schema>/README.md
an entry of schemas[].docs <schema>/<file name>
a table’s guide <schema>/<table>/README.md
an entry of a table’s docs <schema>/<table>/<file name>
a file a document links to its path under the root
  • In a Markdown document, each relative link to a file that was placed elsewhere is rewritten to point at its new path. The sha256 and bytes are those of the rewritten document
  • Two files at the same path in the dataset is an error, and so is one file named for two paths in the dataset (the same document in two tables’ docs, say)

dbt projects

When the root has dbt_project.yml, tabledoc reads target/manifest.json (--manifest changes it). Run dbt compile (or a build) first; without a manifest, tabledoc warns and reads only the table files. load gives this as info instead when the catalog has a table dbt built (one with a source_path), the description has no sql, and it describes each such table, since it keeps their SQL.

  • The tables are the enabled models materialized as table, view or incremental, under the model’s schema and alias. Ephemeral and disabled models, seeds, snapshots and tests make no table, so they are not listed. A model with no description is listed with what dbt fills in only (materialized, sql, depends_on, source_path)
  • The description comes from the model’s yml. description and the columns’ description as they are; keys dbt does not have (title, primary_key, sources, a column’s title, asset and so on) and queria under config.meta
  • Semantics come from the columns’ entity, dimension and granularity, and from the semantic layer’s simple metrics
  • materialized is the model’s materialization, source_path its original_file_path, and sql its compiled_code. depends_on is read from it by DuckDB’s parser. A table of another dataset is written <repository URL>#<schema>.<table>, where the URL comes from the DuckLake ATTACHes in profiles.yml (--profiles, else DBT_PROFILES_DIR, else the root)
  • A guide is the .md with the same name next to the model’s .sql, or README.md in the folder of that name (dbt ignores .md files without a docs block). Other documents are docs under config.meta
  • A table is described in one place. When both a table file and dbt’s yml describe it, that is an error. When dbt’s yml says nothing about it, the table file is read, and sql comes from dbt
models:
  - name: series
    description: One row is one series.
    config:
      meta:
        title: Series
        primary_key: [db, series_code]
    columns:
      - name: db
        description: The same as db in databases
        dimension: categorical
        config:
          meta: {title: Database}

A table dbt does not build (one made in Python, say) is described with a table file, in the same project.

Semantics

What a column means follows dbt’s semantic layer as written from dbt-core 1.12 (on the columns). These are tabledoc’s own keys, not one reader’s.

Key Value Meaning
entity {type, name}, or the type alone A join key. type is primary, unique, foreign or natural. name is the vocabulary the values come from (corporate_number, lg_code)
dimension categorical or time, or {type, name} A column to filter and group by
granularity day, month, year, … How fine a dimension: time column is
metrics (table) [{name, agg, expr, description}] How to aggregate. agg is sum, max, count_distinct, …

In dbt, metrics need the semantic layer (semantic_model: true and a time spine). A project that does not use it can still write entity and dimension on columns.

Keys for one reader

Keys only one reader uses are written under that reader’s name. tabledoc does not check them and returns them under annotations.<reader>.

  • Queria’s keys are written under queria:, the one reader tabledoc knows by name. Queria checks them
  • Other readers’ keys go under annotations: {<reader>: {...}}

Reading from Python

Programs that send the description somewhere, or turn it into another format, read it with tabledoc.read. It checks the files as validate does and returns the description with the older formats read as today’s.

import tabledoc

report = tabledoc.Report()
d = tabledoc.read(tabledoc.Options(root), report)  # root: the folder of tabledoc.yml
if report.failed:
    print(report.render())
d.dataset   # {"name": "boj", "title": …, "guide": "README.md", "docs": ["methodology.md"], "annotations": {…}}
d.schemas   # [{"name": "timeseries", "title": "Time series"}]
d.tables    # [{"schema": "timeseries", "name": "series", "sql": …, "depends_on": […], "guide": …, "columns": […]}]
d.files     # [File(path="README.md", sha256="…", bytes=1234, media_type="text/markdown", local="README.md"), …]
d.source    # {"repository": "https://github.com/…", "commit": "…", "path": "", "dirty": False}
d.dependencies  # {"other": {"url": "https://…"}}: the other datasets the SQL reads, by the name it reads them under, as `dependencies` of FDL's tables.json
d.content(d.files[0])  # the bytes of that file
  • source is the Git working copy: repository is the origin remote as an https URL (without credentials or .git), commit is HEAD, path goes from the repository’s root to the folder of tabledoc.yml, and dirty says whether there were changes not committed. It is None outside Git, or with Options(with_source=False)
  • Documents are referred to by their paths in the dataset. files lists every file whose content is meant to go with the description (guides, README, docs, and what they link to) with that path, its sha256, size and media type, and local, its path under the root. guide, docs, schemas[].guide and schemas[].docs are paths in files. content gives what goes into the dataset for a file: its bytes, a Markdown document with its links rewritten, and for an older-format Markdown table file, the text without its front matter
  • Semantics always come out as {type, name} (dimension: categorical is {"type": "categorical"})
  • Tables are sorted by schema, then name, and keys come in a fixed order. The same files give the same records
  • The structure (types, nullability, row counts, whether a table is a view) is not in it

Writing into a DuckLake catalog

tabledoc load checks the description as validate does, then writes it into a DuckLake catalog as ordinary tables of one schema: _tabledoc unless schema in tabledoc.yml or --schema names another. They are versioned with the data, and any program that reads the catalog reads the description with SQL. With fdl, the catalog is the working metadata fdl clone makes, and fdl commit makes a version of it.

tabledoc load --ducklake work.sqlite --out build/assets
fdl commit work.sqlite -m "..." --assets build/assets
  • --ducklake: the catalog (default: FDL_WORKING). --data-path: where new data files go (default: FDL_DATA_PATH, else the folder fdl clone noted beside the catalog). Data files over HTTP are read with DuckDB’s secrets; when the catalog is fdl’s working metadata and FDL_TOKEN is set, the token is sent to the remote fdl clone noted, so a private dataset’s files can be read
  • --schema: the schema to write into (default: schema in tabledoc.yml, else _tabledoc). Queria reads _queria: give --schema _queria (or schema: _queria) for a dataset published on Queria
  • --out: also write the folder fdl commit --assets takes: each file of the files table under files/<path>. With --assets <folder>, the files below it (those of the build’s asset columns) are linked under data/. The folder has to be empty or written by an earlier load, and outside .fdl/, which only fdl writes
  • --unlisted: the catalog’s tables and views that have no description get a row in tables with published false, and so does a table dbt built that the author described nowhere (only its SQL is known). The tables of the schema written into, and of each schema that holds the tables dataset, schemas, tables, columns and files (one an earlier load wrote into), get none
  • A described table or column, or a primary key column, that is not in the catalog is an error, and so is an asset column that is not varchar. Names match without regard to case, as in DuckDB, and are written as the catalog spells them
  • A value its column cannot hold (issued other than YYYY-MM-DD, say) is an error
  • On an error (with --strict, a warning) nothing is written
Table Primary key From
dataset id (always dataset) tabledoc.yml: language, homepage, repository (else Git’s origin, as https), issued, update_frequency, licenses, sources, contributors, temporal_coverage, spatial_coverage; title_kana, cover and ai_context under queria:
schemas schema schemas[] with a title or a description
tables schema, name each table and view described: licenses, sources, temporal_coverage, spatial_coverage, sql, depends_on, source_path; published and ai_context under queria:
columns schema, table, name each column with a title, a description or an entity: entity is the entity’s name (a code system such as lg_code)
texts schema, table, column, language title, description and topics of the dataset, each schema, table and column, one row for each language they are written in. schema, table and column are '' where they do not apply: the dataset’s row has all three '', a schema’s has table and column '', and a table’s has column ''
files path the files in the dataset: path, sha256, bytes, media_type
  • Text in more than one language (languages) goes into texts, one row for each language, so a dataset written in one language has one row for each thing described. Adding a language adds rows; the other tables do not change
  • The other tables hold one value for each key. For a key written in more than one language outside texts (a license’s title, say), they hold the value in language, and tabledoc warns
  • topics (or keywords, its earlier name) unless queria: has topics. themes under queria: is not written
  • licenses is a list of {id, spdx, url, title, commercial_use, share_alike, attribution_required}; a license written as a string is {id}
  • source_path is the path of the file that defines the table, from the root of repository (dbt’s original_file_path, under the folder of tabledoc.yml in the Git working copy), and sql is the SQL that made it; both are NULL where they are not known. Whether it is a table or a view is not written: the catalog holds that
  • A view’s row has no sql or depends_on: the metadata holds the view’s SQL
  • Keys under annotations: for readers other than Queria are not written
  • The files are not in the catalog. files.sha256 holds the sha256 of each, and fdl commit --assets uploads each file by its content
  • Rows are added, changed and removed by their primary key, so a load changes only the cells that differ. Without a dbt build (no sql in the description), each table keeps the sql, depends_on and source_path the catalog holds
  • Primary keys and asset columns are not written into these tables. When the catalog is the working metadata fdl clone made (it noted <catalog>.fdl.json beside it), load declares them to FDL with fdl declare, which has to be on PATH (fdl-cli 0.34 or later): each table’s primary_key and the asset columns of each table whose columns say asset (a view has neither), no profile for each table and view whose published is false and a profile for each other table described, the other datasets the SQL reads (from dbt’s profiles.yml) as dependencies, and the keys of these tables with files.sha256 as an asset column and no profile. It runs only what differs from fdl declare list, and leaves what the description does not say as FDL has it
  • tabledoc writes no comments (COMMENT ON)

Writing the files from a catalog

tabledoc dump writes the files of the description a catalog holds: tabledoc.yml, a table file for each table described, and every file of the files table at its path. tabledoc load reads them back to the same tables.

tabledoc dump --ducklake work.sqlite --out description/
  • --ducklake: the catalog (default: FDL_WORKING). --schema: the schema the description is in (default: _tabledoc); another schema is written as schema in tabledoc.yml, so load writes back into it. --out: the folder (default: here), outside .fdl/
  • --files: where the files of the files table are, as <files>/<sha256>: a folder or an http(s) URL. Default: assets/ of the repository fdl clone noted beside the catalog. Each file is checked against its sha256
  • --name: the dataset’s name in tabledoc.yml. Default: the last part of the repository’s URL
  • A file already in the folder is not replaced, and nothing is written, unless --force. A path in the catalog that would leave the folder is an error
  • Each table file is at <schema>/<table>.yml, and its guide at <schema>/<table>/README.md. The Markdown files of a folder other than README.md are listed in that folder’s docs
  • The rows of texts are written as the text alone when there is one language and it is the dataset’s language, and as a map otherwise
  • What the tables do not hold is not written: primary keys, asset columns, an entity’s type (written foreign), dimensions, granularity, metrics, keys for other readers, and the other datasets the SQL reads, which come from dbt’s profiles.yml. A table that only --unlisted wrote gets no table file. sql and depends_on are written only for a table without source_path (one dbt does not build)

Writing Markdown for AI

tabledoc export --markdown <folder> checks the description as validate does, then writes it as Markdown: one file for the dataset, one for each schema and one for each table. AI assistants and people read the folder as it is, or it is published with the data’s documentation.

tabledoc export --markdown ai-docs/
File Content
README.md The dataset’s title, description, licenses, sources and the other keys of tabledoc.yml; its schemas and tables, each with a link and the first paragraph of its description; its documents; then its overview (README.md)
<schema>/README.md The schema’s title and description, its tables and documents, then its guide
<schema>/<table>/README.md The table’s title, description, primary key, licenses, sources, materialized; its columns (title, description, semantics, asset) and metrics as tables; the tables it reads and the tables that read it, as links when they are in the dataset; its SQL; its documents; then its guide
  • The files are at the paths in the dataset, and every other file of files (documents, images) is written at its path too, so the links in the guides keep working
  • A guide comes after a rule (---) as written. Its headings are not changed
  • Text written in more than one language is written in the dataset’s language (else the undetermined one, else the first by tag)
  • Keys for one reader (queria:, annotations:) are not written
  • Types, nullability and row counts are not in the description, so they are not written either
  • A file already in the folder is not replaced, and nothing is written, unless --force. Files of an earlier export that are no longer written stay in the folder
  • The folder also gets a .tabledoc-export file. tabledoc does not read the files under a folder that holds one, so the folder can be inside the root
  • On an error (with --strict, a warning) nothing is written

The older format

So that every dataset need not be rewritten at once, tabledoc also reads the format it replaces. Only tabledoc’s format is written anew.

  • Without tabledoc.yml, queria.yml (https://queria.io/schemas/dataset/v1.json) is read as tabledoc.yml. Its top-level title_kana, themes, cover and ai_context are read as if under queria:. catalogs is used for depends_on and returned as dependencies
  • Markdown whose front matter has $schema: https://queria.io/schemas/table/v1.json is read as a table file (the front matter) and its guide (the text below). table: says which table, fields is columns, and published, themes and ai_context are read as if under queria:. The guide is that Markdown’s path, and what is sent is the text without the front matter
  • A field’s semantic: {role, name, agg} is read as: role: entity → entity: {type: foreign, name}, or type: primary when the column alone is the primary_key; role: dimension → dimension: categorical; role: measure → a metric {name: <column>, agg, expr: <column>} on the table (without agg, a warning)
  • tabledoc.yml and queria.yml in the same folder is an error

$schema and versions

  • tabledoc ships its JSON Schemas and serves them at https://tabledoc.queria.io/schemas/<kind>/v<n>.json. The kinds are dataset and table
  • Adding keys keeps the version. Changing what a key means, or removing one, makes v2, and tabledoc reads both for a while
  • A file with a $schema this tabledoc does not know is refused, with a request to upgrade tabledoc

Was this page helpful?