[Pipelines / ML] Fix BigQuery MERGE deduplication, 5xx HTTP retry logic, issue taxonomy count, and TOCTOU file race

[yav02]

Issue Importance: HIGH (P1)

Summary

An audit of data ingestion pipelines, Vertex AI Gemini pipeline, clustering scripts, and unit tests identified bugs affecting ingestion stability, error handling, taxonomy alignment, and test reliability.

Sub-Issues & Sub-Task Importance Ratings

1. [CRITICAL - P0] Missing Staging Deduplication in MERGE Scripts:
   • db/merge_ingest.bq.sql: Staging queries in MERGE scripts lack QUALIFY ROW_NUMBER() OVER (PARTITION BY ...) deduplication, triggering BigQuery MERGE Error 400 when staging duplicate rows.
   • Fix: Add QUALIFY ROW_NUMBER() OVER (...) = 1 windowing to staging SELECT queries.
2. [HIGH - P1] Unhandled 5xx HTTP Errors in Ingestion:
   • upload_over_to_bigquery.py (line 98): Retries HTTP 429 rate-limiting, but hits a break block on any 5xx server error (502/503), aborting execution without retrying.
   • Fix: Include 5xx error codes in the retry loop.
3. [HIGH - P1] Hardcoded Issue Count Mismatch:
   • bigquery_issue_scoring.py: Defines EXPECTED_ISSUE_COUNT = 7, but the taxonomy in db/seed.bq.sql and db/issue_anchors.json now has 9 issues, causing pipeline checks to fail.
   • Fix: Update EXPECTED_ISSUE_COUNT = 9.
4. [MEDIUM - P2] TOCTOU File Unlink Race Condition:
   • big_query_to_data.py (line 182): Executes temporary_path.unlink() in a finally block after if temporary_path.exists():, causing FileNotFoundError if unlinked concurrently in Python 3.13.
   • Fix: Pass missing_ok=True: temporary_path.unlink(missing_ok=True).
5. [MEDIUM - P2] Test Argument Misalignment:
   • tests/test_summary_creation_pipeline.py: Test passes PosixPath into min_confidence positional argument due to signature mismatch, failing JSON manifest serialization tests.
   • Fix: Align function call arguments in test.

Affected Files

• db/merge_ingest.bq.sql
• src/mk_tracking/download_knesset_data/upload_over_to_bigquery.py
• src/mk_tracking/bigquery_issue_scoring.py
• big_query_to_data.py
• tests/test_summary_creation_pipeline.py