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

Here's why: Cloud Dataflow is a fully managed service for stream and batch data processing. It’s ideal for transforming large text files into a more efficient format like Avro, which supports schema evolution and is optimized for BigQuery ingestion. Avro is a row-based storage format that supports compression and is well-suited for BigQuery. BigQuery is Google Cloud’s serverless, highly scalable, and cost-effective multi-cloud data warehouse that supports ANSI SQL. Using compressed Avro allows for parallel loading into BigQuery, which is a Google-recommended best practice for performance and cost-efficiency. Why not the others? B: Using Cloud Storage with permanent external tables in BigQuery is possible, but it’s less performant and flexible than loading the data into native BigQuery storage. C: Gzip-compressed text files are not as efficient for parallel processing or schema enforcement as Avro. D: Cloud Bigtable is not designed for SQL queries; it’s a NoSQL wide-column store, and not suitable for ANSI SQL workloads.

Avro is a Google-recommended format for BigQuery because it supports schema evolution, efficient compression, and parallel processing. Using Cloud Dataflow ensures scalable transformation, and storing the data in BigQuery allows for optimized ANSI SQL queries.

Storing large text files with SQL support** Avro in BigQuery (A) supports compression and efficient queries.

For me it would make more sense to store the data directly in BQ, but A does not make sense, because why compress to Avro if you dont store the avro files and directly saves the data to BQ? You are not using the compression

BigQuery now has physical storage, which makes storage cost fairly cheap on BigQuery with compression. I would go with A.

Gros fichier + sql = GCS + Bigquery

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.