Exam Professional Data Engineer topic 1 question 184 discussion

[C] I found the correct answer based on a real case, where Google's Solutions Architect team decided to move an internal process to use BigQuery. The related doc is here: https://cloud.google.com/blog/products/data-analytics/moving-a-publishing-workflow-to-bigquery-for-new-data-insights

Vote B - "Some recently streamed rows might not be available for table copy typically for a few minutes. In rare cases, this can take up to 90 minutes" https://cloud.google.com/bigquery/docs/streaming-data-into-bigquery#dataavailability

deleting data from my point of view is not a good practice to build datawarehouse solutions. So, C and D are excluded. according to the official documentation, the updating/merging process could last till 90 minutes. 3 hours could be enough.

An append-only staging table ensures that all incoming data is captured without risk of data loss or overwrites, which is crucial for maintaining data integrity in a streaming ingestion scenario. Three-Hour Update Interval: Updating the production table every three hours strikes a good balance between minimizing the latency of data availability for reporting and reducing the frequency of potentially resource-intensive update operations. This interval is frequent enough to keep the production table relatively up-to-date for reporting purposes while ensuring that the performance of both ingestion and reporting processes is not significantly impacted. Frequent updates (like every ninety minutes or every thirty minutes) could introduce unnecessary overhead and contention, especially if the dataset is large or if there are complex transformations involved.

Not C nor D. Moving and deleting: Deleting data from the staging table every 3 or 30 minutes could lead to data loss if the production table update fails, and it also requires more frequent and potentially resource-intensive operations. Options C and D cause rebuilding of the staging table, which slows down ingestion, and may lose data if errors occur during recreation. A or B

Neither. In the current scenario, DataStream (a new google resource) captures the CDC data and uses Dataflow to Replicate the changes to big query.

Vote B C : the doc says streaming data can be used up to 90 minutes not 3 hours B : correct , insert staging table first with append and use merge from staging into production table

C is the answer. https://cloud.google.com/blog/products/data-analytics/moving-a-publishing-workflow-to-bigquery-for-new-data-insights Following common extract, transform, load (ETL) best practices, we used a staging table and a separate production table so that we could load data into the staging table without impacting users of the data. The design we created based on ETL best practices called for first deleting all the records from the staging table, loading the staging table, and then replacing the production table with the contents. When using the streaming API, the BigQuery streaming buffer remains active for about 30 to 60 minutes or more after use, which means that you can’t delete or change data during that time. Since we used the streaming API, we scheduled the load every three hours to balance getting data into BigQuery quickly and being able to subsequently delete the data from the staging table during the load process.

C Following common extract, transform, load (ETL) best practices, we used a staging table and a separate production table so that we could load data into the staging table without impacting users of the data. The design we created based on ETL best practices called for first deleting all the records from the staging table, loading the staging table, and then replacing the production table with the contents. When using the streaming API, the BigQuery streaming buffer remains active for about 30 to 60 minutes or more after use, which means that you can’t delete or change data during that time. Since we used the streaming API, we scheduled the load every three hours to balance getting data into BigQuery quickly and being able to subsequently delete the data from the staging table during the load process. Building a script with BigQuery on the back end https://cloud.google.com/blog/products/data-analytics/moving-a-publishing-workflow-to-bigquery-for-new-data-insights

D : read more on Streaming inserts and timestamp-aware queries as the following link it is the same as this question exactly, but it is quite similar. https://cloud.google.com/blog/products/bigquery/performing-large-scale-mutations-in-bigquery read carefully in the content below. When using timestamps to keep track of updated and deleted records, it’s a good idea to periodically delete stale entries. To illustrate, the following pair of DML statements can be used to remove older versions as well as deleted records. You’ll notice that the above DELETE statements don’t attempt to remove records that are newer than 3 hours. This is because data in BigQuery’s streaming buffer is not immediately available for UPDATE, DELETE, or MERGE operations, as described in DML Limitations. These queries assume that the actual values for RecordTime roughly match the actual ingestion time.

Either C or D But When will we delete stale data on staging table ? Every xxx???? https://cloud.google.com/architecture/database-replication-to-bigquery-using-change-data-capture#prune_merged_data

D. Have a staging table that moves the staged data over to the production table and deletes the contents of the staging table every thirty minutes.

Ans D D. Have a staging table that moves the staged data over to the production table and deletes the contents of the staging table every thirty minutes.

D. Have a staging table that moves the staged data over to the production table and deletes the contents of the staging table every thirty minutes.