---
name: explore
description: 'Use this whenever you need to know what is actually in a database, warehouse, or DuckDB file before you trust it: ranked inventory of what exists, column profiles, PII detection, grain and data-quality problems, verified join inference, Mermaid ER diagrams, guarded ad-hoc SQL probes, and k-means segmentation, producing a draft map without dumping the whole schema into context. Trigger it on an unmet precondition, not on any particular phrasing: if you are about to write or fix SQL against tables whose columns, types, grain, or join keys you have not verified in this session, use this FIRST. That includes dbt work: building a staging or mart model, fixing a broken model, or debugging wrong numbers, whenever the ticket names source tables without spelling out their schema. It also applies mid-task: if you are partway through and hit a table you have not inspected, stop and use this rather than guessing column names or firing off one-off SELECTs. Also use it for direct questions like "what''s in my duckdb", "which tables matter", "how do these tables relate", "is this data any good", "any PII in here", "how many orders have no customer", or "cluster my customers". Explore is read-only and writes nothing but the .dex/ cache. It does not author the model: pair it with transform, which writes the change once you know what you are writing against. To reconcile a project that has fallen out of sync, use maintain.'
---

# Explore

Make sense of a warehouse or a local DuckDB database the way an analytics
engineer does: rank what matters, drill selectively, and persist a draft map.
This is the flagship, fully read-only skill. It absorbs profiling and
relationship inference as capabilities; they are not separate skills.

## How to drive it

Run the engine through the wrapper. It prints one sanitized JSON envelope and
nothing else; read the envelope and decide the next step.

```bash
uv run "${CLAUDE_SKILL_DIR}/scripts/run.py" <subcommand> [flags]
```

dex runs its engine through `uv`, which is a prerequisite and is not installed by
Claude Code. If the shell reports `uv: command not found`, stop and tell the user
to install it (`curl -LsSf https://astral.sh/uv/install.sh | sh`, or
`brew install uv`, or `pipx install uv`), then re-run. Never fall back to raw
Python, `pip`, or a database CLI to do the work another way: the guardrails live in
the engine, so any other path is unguarded.

If the user has no warehouse to point at and wants to see what dex does, `demo`
generates one: a seeded local DuckDB warehouse plus the `.dex/config.yml` for it,
with no credentials and no network, so every subcommand below then runs with no
flags. It only ever creates, so it refuses rather than touch a file that already
exists. Offer it rather than assuming it: a user who does have a warehouse wants
that one read, not a fixture built beside it.

Subcommands, in the usual order:

1. `connect test --path <file.duckdb>` confirms a read-only connection and
   reports capabilities.
2. `explore inventory --rank` returns a ranked object summary (counts and sizes,
   never rows).
3. `explore profile <objects>` (space- or comma-separated) returns column
   profiles, PII flags recorded as (column, category, confidence) and never
   example values, plus candidate keys, the likely grain, and data-quality
   warnings (e.g. a non-unique id that will fan out on joins). A generic
   `*_name` flag's confidence is refined by value-shape evidence from the same
   scan, in both directions: person-shaped values corroborate it, a closed
   reference vocabulary or long labels de-rate it below the firewall's blocking
   threshold, and missing evidence changes nothing (the flag itself is never
   removed). Distinct counts
   are approximate for scale, but any column that looks unique within
   approximation noise is escalated to an exact COUNT(DISTINCT)
   (`distinct_count_exact: true`), so uniqueness and grain verdicts rest on
   proof; a `~` prefix in a warning marks a count that is still approximate.
   A requested object whose cached profile is still fresh (same connector,
   schema unchanged, within `profile_freshness_hours`, default 24) is served
   from the cache (`cache_hit_count`) instead of re-scanned, so profiling a
   table `map` just wrote costs nothing to spend; pass `--refresh` to force a
   re-scan when the source changed in a way the free metadata check cannot see.
4. `explore relationships` returns inferred and declared joins with confidences,
   plus notes explaining what the inference examined (so an empty list is
   meaningful). Add `--verify` to measure each inferred join with an aggregate
   overlap probe (orphan fraction, confidence adjusted). A declared join has two
   sources: a `relationships` test, and (with `--use-project`) an entity two
   semantic models share, which the layer states outright with the key named per
   model. `declared_by` on an edge names that entity, `semantic_join_count` says
   how many came that way, and the notes call out the ones name-based inference
   did not find, which is the interesting set: a semantic layer routinely joins
   columns that share no name at all.
