Link two public CSV tables—and find the records that do not match

Suppose you publish two files: publishers.csv contains one row per publisher, while articles.csv contains many articles per publisher. Keep those subjects in separate tables, connect them with publisher_id, and test the connection before publishing.

A CSV file does not itself declare column types, uniqueness or relationships. Those rules must live in documentation, validation code or separate metadata. The W3C’s CSV on the Web primer describes a reference from a column in one CSV to a column in another as a foreign key and provides a standard metadata format for expressing it (W3C).

Start with two keyed tables

Here is a small sample dataset.

publishers.csv:

publisher_id,publisher_name
P001,North Press
P002,City Desk
P003,Open Research Lab

articles.csv:

article_id,publisher_id,title
A101,P001,Transit survey results
A102,P002,Annual budget data
A103,P999,Park usage counts
A104,,Regional population

The relationship is:

articles.publisher_id -> publishers.publisher_id

publishers.publisher_id is the parent key. It must identify one and only one publisher. articles.publisher_id is the foreign key; repeating it is normal because one publisher can have many articles. Before defining the relationship, test the parent key for blanks and duplicates. The target key should also be stable across releases; see Primary Keys for Published CSV Datasets.

The sample has two different problems:

• A103 contains P999, which does not exist in the parent table. This is an orphan record.
• A104 has no publisher_id. This is a missing foreign key, not an orphan. It is invalid only if the relationship is required.

Do not silently combine those categories. A blank might be allowed for an article with no applicable publisher, while an unmatched nonblank value usually indicates a stale code, typo or missing parent row.

Validate the keys before joining

DuckDB can query CSV files directly, and its read_csv function supports all_varchar = true to skip type detection and treat every column as text (DuckDB CSV documentation). That prevents an identifier such as 0017 from being inferred as a number during this import.

Load the files as reusable query relations:

CREATE OR REPLACE VIEW publishers AS
SELECT *
FROM read_csv('publishers.csv', all_varchar = true);

CREATE OR REPLACE VIEW articles AS
SELECT *
FROM read_csv('articles.csv', all_varchar = true);

Check the parent key first:

SELECT
    publisher_id,
    COUNT(*) AS row_count
FROM publishers
GROUP BY publisher_id
HAVING publisher_id IS NULL
    OR TRIM(publisher_id) = ''
    OR COUNT(*) > 1;

A clean parent table returns no rows. If it returns duplicates, do not proceed directly to a join: every matching child row can be multiplied by the number of duplicate parent rows.

Also check whether the child key is blank:

SELECT *
FROM articles
WHERE publisher_id IS NULL
   OR TRIM(publisher_id) = '';

Decide from the data definition whether to fill, reject or retain those rows. Do not convert an unknown publisher to a made-up code.

Detect orphan records with an anti join

An anti join returns rows from the left table that have no match in the right table. DuckDB documents this behavior explicitly and notes that an anti join never produces more rows than its left input (DuckDB join documentation).

SELECT c.*
FROM articles AS c
ANTI JOIN publishers AS p
    ON c.publisher_id = p.publisher_id
WHERE NULLIF(TRIM(c.publisher_id), '') IS NOT NULL
ORDER BY c.article_id;

For the sample, the result is:

article_id	publisher_id	title
A103	P999	Park usage counts

The WHERE clause excludes missing keys so the output is specifically an orphan report. Investigate each orphan against the source system: correct the child value, add a genuinely omitted parent row, or leave it unresolved and disclose it. Changing case or trimming identifiers during a join can hide source-quality defects, so begin with exact matching and normalize only under a documented rule.

To find the reverse condition—publishers that have no articles—swap the tables:

SELECT p.*
FROM publishers AS p
ANTI JOIN articles AS c
    ON p.publisher_id = c.publisher_id;

Those rows are not necessarily errors. A parent can legitimately have no children.

Create a joined extract only when readers need one

After resolving key errors, enrich the child rows with parent labels:

SELECT
    c.article_id,
    c.title,
    c.publisher_id,
    p.publisher_name
FROM articles AS c
JOIN publishers AS p
    ON c.publisher_id = p.publisher_id
ORDER BY c.article_id;

An inner join excludes unresolved and blank foreign keys. If those rows must remain visible, use a left join and retain a status column that distinguishes matched, missing_key and orphan.

Publishing the original tables preserves their distinct subjects and avoids repeating publisher details on every article. If readers need a single filterable table, publish the joined result as a third, derived CSV. Label it as derived and record the source filenames, release dates, join columns and generation date. That makes the extract reproducible rather than presenting it as an independent source.

The current TablePage homepage says it accepts CSV, TSV, XLSX and XLS uploads and generates public dataset pages with shareable links and filterable tables (TablePage). It does not describe relational foreign-key enforcement or joins across dataset pages. Prepare and validate the relationship before upload; publish separate pages or a derived joined file rather than assuming the publishing layer will perform the join.

Document the relationship beside the files

At minimum, include this in the data dictionary or methodology note:

Item	Value
Parent table	publishers.csv
Parent key	publisher_id
Child table	articles.csv
Foreign key	publisher_id
Cardinality	One publisher to many articles
Missing child keys	State whether permitted
Matching rule	Exact text match
Validation result	Count of blanks, duplicate parents and orphans

For machine-readable documentation, CSV on the Web metadata supports foreignKeys, including references made from one or several child columns to columns in another table. Its validation model requires each foreign-key value combination to identify a unique referenced row (W3C metadata specification). Composite relationships therefore use all key columns together—for example, agency_id plus reporting_year—in both duplicate checks and join conditions.

Run the parent-key, missing-key and orphan checks for every release. A relationship that passed last month can fail when one file updates before the other, an identifier changes format or a parent row disappears.