Home › Learn › Data systems › Upstreams › Learning the joins
Data systems · upstreams, in depth · for data leaders, then builders
Learning the joins between datasets
Every join we build is declared, already used, or proven on the data, and confirmed by the owner.
How to find out how datasets join when nobody can tell you: read what's declared, harvest the joins already in use, propose candidates, measure them on the data, and get the owner to confirm.
For leaders
- Wrong joins are the quietest way to get wrong numbers: they multiply or drop rows and still look like correct output.
- Most sources don't declare their relationships, especially SaaS apps and older systems.
- Learn the joins cheapest first: declared keys and the vendor's model, then joins already used in reports and code, then candidates measured on the data.
- AI can propose joins; only measurements and the owner can confirm them.
In plain words
A family tree from old letters
Building a family tree, you start with what's written down: birth certificates and marriage records. Then the family's own stories. Then guesses from shared surnames and towns, which you check against dates before you write them in. A shared surname proves nothing on its own. Finding joins between datasets works the same way: written-down keys first, then the joins people already use, then guesses, and every guess checked against the data.
Smartly, cheapest first
Six steps to learn the joins
| Step | What we do | What it gives | Effort |
|---|---|---|---|
| 1 · Read what is declared | Foreign keys in the database catalog; the vendor's published data model (Salesforce object relationships, SAP check tables) | Exact, when present | Cheap |
| 2 · Read how people already join | Views, stored procedures, ETL code, report SQL, and the database's query history | The joins the business really uses, including the rules inside them | Cheap to medium |
| 3 · Propose candidates | Matching names and types (customer_id to customers.id); an AI model reading names, comments and sample values | Many candidates, some wrong | Cheap |
| 4 · Measure on the data | For each candidate: is the parent side unique, what share of child values exist in the parent, how many rows match on each side, how many orphans | Evidence for or against each join | Medium; use samples and compact value sketches on big tables |
| 5 · Confirm with the owner | Show the map with the measurements; the owner accepts, rejects or corrects | Agreed joins | A meeting |
| 6 · Write it down and test it | Relationships in the data contract (ODCS supports them since 3.1), tested on every load | Joins that stay true | Automatic after setup |
Evidence
How much each signal is worth
| Signal | Strength | False-match risk |
|---|---|---|
| Declared foreign key | Strong | Low, but the key may be declared and not enforced |
| Used in existing views, reports or query history | Strong | Low; check the join conditions, not just the tables |
| Same name and type (order_id in both) | Medium | Medium: generic names (id, code, type) match everything |
| Most child values found in a unique parent column | Strong | High for small numbers: ids 1 to 100 overlap with almost anything |
| AI model's suggestion | Weak until measured | Medium: plausible-sounding joins that the data doesn't support |
The measurement in step 4 is what turns a guess into a join. For big tables it runs on samples and compact summaries of each column's values, inside the source's environment, so only the measurements leave it. See question L2 on the Upstreams page.
Why it matters
Two ways a wrong join hurts
A join assumed to match one row that sometimes matches two multiplies numbers. A join to a parent that's missing drops rows from an inner join. Both look like correct output. The relationship questions I1 to I7 on the Upstreams page cover how many rows match on each side, orphans, timing and joins as of a past date.
The question list
Every question, why we ask, and what breaks if nobody does
| # | Question to chase | Why we ask | What breaks in production if nobody asks | Do this (not this) |
|---|---|---|---|---|
| JN · Learning the joinswhere relationships come from and how we prove them | ||||
| JN1 | Does the database declare foreign keys, and does the vendor publish a data model? | Declared relationships are the cheapest and most exact source. | Weeks spent guessing joins the catalog already held. | Read the catalog and the vendor's model first. Not: starting from sample data. |
| JN2 | Can we read the views, procedures, ETL code, report SQL and query history that use this data? | Existing joins carry the business rules too. | Our joins are 'correct' but don't match the numbers people trust. | Harvest join conditions from code and query history; ask about the odd ones. Not: ignoring the old system's logic. |
| JN3 | For each proposed join: is the parent unique, what share of child values match, and how many orphans are there? | A join is only real if the data agrees. | A join on small ids that happen to overlap produces confident nonsense. | Measure every candidate; require a unique parent and a high match rate; reject joins on generic low-number ids unless proven. Not: accepting a join because the names match. |
| JN4 | Who confirms each relationship, and where is it recorded? | Unconfirmed joins drift back into guesses. | Two teams join the same tables two ways and report two numbers. | The owner signs off; the relationship goes into the data contract and is tested on every load. Not: knowledge in one engineer's head. |
Design decisions
The decisions to take
How are relationships found?
| Option | Verdict |
|---|---|
| Declared keys and existing code first, then candidates measured on the data, then owner sign-off | Pursue · Pursue. Cheapest evidence first; nothing accepted unmeasured. |
| An AI model proposes joins and we use them | Drop · Drop as a final answer. Keep it as a candidate generator only. |
| Ask the business to draw the model from memory | Pursue when · Pursue only as a starting sketch to check against the data. |
FAQ
Questions people ask
How do you find relationships between tables when there are no foreign keys?
Read how people already join them (views, report SQL, ETL code, query history), propose candidates from names and types, then measure each on the data: the parent side must be unique, most child values must exist in it, and orphans must be few. The data owner confirms the result, and it is recorded in the data contract and tested every load.
Can AI find the joins between tables?
It is good at proposing candidates from names, comments and sample values, and poor at proving them. Treat its suggestions as candidates and accept only those the data measurements and the owner confirm.
Next
Where to go next
Related: relationships and joins and why we ask to scan your data on the Upstreams page.
Back up: Upstreams: know your sources, and the printable questionnaire.