exmergo/dex / claude/skills/explore
explore
VerifiedUse this whenever you need to know what is actually in a database, warehouse, or DuckDB file before you trust it: inventory, column profiles, PII detection, grain and data-quality problems, verified joins, ER diagrams, ad-hoc SQL probes, segmentation, and the semantic layer a repo declares (dbt, Ossie, SLayer, or Cube). Trigger it on an unmet precondition, not on 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 model, fixing one, debugging wrong numbers) whenever the ticket names source tables without spelling out their schema. It applies mid-task too: on reaching a table you have not inspected, stop and use this rather than guess column names. Also for "what's in my duckdb", "is this data any good", "how do these tables relate". Read-only; writes only the .dex/ cache. It does not author the model: transform does, and maintain reconciles a project that has drifted.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 40.3 KB |
| evals/evals.json | config | 13.6 KB |
| references/commands.md | markdown | 37.2 KB |
| references/connectors.md | markdown | 6.9 KB |
| references/probe-playbook.md | markdown | 9.0 KB |
| references/semantic-playbook.md | markdown | 25.4 KB |
| scripts/run.py | script | 31.8 KB |
references/semantic-playbook.md
View on GitHub# Semantic playbook: reading a metric before you trust its number
`explore semantic` reaches a semantic layer: metrics, and the semantic models,
measures, dimensions and entities they are built out of. A metric query is not a
probe. There is no SQL to inspect, the layer decides what the number means, and
the same metric grouped two ways can return two numbers that are both correct and
not comparable. So the work is almost all in `list` and `values`, and `query` is
the short last step.
Three habits, in order of how much they save:
- **Scope the catalog rather than reading the layer.** A whole layer's catalog is
one payload and most of it is about something else. `list --metric <m>` if you
know the metric, `list --for-dimension <d>` if you know the slice, or
`list --search <word>` if you know neither. All three are free, none costs a round
trip beyond the first, and each names its scope in the payload. Use `--search`
when looking for metrics: it returns metrics matched by their own words or by
the elements they reach, together with what those metrics reach. To find an
unused measure, dimension or semantic model while authoring a metric, list
without `--search`.
- **Read the metric's caveats before its dimensions.** `time_axis`, `filter` and
`input_measures` change what the number is. The dimension list only changes how
it is cut.
- **Get the value domain before writing a filter.** `values <dimension>` is the
only way to know what a `--where` may filter to, and on a hosted layer it is the
only Dex command that can reach it at all.
## Discovery order
1. **`list`, scoped.** Start narrow. `--search` is metric-oriented: it takes a
word and matches it against a metric's name, label or description, the names,
labels and descriptions of the elements it reaches, and its groupable tokens.
It returns those metrics with what they reach. A term matching only an element
no metric uses returns an empty catalog; list without `--search` to discover
that unused element while authoring a metric. `--for-dimension pricing_tier`
answers "what can I slice by this", and is also
the cheapest way to find the metrics that can go on one chart against one axis,
because it returns the metrics groupable by **all** the tokens named.
2. **Read the caveats on the metric you picked** (next section). Stop here if they
say the number is not the one you want; going back is cheaper than a wrong
answer presented confidently.
3. **`values <dimension>`** for every dimension you plan to filter on.
4. **`query`**, once, with the group-by and filter you have now justified.
Before concluding anything about the layer, read four payload fields:
- `dimension_scope`. `queryable_paths` means every row is a token you can paste
into `--group-by`. `declarations` means the list is the single-hop declared view
and a query can group by more than it names, which is what `--local` reports
without the `[semantic]` extra. Two backends reporting different dimension
counts for one layer is this field, not a bug. A native Ossie layer always
reports `declarations`, because the format states no join graph to resolve
through.
- `unavailable`. Fields the answering backend structurally cannot supply. An
absent `label` here means "this path cannot carry one", not "the project
declared none", and the difference decides whether looking elsewhere is worth
it. `--api` has no `relation` on a semantic model at all, so use `--local` when
you need the physical side.
- `scoped_to`, `for_dimensions`, `searched_for`. Which narrowing produced this
catalog. Present means you are holding a subset.
- `elided`. What the payload cap cut, per element kind. All zeros and no cap notes
means this is the whole layer. Non-zero means narrow the question rather than
concluding the layer does not have something.
## Reading a metric
**`time_axis` first.** A layer's time token (`metric_time`) is one name over many
physical columns: it resolves to each measure's own aggregation time dimension.
One entry is the ordinary case. **More than one entry means the metric's measures
aggregate over different timestamps**, so grouping by `metric_time` buckets part
of the number by one column and the rest by another, invisibly, in a result that
looks like any other. Worse, one of those columns is often null on rows the other
one has, and those rows are then dropped. If you see two entries, either group by
a named time dimension instead, or say in your answer which parts of the number
are bucketed how.
**`filter` next.** A metric with a filter measures a subset. That is invisible in
the result and it is usually the explanation for a number lower than expected.
**`input_measures`, followed through to the measures.** A measure's `agg` and
`expr` are what the number actually is, and a measure is often a conditional
expression rather than a column: `sum(case when ... then 1 else 0 end)` counts
something narrower than its name suggests. This is also where additivity comes
from. A `sum` over a bare column is additive and can be totalled across any
grouping; an `average`, a `median`, a `count_distinct` and any ratio are not, so
the sum of the grouped rows is not the ungrouped total and you must not present it
as one.
**A ratio's two sides.** `composition.numerator` and `composition.denominator`
name other metrics (or measures). Read both. A ratio is never additive. Two ratios
that share a denominator can be compared and two that do not usually cannot. And
if the two sides live in different semantic models, a group-by valid on one may
not be valid on the other.
**`queryable_granularities`.** The grains the layer will accept for this metric,
which is per metric rather than a fixed ladder. `--grain` is validated against it,
so a refusal here names what the metric does have. An **empty list on a dimension
is an answer**: a categorical dimension has no grain, so do not ask for one.
## `values` versus a query
`values <dimension>` returns one dimension's value domain. Reach for it whenever
you are about to write a `--where`, and before telling a user what the categories
are.
Read `scoped_to` on the result, because it changes what the values mean. Empty
means these are the domain of the column behind the dimension. A metric name means
Dex had to reach the dimension through that metric, because a dimension behind a
join has no other rendering: neither layer will run a distinct-values query with
no measure to join from. Those are then the values **present for that metric**,
which can be narrower than the column's own domain, and the note names the other
metrics that reach it. Pass `--metric` to choose one yourself.
It never claims an exact cardinality, which would cost a second scan. A large
domain comes back capped and `truncated`, and says so.
Use a `query` instead when you want the distribution rather than the domain: group
the metric by the dimension and read the sizes. That costs a query where `values`
often does not. On a SLayer layer it always does: SLayer has no distinct-values
shortcut, so `values` renders a dimension-only query and runs it through the same
cost handshake as a metric query.
## When a command is refused
- **A PII-flagged dimension on `values` refuses the command outright**, rather than
being screened out of a larger answer, because the whole output is values. The
refusal names the two durable ways to clear a dimension reviewed as not personal
data: a `pii_overrides` entry in `.dex/config.yml`, or
`config: {meta: {pii: false}}` on the dimension in the project that declares it. Do not work around it by querying
the same column another way.
- **A PII-shaped `--group-by` or `--where` token refuses the query** before it
runs. `user__email` is the standing example: the token's own shape is enough.
- **A filtered query on a backend that cannot read its own filter dialect is
refused** rather than run with the filter half unscreened. Move the condition
into `--group-by`, or use a backend that reads its filters.
- **An unknown metric or dimension name is refused by name.** A search term that
matched nothing is not: it comes back as a note, because a substring matching
nothing is an honest answer about the layer's words.
- **A backend that declares a field unavailable refuses the command that needs
it**, rather than answering from the empty value. `--for-dimension` on a layer
whose backend lists `dimensions` under `unavailable.metrics` is the standing
example. Read `unavailable` before concluding anything from an empty list.
## When the layer is native Apache Ossie
`vendor: ossie` means the layer is native Ossie documents in the repository rather
than a dbt project. `list` works and reads the same shape as anywhere else, and
the payload says so on `vendor`. Three things differ, and each is a property of the
format rather than a missing feature:
- **`query` and `values` refuse.** Ossie specifies interchange metadata and not a
portable query runtime, so there is no filter grammar and no join planning to
render a governed statement from. Take the physical route instead: a dimension
names its `semantic_model`, that model names its `relation`, and `explore
profile` then `explore query` reach the values under the firewall and the cost
guard. Do not present the refusal as Dex being unable to reach the layer.
- **`--for-dimension` refuses**, because Ossie states no metric-to-dimension
relationship at all. `metrics[].dimensions` is empty and `unavailable.metrics`
says why. An empty groupable list here is not "this metric can be grouped by
nothing".
- **A metric with no `semantic_models` is a metric whose lineage did not resolve**,
not one that reads nothing. Ossie carries no metric-to-dataset reference, so Dex
reports only what a qualified reference in the expression proved, and reports
nothing when none did. A metric named `dataset.metric` is dataset-scoped
(`datasets[].metrics`): it aggregates that one dataset's fields, and its lineage
is that dataset.
A declared key or join names **fields**, and dex follows each field to the column
its expression names, so `explore relationships --use-project --verify` probes
the columns even where a field is named differently. A key or join on a field
with no column (computed, quoted, non-SQL) is named in a note and never probed.
An element with no `column` is also normal here and the notes say which of four
reasons applies: a computed expression, a quoted identifier, a query-backed
dataset source, or a field written only in a non-SQL dialect. In every one of them
Dex declined to guess a column rather than failed to find one, so do not go looking
for the column yourself and do not treat the absence as a data problem.
## When the layer is Cube models
`vendor: cube` means the layer is Cube YAML model files in the repository, read
statically with no Cube server. `list` reads the same shape as anywhere else:
- **A dimension token is `cube.member`**, and a view's member is
`view.member`. A view has no `relation`; each of its members carries the cube
member it exposes in `vendor_params.source`, and that cube's `relation` is the
table to profile.
- **A measure is listed twice on purpose.** An aggregating measure (`sum`,
`count`, ...) is a `measure` and a `simple` metric named `cube.member`. A
calculated `number` measure is a `ratio` or `derived` metric only. Measure
names are scoped to their cube, so two cubes may each have `count`: read
`semantic_model` with the name.
- **There are no entities.** `primary_key` and joins reach `explore map`,
`explore relationships` and `explore profile` through `--use-project`.
- **`compiled_only` lists what Dex did not read**: a JS model, a templated
(Jinja) YAML file, or a cube using `extends`. Those definitions exist; do not
report them as missing, and do not guess at their contents.
- **`query` and `values` need a Cube server, as a renderer.** Dex asks the
server at `semantic.cube.url` for the SQL and runs it itself, so
`CUBEJS_API_SECRET` must be exported (never put it in config), and a host
other than this machine must be named in `DEX_CUBE_ALLOWED_HOSTS`. No Cube
server running, or the wrong secret, is an error naming the fix; with none
available, use `explore profile` and `explore query` on the relations the
catalog names instead.
- **Speak Cube's dialect.** Metrics and dimensions are `cube.member` (or
`view.member`). Cube has no `metric_time`: group by the time dimension with
its grain, `--group-by orders.ordered_at__month` (custom granularities work
the same way). `--where` takes Cube's JSON filter, one object, a list, or
`and`/`or` groups; a date range on the grouped time dimension
(`inDateRange`) also bounds a rolling window. `--order-by` names only the
query's own members, `--order-by=-orders.revenue` for descending.
- **A stale-renderer refusal means restart Cube, not fix the query.** Cube
outside dev mode never reloads an edited model file, so after a
`semantic cube update` the server serves the old model until restarted, and
Dex refuses rather than run SQL from a model nobody reviewed. A refusal naming
a cube planned over another table than its `sql_table` is the same case: the
server holds another model than the repository. A refusal naming a
pre-aggregation means Cube would answer from a rollup table; query members it
does not cover.
- **`--api` hands execution to Cube.** The Cube server runs the query over
`/v1/load` with its own warehouse connection, so it answers on any warehouse
Cube supports (Databricks, ClickHouse and Trino included, which `--local`
refuses), with no estimate and no ceiling: `execution: vendor`, a `cost guard
unavailable` warning and `cost.paradigm: hosted` on every result. Prefer
`--local` where the connector is supported and spend matters. The PII gate
still runs first, and a Cube Core still has to serve the repository's model.
- **On Cube Cloud (`deployment: cube_cloud`) the catalog is the deployment's.**
`list` and the default query read Cube Cloud's compiled model, which can
include JS, Jinja and `extends` definitions the files only name, but carries
no relation or column (declared in `unavailable`), and omits primary keys and
private members. Every result says it was not checked against the repository;
to hold the deployment to the reviewed files, pass `--local`, which has Cube
Cloud render and Dex execute, and refuses a deployment whose members, kinds,
aggregations, filters, member SQL, joins or tables differ from the files.
`CUBE_API_TOKEN` must be exported (never in config); a host that is not
`*.cubecloudapp.dev` / `*.cubecloud.dev` needs `DEX_CUBE_ALLOWED_HOSTS`.
## When the layer is Databricks Metric Views
`vendor: databricks` means the layer is Metric View YAML files in the repository,
read statically with no workspace connection. `list` reads the same shape as
anywhere else:
- **A view is named after its file.** `views/orders.yaml` is the view `orders`,
whatever it is deployed as in Unity Catalog. Tokens are `view.field` and
`view.measure`.
- **A joined field has no `column`.** `customer.name` sits on the joined table,
named in `vendor_params.join`; that table is the one to profile. The view's own
`relation` is its `source`.
- **There are no entities and no time grains.** Joins and `rely` keys reach
`explore map`, `relationships` and `profile` through `--use-project`. A field is
`time` only where its `format` says date. A time bucket is the field's own SQL.
- **Read `vendor_params` before trusting a metric.** A window or `partition`
measure is listed as `derived` with its spec there, and is not modelled. The
view's `filter` is on every metric's `filter`.
- **`query` and `values` run on the Databricks connector only** (#1362). One
view per query; `query` needs at least one measure, while `values` reads a
field alone and takes `--metric` only to scope it. A window or `partition`
measure, a view declaring `parameters` or a `materialization`, and a measure in
`--where` are refused by name. On any other connector, use `explore profile` and
`explore query` on the relations the catalog names.
- **The answer is the reviewed file's, or none.** The deployed view is read back
and held to the file on everything the query relies on (#1363). A refusal for
a difference names the member and the field: relay it, and the fix is to
redeploy the reviewed file or bring the file in line and review it, never to
query around it. A view that cannot be read back is refused with its coverage
reason. `list --deployed` shows the deployed copy, with each view's coverage.
- **A `rely` key is a claim Databricks never checks.** A wrong one returns wrong
numbers silently, so `explore profile --use-project` and `maintain grain`
measure it; a duplicate there is a finding to report.
- **`deployment: databricks_workspace` reads Unity Catalog alone** (#1364): every
Metric View the source allowlist holds, with no file needed, and `query` and
`values` run the same way over it. Nothing is held to a file, so every result
warns that its definitions were read from Unity Catalog and not checked (and
says `source: account` or `definition_source: account`): relay that, and treat
a deployed view as the definition, not a reviewed one. A view is named by its
Unity Catalog name, not a file's stem. A view a configured file describes is
named on its `coverage` (`repository_file`); `--local` holds it to that file.
`coverage: refused` for two copies of one name means two allowlisted schemas
hold it: narrow with `--scope` (and the schema holding its source tables).
## When the layer is Snowflake Semantic Views
`vendor: snowflake` means the layer is Semantic View YAML files in the
repository, read statically with no Snowflake connection. `list` reads the same
shape as anywhere else:
- **Every name carries its view.** A logical table is `view.table`, a member
`view.table.member`, a view-level metric `view.metric`. The part after the
view is what Snowflake's `SEMANTIC_VIEW(...)` clause takes.
- **A fact is a dimension row that never groups.** `vendor_params.kind: fact`,
`groupable: false`: Snowflake queries facts on their own, never beside a
metric, so no metric lists one as groupable.
- **Entities are real.** A primary key is an entity, and relationships are joins
with their arity; they reach `explore map`, `relationships` and `profile`
through `--use-project`. There are no time grains.
- **Read `vendor_params` and `notes` before trusting a metric.** Window metrics,
`NON ADDITIVE BY`, `using_relationships` and ASOF or range relationships are
carried, not modelled. A private metric or fact is marked `access: private`.
- **`sample_values` never reach you.** Only their count does: Snowflake does not
mask them.
- **`query` and `values` run against the deployed view, on Snowflake only.**
Use the catalog's tokens, one view per query
(`sales.orders.revenue --group-by sales.customers.segment`). Facts never go beside a metric or a
dimension: query them alone (`--group-by sales.orders.total_price`). A grain
is written on the time dimension (`sales.orders.order_date__month`); there is
no `--grain` alone. `--where` takes dimensions and facts, never a metric.
- **A refusal naming a difference means the deployment is stale.** Dex reads
the deployed view back and refuses when what the query relies on differs from
the repository's file. Report it; redeploying is the maintainer's, never
Dex's. `explore semantic list --deployed` shows what the account holds, and
its `coverage` says per view whether the read was full.
- **`deployment: snowflake_account` reads the account alone** (#1357): every
semantic view the source allowlist holds, with no file needed, and `query` and
`values` run the same way over it. Nothing is held to a file, so every result
warns that its definitions were read from the account and not checked (and
says `source: account` or `definition_source: account`): relay that, and treat
a deployed view as the definition, not a reviewed one. Names come back as
Snowflake spells them (`SALES.ORDERS.REVENUE`), and tokens match ignoring
case. A view a configured file describes is named on its `coverage`
(`repository_file`); `--local` holds it to that file. `coverage: refused` for
two copies of one name means two allowlisted schemas hold it: narrow with
`--scope`.
## When the layer is SLayer models
`vendor: slayer` means the layer is SLayer model files in the repository. `list`,
`values` and `query` read the same shape as anywhere else, with a few things to
read differently:
- **A metric is a saved SLayer measure**, named `model.measure`, of type
`formula`. Its formula is under `composition.expr`. It declares no aggregation
of its own, which is why `measures` is empty and listed under `unavailable`:
read the formula for what the number counts.
- **A dimension token is `model.column`**, the reference a SLayer query uses. A
metric's groupable tokens are its own model's columns; columns reached through
a join are not listed (`dimension_scope: declarations`), so `--for-dimension`
answers within one model.
- **There are no entities.** The joins and keys are real and reach `explore map`,
`explore relationships` and `explore profile` through `--use-project`.
- **`query` and `values` run, and Dex executes them.** SLayer only renders the
SQL, as a dry run over the model files Dex validated; Dex runs it through the
same PII gate, relation pre-check and cost handshake as the dbt `--local`
backend, and `execution` is `dex`. Treat a result as cost-guarded, exactly as
you would a local dbt one.
- **One model per query.** A SLayer query is rooted at one model, so every
metric you pass must be a saved measure on the same model; query each model's
metrics separately. A formula such as `sum(amount)` is refused: it is a
definition nobody reviewed, so ask for it to be saved as a measure instead.
- **Group by the model's own columns.** `--group-by orders.status` works; a
column on a joined model does not, even where the layer declares the join.
Bucket time with `--grain month`, which applies to the model's
`default_time_dimension` (its `time_axis` in `list`). Order only by a metric or
a column you grouped by, with `-` for descending (`--order-by=-orders.revenue`).
- **A list column groups by its elements, `model.column.value`.** A column
declared `type: ARRAY` is listed with `groupable: false` and its
`element_type`; its elements are the token to group by and to read with
`values` (`--group-by posts.tags.value`, `values posts.tags.value`). Each row
counts the parents holding that element, so a post with two tags counts under
both: the rows overlap and do not sum to the total, and the result says so.
Grouping by the whole list is refused, naming the `.value` token. Filter on
membership with `contains(tags, 'dbt')`, `contains_any(tags, ['dbt', 'sql'])`,
`contains_all(tags, [...])` or `tags.value = 'dbt'`. A list of PII is screened
as its column, so `email.value` is refused wherever `email` would be. Redshift
has no native lists, so a list column is refused there.
- **`--where` is SLayer's filter syntax, not Jinja.** Write
`--where "status = 'paid'"`, with columns read relative to the query's model;
a dotted name (`customers.region = 'EU'`) walks a declared join. The gate
screens every column a clause names, joined ones included, so a PII column
refuses the query wherever it appears. A clause Dex cannot read, or one with
`{variable}` placeholders, is refused: write the value into the clause.
- **`values` before `--where`, as anywhere.** `values orders.status` reads the
column's domain on its own model, with the model's own filters applied, so
`scoped_to` is empty. `--metric` reads the values present for that metric
instead and names it in `scoped_to`, and is refused for a dimension the metric
cannot group by. A grain suffix (`orders.ordered_at__month`) works on a time
dimension only.
- **Not every connector.** DuckDB, BigQuery, Snowflake, Postgres and Redshift
are supported. Databricks and ClickHouse refuse with the reason, because
SLayer's SQL for them would run and return wrong numbers or would need a live
server, and Trino refuses until its rendered SQL has been reviewed. On those, take the physical route: a dimension's `semantic_model`,
that model's `relation`, then `explore profile` and `explore query`.
A model file Dex refuses (a stale `version`, a duplicate name, a join to a model
that is not configured) is named in `notes` with its file and element, and the
rest of the layer still answers. Report the refusal; do not edit the file to
silence it unless asked.
## Cost, and which backend answered
Every result names `execution`. `dex` means Dex rendered the statement and ran it
through its own connector, so the cost handshake applied and spend was surfaced
before it happened. `vendor` means the semantic layer ran it: Dex never held a
statement it could price or cap, so the result carries an explicit warning that
spend is governed there. Do not present a hosted result as cost-guarded, and do
not treat the absence of an estimate as "it was free".
`list` and the catalog side cost no warehouse query on any backend: one GraphQL
round trip hosted, one compiled-artifact read locally, one repository read for a
native layer. `values` and `query` do spend on the two dbt backends and on a
SLayer layer, and on SLayer they take the same handshake as `--local`: a billed
connector returns `needs_confirmation` with the estimate before anything runs. On
a native Ossie layer neither runs at all, so the only spend on that path is the
`explore profile` and `explore query` you reach for instead, and those carry
their own estimate and confirmation.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |