Inventory: one cheap pass
Inventory is a single catalog round-trip with no table scans. For each object it records the cheap facts the catalog already knows: object type (table or view), an estimated row count, and a column count. Byte size is left unknown rather than fabricated, because there is no cheap per-object byte size to read; the row estimate is the size signal that feeds ranking. This pass is what makes selective drill-down possible: you cannot rank what you have not listed, and you should not scan what you have not yet decided is worth scanning. The row count is an estimate where the catalog keeps one and unknown where it does not, and the two are never collapsed into each other. A warehouse maintains no count for a view, and BigQuery maintains none for an external table either; several report that as a zero, which is why the count is decided from the object’s kind rather than taken at face value. Unknown means the size signal cannot participate, so an uncounted object ranks as if it were small until something counts it. Empty means the object holds nothing, which is a finding. Reading the first as the second was how an external table over object storage came to be reported as an empty table with correct column statistics beside the claim.Ranking: turn a list into a shortlist
Ranking scores every object in [0, 1] from cheap signals so attention goes to the objects that matter first. The score blends four normalized signals:- Size (log-damped row estimate): bigger tables matter more, but the log keeps one giant log table from crushing everything else.
- Connectivity (degree in the inferred-join graph): a hub table that many tables reference is central to the model.
- Naming: a boost for analytics-engineering conventions (fact, dimension,
staging, mart prefixes) and entity-shaped names; a penalty for scratch, backup,
temp, and test names. Configured
ranking_hintsadd explicit boosts. - Shape: a moderate column count reads as a real modeled entity; extremely wide or single-column tables are damped.
Profiling: understanding from aggregates, never rows
Profiling builds a column-level picture from SQL aggregates only. For each object it issues one batched aggregate query (a non-null count, an approximate distinct count, and conditionally a min and max), batching wide tables so a single statement never balloons. From those it derives null fraction, distinct count, and a uniqueness signal. The distinct count is approximate for scale, so uniqueness starts as a candidate signal, never a proven key. Because an approximate count can overshoot a genuinely unique column and turn a real key into a false “grain unknown”, profiling then escalates: any column whose approximate distinct count sits near its non-null count (within a bounded band) is re-counted with an exactCOUNT(DISTINCT) in one batched, read-only statement,
capped to the few closest candidates per table so the escalation stays cheap. An
escalated count is marked exact, and only an exact count is allowed to confirm a
key or a table’s grain; downstream consumers (relationship fan-out notes included)
never draw a hard conclusion from an approximation.
When no single column proves unique (the shape of a fact table, whose grain is
exactly what a profile must answer), profiling probes 2-column composite keys. A
pair can only be a key if the product of its members’ distinct counts reaches the
row count, so pairs are pruned on that necessary condition using the counts
already in hand, then ranked (id-shaped members first, smallest product next) and
capped to a few probes issued as one exact distinct-combination statement. A pair
that reuses a column from a better-ranked pair at a similar product is the same
hypothesis with different filler, so it ranks behind every pair that is not, but
it is never removed from the running: the cap is the spend guard, and while it
has room a discarded candidate costs a grain and saves nothing. A pair whose
combination count equals the row count is unique, which is necessary for a key and
not sufficient to be one. On metered connectors the probe spends only inside the
already-confirmed budget, narrowing to the best-ranked pairs the budget covers, and
skipping with an explanatory note only when it cannot cover one.
Two shapes of pair are unique for reasons that have nothing to do with the table’s
identity, and both are excluded from the pool before anything is priced. A pair
whose member is a continuous measure is one: a measurement’s cardinality grows
with the table, so it completes a partner by arithmetic rather than by meaning, and
a key made of money is almost never the grain anybody intended. The other is a pair
anchored on a column that is already unique on almost every row. At that ratio
the handful of rows the column fails to separate get separated by accident by any
partner carrying more than a value or two, so the pair restates the column’s
near-uniqueness instead of discovering a grain. The exception, and the reason this
is a test on the pair rather than on the column, is a partner whose domain is
bounded by the fanout rather than by the table: when a few duplicate order ids are
separated by a three-value line number, the data really does have that grain. A
near-unique timestamp gets no such exception, because a per-row event time is not
an entity whose rows a position column enumerates.
Excluding before probing is deliberate on three counts: the exact answer could not
move a decision either way, the pair would have to be reported and immediately
disclaimed, and dropping it frees a capped slot for a pair that might be the real
grain. None of it applies to a table too small for cardinality-versus-rows
reasoning to mean anything, where every column looks near-unique and every measure
looks continuous: below a fixed row floor everything is asked and the exact count
decides.
What survives enters the candidate keys, smallest proven cardinality first, so the
tightest proven key is the first entry and, absent any single-column key, the
reported grain. Where nothing survives, the grain is reported unknown and the
near-unique column is named with its counts, because the finding there is
duplicates in the source rather than an absent key.
Suppression is never silent. Every combination the probe considered comes back in
key_evidence, one entry per combination, carrying its columns, whether it was
reported or suppressed, and the reason in the profile’s own words. The reported
entries appear in the same order as the candidate keys, so the ranking is readable
rather than implied, and the suppressed ones follow. That is also what makes a
probe that ran and found only artifacts distinguishable from one that never ran,
which the probe’s own budget notes say instead.
A candidate key held as a string is also checked for mixed value shapes, the
trace a partial migration or a merged upstream leaves in an id column. The
profiling scan sorts every non-null value into one of four groups, numeric, dashed
UUID, hexadecimal, or other, and returns only the fraction in each. When two or
more groups each hold at least 5% of the values, the profile names them in a
data-quality note. A value counts as hexadecimal only at 8 characters or more:
below that, ordinary codes such as two-letter country codes or airline seats like
12A are spelt in the letters A to F by chance often enough to clear 5% on their
own. A shorter hex value counts as other instead, so a population of short hashes
mixed into a numeric key is still reported, under that name. The note ends with
the consequence that fits the groups it names: with a numeric group, a cast or
numeric comparison silently drops the rest; without one, a join or filter written
for one shape silently misses the others. Only the fractions and a label derived
from the hex lengths (32-character hexadecimal (md5-shaped)) leave the engine,
never a value.
Two safety rules are enforced at the source, in the SQL that is generated:
- min and max are surfaced only where the extreme value is not itself sensitive: numeric and temporal columns that carry no PII flag. For any string column, or any column flagged as PII, min and max are never even computed, so a raw or sensitive value never leaves the engine.
- All generated SQL is read-only. Beyond the read-only connection, every statement is parsed and refused if it is not a single read-only SELECT.
PII: flagged, never surfaced
PII is detected from column names and aggregate shape, never by inspecting values. A name-pattern table maps a column to a category (email, phone, name, address, government_id, financial, credential, location, date_of_birth) with a base confidence, which aggregate signals then nudge (a near-unique text column on an email-like name strengthens the flag; very low cardinality on a location-like name weakens it). The result is recorded strictly as (column, category, confidence) with no example value, and that flag is what propagates downstream into emitted dbt. For the weakest signal, a generic*_name string column, the profiling scan also
computes three value-shape statistics as in-engine SQL aggregates: the
fraction of values that are all-caps tokens, the fraction shaped like a given
name plus surname, and the average token count. These are regex predicates
inside measuring aggregates, so only numeric fractions leave the engine, never a
value. The evidence moves confidence in both directions: a person-shaped
distribution corroborates the flag up to the exact-token level, while a tiny
closed all-caps vocabulary (a region or nation dimension) or long multi-token
labels (part and product descriptions) de-rate it to reference-data confidence.
When the evidence is missing or ambiguous, the name-derived confidence stands:
absence of evidence never weakens a flag.
The flag itself is never removed by evidence. What a weak flag means is the
consumer’s decision, and the consumers differ: some gate on a flag’s presence at
any confidence, others compare it against a blocking threshold. That split, the
threshold, and the only way a human clears a flag are in
�0.
Relationships: joins from metadata, not scans
Relationship inference reads the profiles already gathered and never scans data to verify referential integrity, so it stays free and read-only at the cost of certainty, which is why every inferred join carries a confidence. The opt-in verification pass is the one place joins are measured, and it batches: the joins sharing a child relation are answered by one read of it, so what verification costs follows the relations it touches rather than the number of joins between them. A join is proposed when a foreign-key-shaped column name matches a parent object whose corresponding column is a candidate key and the types are compatible; confidence reflects how strong the name and key signals are. Candidate keys and the most likely grain come from the uniqueness signals: single columns proven unique, plus the composite keys proven at profile time that survived the artifact exclusions above; a single-column key is always preferred as the grain, and a member of a composite key is never treated as unique on its own. Where the strongest signal is a column unique on almost every row but not all of them, that is what the profile says, naming the ratio, the counts, and how many rows would have to be removed for the column to be unique. It reports no key at all rather than a combination built on the shortfall, because a caller who cannot tell a real key from filler is worse served by several candidates than by one named defect and none. Declared joins come from whichever channels declare one: the dbt project when one is present, and the semantic layer when one is configured, which need not be dbt’s. Absent both, declared joins are simply empty, which is expected because explore is designed to work without either. A declared join carries every column pair it was written with, so a composite is one declaration measured as one complete tuple rather than a first-column proxy that would report a healthy join three times over while no tuple matched. Where two channels declare a join between the same pair of relations on different columns, both are kept and the disagreement is reported; dex does not pick a winner between two things the repository asserts. Declarations refine those verdicts without entering them. A declared grain fills in where measurement found none, and is noted where it disagrees, but a measurement-proven single column still wins the reported grain and the candidate keys stay measurement-only: an unmeasured declared key is a claim, and the cache is a drift baseline rather than a record of what the project asserts. The consequence is that a declared grain needs verifying somewhere else, which is whatmaintain grain does with it, and why that axis reports two different things about
uniqueness. A key measurement proved unique that no longer is has a before and an
after, so it lapsed. A declared combination that does not hold has neither: nothing
changed, the project is asserting a grain the data never had, and the fix is to the
declaration rather than to the data.
Row population: a model against the relation it is built from
Everything above measures a relation on its own terms. A transformation project raises a question none of it answers: not what this relation looks like, but whether it holds the rows it should, given the relation it was built from. That is where the expensive errors live, because they raise nothing. An inner join written where a left join was meant, a filter that quietly excludes NULLs, a de-duplication keyed on the wrong column: each drops rows, none is an error, and uniqueness and not-null tests all still pass over the smaller result. The comparison needs one thing the warehouse cannot supply, which is the model’s driving parent: the relation in its FROM clause, as distinct from the ones it joins. Those are different roles. The driving relation sets how many rows the model starts with, and a join can only reduce that number or multiply it. So the parent is read out of the compiled SQL rather than out of the dependency graph, which knows a model’s parents but not which of them drives it, and it is followed through the chain of CTEs a dbt model compiles to, since the FROM of a compiled model’s final select names an internal CTE almost every time. What the SQL says about itself decides whether the numbers mean anything. A model carrying a filter, an aggregate, a de-duplication, or a set operation was written to hold fewer rows than its parent, and no amount of arithmetic distinguishes a filter that removed the rows it should from one that removed too many. There is no principled bound to compute, so those models are not reported at all rather than reported with a caveat. The cost of that choice is real: a filtered model that also lost rows to a bad join says nothing. The cost of the other choice is worse, because a detector that fires on every aggregate in a project is one nobody reads twice. The same logic runs the other way for growth, where anUNNEST or a lateral is a construct whose entire purpose is turning one
row into several.
Row counts themselves come from the cheapest source that can answer, and which
one answered changes what the finding claims. A stored catalog count is free but
is treated as an estimate, so the finding is not marked exact; an actual count is
a proof. Where counting bills nothing, everything is counted, because a verdict
about a ten percent difference has no business resting on an estimate. Where it
bills something, it is priced and offered rather than taken, and the models it
would have covered are named as uncompared. That last part matters more than it
looks: a warehouse keeps no row count for a view, and a view is dbt’s default
materialization, so on a metered connector the models this can judge for free and
the ones it cannot are split down exactly that line.
Test strength: measuring the tests rather than the data
Everything above measures data. A dbt project also carries assertions about that data, and those assertions are themselves unmeasured: a suite of twenty tests that all pass proves the tests ran, not that any of them would object if the model were wrong. The two are routinely confused, because the only number the ecosystem reports is a count, and a count cannot distinguish anot_null on a
surrogate key from a unit test pinning the arithmetic.
The measurement that does distinguish them is to break the model deliberately and
see whether anything complains. transform test --mutate plants one defect at a
time, drawn from the same taxonomy the rest of this document is organised around:
a boundary comparison that now includes or excludes its edge, a filter dropped or
inverted, an inner join where a left join was meant, a missing CASE branch, an
inverted ratio, a window frame off by one, a sum reporting a max. Each is
built and the model’s own tests are run against it. A defect nothing catches is
reported as a gap in the suite, described as the defect rather than as a diff,
because the reader’s next step is to write a test and not to reread SQL they
already know.
Three properties keep the result honest. Every verdict is relative to the tests
that passed against the unmutated model, so a suite measured against its own
already-failing tests cannot come back looking clean. A mutant the warehouse
refuses outright is reported separately from one the tests caught, since a build
would have failed on it anyway and counting it would flatter the suite. And a
mutant that survives is a statement about detection, not about correctness: some
survivors are defects the current data cannot distinguish at all, such as an
inner join where every key happens to match, which is exactly why the finding
names the test that would catch it rather than claiming the model is wrong.
dex measures which survivors those are. Each one is compared with the unmutated
model on the dev target, and the two kinds need different tests: a survivor the
data cannot tell apart needs a fixture that reaches the case, since no assertion
over today’s rows can see it, and one the data already tells apart needs an
assertion over those rows. The comparison fingerprints every row of each side,
counts the fingerprints, and reports how many rows each side has that the other
does not. It counts a multiset rather than a set, so a mutant that only
duplicates rows still differs, and it returns four integers and never a row.
Two limits are worth knowing. The fingerprint is a 64-bit hash, so two
different rows could in principle collide and read as the same; across the row
counts a dev target holds that is vanishingly rare, but it is not impossible.
And a model has to reproduce its own output for the comparison to mean
anything, so the unmutated model is compared with itself first. A random(),
a uuid or a row_number() over ties fails that self-check and no survivor is
labelled. A floating-point aggregate can differ in its last digits between
evaluations, and the self-check only catches that when it happens to show, so
a distinguishable label on a model that sums floats deserves a second look.
The draft map: composing and persisting
explore map composes the above into the .dex/ cache (never the source of
truth; see canonical-model.md). It ranks first on cheap signals, profiles a
selective top set by default (with a --full option to profile everything, and an
automatic profile-all on small warehouses), infers relationships among the
profiled set, then re-ranks with connectivity for the final scores. The selective
pass never caps silently: the summary states how many objects were profiled versus
skipped and the rule that drew the line. On a re-map, an object that falls outside
this run’s top set keeps its prior profile (carried forward, each dataset stamped
with its own profiled_at) rather than dropping to inventory-only, so coverage
accumulates across runs. The cache is re-derived and replaced on each run so
dropped objects disappear, while the original creation timestamp is preserved. What
is printed back is a counts-level summary, never the cache contents, keeping the
output sense-making rather than a dump.
Diagrams: serializing structure, never drawing values
explore diagram renders the cached map as a Mermaid erDiagram. It is a pure
function of the cache: it opens no connection, needs no credential, spends
nothing, and can be re-run freely while a diagram is being shaped.
The rule that bounds it, and every renderer after it: dex may serialize
structure it has already computed into a text format, and dex never renders data
values into a visual encoding. An entity-relationship diagram of the objects,
keys, and joins is structure. A chart of null fractions or value distributions is
a picture of the data itself, which is a different product’s job. In practice
that admits erDiagram, flowchart, and classDiagram, and rules out pie,
xychart, sankey, and quadrantChart. No column value reaches a diagram: the
cached min and max are never read, and a PII flag renders as its category and
confidence, because flagged-not-hidden is the posture everywhere else too.
A diagram is trusted more readily than the JSON it came from, so the cardinality
rules are strict. Mermaid requires a glyph on every edge and dex’s relationship
record carries no cardinality, so the renderer derives one only from what was
proven. It claims “exactly one” on the parent side only when the parent key is a
proven key and the join was declared in the repository or measured with no
orphans; it degrades to “zero or one” when uniqueness is proven but nothing
measured the overlap; and it degrades to “zero or many” when uniqueness was never
established at all. Declared joins are drawn solid and inferred joins dotted, and
the edge label carries the kind, the confidence, and the orphan fraction, so an
unverified guess says so on its face.
Selection follows the same rule as the rest of explore: a fully attributed
diagram of a large warehouse is a schema dump, so the default draws objects that
were profiled and participate in a join, carrying their grain, key, join, and
PII-flagged columns. --full widens to every eligible object and column, an
entity cap binds in both modes, and everything left out is counted in the notes.
Rendering is deterministic, so the same cache produces byte-identical output and
a regenerated file diffs cleanly. dex writes no diagram file: the text comes back
in the envelope and choosing where it lands is the caller’s decision.
What the agent sees
Every command prints exactly one sanitized JSON envelope (seecommand-contract.md); credentials and raw rows can never cross that boundary,
and a leak is a hard failure rather than a silent scrub. The agent reads the
envelope and decides the next step, so multi-step exploration is the agent
orchestrating stateless subcommands over the repository and the .dex/ cache.
Source: references/methodology.md (edit on main). This page is generated; change the source, not the site.