CleanDims

From connected sources to one canonical ID.

CleanDims reads the schemas and distinct values of your sources, never whole rows. Every step a machine scores, a person approves, and each decision is kept and reused.

INPUTSchemas and distinct valuesHUMAN GATESMappings and uncertain groupingsOUTPUTCanonical IDs and crosswalks

What it is.

Start with the datasets that should join and the text columns they share. Those columns may be named differently, written in different vocabularies or reported at different hierarchy levels. CleanDims builds the shared dimension rather than assuming a common key already exists.

Read schemas and distinct values, never whole rows. Schema information identifies candidate columns; distinct values and their record counts provide the context for grouping without copying complete records out of the source.

Columns are grouped into dimensions using five signals and one score. You approve each mapping before the values are collated. The resulting groupings are reviewed, canonical IDs are published and the outputs are downloaded or synced.

Approvals, review and thresholds.

The dashed gates in the workflow are the two human decisions. First, approve which source columns describe the same dimension. Second, review value groupings that fall below the confidence threshold. A mapping takes effect only when someone approves it.

At or above the threshold: auto-approved. A grouping below the threshold waits for a reviewer. Approve a new canonical value, rename it, move it to another grouping or exclude it when the evidence does not support a join.

Each decision is kept and reused. Review is about resolving the uncertain cases, not repeating every familiar alias. The demo illustrates these decisions with sample data and simulated connections.

The five metrics: evaluating the discipline.

The original learning material describes five useful measures: coverage, drift, resolution latency, resolution coverage and confidence depth. They remain a framework for asking whether dimension management is working, not a claim that all five are released product dashboards.

Coverage asks how broadly relevant dimensions are mapped. Drift asks how often unfamiliar values appear. Resolution latency asks how long uncertain cases wait for a decision. Resolution coverage asks how much of the intended data has a canonical key. Confidence depth asks how reliable those mappings are.

For a join pilot, agree which dimensions and hierarchy levels matter, inspect the unresolved values and confirm the outputs join as intended. Avoid treating an illustrative metric panel as a production service-level commitment.

Publish, use and monitor.

Canonical IDs are published with hierarchy levels. Dimensions include IDs, labels, levels, parents and aliases; crosswalks connect the raw values in each source to those IDs. Join any sources on the ID, at the level where both datasets meet.

Monitor new values, new columns and drift. New values are re-collated and pass through the same review. Reusing approved dimensions reduces repeated work on later joins.

  1. 01 / Discover

    Read sources

    Schemas and distinct values, never whole rows.

  2. 02 / You

    Approve columns

    Columns grouped into dimensions. You approve each mapping.

  3. 03 / You

    Review values

    Unique values collated, cleaned and grouped. You review below-threshold groupings.

  4. 04 / Publish

    Publish IDs

    Stable IDs with labels, hierarchy levels, parents and aliases.

  5. 05 / Join

    Use outputs

    Download or sync dimensions and crosswalks. Join sources on the ID.

  6. 06 / Repeat

    Monitor

    New values, new columns, drift. Saved decisions are reused.

Mappings and uncertain groupings wait for people; the remaining steps carry approved decisions forward.
Schemas and distinct values enter; people approve mappings and uncertain groupings; IDs and crosswalks return to the join.
SCENARIO

A reviewer’s queue for a supplier join.

A procurement analyst is joining invoice vendors to a contract register. AWS and Amazon Web Services, Inc. are familiar aliases; Amazon is ambiguous; an unfamiliar supplier may need a new canonical entry.

The reviewer checks the proposed column mapping, moves the ambiguous value to the appropriate grouping and approves the genuine new vendor. After publishing, both sources have crosswalks to the same canonical dimension. When fresh values arrive, the saved decisions are reused and only uncertain cases wait for review.

Request a pilotBring two datasets and the level you need to join at.