Metabase Database Metadata

Metabase Database Metadata Format

Metabase represents database metadata — synced databases, their tables, and their fields — as a tree of YAML files. Files are diff-friendly: numeric IDs are omitted entirely, and foreign keys use natural-key tuples like ["Sample Database", "PUBLIC", "ORDERS"] instead of database identifiers.

The format is defined by a specification bundled alongside this file as spec.md (upstream source: metabase/database-metadata). The same project ships a CLI (@metabase/database-metadata on npm) that converts the raw JSON exported from a Metabase instance into the YAML tree described by the spec.

Canonical layout

All metadata for a project lives under a top-level .metadata/ directory:

• `.metadata/databases/` — the YAML tree. This is the canonical source for the agent. Read these files to understand the schema, columns, types, and FK relationships.
• `.metadata/table_metadata.json` — the raw JSON exported from the Metabase instance. Potentially multi-megabyte (or multi-gigabyte) JSON with flat databases / tables / fields arrays. Never open, grep, or pass it to tools. It exists only as input to the extractor.

The .metadata/ directory should be gitignored. On large warehouses the extracted metadata can reach gigabytes — committing it would make the repo painful or unusable.

First-time setup

Do not run any of the steps below proactively at session start. Only run them when the user explicitly asks to fetch metadata, set up the workflow, or requests something that plainly requires knowledge of the database schema (e.g. "write a query against ORDERS", "describe what tables exist").

When setup is triggered:

1. Ensure .metadata/ is gitignored

Read the repo's .gitignore and confirm .metadata/ is listed. If it isn't, ask the user before modifying `.gitignore` — e.g.:

.metadata/ is not in .gitignore. Committing it would bloat the repo (metadata can be gigabytes). Shall I add it?

Only edit .gitignore after the user confirms.

2. Export the metadata from Metabase

Fetch table_metadata.json by calling POST /api/ee/serialization/metadata/export on the Metabase instance and writing the response to .metadata/table_metadata.json. The endpoint accepts three boolean query parameters that opt sections in or out — they all default to false, so requests must explicitly set the sections they want:

• with-databases — include the databases array.
• with-tables — include the tables array.
• with-fields — include the fields array.

A typical full export sets all three to true. The user supplies the base URL and an API key (e.g. via METABASE_URL and METABASE_API_KEY env vars):

mkdir -p .metadata
curl -sf -X POST "$METABASE_URL/api/ee/serialization/metadata/export?with-databases=true&with-tables=true&with-fields=true" \
  -H "X-API-Key: $METABASE_API_KEY" \
  -o .metadata/table_metadata.json

If the user has not provided credentials, ask for them before running the call.

3. Extract

Once .metadata/table_metadata.json is in place:

rm -rf .metadata/databases
npx @metabase/database-metadata extract-table-metadata .metadata/table_metadata.json .metadata/databases

Then read the YAML tree under .metadata/databases/ to answer the user's question.

Session start behaviour

At the start of a session, do not run any fetch commands. Just observe what's on disk:

• If .metadata/table_metadata.json and .metadata/databases/ both exist, assume the tree is sufficiently up to date and use it directly. Do not refetch.
• If the tree is missing or only partial, do nothing until the user asks for something that needs it — then fall into the first-time-setup flow above.

If something in the tree looks stale or inconsistent while you're using it, mention it to the user and let them decide whether to refetch. Never refresh silently.

Refreshing (user-initiated only)

If the user explicitly asks to refresh metadata, re-run the export call to overwrite .metadata/table_metadata.json, then re-run the extract step. Always remove .metadata/databases before re-extracting so stale files are not left behind.

Entities

Three entity types, two file types:

Foreign keys

Foreign keys use natural-key tuples, not numeric IDs:

• Database FK: the database name (string) — e.g. "Sample Database"
• Table FK: [database, schema_or_null, table] — e.g. ["Sample Database", "PUBLIC", "ORDERS"]
• Field FK: [database, schema_or_null, table, field, ...nested_field_names] — e.g. ["Sample Database", "PUBLIC", "EVENTS", "DATA", "user", "name"] for a JSON-unfolded column DATA.user.name

Field-level FKs show up as parent_id (nested field parent) and fk_target_field_id (referenced PK for FK columns).

