Skip to content

responses.md

Latest commit

 

History

History
235 lines (187 loc) · 12 KB

responses.md

File metadata and controls

235 lines (187 loc) · 12 KB
Raw

Assignment 03 Responses

Part 4: BigQuery External Tables

Hourly Observations — CSV External Table SQL

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_csv`
(
    valid_date      STRING,
    valid_time      STRING,
    aqsid           STRING,
    site_name       STRING,
    gmt_offset      FLOAT64,
    parameter_name  STRING,
    reporting_units STRING,
    value           FLOAT64,
    data_source     STRING
)
OPTIONS (
    format = 'CSV',
    skip_leading_rows = 1,
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/hourly/*.csv']
);

Hourly Observations — JSON-L External Table SQL

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_jsonl`
(
    valid_date      STRING,
    valid_time      STRING,
    aqsid           STRING,
    site_name       STRING,
    gmt_offset      FLOAT64,
    parameter_name  STRING,
    reporting_units STRING,
    value           FLOAT64,
    data_source     STRING
)
OPTIONS (
    format = 'NEWLINE_DELIMITED_JSON',
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/hourly/*.jsonl']
);

Hourly Observations — Parquet External Table SQL

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_parquet`
OPTIONS (
    format = 'PARQUET',
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/hourly/*.parquet']
);

Site Locations — CSV External Table SQL

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.site_locations_csv`
OPTIONS (
    format = 'CSV',
    skip_leading_rows = 1,
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/sites/site_locations.csv']
);

Site Locations — JSON-L External Table SQL

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.site_locations_jsonl`
OPTIONS (
    format = 'NEWLINE_DELIMITED_JSON',
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/sites/site_locations.jsonl']
);

Site Locations — GeoParquet External Table SQL

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.site_locations_geoparquet`
OPTIONS (
    format = 'PARQUET',
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/sites/site_locations.geoparquet']
);

Cross-Table Join Query

SELECT
    s.StateAbbreviation AS state,
    AVG(h.value) AS avg_pm25,
    COUNT(*) AS observation_count
FROM `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_parquet` AS h
INNER JOIN `project-90eada03-7af6-49f6-ada.air_quality.site_locations_geoparquet` AS s
    ON h.aqsid = s.AQSID
WHERE
    h.valid_date = '07/01/24'
    AND h.parameter_name = 'PM2.5'
GROUP BY s.StateAbbreviation
ORDER BY avg_pm25 DESC;

Part 5: Hive-Partitioned External Tables

Hourly Observations — CSV (hive-partitioned)

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_csv_hive`
(
    valid_date      STRING,
    valid_time      STRING,
    aqsid           STRING,
    site_name       STRING,
    gmt_offset      FLOAT64,
    parameter_name  STRING,
    reporting_units STRING,
    value           FLOAT64,
    data_source     STRING
)
WITH PARTITION COLUMNS (
    airnow_date DATE
)
OPTIONS (
    format = 'CSV',
    skip_leading_rows = 1,
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/hourly/csv/*'],
    hive_partition_uri_prefix = 'gs://musa5090-s26-mayaano-data/air_quality/hourly/csv'
);

Hourly Observations — JSON-L (hive-partitioned)

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_jsonl_hive`
(
    valid_date      STRING,
    valid_time      STRING,
    aqsid           STRING,
    site_name       STRING,
    gmt_offset      FLOAT64,
    parameter_name  STRING,
    reporting_units STRING,
    value           FLOAT64,
    data_source     STRING
)
WITH PARTITION COLUMNS (
    airnow_date DATE
)
OPTIONS (
    format = 'NEWLINE_DELIMITED_JSON',
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/hourly/jsonl/*'],
    hive_partition_uri_prefix = 'gs://musa5090-s26-mayaano-data/air_quality/hourly/jsonl'
);

Hourly Observations — Parquet (hive-partitioned)

CREATE OR REPLACE EXTERNAL TABLE `project-90eada03-7af6-49f6-ada.air_quality.hourly_observations_parquet_hive`
WITH PARTITION COLUMNS (
    airnow_date DATE
)
OPTIONS (
    format = 'PARQUET',
    uris = ['gs://musa5090-s26-mayaano-data/air_quality/hourly/parquet/*'],
    hive_partition_uri_prefix = 'gs://musa5090-s26-mayaano-data/air_quality/hourly/parquet'
);

Part 6: Analysis & Reflection

1. File Sizes

Hourly data (single day — 2024-07-01):

Format File Size
CSV 18,024 KB
JSON-L 42,674 KB
Parquet 638 KB

Site locations:

Format File Size
CSV 1,009 KB
JSON-L 2,868 KB
GeoParquet 477 KB

Analysis: Parquet is by far the smallest format, roughly 28x smaller than JSON-L and 28x smaller than CSV for the hourly data. This is because Parquet uses columnar storage with built-in compression — it stores all values for a single column together, which allows the compression algorithm to work much more efficiently on similar data. JSON-L is the largest because it repeats every field name as a string key on every single row (e.g. "parameter_name" appears ~190,000 times in one day's file), resulting in massive overhead. CSV is much smaller than JSON-L because it only stores field names once in the header row, but it still stores all data as plain text with no compression. Parquet's binary encoding and columnar compression make it dramatically more space-efficient than both text-based formats.

2. Format Anatomy

CSV vs Parquet:

CSV (Comma-Separated Values) is a plain-text, row-oriented format. Each line represents one complete record, with field values separated by commas (or in our case, pipes). The first row contains column headers. Any text editor can open and read a CSV file directly. However, because data is stored row by row, reading a single column requires scanning every row in the file — inefficient for analytical queries that only need a few columns.

Parquet is a binary, column-oriented format. Instead of storing row 1 (all 9 fields), then row 2, then row 3, Parquet stores all values for valid_date together, then all values for valid_time together, and so on. This column grouping enables powerful compression (similar values compress better together) and allows query engines like BigQuery to skip columns entirely when they are not needed. Parquet cannot be read with a text editor — it requires a library like pyarrow or pandas to decode its binary encoding. The trade-off is clear: CSV is human-readable and simple to produce, while Parquet is optimized for large-scale analytical queries at the cost of readability.

3. Choosing Formats for BigQuery

Parquet is preferred over CSV or JSON-L for BigQuery external tables for two main reasons: performance and cost.

On the performance side, BigQuery is a columnar query engine — it reads data one column at a time. When a query like SELECT AVG(value) WHERE parameter_name = 'PM2.5' runs, BigQuery only needs to read two columns out of nine. With Parquet, it can skip the other seven columns entirely, reading only the bytes it actually needs. With CSV or JSON-L, BigQuery must read every row from start to finish just to extract two fields, even though 77% of the data is irrelevant to the query.

On the cost side, BigQuery charges based on the number of bytes scanned. Since Parquet is compressed and column-pruning means fewer bytes are read, queries against Parquet tables cost significantly less than the equivalent query against CSV or JSON-L. For 31 days of hourly data, a single query on Parquet might scan a few hundred MB while the same query on JSON-L could scan several GB — a meaningful cost difference at scale.

4. Pipeline vs. Warehouse Joins

In this assignment, hourly observations and site locations are kept as separate tables and joined at query time in BigQuery. The alternative would be to join them during the prepare step — producing a single "denormalized" file with latitude, longitude, and site metadata embedded in every observation row.

Keeping them separate (joining at query time) has several advantages. First, it avoids data redundancy: site metadata for a given station is stored once, not repeated across every one of the ~190,000 hourly rows that reference it. Second, it is more flexible: if the site locations file is updated (e.g. a station moves), you only need to re-ingest the sites table, not re-process all 31 days of hourly data. Third, BigQuery is optimized for joins on large tables, so the performance cost of joining at query time is relatively low.

Denormalizing during the prepare step has different trade-offs. Queries become simpler — no join syntax needed — and slightly faster because the data is already combined. This approach makes sense when the joined dataset is queried very frequently, when query simplicity matters (e.g. for non-technical dashboard users), or when the data will be exported to a downstream system that does not support joins. The downside is that storage costs increase, and any change to the site locations data requires re-running the full preparation pipeline.

In general, keeping data normalized (separate tables, join at query time) is the better default for a data warehouse, since storage is cheap and the flexibility to update each dataset independently is valuable. Denormalization is a deliberate optimization applied when query performance or simplicity is a higher priority than storage efficiency and pipeline flexibility.

5. Choosing a Data Source

a) A parent who wants a dashboard showing current air quality near their child's school: The best source is the AirNow hourly files ingested into a pipeline (or the AirNow API for simpler use cases). This parent needs near-real-time data — specifically the current AQI at monitoring stations near a specific location. AirNow publishes hourly files updated throughout the day, making it ideal for a dashboard that reflects conditions right now. The AQS system would be wrong here because its quality-assured data lags by 6+ months and cannot answer "what is the air quality this morning." If the dashboard serves many users simultaneously, a pipeline that ingests AirNow files into BigQuery and serves a cached result is more responsible than calling the AirNow API on every page load.

b) An environmental justice advocate identifying neighborhoods with chronically poor air quality over the past decade: The right source is AQS bulk CSV downloads. This advocate needs quality-assured historical data spanning many years — exactly what AQS provides. AirNow's near-real-time data is not quality-controlled and is not designed for multi-year trend analysis. AQS publishes annual and daily summary files going back decades, with rigorous QA/QC applied, making it the authoritative source for long-term pollution trend analysis. The bulk CSV downloads are appropriate because the advocate needs a large dataset (many years, many sites, many pollutants) that would be impractical to retrieve through the rate-limited AQS API.

c) A school administrator who needs automated morning alerts when AQI exceeds a threshold: The best source is the AirNow API for exploratory setup, but a pipeline built on AirNow hourly files for production use. The alert system needs current AQI data checked on a schedule (e.g. every morning at 6 AM). For a single school checking one location once per day, the AirNow API is simple and appropriate. However, if the system needs to check many schools, run reliably without hitting rate limits, or serve as the backbone of a larger district-wide tool, a pipeline that downloads the hourly files and loads them into a queryable store is more robust and responsible. The AQS system is again wrong here — its data is too delayed for operational morning alerts.