...
Due to the restriction on modifying rows in the streaming buffer, the GCP Bigquery agent does not modify the Data table at commit and rollback stage. Instead, the agent adopts a related design utilizing a Transaction ID, unique for each batch.
The Data table must have a Transaction ID column.
The Batch Status table must be created with a Transaction ID column and a Status column.
At commit and rollback stage, the Batch Status is updated with a status code reflecting the current stage that can be used for auditing.
Consumers of the loaded data are expected to always access that data through a view.
This view should join the two tables on Transaction ID where status = 0, for example:
Info | |||
---|---|---|---|
title | Example - View Joining Data Table and Batch Status TableThe following DDL query is used in the BigQuery Query Editor to create a view under the user_analytics Dataset with the table named view1.
|
...