OpenRefine looks like a spreadsheet, but its useful mental model is different: it is a local transformation workspace with column profiling, batch operations, and replayable history. The best workflow separates exploration from irreversible-looking mass edits.
A safe working loop
- Import a copy and confirm encoding, separator, header, and row count.
- Create facets to understand missingness, distributions, and variants.
- State the intended canonical representation.
- Preview a transformation on representative rows.
- Apply it, inspect the changed count, and spot-check failures.
- Export the operation history as JSON.
- Reapply the recipe to a fresh import and compare outputs.
In plain terms: clicking is not the problem. Unrecorded clicking is the problem.
Facets are lightweight profiles
Text facets reveal distinct values and frequency. Numeric and timeline facets reveal range and density. Custom facets can test a condition:
value == null || value.trim() == ""Use facets before transformation to define scope, and after transformation to check whether expected variants disappeared. A facet selection acts as a temporary predicate: operations affect only matching rows, so the selection itself is part of the logic.
Clustering proposes; a human decides
Clustering groups values that may be variants. Key-collision methods normalize features and group equal keys; nearest-neighbor methods use a distance threshold. Different methods expose different risks:
| Method idea | Finds well | Main risk |
|---|---|---|
| Fingerprint | Case, punctuation, token-order variants | Distinct phrases collapse |
| Phonetic key | Similar pronunciation | Language dependence |
| Edit distance | Typos | Short strings over-merge |
Never accept an entire cluster solely because the tool grouped it. Frequency, surrounding columns, and domain knowledge decide whether values mean the same thing.
GREL makes transformations explicit
OpenRefine’s expression language can trim, split, replace, parse, and combine values:
value.trim().toUppercase()if(value.match(/^\d{4}-\d{2}-\d{2}$/), value, null)Build transformations from small steps. A long expression that cleans five concepts at once is hard to validate and harder to explain.
Reconciliation is not string replacement
Reconciliation links a value to an entity in an external vocabulary. A candidate match should preserve the source value, chosen identifier, service, confidence, and review status. Automatic matching is safest when identifiers or strong contextual fields exist; ambiguous names need review.
Operation history is necessary, not sufficient
Exported history records operations, but reproducibility also needs:
- the original input or a stable checksum;
- OpenRefine version and relevant extensions;
- assumptions behind manual cluster choices;
- row counts and quality metrics before and after;
- unresolved and rejected cases.
The recipe should run successfully on a fresh project. If manual choices cannot be explained, history records what happened without explaining why.
Review card
- Confirm import settings and baseline counts first.
- Facets define both evidence and operation scope.
- Clustering produces candidates, not truth.
- Keep transformations small and previewable.
- Reconciliation should preserve identifiers, confidence, and review state.
- Export history and verify it by replaying against a fresh import.