CS 513 Visual Guide to Data Cleaning

Cleaning data involves more than making strings look consistent. A suggested match needs evidence; a constraint violation can have several valid repairs; a cleaned result needs an explanation of how it was produced.

These five diagrams follow that progression. They use small, synthetic datasets based on the CS 513 lectures, not real customer records or a production cleaning engine. SQL syntax is left to the existing notes.

Use Next, Back, and Reset to inspect one change at a time. Scenario buttons restart the current diagram. On a narrow screen, scroll the diagram horizontally. Nothing here edits your files or sends data to a service.

From similar names to a confirmed mapping

A text facet counts distinct values and helps reveal blanks and spelling variants. Clustering goes further: it proposes groups that might represent the same thing. Neither operation establishes identity.

In this example, Harbor Cafe, CAFE HARBOR, and a name with extra whitespace share a token key. But one record is in another city. Even the two records in the same city need corroboration.

Three names share the key “cafe harbor”; the blank is excluded. Without identity evidence, all three stay pending. With a verified directory mapping, only r1 and r2 receive a canonical name, while their original values and rows remain intact.

Switch between missing evidence and a confirmed mapping. The difference is not a more aggressive string rule: it is additional information from a trusted source. Here, two rows share a verified venue ID.

The key-generation demo is restricted to these ASCII names, not a full implementation of OpenRefine. Its clustering documentation distinguishes syntactic similarity from semantic reconciliation. The lecture makes the same point with two similarly named hotels in different cities.

A canonical name is also not a command to delete duplicate-looking rows. Two records may describe separate observations of the same venue. Decide the record’s meaning before deciding whether to merge it.

For the complete workflow, see OpenRefine cleaning.

Why a valid repair may not be the true answer

Suppose each student ID must determine one name. Two IDs have conflicting names, while a third is unambiguous. The rule proves that the conflicting tuples cannot all remain together. It does not prove which spelling is correct.

There are two independent conflicting pairs. Under tuple-deletion-only, set-minimal repair, keeping one tuple from each pair gives four repairs. All preserve (107, Wu). Every repair also preserves IDs 101, 104, and 107, although the names for two IDs vary.

This example allows only tuple deletion and one functional dependency. A repair is set-minimal when no smaller subset of its deletions would suffice. Equivalently here, adding back any removed tuple reintroduces a conflict. All four repairs also delete the same number of tuples, but set-minimal and minimum-cardinality repair are not interchangeable definitions in general.

The final frame asks what is true in every repair. Change the query from “ID and name” to “ID only.” Only (107, Wu) is a certain full tuple, but all three IDs are certain answers to the second query. Compute the query in each repaired world, then intersect the answers; simply querying the intersection of the databases can lose answers.

The four versions are logical alternatives, not four recommendations to execute. Real repairs need a policy and evidence. See database repair and consistent answers.

Follow an output back to its evidence

When someone asks why a cleaned file looks the way it does, its filename is not enough. We need the input version, the activity that transformed it, the rule version, and who was responsible.

clean.csv v2 wasGeneratedBy clean-run-42. That activity used raw.csv v1 and rules.json v3, and wasAssociatedWith a reviewer. If the rule version was not recorded, the dependency stays unknown.

These relations follow the W3C PROV data model: an entity is an artifact, an activity is what happened, and an agent bears responsibility. Agent does not specifically mean an AI agent.

Read the arrow labels: the output points back to its generating activity. This is a provenance view, not a diagram of data being delivered forward. It records dataset-level dependencies, so it cannot explain every cell without finer-grained evidence.

Try the missing-rule scenario. A plausible rule must not be invented to complete the diagram. Provenance makes a result inspectable; it does not, by itself, certify its correctness.

Separate the recipe from the run record

The same pipeline can run on different inputs or apply different human decisions. A diagram of intended steps cannot tell us what happened yesterday.

The plan declares input, normalization, review, and export dependencies. A run record instead names actual input and code versions, a saved review decision, and the output. The synthetic run retains four rows, including one unresolved candidate and one blank name.

Prospective provenance is the workflow structure. Retrospective provenance is evidence from a particular execution. Switch between the two views to see what information changes.

YesWorkflow extracts declared workflow structure from script annotations. Those declarations are not, on their own, proof that a script ran. Runtime evidence has to be captured or reconstructed separately. If a human confirms a mapping, save that decision as an input artifact too; replaying code cannot recover an undocumented judgment.

Reproduction also depends on available data, code, environment, and external dependencies. Read more in workflow automation and provenance.

Compare concept boundaries instead of labels

Two organizations can both use the label “North” while assigning different places to it. This is not merely a spelling problem: the concepts cover different sets.

For complete, nonempty sets, compare A-only members, shared members, and B-only members. The result can be equality, strict inclusion in either direction, partial overlap, or disjointness. For A={1,2} and B={2,3}, all three regions are nonempty, so the relation is partial overlap.

Choose a relation and step through its members. These are invented region IDs with complete membership. Partial overlap requires members shared by both concepts and members exclusive to each. An intersection alone does not distinguish overlap from containment or equality.

This is the local building block of taxonomy alignment, not a complete alignment reasoner. Across a whole hierarchy, parent–child relationships, coverage assumptions, and disjointness constraints must also agree. With incomplete evidence, multiple interpretations may remain possible.

Sources and scope

The diagrams are original teaching examples, redrawn from the mechanisms in the supplied CS 513 materials:

03-OpenRefine-Introduction.pdf, especially clustering and its caveat on pages 35–36, with the OpenRefine introduction transcript.
05-Datalog-123.pdf, pages 11–15, with From IC Checking to IC Repair and Repair with DLV transcripts. The lecture’s larger repair example is reduced to two independent conflicting pairs here.
07-Workflow-Automation-and-Provenance.pdf, especially pages 14–16, and From Workflow Automation to Provenance.
08-Data-Provenance.pdf and Finer Points of Provenance.
11-Taxonomy-Alignment.pdf, including its alignment problem and possible outcomes on pages 10–14.

The practical connection is simple: propose a change, identify the evidence that permits it, preserve uncertainty where evidence is missing, and record what was actually done. More uniform-looking data is not automatically better-understood data.