Daily base rates

1-- core.forex_v2.daily_base_rates
2-- Gap-filled daily rates against the base currency: every calendar day from a
3-- currency's first rate up to today, with the last known rate carried forward
4-- over any day that has none (a missed sync, a currency that stopped quoting, or
5-- today when the sync has not run yet).
6--
7-- Grain: date x base currency x quote currency.
8-- Depends on: staging.forex_v2.rates
9--
10-- Direction as in staging: 1 base = rate x quote. Use core.forex_v2.daily_rates for
11-- any other pair; this model is its input and is handy on its own for a quick
12-- lookup against the base.
13--
14-- is_carried_forward is TRUE on a filled day, and rate_source_date says which day's
15-- rate was used, so a stale rate is visible rather than silent.
16
17WITH src AS (
18    SELECT rate_date, base_currency, quote_currency, rate
19    FROM {{staging.forex_v2.rates}}
20),
21
22pairs AS (
23    SELECT
24        base_currency,
25        quote_currency,
26        MIN(rate_date) AS first_date
27    FROM src
28    GROUP BY 1, 2
29),
30
31-- Extend to today even if the latest sync is behind, so a join on today's date
32-- still finds a rate. The flag below marks those days.
33spine AS (
34    SELECT
35        p.base_currency,
36        p.quote_currency,
37        d AS date
38    FROM pairs p
39    CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(p.first_date, GREATEST(CURRENT_DATE(), p.first_date))) AS d
40)