BigQuery-invalid INTERVAL syntax breaks 4+ signal jobs (fear_greed, entropy_complexity, absorption_ratio, network_correlation)

[C00ldudeNoonan]

Summary

Four signal jobs fail every run with the same BigQuery syntax error:

400 Syntax error: Unexpected keyword ORDER at [N:9]

Root cause: DuckDB/Postgres-style quoted interval literals (INTERVAL '3 years', INTERVAL '90 days') are not valid BigQuery SQL — BigQuery requires an unquoted numeric literal + unit keyword (INTERVAL 3 YEAR). The quoted-string form is a leftover from the pre-BigQuery-migration MotherDuck/DuckDB codebase. BigQuery's parser fails to recover cleanly and reports the error several lines later at the next clause (usually ORDER BY), which makes the real cause non-obvious from the error message alone.

Confirmed broken (ran each job directly against a live BigQuery target, 2026-07-11)

• fear_greed_job — macro_agents/src/macro_agents/defs/signals/fear_greed_composite.py:62,84 — INTERVAL '{lookback}' where lookback = "3 years"
• entropy_complexity_job — macro_agents/src/macro_agents/defs/signals/entropy_complexity.py:69 — same pattern
• absorption_ratio_job — macro_agents/src/macro_agents/defs/signals/absorption_ratio.py:34 — same pattern
• network_correlation_job — macro_agents/src/macro_agents/defs/signals/network_correlation.py:36 — same pattern

Suspected (same pattern found by grep, not execution-tested)

• macro_agents/src/macro_agents/defs/domains/sec/cik_history.py:163 — CURRENT_TIMESTAMP - INTERVAL '90 days'
• macro_agents/src/macro_agents/defs/analysis/economy_state/domain_data_fetchers.py
• macro_agents/src/macro_agents/defs/analysis/economy_state/data_access.py

Fix

Replace INTERVAL '{n} {unit}' (quoted, f-string) with BigQuery's native form, e.g.:

# before
AND date >= CURRENT_DATE - INTERVAL '{lookback}'   # lookback = "3 years"

# after
AND date >= CURRENT_DATE - INTERVAL 3 YEAR

Since several of these use an f-string variable for the interval (lookback = "3 years"), the cleanest fix is a small helper that maps a (n, unit) pair or a validated string to BigQuery's INTERVAL {n} {UNIT} syntax, or just hardcode the unquoted literal at each call site since these are static lookback windows, not user input.

Impact

These jobs have been failing on every scheduled run (daily/weekly) since at least 2026-07-07 per the local deployment's run history — the underlying signal tables have not been updated in that window.

🤖 Generated with Claude Code