pass parameter to Appends function for fetching change history

[jai_prakash]

Dear All,

I have an GA4 dataset on bigquery and because of slow arriving events going to the same event (portioned) table i have to query multiple days to fetch the history. We have a config framework which takes date in the format YYYYMMDD and generates the necessary query. I am trying to generate a query something like below 

WITH 
changed_records AS (
  SELECT
  *,
  DATE(_CHANGE_TIMESTAMP) AS change_date
  FROM
    APPENDS(TABLE `project.dataset.events_20240727`, 
            FORMAT_TIMESTAMP('%FT%T.0000 UTC', TIMESTAMP(PARSE_DATE('%Y%m%d', '20240801'), 'UTC')),
          '2024-08-01T23:59:59.999999 UTC'
          )
)

select event_date, change_date, count(*)
from changed_records
group by 1, 2 order by change_date
;

 i am getting an error on line 8 saying its a not valid signature for APPENDS function. Can you please suggest any workaround for this.

Thanks
Jai Prakash

[caryna · Accepted solution]

Hi @jai_prakash,

Welcome to the Google Cloud Community!

It appears the issue stems from the improper use of the APPENDS function. This is likely due to the fact that GA4 event tables in BigQuery are partitioned based on ingestion time rather than event time.

To address this, you should leverage the _PARTITIONTIME pseudo-column in your queries. This column provides the partition date for each row, helping you correctly manage and query the partitioned data.

Additionally, consider using wildcard tables when dealing with multiple tables that have similar names and compatible schemas. This approach simplifies querying across datasets with uniform structures, making data management more efficient.

I hope the above information is helpful.

[jai_prakash]

Thanks a lot @caryna for the response. Do we need to configure my project to have those pseudo-columns. My dataset is to hold GA4 events and when i queried for the partition date and partition timestamp i get this following error.

[caryna]

Hi @jai_prakash 

To clarify, you are utilizing the change history function with the APPENDS method to monitor changes in a specific table, with your data source being Google Analytics 4 (GA4).

Have you tried adjusting the second parameter to TIMESTAMP('2024-08-01 00:00:00 UTC') and the third parameter to TIMESTAMP('2024-08-01 23:59:59.999999 UTC')?  For additional details, you may want to consult the documentation for the APPENDS function and review any relevant limitations. 

Keep in mind that the default timezone is UTC. 

In my previous response, if you are working with large datasets, consider using the _PARTITIONTIME pseudocolumn to optimize query performance, especially if you're using ingestion-time partitioned tables. However, It looks like you’ve encountered an error that might be related to your table being partitioned by a time-unit column. To confirm this, please check the Details tab of your table. For your convenience, an example screenshot is provided below.

You can find more information on this in the documentation for creating ingestion-time partitioned tables. I apologize for any confusion caused earlier.

Since you're working with a single table (project.dataset.events_20240727),  you might find it beneficial to use a time-unit column-partitioned table based on time units such as datetime, timestamp. For further insights, refer to the GA forum article on Integrating GA4 with BigQuery for Streamlined Data Extraction. 

I hope the above information is helpful. 

[Snoshone07]

Hi @jai_prakash It looks like there’s a bit of confusion with the APPENDS function, as it’s not a valid BigQuery function. This could be a mix-up in terminology or inspired by similar concepts from other platforms. In BigQuery, handling slow-arriving events and working with partitioned tables can be done using UNION ALL or by dynamically querying partitions. Here’s how you could approach it:

1. Using UNION ALL for Specific Partitions

If you know the exact partitions you need to query, you can combine them explicitly like this:

WITH changed_records AS ( SELECT *, DATE(_CHANGE_TIMESTAMP) AS change_date FROM `project.dataset.events_20240727` UNION ALL SELECT *, DATE(_CHANGE_TIMESTAMP) AS change_date FROM `project.dataset.events_20240801` ) SELECT event_date, change_date, COUNT(*) FROM changed_records GROUP BY 1, 2 ORDER BY change_date;

This method works well when you know which partitions you need to include.

2. Dynamically Querying Multiple Partitions

If you’re working with a range of dates, you can dynamically find and query the partitions using INFORMATION_SCHEMA. Here’s an example:

DECLARE start_date STRING DEFAULT '20240727'; DECLARE end_date STRING DEFAULT '20240801'; EXECUTE IMMEDIATE FORMAT( ''' SELECT event_date, DATE(_CHANGE_TIMESTAMP) AS change_date, COUNT(*) FROM `%s.%s.INFORMATION_SCHEMA.PARTITIONS` WHERE partition_id BETWEEN %s AND %s GROUP BY 1, 2 ORDER BY change_date ''', 'project', 'dataset', start_date, end_date );

3. Automating with a Data Pipeline

If querying multiple partitions manually becomes tedious, consider automating the process using a data pipeline. For instance, platforms like Windsor.ai can help streamline the integration of GA4 data into BigQuery. These tools can handle schema changes and slow-arriving events automatically, saving you time and effort.

Final Thoughts

I hope these approaches help resolve your issue! Let me know if you’d like more guidance on optimizing your queries or handling partitioned tables in BigQuery. While working with partitions can feel a bit tricky at first, choosing the right method can make the process much smoother.

Hope this helps!