Integrating Glass Onion
When building an object identifier sync pipeline, there are a bunch of other tasks that you may need to do that Glass Onion does not provide support for: deduplication, false positive detection, etc. Provided below is an abridged and generalized version of the data pipeline that powers our (US Soccer) unified data schema. This is not meant to be the canonical implementation pathway -- just an example based on our environment and needs. Please let us know if you see ways in which we can improve this document (and therefore our implementation)!
In general, our process looks something like this for every object type:

In our pipeline, each object type depends on a "higher-order" object type to have unified identifiers in order to reduce the search space of potential matches (more details in Step 2). Data providers modify competitions and seasons least often, so we synchronize those by hand. That manual work allows us to automate the synchronization process for teams, which unlocks that process for matches, which then unlocks that process for players.
Start: Provider-specific object tables
Our goal with this pipeline should be to take an object's provider-specific tables and generate one "source of truth" table for that object with identifiers we can use across our systems. To achieve that vision, this final table must meet a few different criteria:
- Does not include duplicate rows or identifiers
- Includes most accurate metadata for the object from the data provider that tends to be the most accurate for each metadata field
- Assigns objects unique and durable identifiers that can be used reliably across our systems
Step 1: Data Collection
We collect data from the provider-specific tables into a single Spark DataFrame with the schema:
- data_provider: the data provider's name
- provider_object_id: the ID of the object in the data provider's system
- Any object-specific columns to use for synchronization
- A grouping key (More on this in Step 2)
Here's what this might look like in code for player synchronization:
Step 2: Synchronization
Stuffing all of our data into a common schema is a architectural choice forced by PySpark's GroupedData.applyInPandas(func, schema) to parallelize the work of identifier synchronization across a Spark cluster's nodes. This choice also has the benefit of naturally reducing the search space for synchronization if we have unified identifiers for higher-order objects. For matches and teams, we group by internal identifiers for competition and season, and for players, we group by an internal match identifier.
To use GroupedData.applyInPandas(func, schema) properly, we predefine a target schema (schema) and wrap SyncEngine.synchronize a function (func) to produce that schema consistently. If given providers A to C, our target schema looks something like this:
func will receive a pandas.DataFrame that contains a subset of our big object table without the grouping key added, so we need to do three things:
- Transform this subset into a list of
SyncableContentinstances - Run those instances through
SyncEngine - Format
SyncEngine's result into the target schema.
Thus, func ends up looking something like this:
With these prerequisites in place, we can actually run GroupedData.applyInPandas(func, schema):
Step 3: Resolution
Applying ObjectResolver
Once we have this preliminary set of synchronized identifiers, we can determine which results are valid. We throw all of these "proposed results" in an instance of ObjectResolver (see Methodology for more details on how this class works).
In short, if we can use "proposed results" to form a graph "component" (representing an object) where each provider has only one ID, we accept the "proposals". If for some reason (transitively or explicitly), a "proposal" would result in multiple IDs for the same provider in the same "component", then we reject the "proposal".
We start with our existing set of synced object records from ussf.object.
We can then generate the graph of identifiers via ObjectResolver:
Then, we can layer on our proposals and add any single-identifier proposals to the graph. We hold out any proposals that have previously appeared in our ussf.object_flagged table to avoid adding them to a valid object component in the graph.
partial_hold_reasons = ["duplicate"]
def held_ids(vouched):
held = set()
flagged = normalize_ids(spark.read.table("ussf.object_flagged"))
for row in flagged.select(id_mask + ["flag_reason"]).collect():
vs = [(k, row[k]) for k in id_mask if row[k] is not None]
if row["flag_reason"] in partial_hold_reasons:
vs = [v for v in vs if v not in vouched]
held.update(vs)
return held
# `vouched` IDs come from the ussf.object table. We implicitly trust that these are correct.
vouched = {v for vs in frame_vertices(all_added) for v in vs}
# We grab the previously held-out/flagged IDs, ignoring the IDs we can vouch for (via helper method `held_ids`)
previously_held = held_ids(vouched)
# 2. merge in proposals. A proposal with a held-out ID is, well, held out to avoid that ID being pulled into a valid object's component
proposals = [r for r in raw_records if len(r["ids"]) > 1]
held_proposals = [
r for r in proposals if any(v in previously_held for v in vertices(r))
]
graph.resolve(
[r for r in proposals if not any(v in previously_held for v in vertices(r))]
)
# 3. a vertex in the graph with no edges (IE: a single-ID proposal) becomes its own record, unless it's already in the graph, held out, or in a record left out above
excluded = (
previously_held
| {v for r in held_proposals for v in vertices(r)}
| {
v
for frame in (all_rejected, rejected_proposals)
for vs in frame_vertices(frame)
for v in vs
}
)
for r in raw_records:
if len(r["ids"]) == 1:
v = vertices(r)[0]
if v not in graph and v not in excluded:
graph.add_record(r)
Handling Rejected Proposals
But what happens to rejected proposals? We add these (and the other records they conflict with) to the ussf.object_flagged table for manual review:
Assembling Results Table
We can then convert our graph components into rows, accounting for duplicates...
...and then we can save these into a table:
...and then remove any previously flagged identifiers from the list:
Step 4: Data Formatting
Now that we have flagged and removed duplicate rows, we can prepare the cleaned sync results to be written to the table in our unified schema for this object. This has three stages:
- Removing any duplicate rows (including those we have flagged before this pipeline run)
- Joining our list back to provide-specific tables for object metadata
- Setting object metadata fields based on provider priority
- Assigning a unique and durable identifier for the object
The first two are easy:
dataframe.filter()orMERGE INTOtake care of removing rows with duplicate identifiers.dataframe.join()on a provider identifier brings in data from the provider-specific table.
But the next task poses a philosophical question: if our table for this object is supposed to be a source of truth for this object across all systems, how do we know which data provider is "true"? How do we know which fields they are "true" for? We have to come up with some prioritization strategy for each field in order to properly take object metadata from the "truest" sources first. For example, let's say we have Providers A, B, and C for player data:
- Provider A might be really accurate with birth dates but prefer legal/official names over commonly used names
- Provider B might have common names/mononyms, but have spotty accuracy on birth dates.
- Provider C is ok on names and birth dates, but is extremely accurate with country of birth / national team affiliation.
We might structure our data formatting like so:
This brings us to our final formatting step: building durable, unique identifiers for each object row. There are a number of different ways to do this (GUID, UUID, ULID, auto-increment ints, etc), but we chose to build our own identifier using the object metadata. In the case of players, we used:
- the player's gender
- a normalized version of the player's name, cut to 10 characters (the "prefix")
- the instance of the prefix for that gender in the table: 1, 2, 3, etc. (the "index")
Given a player named "test player" whose "prefix" only appears once in our dataset, results in an identifier that looks something like: female-testplayer-1.
There's one quirk with this: when using a Window, Spark forces you to sort that window. In our player pipeline, we sort this window by player name. This can result in new player rows that are sorted ahead of existing player rows, which throws off the "index" like below:
| Player ID | Player Name | Prefix | Gender | Index |
|---|---|---|---|---|
female-testplayer-1 |
Test Player A | testplayer |
female | 1 |
NULL |
Test Player B | testplayer |
female | 2 |
female-testplayer-2 |
Test Player C | testplayer |
female | 3 |
This could also come up if player rows have ever been deleted from the table. This edge case breaks our two identifier tenets:
- Not durable: if we choose to update all identifiers when new rows appear, these identifiers become unreliable in downstream analyses.
| Player ID (Old) | Player Name | Prefix | Gender | Index | Player ID (New) |
|---|---|---|---|---|---|
female-testplayer-1 |
Test Player A | testplayer |
female | 1 | female-testplayer-1 |
NULL |
Test Player B | testplayer |
female | 2 | female-testplayer-2 |
female-testplayer-2 |
Test Player C | testplayer |
female | 3 | female-testplayer-3 |
- Not unique: if we instead keep existing identifers and only fill in identifiers where they don't exist, we'd end up with duplicate identifiers.
| Player ID (Old) | Player Name | Prefix | Gender | Index | Player ID (New) |
|---|---|---|---|---|---|
female-testplayer-1 |
Test Player A | testplayer |
female | 1 | female-testplayer-1 |
NULL |
Test Player B | testplayer |
female | 2 | female-testplayer-2 |
female-testplayer-2 |
Test Player C | testplayer |
female | 3 | female-testplayer-2 |
We must develop a more complex rule to generate new indices for a given prefix and gender. Here's what we've found to work:
- For all rows with existing identifiers, extract the index.
- Determine the maximum existing index value.
- Start counting new indices from this maximum value.
Here's what our example table would look like if we apply this strategy:
| Player ID (Old) | Player Name | Prefix | Gender | Index (Old) | Index (New) | Player ID (New) |
|---|---|---|---|---|---|---|
female-testplayer-1 |
Test Player A | testplayer |
female | 1 | 1 | female-testplayer-1 |
NULL |
Test Player B | testplayer |
female | 2 | 3 | female-testplayer-3 |
female-testplayer-2 |
Test Player C | testplayer |
female | 3 | 2 | female-testplayer-2 |
Here's what this looks like in code:
Final: unified object table
With our pipeline generating a "clean list" that meets our target criteria:
- Does not include duplicate rows or identifiers
- Includes most accurate metadata for the object from the data provider that tends to be the most accurate for each metadata field
- Assigns objects unique and durable identifiers that can be used reliably across our systems
We can simply execute a MERGE INTO statement into the table for this object in our unified schema:
And just like that: we're done with our pipeline!