Cybersyn's FRED Tables Are Deprecated: What to Use Instead

create or replace table fred_crosswalk (
    series_id string, variable string, frequency string, scale float
) as
select * from values
    ('UNRATE',          'LNS14000000.M_SA',                'M', 100),
    ('CIVPART',         'LNS11300000.M_SA',                'M', 100),
    ('PAYEMS',          'CES0000000001.M_SA',              'M', 0.001),
    ('JTSJOL',          'JOLTSJO0000000LVL.M_SA',          'M', 0.001),
    ('ICSA',            'IC_SA',                           'W', 1),
    ('CPIAUCSL',        'CUSRSA0SA01982-84.M',             'M', 1),
    ('CPILFESL',        'CUSRSA0L1EL1E1982-84.M',          'M', 1),
    ('PCEPI',           'BEA_NIPA_PCE_2.8.4_DPCERG_M_SA',  'M', 1),
    ('PCEPILFE',        'BEA_NIPA_PCE_2.8.4_DPCCRG_M_SA',  'M', 1),
    ('A191RL1Q225SBEA', 'BEA_NIPA_1.1.1_A191RL_Q',         'Q', 1),
    ('INDPRO',          'G17_IP.B50001.S_SA',              'M', 1),
    ('RSAFS',           '44X72SM_SA',                      'M', 0.000001),
    ('HOUST',           'ASTARTSTOTAL_SA',                 'M', 0.001),
    ('UMCSENT',         'the_index_of_consumer_sentiment', 'M', 1),
    ('M2SL',            'H6_M2M2.M_SA',                    'M', 0.000000001),
    ('DFF',             'H15_RIFSPFF_N.D',                 'D', 100),
    ('FEDFUNDS',        'H15_RIFSPFF_N.M',                 'M', 100),
    ('SOFR',            'SOFR',                            'D', 100),
    ('DTB3',            'H15_RIFSGFSM03_N.B',              'D', 100),
    ('DGS2',            'H15_RIFLGFCY02_N.B',              'D', 100),
    ('DGS10',           'H15_RIFLGFCY10_N.B',              'D', 100),
    ('MORTGAGE30US',    'FRM_30_YR',                       'W', 100);

select x.series_id,
       case x.frequency
           when 'M' then date_trunc('month', t.date)
           when 'Q' then date_trunc('quarter', t.date)
           else t.date
       end as date,
       round(t.value * x.scale, 6) as value
from public_data_free.financial_economic_indicators_timeseries t
join fred_crosswalk x on x.variable = t.variable
where t.geo_id = 'country/USA'
  and x.series_id in ('UNRATE', 'DGS10')
order by x.series_id, date desc;

select series_id, date, value
from fred.fred_series_observations_vw
where series_id in ('UNRATE', 'DGS10')
order by series_id, date desc;

with curated as (
    select x.series_id,
           case x.frequency
               when 'M' then date_trunc('month', t.date)
               when 'Q' then date_trunc('quarter', t.date)
               else t.date
           end as date,
           t.value * x.scale as value
    from public_data_free.financial_economic_indicators_timeseries t
    join fred_crosswalk x on x.variable = t.variable
    where t.geo_id = 'country/USA'
)
select c.series_id,
       count(*) as dates_compared,
       count_if(abs(c.value - f.value) > 0.0005 * greatest(abs(f.value), 0.01)) as mismatches,
       max(f.date) as latest_compared
from curated c
join fred.fred_series_observations_vw f
    on f.series_id = c.series_id and f.date = c.date
group by c.series_id
order by c.series_id;