Exam Professional Data Engineer topic 1 question 70 discussion

B. The question is focused on designing storage for very large files, with support for compression, ANSI SQL queries, and parallel loading from the input locations. This can be met using GCS for storage and Bigquery permanent tables with external data source in GCS.

Should be A

copy to gcs and use external tble in bq

Should be A. Check this link for the advantage of load Avro data to BigQuery https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro#advantages_of_avro """The Avro binary format: * Is faster to load. The data can be read in parallel, even if the data blocks are compressed. * Doesn't require typing or serialization. * Is easier to parse because there are no encoding issues found in other formats such as ASCII. When you load Avro files into BigQuery, the table schema is automatically retrieved from the self-describing source data."""

B makes sense

1. Store Avro files in GCS 2. Query them in BigQuery (federated tables)

Answer is B. The requirements are: - storage for compressed text files - parallel loads to SQL tool AVRO is a compressed format for text files, which makes it possible to load chunks of a very large file in parallel to BigQuery. gzip files are seamless in GCS though, but cannot load in parallel to BQ.

Correct Answer: A. Transform text files to compressed Avro using Cloud Dataflow. Use BigQuery for storage and query. This option offers several advantages: - Transforming the text files to compressed Avro using Cloud Dataflow allows for parallel processing of the input data, improving the efficiency of the pipeline. - Compressing the data in Avro format further reduces the storage space required and improves data transfer performance. - Storing the data in BigQuery supports ANSI SQL queries and allows for easy querying of the data. - BigQuery is a fully-managed data warehousing solution, it's scalable and can handle large datasets and concurrent queries, so it's suitable for large text files.

Designing storage solution, not data warehousing -> So Cloud Storage. Support compression -> just use Avro Parallel load -> refers to upload from input locations, NOT download. Load in parallel using -m flag for gsutil cp https://cloud.google.com/storage/docs/uploads-downloads#console

C and D are discarted. A and B are possible. A is the best for query, but … the sentence says: ou also want to support compression and parallel load from the input locations using Google recommended practices. BigQuery only support parallel load from storage, storage support parallel load from CLI. So the only option is B.

B is the answer.

"Very large files" and "long term storage" are two key phrases- both of which indicate to pick cloud storage as option. Hence B is correct.

All the comments argue about [A] and [B] as a storage destination. But there is a limitation on loading compressed Avro files into BigQuery that cuts the Gordian knot: ❗ "... Compressed Avro files are not supported, but compressed data blocks are ..." From: https://cloud.google.com/bigquery/docs/batch-loading-data#loading_compressed_and_uncompressed_data

https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro Advantages of Avro: Avro is the preferred format for loading data into BigQuery. Loading Avro files has the following advantages over CSV and JSON (newline delimited): The Avro binary format: Is faster to load. The data can be read in parallel, even if the data blocks are compressed. Doesn't require typing or serialization. Is easier to parse because there are no encoding issues found in other formats such as ASCII. When you load Avro files into BigQuery, the table schema is automatically retrieved from the self-describing source data.

https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro The Avro binary format: Is faster to load. The data can be read in parallel, even if the data blocks are compressed

B Because they are talking about the parallel loading from input locations.

B. The objetive is to follow the best practices.