DuckDB Already Queries Every File in a Folder, So Build the Part That Infers the Joins
Someone hands you a zip: customers.csv from the CRM, a JSON dump from the billing API, and a SQLite file from an internal app nobody has touched in two years. DuckDB will make all three queryable in a few lines of SQL, with no import step (SELECT * FROM 'customers.csv' works as written). Then you spend the afternoon working out which column joins to which. The CRM says customer_id and stores 00123. The billing API says customerId and stores 123. The SQLite table has a column called customer holding the text 123. Same customer, three spellings.
The first half of that afternoon is a solved problem. The second half is the part worth building: a tool that looks across every column in the folder, proposes which ones refer to the same thing, shows its evidence, and writes the confirmed joins down as views. DuckDB does the loading, profiling and verifying. The new code is the inference.
SQL over a folder is crowded, so leave it alone. DuckDB reads CSV and JSON with type inference, attaches SQLite databases, reads Parquet, and fetches remote files. clickhouse-local and octosql cover neighboring ground, and Steampipe does a similar trick over live APIs instead of saved files. None of them tells you how the tables relate. BI tools such as Power BI and Tableau will often guess a relationship from matching column names, which handles customer_id meeting customer_id and little else. The research is further along. Finding columns whose values are contained in another column is called inclusion dependency discovery, and algorithms such as SPIDER and BINDER exist for it. Little of that has reached a command-line tool you can point at a folder, though commercial catalog and BI products may already go further than name matching.
Profile, Sketch, Then Verify
Register every file as a DuckDB view: CSV and JSON through their readers, Parquet directly, SQLite files attached. API dumps (the kind a local response store piles up) nest, because an invoice holds an array of line items, so unnest arrays into child tables with a synthetic parent key first. That nesting is a relationship the tool knows for certain, and the flattened tables are what the inference sees. Then profile each column in one scan: type, null rate, distinct count (approx_count_distinct is HyperLogLog under the hood and good enough), uniqueness ratio, and value shape, meaning zero-padded digits, a constant prefix like CUS-, UUIDs and their case. It’s the same habit as counting how many records really carry a documented field before building on it: look at the data before you believe the schema.
Normalize before comparing. Trim whitespace, lowercase UUIDs and emails, strip leading zeros from digit strings, strip a prefix that every value shares. The tool tries each transform and keeps the one that raises overlap, and the proposal names it. Say a transform lifts overlap from a few percent to nearly all of it. That jump is the best evidence the tool will ever have, so show it.
Next comes a MinHash signature per column. It estimates how alike two sets of values are, as Jaccard similarity, and 128 hashes per column put the standard error of that estimate near four points at its worst. Foreign keys are lopsided, though. Every order’s customer should appear in the customers table, but the customers table holds plenty of ids no order mentions, so a perfect foreign key can have a low Jaccard score. What you want is containment, the share of one column’s distinct values found in the other. You can recover it from the Jaccard estimate J and the two distinct counts a and b: the overlap is J * (a + b) / (1 + J), and the containment of a in b is that divided by a. HyperLogLog and MinHash are both sketches, small summaries that trade exactness for a fixed size, and a sketch database is the longer treatment of that trade. Sketches only rank candidates, though. The top ones get re-checked with an exact anti-join in DuckDB, and the proposal quotes the exact figure.
Don’t fold everything into one magic score. Use gates first: types compatible after normalization, one side nearly unique (the key side), containment above a threshold. Survivors get ranked by containment and name similarity. Tokenize names so customerId, customer_id and customers all yield customer, and compare against the table name too, because orders.customer pointing at customers.customer_id is the pattern you’ll see most. Print the weights so nobody has to take the ranking on faith.
The output would look like this, on made-up data (relate is a placeholder for a tool that doesn’t exist yet):
$ relate ./export
5 sources loaded, 41 columns profiled
1 app.db: orders.customer -> customers.csv: customer_id score 0.96
containment 1,196 of 1,203 distinct values found (99.4%)
key side customer_id unique in all 1,240 rows
transform leading zeros stripped ("00123" matches 123)
names table "orders" + column "customer" ~ "customer_id"
orphans 7 values, all above the largest id in customers.csv
sample order 55120 -> customer 00123, country DE
2 billing.json: customerId -> customers.csv: customer_id score 0.94
containment 1,188 of 1,190 distinct values found (99.8%)
key side customer_id unique in all 1,240 rows
transform leading zeros stripped
skipped (see --why)
app.db: orders.status -> billing.json: state 4 distinct values per side, no key side
app.db: orders.id -> billing.json: invoiceId sequential ids overlap by construction
Each proposal carries its reasons. The orphans line says all seven unmatched customers sit above the largest id in customers.csv, which suggests a stale export, so the relationship stands and the score doesn’t drop.
False Positives Decide Whether Anyone Trusts It
A status column with four values is fully contained in any other column that uses the same four values. Ratings from 1 to 5, boolean flags and country codes do the same. Sequential ids are worse. orders.id runs from 1 to 9,000, so it contains every smaller integer in the folder, order_items.quantity included. Put a cardinality floor on both sides, and let small domains through only when the names agree, as in status_id into statuses.id. For sequential keys the data can’t settle it, so the name evidence has to carry the proposal.
Comparing every column with every other one sounds like the scaling problem, and for a folder it isn’t. A few hundred columns make tens of thousands of pairs, a comparison is a pass over 128 integers, and the type and key-ness gates remove most pairs before any signature gets read. LSH banding on the signatures is how you’d handle a data lake with a hundred thousand columns. It’s a version 0.2 problem at the earliest.
Dirty keys are the daily reality: trailing spaces, mixed case, a prefix on one side only, ids stored as floats (123.0) because a spreadsheet touched the file. Normalization handles the known forms, and the orphan list lets a human spot the next one. Composite keys multiply the candidate space, so v0.1 tests only the ones you name on the command line.
Explaining confidence is the other half. A score of 0.96 tells the reader nothing. Containment, uniqueness, the name match and the transform used tell them a lot, as do sample joined rows and sample orphans. Let people reject a proposal and record the rejection in the relationship file, so the next run doesn’t make the same suggestion. Following the ids between an API’s endpoints is this same inference problem inside a single API, where responses at least link to each other. For a folder of exports nothing links anything, so every proposal has to carry its own evidence.
What Version 0.1 Does
Confirmed relationships go in a small file, a list of from, to, transform and status entries, and the tool generates views from it. Use LEFT JOIN, so orphans show up as NULLs instead of vanishing from the result. Version 0.1 is a CLI over a folder with DuckDB underneath: profiling, MinHash containment, name similarity, proposals with samples, saved relationships, generated views. It refuses a GUI, writing back to any source, and fuzzy entity resolution (“Acme Inc” against “ACME Incorporated”), which is a different and much harder problem. If the joined data then has to move somewhere, a tiny ETL binary is the next tool in the chain.
DuckDB reads the files. Somebody still has to say what joins to what.