Corrective Cultural Heritage Data: Pool Matching Without a Unique Key

When the SK Number Turns Out Not to Be a Unique Key

The corrective import of cultural heritage data from PPKD (Pusat Pengembangan Kebudayaan Daerah) should have been straightforward: match each new data row with an existing entity in the database using the SK (Surat Keputusan) number as the key, then fix spelling and fill in empty administrative columns. That assumption fell apart when data analysis revealed that 22 source rows shared only 6 SK numbers — a single SK designates multiple cultural heritage objects at once.

Direct SK-based matching merged 9 objects into 1 entity and overwrote names. The result: data that was already correct got corrupted by a process that was supposed to fix it. Row numbers (no) weren't a viable alternative key either, since rows 18–22 in the new set were a permutation of the old set.

Candidate Pool and Overlap Coefficient

The implemented solution splits the process into two stages. First: normalizing the SK number produces a candidate pool — a set of entities sharing the same SK. Second: each source row is matched 1:1 with an entity in the pool based on name similarity using the overlap coefficient (Szymkiewicz–Simpson).

The overlap coefficient is defined as the size of the intersection of two sets divided by the size of the smaller set. This formula was chosen because names in government data often have significant length differences — one set is almost always a subset of the other. A threshold of ≥ 0.5 means at least half of the tokens in the shorter name must match.

// Name normalization: lowercase, collapse whitespace, tokenize
func normalizeName(s string) []string {
    s = strings.ToLower(strings.TrimSpace(s))
    s = regexp.MustCompile(`\s+`).ReplaceAllString(s, " ")
    return strings.Fields(s)
}

// Overlap coefficient: |A ∩ B| / min(|A|, |B|)
func overlapCoefficient(a, b []string) float64 {
    setA := make(map[string]bool)
    for _, t := range a { setA[t] = true }
    inter := 0
    for _, t := range b {
        if setA[t] { inter++ }
    }
    minLen := len(a)
    if len(b) < minLen { minLen = len(b) }
    if minLen == 0 { return 0 }
    return float64(inter) / float64(minLen)
}

The 0.5 threshold isn't a magic number , it's the point where pairs that are too different get automatically rejected without losing valid matches. On the cultural heritage dataset, this value balances tolerance for spelling variations (e.g. "Negri" vs "Negeri") with preventing false positives.

The "Used" Guard for 1:1 Assignment

A candidate pool alone isn't enough. Without a prevention mechanism, two source rows sharing the same pool could overwrite the same entity , producing EntitiesUpdated == 2 when it should be 1. The "used" guard solves this: every entity that has been matched is flagged and removed from the candidate pool for subsequent rows.

// used: set of already-matched entities (prevents double-assignment)
used := make(map[int64]bool)

for _, row := range sourceData {
    candidates := pool[normalizeSK(row.NoSK)]
    bestScore, bestID := 0.0, int64(0)
    for _, ent := range candidates {
        if used[ent.ID] { continue }
        score := overlapCoefficient(
            normalizeName(row.Nama),
            normalizeName(ent.Name),
        )
        if score >= 0.5 && score > bestScore {
            bestScore, bestID = score, ent.ID
        }
    }
    if bestID == 0 {
        skip++ // no matching candidate ,  skip
        continue
    }
    used[bestID] = true
    // execute UPDATE for bestID
}

Without this guard, a second row whose best candidate overlaps with the first row's match would overwrite the already-updated entity. An integration test proves this: the fixture deliberately places two source rows that both best-match the same entity within one SK pool. With the guard, the second row finds no candidate and is skipped. Without it, both rows succeed in overwriting.

Selective Column Ownership

The corrective update only touches columns where the PPKD dataset is the authority: name spelling, address, subdistrict, designation rank, notes, and the empty year-of-designation field. Coordinates, media, and publication status are never modified , these three columns remain owned by the previous dataset.

This approach separates who "owns" specific columns. The PPKD 2026-10-13 set is the authority for administrative data and spelling. The 2026-10-09 set remains the authority for geospatial data and media. By restricting which columns can be updated, the corrective process never corrupts already-verified data.

One technical detail worth noting: the tahun_sk column in the source data can be either a number (2018) or a string ("2024"). The FlexibleString pattern , a custom type implementing UnmarshalJSON , handles both forms without data loss. The same pattern is already used in the cultural institution importer for the year-established field.

All data-writing CLI tools are protected by a fail-closed APP_ENV=development guard. This guard ensures that inherently destructive processes (bulk UPDATE) never accidentally run in a production environment.