Check a join before combining two files

Does the join preserve what a row means?

A technically successful join can distort a total. The cause is often the grain: one file describes businesses, the other establishments or periods. Two occurrences on the left and three on the right produce six associations for one key. Check the join before calculating sums or rates.

1. Define grain and contract

   State what one row represents in each file, the period and expected cardinality: one-to-one or many-to-one. If the right file has multiple periods, add period to the key or filter it explicitly.

2. Preserve identifiers

   Load codes and references as text. Keep leading zeros, case and spaces unless a normalization rule is justified. A failed exact match requires investigation, not an automatic fuzzy merge.

3. Inspect keys

   Count empty keys, groups of repeated keys and unmatched rows. A repeated key may be legitimate at a finer grain. Decide whether to retain a version, aggregate or expand the key before deduplication.

4. Compare volumes

   Calculate expected inner and left join row counts. Then check counts and sums before and after; an increase is acceptable only if the grain change is intended and documented.

Check two CSV files

Calculated in your browser. Files and entered values are not sent to the server. UTF-8 CSV: maximum 2 MB and 10,000 data rows per file.

Exact case- and space-sensitive matching; empty keys do not match. Unmatched rows include empty keys. No merged file is produced.

Situations and decisions

Typical situations for preparing a check. They do not describe completed assignments or actual observations.

Record to retain

• File names and versions; grain, key and period on each side.
• Normalization rules, treatment of empty keys and duplicates.
• Before/after counts, unmatched rows and validation decision.

Official references

• W3C · Tabular Data Model
• PostgreSQL · Joined Tables

Related source profiles

• data.gouv.fr
• Avis de situation Sirene · Insee
• GLEIF — Global LEI Index

Related protocols

• Assess a public dataset before reuse
• Keep a reproducible version of public data
• Query a public API and retain a verifiable extract
• Compare public indicators without false equivalence

Checks before reaching a conclusion

• Is the right table unique on the key for a many-to-one relationship?
• Are unmatched rows examined before being treated as zeros?
• Is the total calculated at the same grain before and after?