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
tabledoc validate: check the author’s filestabledoc load: check them and write the description into a DuckLake catalog (Writing into a DuckLake catalog)tabledoc dump: write the files of the description a catalog holds (Writing the files from a catalog)tabledoc export --markdown: check them and write them as Markdown, one file per table (Writing Markdown for AI)
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>.ymldescribes<schema>.<table>. A file directly under the root is in themainschema 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 outsidemodels:
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 thetitle,urlandpathof 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’sname,language,$schema,issued,update_frequency,dimension,granularity,agg,expr,sql,depends_on,spdx,id,roles,spatial_coverage,temporal_coverage, and the paths of guides anddocs. Written in other languages, they would no longer point at the same table, column or file - Keys under
queria:andannotations: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,
descriptionis the text alone, inlanguage. dbt does not take a map there tabledoc.readreturns these values as maps always: the text alone becomes{<language>: text}, and{und: text}withoutlanguage
Guides and documents
- A table’s guide is the
.mdwith the same name in the same folder as its table file (series.mdforseries.yml), orREADME.mdin the folder of that name (series/README.md). It has no front matter - The dataset’s overview is
README.mdat the root. A schema’s guide isschemas[].guide, else<schema>/README.md - Other documents are listed in
docs: the dataset’s intabledoc.yml, a schema’s inschemas[].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 indocs
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
sha256andbytesare 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,vieworincremental, under the model’sschemaandalias. 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.
descriptionand the columns’descriptionas they are; keys dbt does not have (title,primary_key,sources, a column’stitle,assetand so on) andqueriaunderconfig.meta - Semantics come from the columns’
entity,dimensionandgranularity, and from the semantic layer’s simplemetrics materializedis the model’s materialization,source_pathitsoriginal_file_path, andsqlitscompiled_code.depends_onis 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 inprofiles.yml(--profiles, elseDBT_PROFILES_DIR, else the root)- A guide is the
.mdwith the same name next to the model’s.sql, orREADME.mdin the folder of that name (dbt ignores.mdfiles without a docs block). Other documents aredocsunderconfig.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
sqlcomes 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
sourceis the Git working copy:repositoryis theoriginremote as an https URL (without credentials or.git),commitis HEAD,pathgoes from the repository’s root to the folder oftabledoc.yml, anddirtysays whether there were changes not committed. It is None outside Git, or withOptions(with_source=False)- Documents are referred to by their paths in the dataset.
fileslists 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, andlocal, its path under the root.guide,docs,schemas[].guideandschemas[].docsare paths infiles.contentgives 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: categoricalis{"type": "categorical"}) - Tables are sorted by
schema, thenname, 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 folderfdl clonenoted beside the catalog). Data files over HTTP are read with DuckDB’s secrets; when the catalog is fdl’s working metadata andFDL_TOKENis set, the token is sent to the remotefdl clonenoted, so a private dataset’s files can be read--schema: the schema to write into (default:schemaintabledoc.yml, else_tabledoc). Queria reads_queria: give--schema _queria(orschema: _queria) for a dataset published on Queria--out: also write the folderfdl commit --assetstakes: each file of thefilestable underfiles/<path>. With--assets <folder>, the files below it (those of the build’s asset columns) are linked underdata/. The folder has to be empty or written by an earlierload, and outside.fdl/, which only fdl writes--unlisted: the catalog’s tables and views that have no description get a row intableswithpublishedfalse, 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 tablesdataset,schemas,tables,columnsandfiles(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
assetcolumn that is notvarchar. Names match without regard to case, as in DuckDB, and are written as the catalog spells them - A value its column cannot hold (
issuedother thanYYYY-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’stitle, say), they hold the value inlanguage, and tabledoc warns topics(orkeywords, its earlier name) unlessqueria:hastopics.themesunderqueria:is not writtenlicensesis a list of{id, spdx, url, title, commercial_use, share_alike, attribution_required}; a license written as a string is{id}source_pathis the path of the file that defines the table, from the root ofrepository(dbt’soriginal_file_path, under the folder oftabledoc.ymlin the Git working copy), andsqlis 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
sqlordepends_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.sha256holds the sha256 of each, andfdl commit --assetsuploads 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
sqlin the description), each table keeps thesql,depends_onandsource_paththe catalog holds - Primary keys and asset columns are not written into these tables. When the catalog is the working metadata
fdl clonemade (it noted<catalog>.fdl.jsonbeside it),loaddeclares them to FDL withfdl declare, which has to be onPATH(fdl-cli 0.34 or later): each table’sprimary_keyand the asset columns of each table whose columns sayasset(a view has neither), no profile for each table and view whosepublishedis false and a profile for each other table described, the other datasets the SQL reads (from dbt’sprofiles.yml) as dependencies, and the keys of these tables withfiles.sha256as an asset column and no profile. It runs only what differs fromfdl 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 asschemaintabledoc.yml, soloadwrites back into it.--out: the folder (default: here), outside.fdl/--files: where the files of thefilestable are, as<files>/<sha256>: a folder or an http(s) URL. Default:assets/of the repositoryfdl clonenoted beside the catalog. Each file is checked against its sha256--name: the dataset’snameintabledoc.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 thanREADME.mdare listed in that folder’sdocs - The rows of
textsare written as the text alone when there is one language and it is the dataset’slanguage, 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’sprofiles.yml. A table that only--unlistedwrote gets no table file.sqlanddepends_onare written only for a table withoutsource_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-exportfile. 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 astabledoc.yml. Its top-leveltitle_kana,themes,coverandai_contextare read as if underqueria:.catalogsis used fordepends_onand returned asdependencies - Markdown whose front matter has
$schema: https://queria.io/schemas/table/v1.jsonis read as a table file (the front matter) and its guide (the text below).table:says which table,fieldsiscolumns, andpublished,themesandai_contextare read as if underqueria:. 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}, ortype: primarywhen the column alone is theprimary_key;role: dimension→dimension: categorical;role: measure→ a metric{name: <column>, agg, expr: <column>}on the table (withoutagg, a warning) tabledoc.ymlandqueria.ymlin 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 aredatasetandtable - 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
$schemathis tabledoc does not know is refused, with a request to upgrade tabledoc