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

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

StepWhat we doWhat it givesEffort
1 · Read what is declaredForeign keys in the database catalog; the vendor's published data model (Salesforce object relationships, SAP check tables)Exact, when presentCheap
2 · Read how people already joinViews, stored procedures, ETL code, report SQL, and the database's query historyThe joins the business really uses, including the rules inside themCheap to medium
3 · Propose candidatesMatching names and types (customer_id to customers.id); an AI model reading names, comments and sample valuesMany candidates, some wrongCheap
4 · Measure on the dataFor 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 orphansEvidence for or against each joinMedium; use samples and compact value sketches on big tables
5 · Confirm with the ownerShow the map with the measurements; the owner accepts, rejects or correctsAgreed joinsA meeting
6 · Write it down and test itRelationships in the data contract (ODCS supports them since 3.1), tested on every loadJoins that stay trueAutomatic after setup

Evidence

How much each signal is worth

SignalStrengthFalse-match risk
Declared foreign keyStrongLow, but the key may be declared and not enforced
Used in existing views, reports or query historyStrongLow; check the join conditions, not just the tables
Same name and type (order_id in both)MediumMedium: generic names (id, code, type) match everything
Most child values found in a unique parent columnStrongHigh for small numbers: ids 1 to 100 overlap with almost anything
AI model's suggestionWeak until measuredMedium: 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 chaseWhy we askWhat breaks in production if nobody asksDo this (not this)
JN · Learning the joinswhere relationships come from and how we prove them
JN1Does 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.
JN2Can 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.
JN3For 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.
JN4Who 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?

OptionVerdict
Declared keys and existing code first, then candidates measured on the data, then owner sign-offPursue · Pursue. Cheapest evidence first; nothing accepted unmeasured.
An AI model proposes joins and we use themDrop · Drop as a final answer. Keep it as a candidate generator only.
Ask the business to draw the model from memoryPursue 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.