My data has no obvious key. How do I find out what identifies a record?¶

Three organizations send you their product catalogs in one file, and a supplier list in another. Neither file has an id column. product_code looks like a key, but it is not: each organization numbers its own catalog, so P000 appears three times. Keyed by product_code alone, the 150 products would become 50 vertices, and three different products would be written as one.

GraFlo can propose the key from the data. It looks for the smallest set of columns whose values no two rows share, and writes it into the manifest for you to review. Then you ingest with it.

What you need¶

GraFlo installed (pip install graflo).
No database is needed. The graph is written to a directory, as in the file backend example (14).

The data¶

data/products.csv has 150 rows, 50 product codes for each of three organizations. The first rows:

org	product_code	name	category	updated_at
acme	P000	Widget 0	parts	2024-01-15
globex	P000	Widget 1	tools	2024-02-15
initech	P000	Widget 2	supplies	2024-03-15
acme	P001	Widget 3	parts	2024-04-15

data/suppliers.csv has 120 rows with a supplier_code (SUP-0000, ...), a name and a country.

Steps¶

1. Declare the vertices without an identity¶

manifest.yaml lists the properties of each vertex type and no identity. With identity_from_all_properties: true, a vertex without an identity is identified by all its properties together. The manifest loads, but only rows that agree in every column would count as the same product: a product whose updated_at changes would become a second vertex.

vertex_config:
    identity_from_all_properties: true
    vertices:
    -   name: product
        properties:
        -   org
        -   product_code
        -   name
        -   category
        -   updated_at
    -   name: supplier
        properties:
        -   supplier_code
        -   name
        -   country

2. Let GraFlo propose the identities¶

cd examples/15-identity-inference
uv run python infer.py

infer.py passes the rows of each file to apply_identity_inference_to_vertices:

samples = {name: read_rows(path) for name, path in SAMPLE_FILES.items()}
vertices, results = apply_identity_inference_to_vertices(
    list(vertex_config.vertices), samples
)

For each vertex type, inference ranks the columns, preferring those whose name ends in id, key or code. If some column has a different value in every row, the best-ranked such column wins. Otherwise inference adds columns in rank order until no two rows share the combination.

The script writes the manifest with the proposed identities to artifacts/manifest-inferred.yaml, and sets identity_from_all_properties: false there, so that loading it fails if a vertex has no identity:

-   name: product
    properties:
    -   name: org
    -   name: product_code
    -   name: name
    -   name: category
    -   name: updated_at
    identity:
    - product_code
    - org

3. Ingest with the proposed identities¶

uv run python ingest.py

ingest.py loads both files through the inferred manifest into artifacts/csv-backend, then counts the records and their distinct identities.

What you should see¶

infer.py prints:

product   composite  identity=['product_code', 'org'] confidence=1.0
supplier  unary      identity=['supplier_code'] confidence=1.0
Wrote artifacts/manifest-inferred.yaml

supplier_code is unique, so it is a one-column (unary) key. name is unique among the suppliers too; supplier_code wins because of its name. product_code is not unique, so inference adds the next-ranked column, org, and the pair is unique (composite). confidence is the share of five random samples, each 80% of the rows, on which the key stayed unique.

ingest.py prints:

product   150 records, 150 distinct identities (product_code, org)
supplier  120 records, 120 distinct identities (supplier_code)

Every row has its own identity: 150 products and 120 suppliers, which is what a correct key gives on this data.

What goes wrong¶

A key that is unique in the data is not always a key of the thing. In this data product_code together with name is unique too, because no two organizations happen to give the same code the same name. Move org below name in the product properties of manifest.yaml and run infer.py again: the proposal becomes ['product_code', 'name']. Among columns that rank equally, inference takes them in the order the manifest lists them. Only you know that a product belongs to an organization, so review every composite key before you use it.

Inference also refuses to guess from little data: with fewer than 100 rows for a vertex type (min_sample_size of IdentityInferenceConfig), the strategy is no_viable_identity and the vertex is left unchanged.

What to read next¶

Link by an alternative identifier: a source that names records by another key than the identity.
Finding a key for your data: the same steps for your own data, and every tuning option.
Vertex identity: what an identity does when records are written.

Files¶

The example lives in examples/15-identity-inference.

manifest.yaml

