BigQuery - FULL OUTER JOIN with USING causes casting error

I believe the way BQ is compiling your CTEs, the CAST(a_value as DATE) in your final WHERE clause is trying to be evaluated as if it were right after your self join and is causing a conversion/casting error before the other filters are applied.

I find it helpful to do as much casting as early as possible:

WITH 
    RawData AS (...),
    Joined AS (
        SELECT 
            IFNULL(a.type, b.type) AS type,
            SAFE_CAST(a.value AS DATE) AS a_date, -- Handle casting ASAP
            SAFE_CAST(b.value AS DATE) AS b_date
        FROM RawData AS a
        FULL OUTER JOIN RawData AS b USING (type) 
    ),
    Filtered AS (
        SELECT * FROM Joined WHERE type = 'date'
    )
SELECT * FROM Filtered
WHERE a_date BETWEEN '2020-12-01' AND '2020-12-31'
  AND b_date BETWEEN '2020-12-01' AND '2020-12-31'

Even better would be to filter your table before you do a join so it is joining fewer rows!

WITH 
    RawData AS (...),
    Filtered AS (
        SELECT 
            *, SAFE_CAST(value AS DATE) AS date_value -- Handle casting ASAP
        FROM RawData 
        WHERE type = 'date'   -- Reduce Size Early On!
          AND SAFE_CAST(value as DATE) BETWEEN '2020-12-01' AND '2020-12-31' -- Only do it 1 time here since it is the same range!
    ),
    Joined AS (
        SELECT 
            COALESCE(a.type, b.type) AS type,
            a.date_value AS a_date,
            b.date_value AS b_date
        FROM Filtered AS a
        FULL OUTER JOIN Filtered AS b USING (type) 
    )
SELECT * FROM Joined