Data Engineering Zoomcamp 2025

Homework 3: Data Warehousing Statistics

Distribution of scores and reported study time for this homework.

Submissions

583

Median total score

8

Average total score

8

Score distribution

All values are points.

Questions score

Min
2
Median
8.0
Max
8
Q1
8.0
Avg
7.6
Q3
8.0

Learning in public score

Min
-
Median
0.0
Max
7
Q1
0.0
Avg
0.7
Q3
0.0

Total score

Min
2
Median
8.0
Max
16
Q1
8.0
Avg
8.3
Q3
8.0

Time distribution

All values are hours reported by students.

Lectures

Min
0.0
Median
3.0
Max
40.0
Q1
2.0
Avg
4.5
Q3
5.0

Homework

Min
0.0
Median
2.0
Max
20.0
Q1
1.0
Avg
3.0
Q3
4.0

Question breakdown

Correctness and answer distribution per question.

1. Count of records for the 2024 Yellow Taxi Data

567 / 583 correct (97.3%)

1 65,623 2 (0.3%)
2 840,402 3 (0.5%)
3 20,332,093 567 (97.3%)
4 85,431,289 9 (1.5%)

2. Estimated amount of data

522 / 583 correct (89.5%)

1 18.82 MB for the External Table and 47.60 MB for the Materialized Table 27 (4.6%)
2 0 MB for the External Table and 155.12 MB for the Materialized Table 522 (89.5%)
3 2.14 GB for the External Table and 0MB for the Materialized Table 23 (3.9%)
4 0 MB for the External Table and 0MB for the Materialized Table 7 (1.2%)

3. Why are the estimated number of Bytes different?

567 / 583 correct (97.3%)

1 BigQuery is a columnar database, and it only scans the specific columns requested in the query. Querying two columns (PULocationID, DOLocationID) requires reading more data than querying one column (PULocationID), leading to a higher estimated number of bytes processed. 567 (97.3%)
2 BigQuery duplicates data across multiple storage partitions, so selecting two columns instead of one requires scanning the table twice, doubling the estimated bytes processed. 5 (0.9%)
3 BigQuery automatically caches the first queried column, so adding a second column increases processing time but does not affect the estimated bytes scanned. 5 (0.9%)
4 When selecting multiple columns, BigQuery performs an implicit join operation between them, increasing the estimated bytes processed 5 (0.9%)

4. How many records have a fare_amount of 0?

553 / 583 correct (94.9%)

1 128,210 14 (2.4%)
2 546,578 10 (1.7%)
3 20,188,016 4 (0.7%)
4 8,333 553 (94.9%)

5. The best strategy to make an optimized table in Big Query

569 / 583 correct (97.6%)

1 Partition by tpep_dropoff_datetime and Cluster on VendorID 569 (97.6%)
2 Cluster on by tpep_dropoff_datetime and Cluster on VendorID 4 (0.7%)
3 Cluster on tpep_dropoff_datetime Partition by VendorID 5 (0.9%)
4 Partition by tpep_dropoff_datetime and Partition by VendorID 3 (0.5%)

6. Estimated processed bytes

554 / 583 correct (95.0%)

1 12.47 MB for non-partitioned table and 326.42 MB for the partitioned table 8 (1.4%)
2 310.24 MB for non-partitioned table and 26.84 MB for the partitioned table 554 (95.0%)
3 5.87 MB for non-partitioned table and 0 MB for the partitioned table 5 (0.9%)
4 310.31 MB for non-partitioned table and 285.64 MB for the partitioned table 9 (1.5%)

7. Where is the data for external tables stored?

566 / 582 correct (97.3%)

1 Big Query 7 (1.2%)
2 Container Registry 4 (0.7%)
3 GCP Bucket 566 (97.3%)
4 Big Table 4 (0.7%)

8. Always clustering

545 / 582 correct (93.6%)

1 True 36 (6.2%)
2 False 545 (93.6%)

Calculated: 14 February 2025, 10:49