Type attributes on fields

• `database_type` — the raw native type string from the driver (BIGINT, VARCHAR, JSONB, etc.). Database-specific.
• `base_type` — the Metabase type matching the native type (type/BigInteger, type/Text, type/Structured, etc.).
• `effective_type` — the type Metabase treats the column as at query time. Only emitted when it differs from base_type (i.e. coercion is configured).
• `coercion_strategy` — the rule producing effective_type from base_type (e.g. Coercion/ISO8601->DateTime, Coercion/UNIXMilliSeconds->DateTime).
• `semantic_type` — business-domain label (type/PK, type/FK, type/Email, type/Category, type/Latitude, etc.). Drives UI and some analytical behavior.

See the bundled spec for the full type hierarchy and available coercion strategies.

Reading the spec

This skill ships with a local snapshot of the spec as spec.md, alongside SKILL.md.

Read it on demand, not eagerly. Open spec.md only when you actually need detail beyond what SKILL.md summarizes — e.g. the full base-type / semantic-type hierarchy, the complete list of coercion strategies, or the exact folder-path rules. Do not open it at session start, and do not open it for tasks unrelated to the metadata tree.

If the bundled copy looks out of date with the upstream package, the skill's own README.md documents how to refresh it with extract-spec.

metabase-database-metadata (skill)

This skill ships a local snapshot of the Metabase Database Metadata specification as spec.md, sitting next to SKILL.md. The SKILL references that bundled file directly, so an agent loading this skill never has to fetch the spec on its own.

Refreshing the bundled spec

When the upstream format changes, refresh the bundled copy by running this from inside the skill folder:

npx @metabase/database-metadata extract-spec --file ./spec.md

Commit the regenerated spec.md alongside SKILL.md.

Files

• SKILL.md — the skill itself. Read by the agent.
• spec.md — the v1 specification of the Metabase Database Metadata Format. Agent reads this on demand when it needs details beyond what SKILL.md summarizes (full type hierarchy, coercion strategies, exact folder-path rules, etc.).

Metabase Database Metadata Format

Version: 1.0.0

Overview

Metabase database metadata is a read-only snapshot of databases, tables, and fields that have been synced from a connected data source. This specification describes the default format for exporting that metadata to disk: one YAML file per database, and one YAML file per table with its fields nested inside.

The format is designed to be portable and reviewable: numeric IDs are omitted or replaced with human-readable natural keys (database name, [database, schema, table] tuples, etc.). Files can be diffed, grepped, and edited by hand.

The raw table_metadata.json is a single flat JSON document with databases, tables, and fields arrays, optimized for transport rather than reading. It can be arbitrarily large — tens or hundreds of megabytes on warehouses with many tables — and is not intended for direct consumption. Tools and humans should read the extracted YAML tree under databases/ instead, where each entity lives in its own small file.

Table of Contents

1. Entity Keys 2. Field Types 3. Folder Structure 4. Database 5. Table 6. Field

---

Entity Keys

Database objects are referenced using natural keys instead of numeric IDs.

For schemaless databases, the schema component is null (e.g., ["My Database", null, "my_table"]).

For JSON-unfolded fields, the Field FK extends beyond 4 elements with the nested path: ["Sample Database", "PUBLIC", "EVENTS", "DATA", "user", "name"] represents the JSON path DATA.user.name.

Numeric primary keys (id) are not emitted. Each entity is identified by its position in the folder tree and by the natural-key foreign keys on child entities.

---

Field Types

Each field has four type attributes that describe the column at different layers: database_type (the native SQL type), base_type (the matching Metabase type), effective_type (the type after any coercion), and semantic_type (the business-domain role). An optional coercion_strategy defines the rule that produces effective_type from base_type.

database_type

The native SQL type reported by the database driver, verbatim (e.g., BIGINT, VARCHAR, DOUBLE PRECISION, TIMESTAMP WITH TIME ZONE, JSONB). This value is database-specific: the same logical type can appear with different spellings across engines (INT4 vs INTEGER, CHARACTER VARYING vs VARCHAR, DOUBLE vs DOUBLE PRECISION). Metabase uses database_type for informational purposes and when generating native SQL; it is not portable across engines.

