Dovetail

Someone hands you a folder of files. Before you can ask anything of them you need two answers: how each file loads into DuckDB, and which columns join which tables. Is orders.customer_id really a key into customers, or do the numbers just happen to overlap?

Dovetail answers both. dovetail survey reads a file and prints the DuckDB SQL that loads it. dovetail relate reads a loaded database, finds the columns that look like foreign keys, and checks each one against the data before it calls it a key. Dovetail writes SQL and a descriptor for you to run and keep; it never runs them itself.

What Dovetail does

How does this file load? dovetail survey detects a file's format (CSV, TSV, Parquet, NDJSON or JSON) and how its rows are laid out, and prints the load. It prefers a standalone .sql load that DuckDB runs with nothing else installed, and says how confident it is. A detection it isn't confident of is surfaced for you to confirm rather than printed as if it were sure.

How do these tables relate? dovetail relate scores every candidate foreign key on how its name reads, how far its values overlap the parent column's, and how much the parent column looks like a key. Then it checks each candidate against the data:

• Accepted: every child value is found in the parent, and the parent's values are unique. Only these become constraints.
• Review: plausible, but not proven, such as a parent column that holds a duplicate. Dovetail prints it for you to decide.
• Rejected: a value with no parent row, or a coincidence such as two active columns that both hold true and false. Dovetail doesn't print these.

After the keys, relate says what each table seems to be for (an entity, an event, a reference list such as a lookup, a link between two tables, a snapshot over time, or unclear), with the evidence for each. Dovetail's table roles notes describe how it decides.

Dovetail decides, and you run what it decides: in DuckDB, or as the steps of an Arcform Protocol.

Installation

Build Dovetail from source with a recent Rust toolchain:

git clone https://github.com/meridian-online/dovetail
cd dovetail
cargo install --path crates/dovetail

The build compiles DuckDB into the binary, so the first build takes several minutes, and relate needs no separate DuckDB to read a database. The loads survey prints are run with your own duckdb.

Use it

We'll use five small tables, with values taken from Dovetail's relate fixture: customers, their orders, shipments of the orders, and products in categories. Two of them carry a flaw. One shipment names order 99, which doesn't exist, and categories lists id 3 twice. Make a new folder for them, shop, so they don't overwrite another page's files, and write them there:

mkdir -p shop && cd shop
cat > customers.csv <<'EOF'
id,name
1,Ada
2,Alan
3,Grace
4,Edsger
5,Barbara
EOF
cat > orders.csv <<'EOF'
id,customer_id,total
10,1,9.9
11,2,4.5
12,1,3.3
13,3,8.0
14,5,1.2
EOF
cat > shipments.csv <<'EOF'
id,order_id
100,10
101,11
102,99
EOF
cat > categories.csv <<'EOF'
id,name
1,tools
2,home
3,garden
3,garden-dup
EOF
cat > products.csv <<'EOF'
id,category_id,label
1,1,hammer
2,2,lamp
3,3,rake
4,1,wrench
EOF

Survey a file. Dovetail prints its verdict, then the load, then a Data Package descriptor of the file; sed keeps the first two:

dovetail survey customers.csv | sed -n 1,7p

customers.csv: rung=sql — Csv FlatTable detected at 85% confidence; loadable by DuckDB natively
-- Generated by dovetail survey
-- source: customers.csv
-- format: Csv  structure: FlatTable  rung: sql

CREATE OR REPLACE TABLE "customers" AS
SELECT * FROM read_csv('customers.csv', header = true, delim = ',');

dovetail survey customers.csv orders.csv shipments.csv categories.csv products.csv prints the same for each file. Run the five loads it prints in DuckDB, opened on a new database with duckdb shop.db in the same folder:

CREATE OR REPLACE TABLE "customers" AS
SELECT * FROM read_csv('customers.csv', header = true, delim = ',');
CREATE OR REPLACE TABLE "orders" AS
SELECT * FROM read_csv('orders.csv', header = true, delim = ',');
CREATE OR REPLACE TABLE "shipments" AS
SELECT * FROM read_csv('shipments.csv', header = true, delim = ',');
CREATE OR REPLACE TABLE "categories" AS
SELECT * FROM read_csv('categories.csv', header = true, delim = ',');
CREATE OR REPLACE TABLE "products" AS
SELECT * FROM read_csv('products.csv', header = true, delim = ',');

