Exam DP-203 topic 1 question 5 discussion

1: Parquet - column-oriented binary file format 2: AVRO - Row based format, and has logical type timestamp https://youtu.be/UrWthx8T3UY

Shouldn't the answer for Report 1 be Parquet? Because Parquet format is Columnar and should be best for reading a few columns only.

1.Parquet 2.AVRO

report 1: Parquet (column-based file format optimized for reading specific columns) report 2:Avro(Row-based file format)

the answer should be Parquet for report 1

The goal is: The solution must minimize read times. I made small test on Databrick plus DataLake. The same file saved as Parquet and Avro 9 mln of records. Parquet ~150 MB Avro ~700MB Reading Parquet is always 10 times faster that Avro. I checked: - for all data or small range of data with condition - all or only one column So I will select option: - Parquet - Parquet

1. Parquet 2. Avro https://docs.microsoft.com/en-us/azure/storage/blobs/data-lake-storage-best-practices "Consider using the Avro file format in cases where your I/O patterns are more write heavy, or the query patterns favor retrieving multiple rows of records in their entirety. Consider Parquet and ORC file formats when the I/O patterns are more read heavy or when the query patterns are focused on a subset of columns in the records."

To minimize read times for the two reports, it is recommended to store the data in the data lake in the parquet format. Parquet is a columnar storage format that is optimized for querying large datasets. It stores data in a compact and efficient manner, allowing for fast querying and filtering of data. In this case, Report1 needs to read only three columns from a file that contains 50 columns. Since parquet stores data in a columnar format, the query can skip reading the unnecessary columns and only read the required ones, which can greatly improve the read performance. Report2 needs to query a single record based on a timestamp. Parquet also supports efficient filtering and querying based on specific values, such as timestamps, making it a good choice for this report as well. Other formats, such as avro, csv, and tsv, may not provide the same level of performance for these types of queries. Therefore, it is recommended to use parquet to store the data in the data lake.

Report 1: parquet Columnar Storage: Parquet stores data in columns, allowing efficient reading of only the required columns (3 out of 50). Report 2: Avro. Fast Single-Record Access: Optimized row-based formats excel at quickly accessing individual records based on a specific condition, such as a timestamp. I don't understand why incorrect answers are being provided, causing confusion.

Recommendations: Report1: Use Parquet Reason: Parquet is a columnar storage format that is optimized for reading specific columns. Since Report1 needs to read only three columns out of 50, Parquet allows reading just those columns efficiently without scanning the entire file. Report2: Use Avro Reason: Avro is a row-based storage format, which is efficient for retrieving entire rows based on a specific condition, such as a timestamp. It allows quick access to individual records, making it suitable for Report2's requirement.

Why cant it be Parquet for both reports? Parquet supports timestamp too

1- Parquet 2- Avro Parquet for Column selection(stored in columnar format) and Avro for row selection (stored in row format)

1. Parquet 2. Avro

Parquet Avro

1. Parquet - Column oriented data store 2. AVRO - supports timestamps

Agree: 1: Parquet - ideal for columnar forma 2: AVRO: Row-based with logical timestamp

--> TLDR <-- AVRO PARQUET ORC Anal. Queries v v Write Ops (ETL ops) v Nested Data v ACID Properties v Sch.Flexibility v