Explore

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.

uv run --no-project --script "${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.

The first command in a fresh environment installs the engine, so it can take tens of seconds where later ones take well under a second. --warm pays that install up front and exits without running anything:

uv run --no-project --script "${CLAUDE_SKILL_DIR}/scripts/run.py" --warm

Offer it once at setup. It is not something to run before an ordinary command.

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 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.

   Three backends answer these commands, 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.

   The third backend is semantic.vendor: ossie, native Apache Ossie documents read out of the repository with no dbt project and no MetricFlow in the path (needs the [ossie] extra). It is catalog-first: list answers, and values, query and --for-dimension refuse by name, because Ossie specifies interchange metadata and no portable query runtime. Those refusals are the format's shape rather than a missing feature, and each one names the physical route instead: a dimension carries its semantic_model, that model carries its relation, and explore profile then explore query reach the values under the firewall and the cost guard. --api is refused too; Ossie has no hosted deployment.

   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, when values answers rather than a query, and what changes when the layer is native Ossie.

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.

An over-ceiling refusal now carries a calibration line drawn from .dex/spend.jsonl: what this connector's last few settled commands actually billed as a fraction of what they were estimated at, or a sentence saying the project has too little history to say. On a partitioned or clustered warehouse a dry-run estimate is an upper bound, so this is often the difference between a budget that admits the work and one that does not. Relay it verbatim when you surface the refusal, and note the part callers get wrong: the ceiling is checked against the estimate, so a budget set at the observed fraction of the estimate is refused again. It is still the user's decision, never yours.

When a needs_confirmation envelope carries suggested_session_ceiling, the project has never decided whether the day's total spend is bounded, and this is the one time it is asked. Surface it beside the per-command estimate and get the user's answer: --session-ceiling <value> sets a cumulative cap for the project (the suggestion is five times this command's estimate, a starting point, not a recommendation), and --no-session-ceiling records that the project runs unbounded. Either one is written to .dex/config.yml and reported as a diff, and nothing asks again. Add it to the same re-issue that carries --confirm --budget, or the confirmed run will stop once to ask. Never answer it on the user's behalf: it is a durable project setting, not a per-command flag.

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.