Quit DuckDB, then point relate at the database:

dovetail relate shop.db

ACCEPT  orders.customer_id -> customers.id  (verified: 0 orphans, parent is unique, confidence 0.95 ≥ 0.60)
REVIEW  products.category_id -> categories.id  (plausible (parent not provably unique); surfaced for review — confidence 0.85)

dovetail relate: 1 accepted, 1 to review

-- Generated by dovetail relate — accepted foreign keys
ALTER TABLE "orders" ADD CONSTRAINT "fk_orders_customer_id" FOREIGN KEY ("customer_id") REFERENCES "customers" ("id");

typing: floor: DOVETAIL_FINETYPE_EXTENSION is not set; expected the path of FineType v0.6.64's release .duckdb_extension
entity    orders      0.75, next event 0.00: id unique across 5 rows
reference customers   1.00, next entity 0.80: id unique across 5 rows; pointed at by orders
unclear   categories  entity 0.35, next event 0.00: name unique across 4 rows
unclear   products    entity 0.35, next event 0.00: id unique across 4 rows
unclear   shipments   entity 0.35, next event 0.00: id unique across 3 rows
fold customers into orders as customer, along orders.customer_id -> customers.id

Each of the three candidates came out differently:

• orders.customer_id → customers.id is accepted: every order's customer is in customers, and customers.id is unique. It's the only one written as an ALTER TABLE constraint.
• products.category_id → categories.id is for review: every product's category exists, but categories.id holds 3 twice, so it can't be proven a key.
• shipments.order_id → orders.id is rejected, because order 99 has no row, and isn't printed.

The typing: line says the table roles beneath it were worked out without Finetype's DuckDB extension, on Dovetail's fallback that needs no model; the table roles notes say how to point it at the extension. Of the five tables, it reads orders as an entity and customers as a reference list that orders points at, and suggests folding customers into orders; the other three it calls unclear.

Commands

Each command below runs in the folder holding shop.db, after the steps above. dovetail --help lists them, and dovetail <command> --help gives a command's options.

dovetail survey <paths...> detects each file's format and row structure and prints its load and descriptor, as under Use it.

dovetail relate <db> finds and checks the foreign keys in a DuckDB database. --out also writes a datapackage.json that records each accepted key, and each key for review, in its table's schema, with its evidence, confidence and status. It prints the report above, and one more line:

dovetail relate shop.db --out datapackage.json
# → dovetail relate: wrote descriptor → datapackage.json

dovetail jaq runs a jq program with the copy of jaq built into Dovetail, and prints each result as a line of JSON. Here it lists the keys --out wrote, with their status; a key for review is recorded as suggested:

dovetail jaq '.resources[].schema.foreignKeys[]? | {fields, reference, status: ."x-dovetailStatus"}' datapackage.json

{"fields":["customer_id"],"reference":{"resource":"customers","fields":["id"]},"status":"accepted"}
{"fields":["category_id"],"reference":{"resource":"categories","fields":["id"]},"status":"suggested"}

dovetail model writes an Ossie semantic model of a loaded database: its tables and their fields, the joins between them, what each table is for and the evidence behind each decision. The sample pass proposes the model from a sample of each table; the full pass checks a proposal against every row. Dovetail's model notes describe the file.

dovetail model --db shop.db --pass sample --out shop.ossie.yaml
# → dovetail model: typing fell back to the floor: DOVETAIL_FINETYPE_EXTENSION is not set; expected the path of FineType v0.6.64's release .duckdb_extension

Where to go next

• Arcform: runs a pipeline of loads and SQL steps from one file, such as the loads Dovetail prints.
• Finetype: names the type of each column a file holds.
• Dovetail's relate fixture holds more cases than this page's five tables, each with the outcome it should get.

Dovetail's source is on GitHub.