All five engines' fetch_schema run the same query: information_schema.columns, five fields — schema, table, column, data type, column comment. No primary keys, no foreign keys, no unique constraints, no cardinality. So in explore_mappings's prompt a wide table is a bare list of names and types, and whether item_id is a key or a quantity is a guess from the column name.
Two consequences:
- The prompt asks for metrics (aggregatable,
sum/count/avg) and dimensions (group-by columns). A join key is neither, and definition's four fields — table / expr / sql / unit — have nowhere to record "this key points at that table". A key can only surface by accident, as a dimension, with its role written down as group-by.
- An object whose fields live across several tables or databases cannot be expressed at all: nothing says what two tables align on. Same-concept-different-source is already allowed by
UNIQUE (kb_id, concept_id, source), but 0011 meant that as two alternative definitions of one number, not as two halves of one object.
What to add
Keys. A fetch_keys() on the QueryEngine trait, default empty. Postgres joins table_constraints + key_column_usage + constraint_column_usage; MySQL is cheaper, since key_column_usage carries referenced_table_name / referenced_column_name directly. Trino's information_schema has no constraint tables and returns nothing; Snowflake and Databricks do expose constraints, but theirs are informational and frequently absent. Returning nothing must degrade to today's behaviour rather than fail.
Sampling and cardinality. This is the half that works where keys do not exist — and a wide table is exactly that source, because it is wide precisely because the joins were flattened away. Per table: a few sample rows, and a distinct count for candidate columns. item_id at 40k distinct of 41k rows against item_count in the low tens separates a key from a quantity better than any name heuristic, and it also catches the trap columns (an integer store_id sums cleanly and means nothing). Runs through the existing read-only gate and ROW_CAP.
The prompt then carries lines like orders.item_id → items.id and item_id: 40k distinct of 41k rows, e.g. A1002, A1003, and definition gains a slot for the key.
Net gain for today's human flow, independent of any switch, and a prerequisite for automation: measure before and after with #501.
All five engines'
fetch_schemarun the same query:information_schema.columns, five fields — schema, table, column, data type, column comment. No primary keys, no foreign keys, no unique constraints, no cardinality. So inexplore_mappings's prompt a wide table is a bare list of names and types, and whetheritem_idis a key or a quantity is a guess from the column name.Two consequences:
sum/count/avg) and dimensions (group-by columns). A join key is neither, anddefinition's four fields —table / expr / sql / unit— have nowhere to record "this key points at that table". A key can only surface by accident, as a dimension, with its role written down as group-by.UNIQUE (kb_id, concept_id, source), but 0011 meant that as two alternative definitions of one number, not as two halves of one object.What to add
Keys. A
fetch_keys()on theQueryEnginetrait, default empty. Postgres joinstable_constraints+key_column_usage+constraint_column_usage; MySQL is cheaper, sincekey_column_usagecarriesreferenced_table_name/referenced_column_namedirectly. Trino'sinformation_schemahas no constraint tables and returns nothing; Snowflake and Databricks do expose constraints, but theirs are informational and frequently absent. Returning nothing must degrade to today's behaviour rather than fail.Sampling and cardinality. This is the half that works where keys do not exist — and a wide table is exactly that source, because it is wide precisely because the joins were flattened away. Per table: a few sample rows, and a distinct count for candidate columns.
item_idat 40k distinct of 41k rows againstitem_countin the low tens separates a key from a quantity better than any name heuristic, and it also catches the trap columns (an integerstore_idsums cleanly and means nothing). Runs through the existing read-only gate andROW_CAP.The prompt then carries lines like
orders.item_id → items.idanditem_id: 40k distinct of 41k rows, e.g. A1002, A1003, anddefinitiongains a slot for the key.Net gain for today's human flow, independent of any switch, and a prerequisite for automation: measure before and after with #501.