schema:
    metadata:
        name: catalog
    graph:
        vertex_config:
            identity_from_all_properties: true
            vertices:
            -   name: product
                properties:
                -   org
                -   product_code
                -   name
                -   category
                -   updated_at
            -   name: supplier
                properties:
                -   supplier_code
                -   name
                -   country
        edge_config:
            edges: []
    db_profile: {}
ingestion_model:
    resources:
    -   name: products
        pipeline:
        -   vertex: product
    -   name: suppliers
        pipeline:
        -   vertex: supplier
bindings:
    connectors:
    -   regex: "^products\\.csv$"
        sub_path: data
        resource_name: products
    -   regex: "^suppliers\\.csv$"
        sub_path: data
        resource_name: suppliers

infer.py

"""My data has no obvious key. How do I find out what identifies a record?

Reads ``manifest.yaml``, whose vertices declare no identity, and the CSV files
in ``data/``. Proposes an identity for each vertex type from the rows, prints
the proposal and writes the manifest with the proposed identities to
``artifacts/manifest-inferred.yaml``. Run it from this directory:

    uv run python infer.py
"""

import csv
from pathlib import Path

import yaml
from suthing import FileHandle

from graflo import GraphManifest
from graflo.architecture.schema.vertex import VertexConfig
from graflo.db.identity_inference import apply_identity_inference_to_vertices

#: The file whose rows are the sample for each vertex type.
SAMPLE_FILES = {"product": "data/products.csv", "supplier": "data/suppliers.csv"}
OUTPUT = Path("artifacts/manifest-inferred.yaml")


def read_rows(path: str) -> list[dict[str, str]]:
    """Read one CSV file as a list of rows."""
    with open(path, newline="", encoding="utf-8") as f:
        return list(csv.DictReader(f))


manifest = GraphManifest.from_config(FileHandle.load("manifest.yaml"))
manifest.finish_init()
schema = manifest.require_schema()
vertex_config = schema.core_schema.vertex_config

samples = {name: read_rows(path) for name, path in SAMPLE_FILES.items()}
vertices, results = apply_identity_inference_to_vertices(
    list(vertex_config.vertices), samples
)

for name, result in results.items():
    print(
        f"{name:<9} {result.strategy:<10} identity={result.identity} "
        f"confidence={result.confidence}"
    )

# Every vertex now has an identity; from here on, a missing one is an error.
inferred_config = VertexConfig(
    vertices=vertices,
    force_types=vertex_config.force_types,
    identity_from_all_properties=False,
)
core_schema = schema.core_schema.model_copy(update={"vertex_config": inferred_config})
inferred = manifest.model_copy(
    update={"graph_schema": schema.model_copy(update={"core_schema": core_schema})}
)

OUTPUT.parent.mkdir(parents=True, exist_ok=True)
with OUTPUT.open("w", encoding="utf-8") as f:
    yaml.safe_dump(inferred.to_minimal_canonical_dict(), f, indent=4, sort_keys=False)
print(f"Wrote {OUTPUT}")

ingest.py

"""Ingest the products and suppliers with the identities that infer.py proposed.

Reads ``artifacts/manifest-inferred.yaml``, writes the graph to the file
backend in ``artifacts/csv-backend`` and prints, per vertex type, how many
records were written and how many distinct identities they have. Run it from
this directory, after ``infer.py``:

    uv run python ingest.py
"""

from pathlib import Path

from suthing import FileHandle

from graflo import GraphManifest
from graflo.architecture.backend import GraFloBackendReader
from graflo.connections import GraFloBackendConfig
from graflo.hq import GraphEngine
from graflo.hq.caster import IngestionParams

manifest = GraphManifest.from_config(
    FileHandle.load("artifacts/manifest-inferred.yaml")
)
manifest.finish_init()

backend = GraFloBackendConfig(output_dir=Path("artifacts/csv-backend"))
engine = GraphEngine(target_db_flavor=backend.connection_type)
engine.define_and_ingest(
    manifest=manifest,
    target_db_config=backend,
    ingestion_params=IngestionParams(clear_data=True),
    recreate_schema=True,
)

reader = GraFloBackendReader(backend.output_dir)
vertex_config = manifest.require_schema().core_schema.vertex_config
for vertex in vertex_config.vertices:
    records = [
        doc for batch in reader.iter_vertex_batches(vertex.name) for doc in batch
    ]
    distinct = {tuple(doc[field] for field in vertex.identity) for doc in records}
    print(
        f"{vertex.name:<9} {len(records)} records, "
        f"{len(distinct)} distinct identities ({', '.join(vertex.identity)})"
    )