Tables and joins

The diagram is the model. Tables are the datasets you have added, and the lines between them are joins.

Adding tables

Drag a dataset from the list on the left onto the canvas. It lands where you drop it, snapped to the grid and never on top of another table. Tick several and drag them together, or use Add to model in the selection bar.

Positions and zoom are remembered, so a model opens the way you left it.

A table is the dataset as it stands. There are no SQL-defined tables and no bridge tables to build: where a genuine many-to-many exists, the joining table is already in your source, and you add it like any other.

To take one out, select it and press Del. Nothing happens to the dataset, only to this model's description of it.

Finding joins

The sparkle button in the canvas toolbar controls detection. It is on to begin with, so Flitch looks for joins whenever a table arrives. Switching it on also scans what is already there, rather than only promising to look next time. The setting is per model and remembered in your browser, so it is yours rather than your team's.

Detection compares column names and foreign key patterns, then samples real values from both tables and tests how far they overlap. It is not a language model, and it costs no credits.

For warehouses, Flitch reads the real foreign keys out of the source schema instead of guessing. Those are authoritative and skip detection entirely.

The review queue

When something is waiting, a badge appears in the toolbar with the count. Open it to see each suggested join, preview it on the canvas, and accept or dismiss it. A dismissal is remembered: the same join will not be proposed again.

Only joins whose two tables are both on this canvas are offered, and reciprocals are folded together so you rule on a pair once rather than twice.

Drawing one yourself

Detection cannot see a join whose columns share no name and no values. Draw those by hand.

• Drag between two tables on the canvas. Flitch picks the likeliest pair of columns from their names and draws the join. Correct it in the panel if it guessed wrong.
• Use the join button in the toolbar when both tables are not on screen at once. Pick the two tables, then the columns.

A join you draw is part of the model straight away. It does not go to the review queue, because you are the one asserting it.

The join panel

Click a line to open it. Both tables are shown with their columns, and the join is the pair you have picked, one on each side. Primary keys are marked.

Under the columns, Flitch reports how well the values actually match: what share of one side's values are found in the other, in both directions. A join whose columns share a name and nothing else reads low here, which is usually the explanation for a chart that has quietly lost rows. Where a table was sampled rather than read whole, the panel says so.

Edits are held until you press Save, so trying two cardinalities costs nothing.

Only one join between a pair of tables can be active at a time. Switching one on stands the other down, otherwise the same rows get counted twice.

Deleting a join leaves both tables untouched. Anything that was filtering across it stops.

Which way filters travel

This is the setting that changes numbers, so it is worth understanding before you change it.

When someone filters a chart on one table, the filter can carry across a join to the other. Filtering decides whether it does.

Flitch sets it from the cardinality when the join is created:

Filtering the many side is deliberately a dead end. Narrowing a list of facts says nothing about which dimension rows exist, so the filter stops there.

Seeing the rows

The table button in the toolbar opens the selected table's rows underneath the canvas. It is a standing preference, so it stays open as you click from table to table.