Hello QuickSight Community,
I’m currently working with BigQuery datasets in QuickSight and I’m trying to understand how incremental refresh works with date-partitioned tables in BigQuery.
In my case, I have Firebase events stored in BigQuery. Each event has a timestamp and by the end of 24 hours, all events are rolled up into their date-partitioned table. When querying for events, I’ve used the wildcard character (e.g., event*) to denote a search on all date partitions. However, this method is both expensive and slow.
The preferred method is to specify the complete table name (e.g., events_20240123). But if I want to select events for multiple dates, it’s better to use _TABLE_SUFFIX in the WHERE clause. This limits the number of partitions searched and returns data faster and cheaper in the long run.
My question is: How can I implement this approach in QuickSight when getting data from BigQuery, specifically using _TABLE_SUFFIX for incremental refresh?
I’m aiming this question at the QuickSight development team and experts in the community. I’d appreciate any best practice approaches or advice you can provide.
Thank you in advance for your help!
Edit:
to clarify more, here is what a topical BigQuery looks like with using TABLE_SUFFIX
select
_TABLE_SUFFIX,
event_date,
FORMAT_TIMESTAMP("%F %T", TIMESTAMP_MICROS(event_timestamp),"Asia/Riyadh") as ksa_event_ts,
(select value.int_value from unnest (event_params) where key = 'ga_session_id' ) as ga_session_id,
device.category,
device.mobile_brand_name,
device.mobile_model_name,
device.mobile_marketing_name,
device.operating_system,
device.operating_system_version,
device.language,
geo.city,
geo.country,
geo.region,
app_info.version,
platform
from `analytics_98767654`.`events_*`
where
_TABLE_SUFFIX >= '20240101' and _TABLE_SUFFIX < '20240103' event_name = 'session_start';
this query returns all “session_start” events since Jan 1st. and because the _TABLE_SUFFIX was specified in the WHERE clause, the search will be limited to 2 partitioned tables:
analytics_98767654.events_20240101
analytics_98767654.events_20240102
If the _TABLE_SUFFIX was not specified then the search will include all partitions since 2019