5. `explore map` writes or updates the `.dex/` cache and returns the map
   (`--verify` works here too). Alongside the counts, `data.objects` gives each
   top-ranked object its row count, detected grain, candidate key, notable
   columns (each carrying the role that earned it a place: `grain`, `key`,
   `join`, or a PII flag) and data-quality findings, and `data.edges` gives the
   join edges in the same shape `explore relationships` returns. With
   `--use-project` each object also carries `semantic_models`, the semantic models
   that sit on that relation, which is what separates a load-bearing table from a
   merely large one: empty means nothing in the layer reads it. **Read that
   payload instead of chaining `profile` and `relationships` to re-derive it**;
   go to those two when you need one object in full, or a value domain, which
   `map` never carries. It is budgeted: 25 objects by rank, 12 columns per
   object, 40 edges, 5 findings per object. Every cap binds in every mode and
   every elision is counted in `notes` and in an `elided_*` field, so an empty
   `notes` means nothing was cut. `--detail` widens the selection to every column
   and to objects that were inventoried but never profiled, and lifts no cap; it
   spends nothing, unlike `--full`. Past 50 objects it profiles only the top 25
   by rank and says so in `notes` (with `skipped_count`); pass `--full` to
   profile everything. On a re-map, objects skipped this run keep their prior profiles
   (`carried_forward_count`), each stamped with its own `profiled_at` so
   staleness is visible instead of column detail silently vanishing. A selected
   object whose cached profile is still fresh (same connector, schema unchanged,
   profiled within `profile_freshness_hours`, default 24) is reused without a
   re-scan (`cache_hit_count`), so re-runs cost nothing to spend; pass
   `--refresh` to force a full re-profile when the source changed in a way the
   free metadata check cannot see (e.g. rows changed but the schema did not).
   `explore relationships` and the standalone `explore profile` reuse fresh
   profiles the same way.
6. `explore diagram [--full]` renders the cached map as a Mermaid ER diagram in
   `data.mermaid`. Free and connectionless (it reads the cache, never the
   warehouse), so it is safe to re-run while shaping the picture. **Reproduce the
   string verbatim in a fenced ```mermaid block so the human can see it, and
   write it to a `.mmd` or a markdown file when they want one on disk: the
   engine deliberately writes no file.** Never redraw or "tidy up" the diagram
   by hand. The glyphs are claims the engine derived from evidence, and a
   plausible-looking cardinality you supplied is exactly the overclaim this
   command exists to prevent: declared joins are solid, inferred dotted, and an
   unverified inference never says "exactly one". A solid line labelled with a
   semantic entity is a join the semantic layer declares; look the entity up with
   `explore semantic list`. Read `notes` before presenting
   it, since it states any object or column that was left out; `--full` widens
   from the default (profiled, joined objects and their grain, key, join, and
   PII columns) to everything eligible.
7. `explore query "<SELECT ...>" ["<SELECT ...>" ...]` answers ad-hoc questions
   the fixed commands don't cover: you write the SQL, the engine's query firewall
   refuses or bounds it. Pass a statement per argument, or `--sql-file <path>`
   for a longer list, and ask a whole chain of questions in one call rather than
   one call each; each statement is judged and answered on its own, so a refusal
   on one does not cost you the others, and `data.results` carries one entry per
   statement. A table you have not profiled, including a model you just built, is
   profiled for you and the statement then runs, so probing something new is one
   call rather than three; the envelope says what it profiled, and on a metered
   connector that profile is priced into the same confirmation as the statements.
   Results come back row-major and capped; a refusal names the offending column
   and the fix, so one rewrite is enough. Read `${CLAUDE_SKILL_DIR}/references/probe-playbook.md` before
   writing a probe: it maps common questions to effective probe shapes.
8. `explore cluster <object> [--features a,b,c] [-k N]` runs k-means over a
   bounded sample of the object's numeric columns and returns the segment
   structure: per-cluster sizes and fractions, centroids (each coordinate is a
   cluster's mean of that feature, an aggregate), the silhouette score, and,
   when `-k` is omitted, the k it picked plus the silhouette sweep it chose from.
   Requires the `.dex/` cache (run `map`/`profile` first) so features can be
   auto-selected from profiled numeric, non-PII, non-key columns; pass
   `--features` to choose them yourself (naming a PII column, or a key, opts it
   in deliberately, and only its mean is ever reported). A key is never a
   feature: its mean is meaningless, and a fact table is mostly keys plus a
   handful of measures, so clustering on them just partitions surrogate ranges.
   Keys are the unique columns, the columns that join out (from the joins `map`
   inferred), and the columns named like one; prefer `map` over a bare
   `profile` here, because without inferred joins a foreign key is caught only
   if its name gives it away. The notes name every excluded column, so check
   them before trusting a result. Two things the silhouette alone will not tell
   you, both of which the notes will. A cluster holding under 1% of the sample
   is an outlier pocket, not a segment, and it pushes the score up precisely
   because it sits so far out: report that as outlier detection, or re-run with
   `-k` to split the bulk. And on connectors that cannot seed a sample the draw
   changes per run, so two runs can disagree on k; the envelope's
   `sample_repeatable` says which case you are in, and comparing runs across
   different draws is meaningless. Only aggregates cross the
   boundary: the sample rows are clustered in-process and never enter context.
   On a metered connector it takes the same cost handshake as the scanning
   commands below (only the feature columns are scanned, and a dialect-aware
   sample clause reads a fraction), so surface the estimate and get a budget
   first. Needs the `[cluster]` extra (scikit-learn); the wrapper installs it
   automatically for this subcommand.
9. `explore semantic list` and `explore semantic query` reach the dbt semantic
   layer. `list` is discovery, and it returns the layer's objects rather than
   three lists of names: semantic models (the unit the layer is organized
   around, each with the transformation model it sits on and its default time
   dimension), metrics (which dimensions each can be grouped by, the measures it
   reads, a ratio's two sides, any filter that makes it a subset, the grains it
   can be queried at, and the time column a time grouping resolves to),
   dimensions (the token to group by, plus the definition, owning model, and
   queryable grains behind it),
   entities (one declaration per semantic model, each with its own join key, so
   the declared join graph is readable), and measures (the aggregation and
   expression the number is made of, which is often a conditional rather than a
   column). It also reaches the warehouse the rest of `explore` describes: a
   semantic model carries the `relation` it sits on and each dimension, entity
   declaration and measure carries its `column`, so "which table is behind this
   metric" is the metric's `semantic_models` followed to their relations, and
   `explore profile <relation>` is the next call. An element defined as an
   expression carries no column rather than a guessed one. The hosted backend
   exposes no relation at all and declares that in `unavailable`, so use `--local`
   when you need the physical side. Read a metric's `input_measures` through to those measures before
   trusting what a number counts, and read its `time_axis` before trusting a time
   series: `metric_time` is not one column but each metric's own aggregation time
   dimension, so two entries there mean the metric's measures bucket by different
   timestamps and grouping by `metric_time` splits the number between them. On a large layer, `list --metric <m>` narrows
   the catalog to those metrics and what they reach, at no extra cost; the
   payload names the scope in `scoped_to`, so a scoped catalog is never mistaken
   for the whole layer. `list --for-dimension <d>` asks the reverse question,
   returning the metrics groupable by all the named tokens: use it when you know
   the slice you want rather than the metric, and to find the metrics that can go
   on one chart against one axis. It inverts the dimension list each metric
   already carries, so it costs nothing extra and refuses an unknown token by
   name rather than answering "no metrics". Two payload fields carry differences
   between the
   backends rather than leaving them to be inferred: `dimension_scope` says
   whether a dimension row is one declaration or one groupable path (which is
   why the two backends report different dimension counts for one layer), and
   `unavailable` names fields a backend structurally cannot supply. `--local`
   resolves the join graph through MetricFlow where the `[semantic]` extra is
   installed, which is what makes its dimension lists the tokens a query can
   actually use; without it the payload says `declarations` and a note names the
   extra. `query` takes a positional
   metric after the explicit mode (with `--metric` kept for compatibility) and a
   `--group-by <entity__dim>` (plus optional `--where`, `--grain`, and
   `--limit`) and returns a metric's values as a capped, columnar result. Name
   flags take a comma-separated list or a repeated flag (`--group-by a,b` is
   `--group-by a --group-by b`); `--where` is never split. `--grain` is checked
   against the grains the layer reports for the metrics queried, so a refusal
   names the ones that metric has. `values <dimension>` returns that dimension's
   value domain, which is what you need before you can write a `--where` filter
   and the one thing no other dex command can reach on a hosted layer (`profile`
   cannot see a semantic dimension). Read `scoped_to` on the result before
   trusting the list: empty means these are the column's own values, and a metric
   name means dex had to reach the dimension through that metric because it is
   only reachable through a join, so the values are the ones present for that
   metric. Pass `--metric` to choose it yourself. A PII-flagged dimension refuses
   this command outright rather than being screened, because the whole output is
   values. Two backends answer
   these, chosen by `.dex/config.yml` `semantic.vendor` and `semantic.deployment`
   (the older `semantic.backend` spelling of the two still works), and overridable
   with `--local` / `--api`. Those two flags name who executes, not which vendor:
   every result reports it as `execution` (`dex` or `vendor`). `--local` renders
   the SQL with MetricFlow and executes it through dex's own connector and cost
   handshake, so cost is surfaced before spend (needs a dbt project parsed at
   least once, and for `query` the `[semantic]` extra; `list` reads the project
   and needs no extra). `--api` sends the query to a hosted
   dbt Cloud deployment (needs only a host, an environment id, and a
   `DBT_SL_TOKEN`, plus the `[semantic-api]` extra, no local project). The hosted
   backend is the one place the cost guard cannot apply: dbt Cloud executes
   server-side, so the result carries an explicit warning that spend is governed
   there, not by dex, and no `--confirm` is asked. Either way a PII-shaped grouped
   or filtered dimension (e.g. `user__email`) is refused before the query runs,
   and on `--api` the layer's own PII metadata is fetched per metric so a
   multi-metric query stays authoritative rather than falling back to names.
   This queries the layer; authoring it is `transform`'s job.

Rules of engagement for `query`: prefer the fixed commands when they answer the
question; one probe answers one question; batch related measures into a single
query rather than issuing many; aggregates over PII-flagged columns must be
measuring (COUNT, APPROX_COUNT_DISTINCT, AVG(LENGTH(...))), never value-carrying
(MIN, ANY_VALUE, STRING_AGG). The FROM clause may unnest JSON and array
columns in the connector's native idiom, which is the right way to explore
schemaless data (for example "which keys appear across every row of this JSON
column"): BigQuery `t, UNNEST(JSON_KEYS(doc)) AS k`, Snowflake
`t, LATERAL FLATTEN(input => doc) f`, Databricks
`t LATERAL VIEW EXPLODE(json_object_keys(doc)) x AS k`, Postgres
`t, jsonb_object_keys(doc) AS k`, Redshift `t, UNPIVOT t.doc AS v AT k`,
DuckDB `t, UNNEST(json_keys(doc)) AS u(k)`, ClickHouse
`t ARRAY JOIN JSONExtractKeysAndValuesRaw(doc) AS kv` (there is no lateral
join; ARRAY JOIN is the expansion). The unnested value must come from
a column of a table in the query (bare, or through a JSON/array function);
unnesting a subquery, another table, a literal, or a generator is refused,
and the unnest's outputs inherit the source column's PII flags. A column whose flag was de-rated below the 0.5
blocking threshold projects normally, with an envelope warning naming it; treat
the warning as information for the user, not an error to fix. If the user says a
refused column is not personal data, recommend a `pii_overrides` entry in
`.dex/config.yml` (fully qualified column, optional reason): it unblocks
querying immediately, survives re-profiles, and is reviewable in git. Never
hand-edit `.dex/cache.json` to clear a flag. Never fall back to raw Python or a
database CLI to run SQL; the firewall path is the only sanctioned one.

## Cloud and database targets (BigQuery, Snowflake, Databricks, Postgres, Redshift, ClickHouse)

A remote warehouse or database replaces `--path` with connector config. Start
with `connect test --connector <name>` (or set `connector:` plus the matching
block in `.dex/config.yml`: `bigquery:` with `project` and a `datasets`
allowlist, `snowflake:` with the pinned `warehouse` and a `databases`
allowlist, `databricks:` with the pinned SQL `warehouse` and a `catalogs`
allowlist, `postgres:` with a `schemas` allowlist, `redshift:` with the
Serverless `workgroup` and a `schemas` allowlist). Credentials are
discovered, never asked for: if the envelope reports missing or expired
credentials, relay the fix it names (for BigQuery
`gcloud auth application-default login`; for Snowflake a `connections.toml`
entry or `SNOWFLAKE_*` env; for Databricks `databricks auth login` or
`DATABRICKS_*` env; for Postgres `DATABASE_URL`, `PG*` env, or a
`pg_service.conf` entry; for Redshift the AWS credential chain
(`aws configure`, `AWS_*` env) or `REDSHIFT_*` env) and never ask the user to
paste a key, token, or password.

On a metered connector, scanning commands (`profile`, `map`, `relationships`,
`query`) run a two-step handshake. The first call returns
`needs_confirmation` with an estimate in `cost.estimate` (and a per-table
breakdown where relevant): an exact dry-run byte figure on BigQuery, a
heuristic labeled `estimate_quality: "heuristic"` in warehouse-seconds on
Snowflake (credits alongside), a floor labeled `estimate_quality: "low"` in
warehouse-seconds on Databricks (DBUs alongside; it sharpens itself inside
the confirmed budget), a heuristic in compute-seconds on Redshift (RPU-hours
alongside; Serverless estimates carry the 60-second wake minimum once), and
database-seconds on Postgres (no dollars; the guarded quantity is load on
the operational database) and on ClickHouse (self-hosted, also no dollars;
estimated free by the non-executing `EXPLAIN ESTIMATE`, which prices after
primary-key pruning, and reporting `estimate_basis` so you can tell a pruned
plan estimate from a whole-relation fallback). Surface the
estimate to the user in human units, get an explicit budget from them, and
re-issue the same command with `--confirm` and `--budget <magnitude>` in the
paradigm's unit. Never invent a budget the user did not agree to, and never
retry with a raised budget on an over-ceiling refusal without asking.
Metadata is free (`connect test`, `inventory` run immediately), and OK
envelopes report actual spend under `data.spend`.

On BigQuery a profiling estimate holds a 10 MB floor per table for each
escalation query a profile may still issue after its aggregate scan, so on a
warehouse of many small tables most of the number can be reserve for work that
never happens. Both the handshake and the over-ceiling refusal report that split
(`reserved_bytes` and `reserved_queries`, and in the prose). Pass it on when you
surface the estimate: whether a number is scan or reserve changes whether
raising the budget is buying work or headroom.

When an estimate is larger than the work deserves, narrow the scope rather than
raise the budget. `--scope` (repeatable) bounds a command to part of the
configured source allowlist, in the connector's own vocabulary: a dataset on
BigQuery, a `schema` or `database.schema` on Snowflake, a `catalog.schema` on
Databricks, a schema on Postgres or Redshift, a database on ClickHouse (whose
identifiers are two-part `database.table`: there is no catalog level). It is
free to resolve, it can only narrow what
`.dex/config.yml` already allows, and a scope that names nothing is refused with
the schemas that do exist listed. So `explore map --scope <schema>` is the first
thing to reach for on a warehouse whose full map would be expensive.

## Guardrails (enforced in the engine, not here)

- Read-only against data. The connection is opened read-only and generated SQL is
  SELECT-only. Never propose a write to source data.
- Sense-making, not enumeration. Rank and drill selectively; never paste a full
  schema into context.
- Profile, don't exfiltrate. Understanding comes from aggregates. PII is flagged,
  never surfaced, and the query firewall enforces it on your own SQL: values
  cross the envelope only from profiled columns whose flag is absent or below
  the blocking threshold, bounded and capped. Only a human's `pii_overrides`
  entry clears a flag entirely; never suggest weakening the detection.