How relationships are inferred
A lakehouse has no declared keys, so names are all there is — and every result says which rule produced it, so you can overrule it. Declared keys and foreign keys (warehouses) always win. A column that could point at two tables is reported, never guessed; a column no rule matches is left unlinked and you can pass it as confirmed. The example on /check shows the rules on a synthetic schema. The rules, in the engine's own words:
| Rule | Applies to | Means |
|---|---|---|
| K0 | keys | declared primary key |
| K1 | keys | keys override from the caller |
| K2 | keys | a column named id, no, nr, <table>_id, <table>id, <singular>_id, <singular>id, <table>_key, <table>_code, <table>_nr, <table>_no, <table>_sk, <table>_bk, or an abbreviation of the table name (four letters or more) with one of those suffixes (<table>snr too: the Swedish genitive, Leverantorsnr in Leverantor); a leading dim_/fact_/tbl_ is not part of the table's name; when nothing matches literally, the same through the synonym table (KundNr in kunder, cust_id in dim_customer), reported under synonyms |
| K3 | keys | nothing — the table gets no key; it can be queried but not by pk, and nothing can point at it |
| K4 | keys | junction: no column names a key and the table starts with two or more reference columns; together they are the key (not applied to history tables with a start_/valid_from/effective_ column) |
| K5 | keys | line: no column names a key; the reference to the header table plus a line-number column (line_no, position, seq…) |
| R0 | relationships | declared foreign key |
| R1 | relationships | column named <B>_id / <B>id / <singular B>_id / <singular B>id (also _key, _code, _nr, _no, _sk, _bk) where B has a single key |
| R2 | relationships | column has exactly the same name as B's single key column, and that name is not just id |
| R3 | relationships | all of B's composite key columns appear in A |
| R4 | relationships | weaker name match when R1 and R2 find nothing: the R1/R2 shapes through the synonym table (Kunde_ID → Kunden, cust_id → customers, noted on the relationship), a role before B's name (manager_staff_id → staff), or an abbreviation of B of four letters or more (o_custkey → customer); never across suffixes (wwi_order_id does not point at a table keyed by order_key) |
| R5 | relationships | checked against the rows: a code column <stem>_code / <stem>_no / <stem>_key where a table with the same stem (singular/plural, snake/camel) has the key code/no/key, or a role name (ship_to_id, created_by, approved_by) whose role word says what the table holds; planned only when every value is in that table's key, the type family is the same, there are two or more distinct values and one table qualifies, otherwise proposed with the reason |
Names are matched literally first. When nothing matches, a small documented synonym table is tried: abbreviations (cust, prod, ord, emp, dept, acct, …), Swedish and German business words in singular and plural (kund/kunder, artikel/artiklar, leverantör, kunde/kunden, bestellung, rechnung, mitarbeiter, …), after transliteration of å ä ö ü ß; a leading dim_, fact_ or tbl_ is not part of a table's name, and _sk/_bk count as key suffixes. Every match that rests on the table is listed under synonyms in plan_backend and on the relationship's note, and counts as the weakest rule (R4), so the plan never hides that it translated a name. Measured on eleven schemas in docs/INFERENCE.md; the five public schemas that need no synonyms are unchanged to the number.
More on how they interact, with examples: https://github.com/Krusmaster/fabric-backend#how-the-relationships-are-inferred