Making dynamic queries based on availability of BigQuery tables.
If events_fresh_ exist, only query this table.
Else, query events_ & events_intraday_ (if it exist).
If yesterday doesn't exist in events_, query events_intraday_ against yesterday + today, else only against today.
#BigQuery #GA4
If events_fresh_ exist, only query this table.
Else, query events_ & events_intraday_ (if it exist).
If yesterday doesn't exist in events_, query events_intraday_ against yesterday + today, else only against today.
#BigQuery #GA4
Comments
What approach do you use?
Mine:
1. Query metadata in a CTE, output list of suffixes
2. Later, use: where _table_suffix in ( select * from suffixes )
set events_fresh_exists = (
select count(1) > 0
from `something.analytics_123.__TABLES_SUMMARY__`
where table_id like 'events_fresh_%'
);
begin
if events_fresh_exists then
-- Query events_fresh_*
else
-- Query events_*
-- If events_intraday_* table exists, query it for today
if events_intraday_exists then
with events_dates as (
select
distinct date
from
ga4_events
)
select
*
from
ga4_events
union all
select
*
from
events_fresh
left outer join
events_dates
on
events_fresh.event_date= events_dates .date
where events_dates .date is null