metadata

# Metadata Metabase reads the raw column types from your warehouse; **metadata** is the layer you edit on top to make columns behave well — the right filter widget, joins, formatting, maps. You set it per-column with `mb field update <id>` and per-table with `mb table update <id>`. Both are **PATCH** — send only the keys you're changing. Metadata is a small set of fields with large, indirect effects. Get the field ids from `mb table get <id> --include fields` (or `mb table fields <id>`); inspect a column's shape with `mb field get <id>`, its live cardinality with `mb field summary <id>`, its cached distinct set with `mb field values <id>`. To find rows by what a person typed, `mb field search <id> <search-id> --value <text>` answers `{ value, label }` pairs (an id column searched by a name column gives id/name pairs across the FK; both must land on one table, or it answers nothing), and `mb field remapping <id> <remapped-id> <value>` resolves one value to its display value. General flag/output/body mechanics live in `core`. ## The causal chain — set X, unlock Y This is the whole point of the skill. Each edit below is a key in the `field update` (or `table update`) body; the value change is what turns a feature on. | Set (via `field update`) | Unlocks / does | | -------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | `semantic_type: "type/PK"` | marks the row's identity key — enables record/detail view, and lets other tables' FKs point here | | `semantic_type: "type/FK"` **+** `fk_target_field_id: <pk field id>` | the join relationship — implicit FK joins in queries (`mbql` `source-field`), query-builder join suggestions, and dashboard **linked filters** | | `semantic_type: "type/Currency"` / `type/Email` / `type/City` / … | correct display formatting and the matching filter widget (and region/pin maps for location types) | | `has_field_values: "list"` (or `"auto-list"`) | a **dropdown** filter widget, backed by a scanned distinct-value set | | `has_field_values: "search"` | a **search box** (no value set stored) — for high-cardinality columns | | `has_field_values: "none"` | a plain input box, no dropdown | | `visibility_type: "sensitive"` or `"retired"` | **blocks queries** that touch the field — not a UI hint, an error | | `visibility_type: "hidden"` | removes the column from the query builder and data reference (SQL can still read it — **not access control**) | | `visibility_type: "details-only"` | hidden in table views, shown in the single-record detail view (for long blobs) | | `coercion_strategy: <strategy>` | **actually casts** the column — the only entry here that changes the value's type (below) | | `display_name` / `description` | the human label and help text shown everywhere | `table update` carries the table-level equivalents: `display_name`, `description`, `visibility_type` (`hidden` / `technical` / `cruft` — hides the whole table from the builder), `field_order`, and `entity_type`. <!-- requires: bulkTableEdit --> To set the same `entity_type`, `data_layer`, `data_source`, `data_authority`, `owner_email`, or `owner_user_id` on many tables at once, `mb table bulk-edit --body '{"schema_ids":["1:public"],"data_layer":"final"}'` takes `table_ids`, `database_ids`, and `schema_ids` (`"<db-id>:<schema>"`) selectors in the same body. A configured `data_authority` cannot be set back to `unconfigured`, and `data_source` never moves to or from `metabase-transform`; a selected table that breaks either rule fails the call. The server answers the same whether or not a selector matched a table, so confirm the edit with `mb table list` (e.g. `--data-layer final`). <!-- /requires --> <!-- requires: tableUserValueWithdrawal, bulkTableEdit --> `null` withdraws an edit: `data_source` and `data_layer` read back empty, while `entity_type`, `owner_email`, `owner_user_id`, and `data_authority` fall back to the values Metabase keeps for the table — the entity type it derives from the table name, and usually no owner and `unconfigured`. A call that fails on a rule may already have edited the selected tables that carried no earlier edit, so re-check with `mb table list` after a refusal. <!-- /requires --> ## Foreign keys are the highest-leverage edit A FK relationship is what makes a warehouse browsable. Set it in **two keys on the FK column**, in one PATCH: ```bash mb field update 1711 --body '{"semantic_type":"type/FK","fk_target_field_id":1684}' --profile <n> --json ``` `1711` is `orders.customer_id`; `1684` is `customers.id` (which should itself be `type/PK`). Once set: - Queries can pull columns from the related table with no explicit join — `["field", {"source-field": 1711}, 1682]` in MBQL (see `mbql`). - Dashboard **linked filters** become possible (a State filter narrowing a City filter). **Linked filters read only these table-metadata FKs** — never a join you wrote inside a saved question — which is why a linked filter that "shows values it shouldn't" almost always means the FK isn't set in metadata. (See `dashboard`.) `mb table fks <id>` lists the FKs already pointing at a table (origin field with its table, destination field), so check it before adding one; it leaves out origins in tables you cannot read, so an absent FK is conclusive only for an admin. Removing the `type/FK` semantic type auto-clears `fk_target_field_id`. Point a FK only at a field in the **same database** — a cross-database target is invalid and never resolves to a relationship. ## Semantic types are labels, not casts The commonest misconception. `semantic_type: "type/Quantity"` on a text column does **not** make it a number — it changes formatting and widget choice, nothing about the stored value. Sorting still sorts as text. To genuinely change the type, use **`coercion_strategy`**, which casts `base_type` → an `effective_type` (e.g. a Unix-epoch integer read as a timestamp, or a numeric string read as a number): ```bash mb field update 42 --body '{"coercion_strategy":"Coercion/UNIXSeconds->DateTime"}' --profile <n> --json ``` `base_type`, `effective_type`, and the physical `name` are **read-only** — set by warehouse sync, never editable here. For a durable transformation (splitting, combining, recomputing columns), build a `transform` rather than leaning on coercion. The full semantic-type catalog — every value grouped by the base type it attaches to, plus the `has_field_values` and `visibility_type` value tables and the exact writable-key lists — is in `references/semantic-types.md` (`mb skills get metadata --full`). ## Sync, scan, fingerprint — three different refreshes When a column looks stale or missing, know which one you need (`db` and `table` verbs, mechanics in `core`): - **Sync** (`mb db sync-schema <id> --wait`) — re-reads table/column **structure** (new tables, new columns, types). Run after a schema change. `mb table sync-schema <id>` re-syncs one known table (columns, fingerprints, field values), never finds a new table, and offers nothing to wait on. - **Scan / rescan** (`mb db rescan-values <id>`, or `mb table rescan-values <id>` for one table) — refreshes the cached **distinct-value sets** behind dropdown filters, skipping a set unread for 14 days or never read. Run when a `list` column's values changed but its dropdown is stale. `mb table discard-values <id> --yes` deletes a table's sets and their custom display values; no scan recreates them, only the next read does. - **Fingerprint** — value-distribution stats (min/max, null count) computed on a sample; drives smart defaults. Refreshed by sync; not a separate CLI verb. A newly connected database or a missing expected column usually just needs a `db sync-schema <id> --wait` before you conclude anything. <!-- requires: bulkTableSync --> For a set of tables, `mb table bulk-sync-schema`, `bulk-rescan-values`, and `bulk-discard-values --yes` take `--table-ids`, `--db-ids`, and `--schemas 1:public` selectors (`1:` for the tables with no schema) and need an admin or data analyst. <!-- /requires --> <!-- requires: fieldDataSensitivity --> ## Data sensitivity is a label a person owns Each column carries a `data_sensitivity` label (`SEC_KEY`, `SYS_TELEMETRY`, `PHI`, `BIO_GEN`, `PCI_FIN`, `SENS_PERS`, `PII`, `CORP_IP`, `BIZ_CONF`, `PUBLIC`, most severe first) or `null`. A server that enables its classifier (off by default) labels columns from their name, types and fingerprint during sync; otherwise only people do. `mb field set-sensitivity <id> PII` labels one column and the classifier never overwrites it; `mb field set-sensitivity <id> none` withdraws that label, leaving the classifier's if there is one. Read it back with `mb field get <id>`. The label masks and restricts nothing. <!-- /requires --> ## Glossary — the business terms behind the columns Column metadata says what a field is; the glossary says what the business means by a word. Entries are instance-wide `{term, definition}` pairs, listed on the Glossary pages of Data Studio and the Data Reference and fed to Metabot as context, so defining "Churn" or "Active customer" once shapes every answer built on those columns. `mb glossary list --search <text>` matches terms and definitions (there is no `get`); `mb glossary create --term "Churn" --definition "…"` adds one, or pass a body with exactly `term` and `definition`; `mb glossary update <id>` replaces both fields; `mb glossary delete <id> --yes` removes one. Terms are unique. ## Don't - Don't expect a `semantic_type` to cast — it's a label. Use `coercion_strategy`, or a `transform`. - Don't edit `name`, `base_type`, or `effective_type` — they're read-only from sync. - Don't treat `visibility_type: hidden` as security — it only hides from the builder; native SQL still reads the column. Real restriction is a permissions concern, outside the CLI. - Don't set a `type/City` / `type/State` filter and expect a map or clean dropdown if the values are inconsistent (abbreviations mixed with full names) — fix the values first (a `transform`), then the metadata. - Don't blame the data when a dashboard linked filter misbehaves — check the FK is set here first.