Foundry Data Ingestion — Data Engineering Guide¶

Why This Approach¶

Data science and data engineering are the first pass on any new data source. By the time a provider's data reaches Foundry, your team has already done the hard work — researching the provider's API, understanding their data model, identifying the core fields, and mapping them to our domain. You know what a "participant" or "team" looks like for that provider better than anyone.

This architecture puts that expertise at the centre. Rather than encoding provider-specific knowledge in application code (which requires a developer, a PR, a deployment), the standardized provider tables let your team define what the data looks like and how it maps. The application just reads what you've prepared.

The result: - You control the data pipeline end-to-end — from raw provider API to standardized table to crosswalk mapping - New providers don't need code changes — your team adds tables and crosswalk entries, Foundry imports from them - Schema is consistent — every provider's participants table has the same columns, so your ETL tooling is reusable across providers - Identity resolution stays in your domain — you decide which FoxSports player is the same as which SuperCoach player, not application code

Overview¶

Foundry uses ClickHouse as the source of truth for external provider data and entity identity mapping. Data engineering is responsible for:

Populating provider tables — standardized tables containing entity data from each provider
Populating the crosswalk — the entity_mapping_crosswalk table that links provider IDs to canonical Foundry IDs

Foundry's generic importers read from these tables to create domain aggregates. Adding a new provider requires zero application code changes — just new table data and crosswalk entries.

Architecture¶

YOUR ETL PIPELINE
  Raw provider APIs/files → Transform → Load into ClickHouse provider tables
  Match entities across providers → Load into entity_mapping_crosswalk

CLICKHOUSE (foundry database)
  participants                 ← you populate these (one row per provider per entity)
  teams                        ← all tables carry a `provider` LowCardinality(String) column
  competitions
  seasons
  sporting_events
  venues
  entity_mapping_crosswalk     ← you populate this

FOUNDRY (reads from ClickHouse)
  Generic importers read provider tables + crosswalk → create domain aggregates

Schema design note. Every provider table is keyed by (sport, provider, provider_id) (or (provider, provider_id) for venues — no sport column). One table per entity type, not one per provider × entity. Rationale:

Schema evolution is a single ALTER TABLE per entity — not one per provider.
Onboarding a new provider is data-only — no DDL, no application change. Just start inserting rows with your new provider value.
Mirrors the entity_mapping_crosswalk layout (keyed by (entity_type, provider, provider_id)).
Cross-provider queries become one SELECT — no UNION ALL over N tables.
Per-provider queries still hit the primary index prefix, so performance is unchanged.

Process Maps¶

Onboarding a New Provider¶

flowchart TD
    A[Data Science researches new provider API] --> B[Identify core entities and fields]
    B --> C[Map provider fields to standardized schema]
    C --> D[Data Engineering builds ETL pipeline]
    D --> G[Populate entity tables with provider = 'newguy']
    G --> H{Entities already exist in other providers?}
    H -->|Yes| I[Match to existing canonical IDs via crosswalk]
    H -->|No| J[Generate new canonical IDs]
    I --> K[Insert crosswalk entries]
    J --> K
    K --> L[Trigger Foundry generic import]
    L --> M[Domain aggregates created with canonical IDs]

Ongoing Data Ingestion¶

flowchart TD
    A[Provider API / data feed updates] --> B[ETL transforms to standardized schema]
    B --> C[Upsert rows into provider tables]
    C --> D{New entities?}
    D -->|Yes| E[Add crosswalk entries - match or generate canonical IDs]
    D -->|No| F[Crosswalk already has mappings]
    E --> G[Trigger Foundry import]
    F --> G
    G --> H[Generic importer reads provider table]
    H --> I[Batch resolve canonical IDs from crosswalk]
    I --> J{Crosswalk entry exists?}
    J -->|Yes| K[Create/update domain aggregate]
    J -->|No| L[Skip - entity not mapped yet]

Adding a New Field¶

flowchart TD
    A[New field needed - e.g. home_ground on teams] --> B[Foundry developer creates ClickHouse migration]
    B --> C[Migration ALTERs all existing provider team tables]
    C --> D[Migration updates template for future providers]
    D --> E[Foundry developer updates domain types]
    E --> F[Deploy Foundry - migration runs on startup]
    F --> G[Data Engineering populates new column via ETL]
    G --> H[Next import picks up new field]

Cross-Provider Entity Resolution¶

flowchart TD
    A[FoxSports player 12345] --> D[Crosswalk]
    B[SuperCoach player nrl/2026/classic/1] --> D
    C[StatsPerform player SP-999] --> D
    D --> E[canonical_id: abc-123]
    E --> F[Single Foundry ParticipantAggregate]
    F --> G[ParticipantContract read model]

Provider Tables¶

Naming Convention¶

Tables are one-per-entity with a provider LowCardinality(String) discriminator column. Full list: foundry.participants, foundry.teams, foundry.competitions, foundry.seasons, foundry.sporting_events, foundry.venues.

Provider names are lowercase strings in the provider column: foxsports, statsperform, supercoach, espn, nflverse.

Entity tables (one per entity type): foundry.participants, foundry.teams, foundry.competitions, foundry.seasons, foundry.sporting_events, foundry.venues.

Each row carries a provider discriminator — e.g. a FoxSports participant is a row in foundry.participants with provider = 'foxsports'.

Table Schemas¶

All tables use ENGINE = ReplacingMergeTree(updated_at). Insert a row with the same ORDER BY key and a newer updated_at to update it — ClickHouse deduplicates on merge.