The explorer cannot tell a key from a number

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.

Schema columns carry primary-key, foreign-key and nullability flags (d…

Schema columns carry PK/FK/nullable on MySQL engines (deeplethe#502 c…

Schema columns carry primary and foreign keys (#671)

Schema columns carry PK/FK on MySQL/MariaDB (deeplethe#502 cut 2)

Schema columns carry PK/FK on MySQL/MariaDB (#678)

@rollroyces

@rollroyces @WaylandYang Is anyone on the second half of this, the sampling and cardinality? The keys half landed as #671 and #678 (#682 was closed for Trino), and both exploration prompts now mark [PK] and [FK→…]. If no one is, I'd like to take it. This is the scope I have in mind.

Reading. Each table that makes it into the prompt gets one statement, SELECT * FROM <schema>.<table>, with identifiers quoted per engine. It goes through the path query_data uses: guard_sql_for, then the engine's execute, which applies its statement timeout and row cap. There is no new trait method, and the same statement runs on all five engines. From the rows that come back, for each column: how many distinct values the sample holds, and up to two examples, each cut short.

The prompt. The column line carries this after the key markers and before the comment, and the legend line says what it means. On wide, the first 200 rows give:

  buyer_id bigint {193 of 200 distinct, e.g. 357, 607}
  qty integer {5 of 200 distinct, e.g. 1, 4}
  ver smallint {1 of 200 distinct: 1}
  price_old bigint {all 200 NULL}
  amt_pay bigint {200 of 200 distinct, e.g. 21186, 55622}

As the corpus's own header says, on a flattened table this is the only thing that tells buyer_id from qty. It also shows the corpus's other traps: ver always 1, price_old always NULL, integer keys that sum cleanly.

Distinct counts come from the sample, not the table. A table-wide count(DISTINCT …) costs a full scan per column, sixty on wide. The sample separates a key from a quantity just as well, so the line says "of 200" and claims nothing about the whole table. If you want exact counts on small tables, say below what size.

Budget. MAX_SCHEMA_CHARS stays at 12,000. The sample adds about 40 characters a column, so fewer columns fit; I'd report how many of wide's and TPC-H's columns still do. Only tables that fit are sampled, so the number of statements stays bounded too.

Two questions for you.

Sample values would reach the model. Exploration sends names, types and comments today, although query_data already sends rows in chat. Should a data source be able to turn sampling off?
Since 0036, a key is a relation in the alignment proposal (Exploration proposes an alignment per table #554). I'd leave this issue's "definition gains a slot for the key" to that, and do only the reading and the prompt here.

Measurement. Before and after, mappings.mjs --fresh on wide and TPC-H, three runs each, reading covered, right, wrong, broken and traps_hit.

@Maya-Kid

Thanks @Maya-Kid, and sorry for the wait. No one is on the second half; please take it. The scope is right: one SELECT * per table through the query_data path, counts from the sample and said to be from the sample, nothing new on the trait.

Your two questions:

1. A switch, default on. A data source gets a setting that turns sampling off, and it is on unless someone turns it off. The reasoning: exploration only runs when an admin asks for it, and mounting a source already means query_data sends up to 200 of its rows to the same model whenever someone asks a question, so sampling crosses no boundary that mounting has not. Off by default would leave the flattened tables, the ones this is for, where they are today. Say on the exploration control what it does ("reads up to 200 rows of each table; sample values are sent to the chat model"), so the person pressing it knows.

One requirement that matters more than the switch: sample values go into the prompt and nowhere else. Not into the exploration record, not into a proposal's stored payload, not into the log. A proposal may say what it concluded ("buyer_id is a key"), not quote the values that led there.

2. Agreed. Leave the slot for the key to #554. This issue is the reading and the prompt.

Smaller points:

Counts from the sample only. No exact counts on small tables; one rule is easier to read in the prompt than two.
Skip columns whose values are not worth showing: binary types, and long text beyond a short cut. A cut-short example of a 2,000-character JSON blob tells the model nothing.
The budget report (how many of wide's and TPC-H's columns still fit under 12,000) belongs in the PR description, next to the before and after from mappings.mjs.
The setting needs a migration. The next free number is 0103 at the moment; count again when you open the PR.

@WaylandYang

Thanks @WaylandYang, I'll take it. The plan:

Reading. Each table that makes it into the prompt gets one statement through guard_sql_for and the engine's execute, the path query_data uses: the parser gate, the 200-row cap, and the engine's own timeout and read-only setting where it has them (a read-only session on Postgres and MySQL, readonly=1 on every ClickHouse request; Databricks has no statement timeout, we only stop waiting). Identifiers are quoted per engine; there are six engines now that ClickHouse is in (A ClickHouse warehouse can be mounted read-only, with slow queries stopped by the server timeout or KILL QUERY #1094). One change from the SELECT * you approved: the statement lists the table's columns and leaves out the binary types it recognises, so those values are never fetched. A table that can't be read (permissions, timeout) is explored without a sample. Its failure is logged by table name only, because an engine's error message can quote a value from the table.
The prompt. After the key markers and before the comment: {193 of 200 distinct, e.g. 357, 607}, {1 of 200 distinct: 1}, {all 200 NULL}. Long text (values too long to show cut short) and JSON show no examples, only the count. That keeps a little more than the skip you asked for, because the count still shows a column that is all NULL or holds one value. The legend says the counts come from the rows read, not the whole table. MAX_SCHEMA_CHARS stays at 12,000, and with sampling off the prompt is exactly today's.
Nowhere else. The sample stays in memory for one run, in a type that can't be printed or serialized, and the schema document is untouched. The model can repeat what it saw, so every reply is checked before anything from it is stored or logged. That includes the paths that carry raw reply text today: describe_data's parse-failure log line, and model errors that quote the response body into the run record and the job's last error. A shown example of three or more characters that the prompt doesn't also carry outside the sample (a column comment, the conventions, a name) becomes … in names, summaries, the data description and questions, and the run record. A proposal whose definition contains one is dropped and counted. This can't catch one- or two-character values, or a value the model reformats (21,186 for 21186); the PR will say so. A test has a scripted model echo unique sample values into every field, then searches every table in Utopia's own database and the captured log for them.
The switch. data_sources.sample_on_explore, on by default, in migration 0113 (0112 is held by Let a person connect Google Drive and sync the folders they choose #1222; I'll count again when I open the PR). A deployment admin turns it off in the data-source list. The exploration control says "Reads up to 200 rows of each table; sample values are sent to the chat model" and names any mounted source that has it off.
Records. A dated section in 0036, the lakehouse design page, and revisions of the two bench notes that say exploration doesn't read rows (the bench README and the header of tpch.sql).
Measurement. mappings.mjs --fresh on wide and TPC-H, three runs each, before and after on the same chat model. The PR description will give covered, right, wrong, broken, traps_hit, and how many columns still fit under 12,000.

One question, on where the rule stops for a definition. I'll build it strictly: a definition that contains such an example is dropped. That costs definitions that filter on codes. TPC-H's urgent_orders is count(*) WHERE o_orderpriority IN ('1-URGENT', '2-HIGH'). The column comment (Priority, 1-URGENT .. 5-LOW) carries 1-URGENT but not 2-HIGH, so the definition is dropped whenever 2-HIGH was among the examples shown, and in a --no-comments run whenever either one was. The one exception I can see that is simple to state goes by count: let through the examples of a column with 20 or fewer distinct values in the sample. But that would also let through a column holding a dozen customer names or cities. Should it stay strict, or do you want that exception?

#1179 rewrites the proposal half of explore. The sampling and the check live in their own module, so whichever lands second wires the check into the other's write path.

Navigation Menu

Repository navigation

The explorer cannot tell a key from a number #502

What to add

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

The explorer cannot tell a key from a number #502

Description

What to add

Activity

Maya-Kid commented on Sep 28, 2026

WaylandYang commented on Oct 1, 2026

Maya-Kid commented on Oct 15, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions