Explore

---
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|values|query` reach the semantic layer: the metrics an
   author defined, and the semantic models, measures, dimensions and entities
   they are built out of. Distinct from the warehouse commands above, and from
   the top-level `semantic` group, which *authors* the layer where this *queries*
   it.

   `list` is discovery and 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, its default time dimension, and the physical
   `relation` underneath), 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 `time_axis`, the physical time column a time
   grouping resolves to), dimensions (the token to group by, plus the bare
   definition, owning model, queryable grains and `column` 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 actually made of, which is often a conditional rather than a
   column). An element defined as an expression carries no column rather than a
   guessed one. 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; `--api` exposes no relation at all and declares that in
   `unavailable`, so use `--local` when you need the physical side.

   Three free ways to narrow it, and they compose. `--metric <m>` keeps those
   metrics and what they reach. `--for-dimension <d>` asks the reverse question,
   returning the metrics groupable by all the named tokens, which is what you want
   when you know the slice rather than the metric and is also the cheapest way to
   find the metrics that can go on one chart against one axis. `--search <t>`
   takes a word rather than a name and matches it against every element's name and
   against the project's own label and description. Each names its scope in the
   payload (`scoped_to`, `for_dimensions`, `searched_for`), so a subset is never
   mistaken for the layer; an unknown metric or dimension is refused by name,
   while a search term that matched nothing comes back as a note. The catalog is
   also capped, with every cut counted in `elided` and named in `notes` and
   `--full` to lift the caps. `elided` is always present, so all zeros and no cap
   notes is the positive statement that this is the whole layer. Prefer narrowing
   over `--full`: it decides which part comes back rather than letting a cap
   decide.

   `values <dimension>` returns that dimension's value domain, which is what you
   need before writing a `--where` filter and the one thing no other dex command
   can reach on a hosted layer (`profile` cannot see a semantic dimension). A
   PII-flagged dimension refuses this command outright rather than being screened,
   because the whole output is values.

   `query` takes a positional metric after the explicit mode (with `--metric` kept
   for compatibility), a `--group-by <entity__dim>`, and optional `--where`,
   `--order-by`, `--grain` and `--limit`, and returns the 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, because a filter clause carries its own commas. `--grain` is checked
   against the grains the layer reports for the metrics queried, so a refusal
   names the ones that metric has.

   Two payload fields carry legitimate 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 can
   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.

   Two backends answer all three, chosen by `.dex/config.yml` `semantic.vendor`
   and `semantic.deployment` (the older `semantic.backend` spelling still works),
   overridable with `--local` / `--api`. Those two flags name **who executes**, not
   which vendor, and 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 the `[semantic]` extra for `values` and
   `query`; `list` reads the project and needs no extra). `--api` sends the query to
   a hosted dbt Cloud deployment (needs a host, an environment id and a
   `DBT_SL_TOKEN`, plus `[semantic-api]`, and 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 and no
   `--confirm` is asked. Either way a PII-shaped grouped or filtered dimension (for
   example `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.

   Read `${CLAUDE_SKILL_DIR}/references/semantic-playbook.md` before running a
   metric query: a metric's `time_axis`, `filter` and measures decide what the
   number *is*, and the playbook covers the discovery order, the additivity and
   time-axis traps this surface is full of, and when `values` answers rather than
   a query.

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.