Record ids

​

In short

• Key your tables on _ploti_record_id. Store it with _ploti_dataset_id when one table holds several datasets.
• Store the id as it is. Don’t parse it or try to rebuild it from the record’s data.
• Apply each changes file as written: upsert or delete by id. Your copy then matches the version.
• A record Ploti could not match to the version before arrives as a delete of the old id and an added row with a new one. Anything you attached to the old id needs attaching again.
• When a version arrives as feed.dataset.resync_required with reason record_ids_changed, reload the dataset and rewrite the ids you store with the version’s id map.

​

What an id identifies

• It is scoped to its dataset. The same parcel in two datasets has two ids, and nothing about the ids links them. To key records from several datasets in one table, store the id with _ploti_dataset_id.
• It is not a source key, a row number or a hash of the row. It cannot be recomputed from the record’s data, so a record whose values all change can keep its id, and two identical rows have different ids.
• It is opaque. It is a string that starts with rec_. Nothing else about its format is promised: store it as it is and do not parse it.
• It names one record and is not given to another. A record you were sent as removed that later returns at the source usually comes back under a new id. It takes back its old id only when Ploti ties it to that record, in a version that says so with an id map or in a reload of a version you had.

​

What you can rely on

• No two rows of a version share an id, and a version’s snapshot, changes file and id map use the same ids.
• A record matched to the version before keeps its id. A record that is not matched arrives as a delete of its old id and an added row with a new one, or, in a version you reload, as a new id in its snapshot.
• A matched record’s id changes only in a version sent as feed.dataset.resync_required with reason record_ids_changed, and that version’s id map gives its new id.
• When a version you already had is released again, its rebuild is matched to what Ploti kept for that version, and a record it matches keeps the id you hold. The rebuild arrives as feed.dataset.resync_required: reload it, and a record it did not match has a new id in its snapshot. When it kept records but none could be compared, the rebuild is held for review rather than sent.
• A version waits for Ploti’s matching agent before it is released, so the ids you receive are normally the ones matching settled on. When a later match ties a record you hold under one id to an earlier one, the version that applies it says so with an id map.

​

How records are matched

1. Key. When the dataset is keyed (keyed is true, with keyColumns such as a permit number), a record whose key value appears once in each version is the same record. An edit arrives as one changed upsert.
2. Same content. A record identical to one in the version before is the same record, compared over the columns both versions have in the same shape (the same name and type). A column holding one value in every row of a version, such as the time the source was read, is a property of the version and is left out. Identical rows pair one to one, so duplicates keep distinct ids.
3. Agent. A record left over in the new version goes to Ploti’s matching agent when it shares values with a left-over record of the version before, or lies within about 50 m of it. It keeps the old id when the agent judges it the same record, edited or renumbered.
   • When a column turned over in most left-over records and the records are otherwise identical, the agent judges that column once for the whole version. Examples are a source renumbering its ids, or a time it stamps on every edit.
   • At most 200 records a version are judged one by one, chosen among the first 20,000 left over, and none when more than 250,000 are left over. The rest get new ids.

• the agent still has not decided, or cannot be asked at all (identity_unresolved). A version whose last ask is cancelled is held at once.
• most of its records would get new ids while its row count barely moved (identity_ambiguous)

• A record matches by key only when the key columns are unchanged.
• A record matches by content only when both versions have the same set of columns.
• When Ploti kept records but nothing can be compared, a version that leaves any of its records unmatched is held for review (identity_ambiguous).

​

Keys and schema changes

• A version where the key has duplicates or nulls is held for review (key_violation). The key is not unpinned.
• Pinning a key for the first time is a MINOR schema change with keyColumnsChanged: true.
• A key that changes from one set of columns to another, or is dropped, is a MAJOR change and is held for review.

• Matching against the previous release uses only the columns both versions have in the same shape, and the new key when the version before has its columns.
• Matching against what Ploti kept uses the key only if it is unchanged.

​

What is not promised

