tycoon data query / schema / clean¶
Three lightweight commands for inspecting and tearing down the warehouse without spinning up dbt or Rill.
query — read-only SQL¶
Runs a SQL query against the warehouse and prints the result as a Rich table, or as CSV, JSON or Markdown with --format.
Synopsis¶
tycoon data query SQL [OPTIONS]
Arguments:
SQL SQL query to execute
Options:
--db PATH Override which DuckDB to query. Default: warehouse
--allow-writes On MotherDuck, run statements other than read-only queries
--source NAME Query a source's raw schema (resolves to data/raw_<name>.duckdb
or main raw.duckdb's raw_<name> schema)
-f, --format table (default), csv, json or markdown
-h, --help Show this message and exit
Examples¶
# Default — query the warehouse (data/warehouse.duckdb)
tycoon data query "SELECT count(*) FROM mart.fct_orders"
# Query the raw layer for a specific source
tycoon data query --source github "SELECT * FROM raw_github.issues LIMIT 5"
# Query the metadata DB (observability)
tycoon data query --db .tycoon/metadata.duckdb \
"SELECT command, started_at, success FROM dbt_runs ORDER BY started_at DESC LIMIT 5"
# Query a synced snapshot
tycoon data query --db ./snap.duckdb "SHOW ALL TABLES"
# Rows only, for a script or notebook
tycoon data query --format csv "SELECT * FROM mart.fct_orders" > orders.csv
tycoon data query --format json "SELECT * FROM mart.fct_orders LIMIT 5" | jq '.[0]'
Output formats¶
--format (or -f) picks how the rows come back:
| Format | Output |
|---|---|
table |
The default. A Rich table with a title and a N row(s) returned line. |
csv |
A header line, then one line per row. NULL is an empty field. LIST, STRUCT and MAP values use Python's text form, such as [1, 2] and {'a': 1}. |
json |
A JSON array with one object per row, keyed by column name. When two columns share a name, as in SELECT a.id, b.id, the second key becomes id_1. NULL is null, DECIMAL values are numbers, LIST, STRUCT and MAP come out as nested JSON, and dates, timestamps and other types are strings. A DOUBLE that is NaN or infinite is null, since strict JSON parsers such as JSON.parse reject the bare NaN Python would otherwise write. |
markdown |
A GitHub-flavored Markdown table, ready to paste into an issue or a doc. Nested values use the same text form as csv. |
In all three, a BLOB is its hex digits (dead for the bytes DE AD) and an INTERVAL is an ISO 8601 duration such as P1DT2H30M.
csv, json and markdown print the rows and nothing else, so the output can go straight into a file or another tool. Errors still go to stderr. The format name is not case-sensitive.
In table format, a result too wide for the terminal (one where some column couldn't show its full name) prints one block per row instead, with each column name beside its value, like psql's expanded display. A result that fits keeps the usual column layout. An empty result that is too wide lists the column names one per line, so you can still see what the query would have returned.
Live warehouse via Quack (v0.1.9)¶
When a Quack server is holding the warehouse — i.e. tycoon start is running — a plain warehouse query (no --db / --source / --raw) attaches over Quack instead of opening the file. You'll see the result labelled warehouse (Quack). This is what lets you query the live warehouse while the rest of the stack is up, rather than hitting DuckDB's single-writer file lock. When no server is running, the command opens the file directly as before. It's automatic — there's no flag.
--source is a convenience for the common case of "show me the raw landed data". It walks tycoon's path resolution:
config.raw_dbif it contains theraw_<name>schema (single-DB mode — current default)data/raw_<name>.duckdbif the per-source file exists- Any other
data/*.duckdbwhose name matches the schema
Limitations¶
- DuckDB / MotherDuck only. Snowflake / BigQuery / Redshift warehouses can be registered, but
querydoesn't dispatch to them yet. Carried forward as a known limitation. - Read-only by default. A local file is opened with
read_only=True. Usetycoon run duckdb ...for ad-hoc writes. - MotherDuck needs
--allow-writesto write. DuckDB cannot open anmd:database read-only, so tycoon checks each statement first and runs only SELECT, WITH,FROM tbl-first queries,TABLE tbl, VALUES, set operations, DESCRIBE, SHOW, SUMMARIZE, PIVOT, UNPIVOT and EXPLAIN of those. Anything else, a statement tycoon cannot parse (including a query nested too deeply, or a schema-qualifiedTABLE s.t; writeSELECT * FROM s.t), or any statement in a multi-statement input exits 1 before connecting, withRefusing to run DROP against MotherDuck (md:x)and the flag to pass. - The MotherDuck check is not a permission boundary. It reads statement types only and does not inspect function calls inside a SELECT, so
SELECT nextval('s')advances a sequence,read_csvreads a path or URL, andFROM checkpoint()runs, all without--allow-writes. It guards against accidental statements. Scope your token if you need more than that. - A SQL comment cannot start the argument.
tycoon data query "-- note\nSELECT 1"is read as an option; put--before it:tycoon data query -- "-- note\nSELECT 1".
schema — dump tables, row counts, sizes¶
tycoon data schema # raw database, warehouse, other data/*.duckdb files
tycoon data schema --warehouse # the warehouse only
tycoon data schema --schema main_marts # one schema
tycoon data schema --raw --schema 'raw_*' # schemas matching a glob, in the raw database
tycoon data schema --include-dlt # also list dlt's _dlt_* tables
tycoon data schema --exact # exact counts on a MotherDuck database
Lists the tables in the raw database and the warehouse, with row counts and each DuckDB file's size. Any other .duckdb file in data/ is listed too. Useful for "what's in this DB?" and "which tables have rows?" without writing SQL.
On a MotherDuck database (database.warehouse: md:<name>), row counts are estimates read from the catalog, printed as ~1,000 rows (estimate), because an exact count(*) per table is slow and spends compute. A view has no estimate and prints view (use --exact to count); a table whose catalog reports no estimate prints no estimate (use --exact to count). Pass --exact to count every table and view. Local DuckDB files always show exact counts.
The estimate does not drop after deletes. It comes from DuckDB's table statistics: a table that held 100,000 rows and lost half of them still shows ~100,000 rows (estimate), where --exact shows 50,000 rows. Use --exact when the figure matters.
Synopsis¶
tycoon data schema [OPTIONS]
Options:
--schema TEXT Only show schemas matching this name or glob
--raw Only show the raw database
--warehouse Only show the warehouse
--include-dlt Also show dlt's _dlt_* bookkeeping tables
--exact Count rows exactly on a MotherDuck database instead of estimating
-h, --help Show this message and exit
Narrowing the output¶
--rawand--warehouseeach pick one database, the same split asdata query --raw. Passing either one leaves out the otherdata/*.duckdbfiles. Pass both to see the raw database and the warehouse without those extra files.--schematakes a schema name or a shell-style glob (*,?,[...]). Matching ignores case. Quote a glob so your shell doesn't expand it. If nothing matches, the command says so.- dlt keeps bookkeeping tables such as
_dlt_loadsand_dlt_versionin every dataset it loads. They're left out by default, and theTablesline says how many were hidden.--include-dltlists them.
On a MotherDuck database, --exact runs one count(*) query per listed table, and tables left out by these options aren't counted, so a narrow --schema keeps --exact cheap. With --raw or --warehouse, only the database shown needs a MotherDuck token.
clean — remove warehouse files¶
tycoon data clean # default: removes data/*.duckdb except metadata
tycoon data clean --all # also wipes scaffolded files (rill/, dbt target/)
tycoon data clean --metadata # explicitly remove .tycoon/metadata.duckdb
Synopsis¶
tycoon data clean [OPTIONS]
Options:
--all Also remove rill/tmp, dbt target/, dbt_packages/, logs/
--metadata Remove .tycoon/metadata.duckdb (preserved by default, even with --all)
-h, --help Show this message and exit
What gets cleaned by default¶
data/raw.duckdb,data/warehouse.duckdb, and any otherdata/*.duckdbfiles- Per-source raw DuckDBs (
data/raw_*.duckdb) - Parquet exports under
data/parquet/
What's preserved¶
.tycoon/metadata.duckdb— observability run history. Pass--metadatato remove explicitly.tycoon.yml— your config.dbt_project/source files — onlytarget/anddbt_packages/are removed (and only with--all).rill/source files — onlyrill/tmp/is removed (and only with--all).
The --metadata carve-out exists because routine tycoon data clean --all cycles shouldn't nuke run history; you usually want to wipe data without losing the "what changed yesterday" trail.
Why preserve metadata.duckdb?¶
It's the source of truth for tycoon data history, the auto-generated Rill dashboards (_tycoon_dlt_usage / _tycoon_dbt_usage), and the schema-diff captures from v0.1.3. Re-ingesting after clean will append new rows to the existing tables — tycoon data history keeps showing yesterday's runs alongside today's.
If you genuinely want a fresh start: tycoon data clean --all --metadata.
Related¶
tycoon data history— terminal view overmetadata.duckdbtycoon data sync— pull a fresh snapshot from a cloud warehouse- Reference: tycoon.yml
databaseblock