I am trying to move the GA4 data of two websites from BigQuery to a Snowflake table using Matillion ETL. BigQuery tables are named events_YYYYMMDD format. The query I am using in the Matillion BigQuery orchestration job is below:
select * from events_* WHERE _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 day));
I have to run this job multiple times a day since the time at which GA4 data becomes available in BigQuery is unpredictable. Also, I will have multiple websites for which data comes to the same Bigquery account at uneven times. I need to capture all these data to Snowflake.
But Running this job multiple times results in duplicate records in the Snowflake table. How can I ensure only unique records are moved from BigQuery to Snowflake?