• That every edit keeps its id. Matching is conservative. A record the key, its content and the agent cannot tie to the version before gets a new id, and Ploti prefers that to guessing.
• Identity across datasets. Two datasets that describe the same real-world thing give it unrelated ids. To have Ploti tie records of different datasets together, ask for a custom feed: its link columns hold the record id of the row each record belongs to.
• Matching across a gap. A record is matched only to the release before it. One that leaves the source and returns in a later version you receive is a new record. The exception is a keyed record dropped by a version that was discarded, as above.
• Anything about the id’s format beyond the rec_ prefix, including its length and how it is made.

​

The hard cases

​

A record that was not matched

• a delete of the old id, with _ploti_delete_reason removed_at_source
• an upsert with _ploti_change added, under a new id

​

Merges and splits

• Merge. When the source combines two records into one, one of them keeps its id, as a changed row when its values changed. The other arrives as a delete with removed_at_source. The delete reason is the same as for a record that left the source, so it cannot tell you a merge happened.
• Split. When the source divides one record into several, one of them keeps the id and the rest arrive as added rows with new ids.

​

When ids are replaced

• Read the map as old id to new id for every record that continues. Many of its rows can map an id to itself: those records kept their ids.
• An id you hold that is not in _ploti_previous_record_id belongs to a record the version no longer has. A record of the version whose id is not in _ploti_record_id is new in it.
• A map covers one step: from the version you hold to this version. Apply versions in order, and apply each one’s map before the next.
• Reload the dataset from the version’s snapshot as usual, then rewrite any ids your own systems store with the map.

​

A reload without an id map

• an id in both is the same record
• an id only you hold is gone
• an id only in the snapshot is new

​

A held version

​

Ids on the map and in dataset watches

• It has an entry for every version that goes live on the map, numbered by the map, not by _ploti_version. A feed version can span several of them, such as a version that was held for review and discarded.
• It does not wait for Ploti’s matching agent. A record can show as removed and a new id as added in one version, and a renamed change in the next says the new id now names the old record.
• A keyed record that a map version dropped can take back its old id when it returns, for a dataset no subscriber was sent that removal.

​

Worked example

CREATE OR REPLACE TABLE permits AS
SELECT * FROM read_parquet('v42/snapshot/*.parquet');
CREATE UNIQUE INDEX permits_id ON permits (_ploti_record_id);

INSERT OR REPLACE INTO feed_state (dataset_id, version)
VALUES ('ds_orange_fl_building_permits', 42);

import duckdb

con = duckdb.connect("feeds.duckdb")
dataset, version, previous = "ds_orange_fl_building_permits", 43, 42

(held,) = con.execute(
    "SELECT version FROM feed_state WHERE dataset_id = ?", [dataset]
).fetchone()
if version <= held:
    pass  # already applied: skip
elif previous != held:
    raise RuntimeError("chain broken: reload from the dataset's current version")
else:
    con.execute("BEGIN")
    # Every upsert and delete removes the row it names; upserts then insert
    # the record as of this version.
    con.execute("""
        DELETE FROM permits WHERE _ploti_record_id IN (
          SELECT _ploti_record_id FROM read_parquet('v43/changes/*.parquet'))
    """)
    con.execute("""
        INSERT INTO permits BY NAME
        SELECT * EXCLUDE (_ploti_op, _ploti_change, _ploti_delete_reason,
                          _ploti_changed_columns, _ploti_match, _ploti_match_score)
        FROM read_parquet('v43/changes/*.parquet')
        WHERE _ploti_op = 'upsert'
    """)
    con.execute(
        "UPDATE feed_state SET version = ? WHERE dataset_id = ? AND version = ?",
        [version, dataset, held],
    )
    con.execute("COMMIT")

UPDATE my_notes SET record_id = m._ploti_record_id
FROM read_parquet('v44/id_map/*.parquet') AS m
WHERE my_notes.record_id = m._ploti_previous_record_id
  AND m._ploti_previous_record_id <> m._ploti_record_id;

-- Ids that no longer name a record in this version.
SELECT n.* FROM my_notes n
WHERE n.record_id NOT IN (SELECT _ploti_record_id FROM permits);

SELECT p._ploti_record_id AS gone
FROM permits p
ANTI JOIN read_parquet('v45/snapshot/*.parquet') s USING (_ploti_record_id);