Data Engineering Zoomcamp 2024

Homework 3: Data Warehouse Statistics

Distribution of scores and reported study time for this homework.

Submissions

719

Median total score

7

Average total score

7

Score distribution

All values are points.

Questions score

Min
-
Median
7.0
Max
7
Q1
6.0
Avg
6.4
Q3
7.0

Learning in public score

Min
-
Median
0.0
Max
7
Q1
0.0
Avg
0.6
Q3
1.0

Total score

Min
-
Median
7.0
Max
15
Q1
6.0
Avg
7.1
Q3
7.0

Time distribution

All values are hours reported by students.

Lectures

Min
0.0
Median
3.0
Max
80.0
Q1
2.0
Avg
4.7
Q3
5.0

Homework

Min
0.0
Median
2.0
Max
74.0
Q1
2.0
Avg
3.6
Q3
4.0

Question breakdown

Correctness and answer distribution per question.

1. What is count of records for the 2022 Green Taxi Data?

697 / 719 correct (96.9%)

1 65 623 481 7 (1.0%)
2 840 402 699 (97.2%)
3 1 936 423 10 (1.4%)
4 253 647 3 (0.4%)

2. What is the estimated amount of data in the tables?

653 / 719 correct (90.8%)

1 0 MB for the External Table and 6.41MB for the Materialized Table 655 (91.1%)
2 18.82 MB for the External Table and 47.60 MB for the Materialized Table 22 (3.1%)
3 0 MB for the External Table and 0MB for the Materialized Table 28 (3.9%)
4 2.14 MB for the External Table and 0MB for the Materialized Table 11 (1.5%)

3. How many records have a fare_amount of 0?

697 / 719 correct (96.9%)

1 12 488 8 (1.1%)
2 128 219 7 (1.0%)
3 112 4 (0.6%)
4 1 622 699 (97.2%)

4. What is the best strategy to make an optimized table in Big Query?

682 / 719 correct (94.9%)

1 Cluster on lpep_pickup_datetime Partition by PUlocationID 18 (2.5%)
2 Partition by lpep_pickup_datetime Cluster on PUlocationID 684 (95.1%)
3 Partition by lpep_pickup_datetime and Partition by PUlocationID 11 (1.5%)
4 Cluster on by lpep_pickup_datetime and Cluster on PUlocationID 3 (0.4%)

5. What's the size of the tables?

649 / 719 correct (90.3%)

1 22.82 MB for non-partitioned table and 647.87 MB for the partitioned table 13 (1.8%)
2 12.82 MB for non-partitioned table and 1.12 MB for the partitioned table 650 (90.4%)
3 5.63 MB for non-partitioned table and 0 MB for the partitioned table 20 (2.8%)
4 10.31 MB for non-partitioned table and 10.31 MB for the partitioned table 26 (3.6%)

6. Where is the data stored in the External Table you created?

678 / 719 correct (94.3%)

1 Big Query 30 (4.2%)
2 GCP Bucket 680 (94.6%)
3 Big Table 3 (0.4%)
4 Container Registry 4 (0.6%)

7. It is best practice in Big Query to always cluster your data

568 / 719 correct (79.0%)

1 True 147 (20.4%)
2 False 570 (79.3%)

Calculated: 13 October 2024, 17:46