database_type always maps deterministically to a base_type for a given driver — see the table below for typical pairings.

base_type

The raw Metabase type that matches the column's native database type. This is what the driver reports the column as (e.g., a Postgres BIGINT → type/BigInteger, VARCHAR → type/Text, DOUBLE PRECISION → type/Float).

base_type is always one of the types below and never a semantic type like type/PK.

Common base types (selected from the type hierarchy):

effective_type

The type Metabase actually treats the column as when running queries. If no coercion is applied, effective_type equals base_type and is omitted from the YAML. It is emitted only when coercion changes the type.

For example: a VARCHAR column whose base_type is type/Text but that stores ISO-8601 timestamps would have effective_type: type/DateTime and coercion_strategy: Coercion/ISO8601->DateTime.

coercion_strategy

An optional rule that tells Metabase how to convert base_type → effective_type at query time. Absent unless coercion is configured.

Built-in coercion strategies:

semantic_type

An optional label describing how the column is used in the business domain. Semantic types sit in a separate hierarchy (rooted at Semantic/* or Relation/*) and don't affect how values are read or converted — they drive UI choices (icons, default visualizations, filter widgets) and some analytical behavior (e.g., auto-binning for type/Category).

Common semantic types, grouped by purpose:

semantic_type is always compatible with effective_type (or base_type when no coercion is in play) — e.g., type/Latitude only makes sense on type/Float, type/Email only on type/Text.

---

Folder Structure

By convention, metadata is extracted under a .metadata/databases/ directory, with each database occupying its own folder. The exporter itself doesn't enforce this location; it writes the tree below into whatever folder the caller passes.

.metadata/
└── databases/
    └── {database}/
        ├── {database}.yaml
        ├── schemas/
        │   └── {schema}/
        │       └── tables/
        │           └── {table}.yaml
        └── tables/                         # Schemaless databases
            └── {table}.yaml

Path Construction Rules

• Database, schema, and table names are used verbatim as folder and file names. Case and spaces are preserved (e.g. Sample Database/, PUBLIC/, ORDERS.yaml).
• Only characters that are invalid in paths are escaped: / becomes __SLASH__, \ becomes __BACKSLASH__.
• A database's YAML file ({name}.yaml) lives at the root of its folder.
• Tables are nested under schemas/{schema}/tables/{table}.yaml when the database has schemas, or directly under tables/{table}.yaml for schemaless databases.
• A table's YAML file embeds all of its fields inline — there is no separate file per field.

---

Database

A Database entry describes a connected data source (e.g., a Postgres or MySQL instance). Only identifying information is included; connection details are not part of this format.

The database YAML file lives at databases/{slug}/{slug}.yaml.

Schema

Example

name: Sample Database
engine: postgres

---

Table

A Table entry describes a single physical table (or view) within a database. Its fields are nested directly in the same YAML file.

The table YAML file lives at databases/{db_slug}/schemas/{schema_slug}/tables/{table_slug}.yaml (or databases/{db_slug}/tables/{table_slug}.yaml for schemaless databases).

Schema

Example

name: ORDERS
db_id: Sample Database
schema: PUBLIC
description: Confirmed Sample Company orders for a product, from a user.
fields:
  - name: ID
    base_type: type/BigInteger
    database_type: BIGINT
    semantic_type: type/PK
  - name: TOTAL
    description: The total billed amount.
    base_type: type/Float
    database_type: DOUBLE PRECISION

---

Field

A Field entry describes a single column. Fields are nested inline inside their Table; there is no separate field file.

A field's table is implied by its position in the enclosing table's fields array, so table_id is not emitted on nested fields. Only parent_id appears when the field is a child of another field (e.g., JSON-unfolded nested columns).

Schema

Example

name: CREATED_AT
description: The order creation timestamp.
base_type: type/Text
database_type: TEXT
effective_type: type/DateTime
semantic_type: type/CreationTimestamp
coercion_strategy: Coercion/ISO8601->DateTime

Nested Fields

JSON-unfolded columns use parent_id to reference the enclosing field:

name: name
base_type: type/Text
database_type: TEXT
parent_id:
  - Sample Database
  - PUBLIC
  - EVENTS
  - DATA
  - user