Skip to content

BigQuery schema gaps and orphaned social_post handles #7

Description

@yav02

An audit of db/schema.bq.sql, the staging schemas in db/staging/, and docs/DB_SCHEMA.md against the live dataset found several discrepancies.

1. Missing staging columns

db/schema.bq.sql defines extended_summary_he and generation_run_id on mk_issue_summary, but db/staging/stg_summaries.schema.json omits both. This causes bq load schema mismatches during summary ingestion.

Fix: add both columns to stg_summaries.schema.json and document them in docs/DB_SCHEMA.md.

2. Undocumented table

summary_generation_run exists in db/schema.bq.sql but is entirely absent from docs/DB_SCHEMA.md.

Fix: document the table and its clustering keys.

3. Stale issue counts in comments

db/schema.bq.sql and docs/DB_SCHEMA.md both state 7 issues in comments, while db/seed.bq.sql seeds 9. This is the same underlying drift tracked in the pipelines issue covering EXPECTED_ISSUE_COUNT; fix the comments as part of reconciling the taxonomy, not separately.

4. Orphaned handles in the live dataset

social_post contains roughly 36 rows where mk_id holds a raw account handle rather than a UUID, violating the foreign-key relationship the schema and docs/INGESTION_CONTRACT.md both assume.

Fix: a one-off statement mapping the affected handles to their mk.id, plus a change to the pipeline's handle resolver so unresolvable handles are dropped rather than written through as text.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions