Exam Professional Data Engineer topic 1 question 46 discussion

this is one of the sample exam questions that google has on their website. The correct answer is B

Answer: B Description: B is correct because regional storage is cheaper than BigQuery storage.

BigQuery is a powerful data warehouse designed for analyzing large datasets efficiently. Partitioning tables allows you to manage large datasets by splitting them into segments based on a key, such as time. By creating a partitioned table and updating it every 30 minutes, you can load the new price data directly into the correct partitions. BigQuery’s partitioned tables optimize both the storage and querying cost because BigQuery only scans the relevant partitions when querying, minimizing the amount of data read and hence reducing costs. Partitioning by time (e.g., timestamp or date columns) is particularly effective for datasets with periodic updates (like price data) since each batch of data will be loaded into the corresponding partition.

Actually, in this question, I think B is the most suitable. C, D are somehow overkill. A due to the minimum partition granularity. However, with B, the data could not be previewd also it is not possible to estimate the cost.

D. Store the data in a file in a regional Google Cloud Storage bucket. Use Cloud Dataflow to query BigQuery and combine the data programmatically with the data stored in Google Cloud Storage. Here's why this approach is ideal: Cost-Effective Storage: Cloud Storage offers regional storage classes that are cost-effective for frequently accessed data. Storing the price data in a regional Cloud Storage bucket keeps it readily available. Cloud Dataflow for Updates: Cloud Dataflow is a managed service for building data pipelines. You can create a Dataflow job that runs every 30 minutes to: Download the latest economic data file from Cloud Storage. Process and potentially transform the data as needed. Load the updated data into BigQuery. BigQuery Integration: BigQuery seamlessly integrates with Cloud Dataflow. The Dataflow job can directly load the processed data into a BigQuery table for further analysis with your customer data.

BigQuery supports partitioned tables, which allow for efficient querying and management of large datasets that are updated frequently. By loading the updated data into a new partition every 30 minutes, you can ensure that only relevant partitions are queried, reducing the amount of data processed and thereby minimizing costs. What's wrong with B ? While creating a federated data source in BigQuery pointing to a Google Cloud Storage bucket is feasible, it might not be the most efficient for data that is updated every 30 minutes. Querying federated data sources can sometimes be more expensive and less performant than querying data stored directly in BigQuery.

Federated queries let you send a query statement to Cloud Spanner or Cloud SQL databases not to cloud storage

ChatGPT says partitioned tables is the best approach, The answers here are quite contrasting with that answer, Even i thought it has to be option A, I am so confused now? Any proper straight forward answer ?

Answer B: Uploading data into staging tables/ external tables or federated source in BQ is the best approach. Option A is also good approach, anyone can explain about his part what is wrong about this?

B is right

Discounting A due to limitations on partitions Discounting C because datastore does not fit into the nature of data we are talking about and federation between BQ and datastore it an overkill Between B and D, updating the price file on GCS and joining BQ tables and external tables sourcing data from GCS is most cost optimal way for this use case

Consideration: As cheaply as possible. Make sure data stays up to date. Initially chose A. But in actuality there is no need to maintain or store past data so storage of past data and partitioning doesn't seem like a key requirement. Instead we can connect just to a single Cloud Storage file, either by: i. replace previous prices with latest prices ii. store previous prices in GCS if required to be retained

B is most inexpensive approach.

The technical requirement is having frequently access info to join with other BQ data, as cheap as possible. B fits perfectly. Corner cases for external data sources: • Avoiding duplicate data in BigQuery storage • Queries that do not have strong performance requirements • Small amount of frequently changing data to join with other tables in BigQuery https://cloud.google.com/blog/products/gcp/accessing-external-federated-data-sources-with-bigquerys-data-access-layer

I would say D, regional Google Cloud Storage bucket - cheap. A - not cheap B - NoSQL database for your web and mobile applications C - Federated queries let you send a query statement to Cloud Spanner or Cloud SQL databases And we need to combine data in DQ with data from bucket

according to this : https://cloud.google.com/bigquery/docs/external-data-sources Federated queries don't work with Cloud Storage. how can it be B ?

I voted for B