Parquet Flight Delays: Airline Delay Analysis

Read three quarterly Parquet files with schema evolution (NULL-filled columns), plus file_filter and file_metadata for per-file drill-down.

Category: parquet

Syntax

-- ============================================================================
-- SETUP
-- ============================================================================
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
    COMMENT 'External tables, demo datasets and file-backed data';

CREATE SCHEMA IF NOT EXISTS {{zone_name}}.parquet_flights
    COMMENT 'Parquet-backed flight delay tables with schema evolution';

-- Table 1: union of all 3 quarterly files, NULL-filling added columns
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.parquet_flights.all_flights
USING PARQUET
LOCATION '{{data_path}}'
OPTIONS (
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- Table 2: Q1 only via file_filter glob
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.parquet_flights.q1_flights
USING PARQUET
LOCATION '{{data_path}}'
OPTIONS (
    file_filter   = '*q1*',
    file_metadata = '{"columns":["df_file_name"]}'
);

-- ============================================================================
-- QUERIES
-- ============================================================================
-- Full scan (120 rows across 3 files)
ASSERT ROW_COUNT = 120
SELECT * FROM {{zone_name}}.parquet_flights.all_flights;

-- Q1-only filter (40 rows)
ASSERT ROW_COUNT = 40
SELECT * FROM {{zone_name}}.parquet_flights.q1_flights;

-- Schema-evolution proof: NULL counts per source file
ASSERT ROW_COUNT = 3
SELECT df_file_name,
       COUNT(*) AS total_rows,
       COUNT(*) FILTER (WHERE delay_reason  IS NULL) AS null_delay_reason,
       COUNT(*) FILTER (WHERE carrier_code IS NULL) AS null_carrier_code
FROM {{zone_name}}.parquet_flights.all_flights
GROUP BY df_file_name
ORDER BY df_file_name;

-- Airline performance aggregation
ASSERT ROW_COUNT = 5
ASSERT VALUE avg_delay = 52.0 WHERE airline = 'Southwest Airlines'
SELECT airline,
       ROUND(AVG(delay_minutes), 1) AS avg_delay,
       COUNT(*) AS total_flights,
       COUNT(*) FILTER (WHERE status = 'On Time')   AS on_time,
       COUNT(*) FILTER (WHERE status = 'Delayed')   AS delayed,
       COUNT(*) FILTER (WHERE status = 'Cancelled') AS cancelled
FROM {{zone_name}}.parquet_flights.all_flights
GROUP BY airline
ORDER BY avg_delay DESC;

-- ============================================================================
-- CLEANUP
-- ============================================================================
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.parquet_flights.all_flights WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.parquet_flights.q1_flights WITH FILES;
DROP SCHEMA         IF EXISTS {{zone_name}}.parquet_flights;
DROP ZONE           IF EXISTS {{zone_name}};

Description

## When to Use Use this demo when landing Parquet datasets that have evolved over time, columns added in later quarters, monthly dumps with new fields, or partner feeds whose schema drifts release to release. It shows how DeltaForge unifies heterogeneous Parquet files into one external table while preserving per-file traceability through system metadata columns. ## What You Will Learn 1. How CREATE EXTERNAL TABLE USING PARQUET auto-detects types from embedded Parquet metadata without a schema definition 2. How DeltaForge NULL-fills columns that are present in some files but missing from others (schema evolution across quarters) 3. How to use the `file_filter` OPTION with a glob pattern to restrict a table to a single file 4. How to attach `df_file_name` and `df_row_number` system columns via the `file_metadata` OPTION for provenance 5. How to write cross-cutting ASSERT VALUE checks that verify row counts, NULL counts, and per-file breakdowns ## Prerequisites - DeltaForge GUI running with a workspace open - The three demo files (`flights_2025_q1.parquet`, `flights_2025_q2.parquet`, `flights_2025_q3.parquet`) accessible at `{{data_path}}` - Permission to CREATE ZONE and CREATE SCHEMA in the target workspace

Pitfalls

Schema evolution is union-by-name: columns added in later files are NULL-filled in earlier files, but a type change for the same column name (e.g. INT32 → INT64, or STRING → TIMESTAMP) is a hard error, not a silent coercion, keep types stable across quarters.
`file_filter` uses a glob pattern against the file name only, not the full path. `'*q1*'` matches any file with `q1` in its name; it will not narrow a recursive scan by directory, use `recursive = 'true'` plus a filename-anchored pattern for that.
`df_file_name` and `df_row_number` must be requested explicitly via the `file_metadata` OPTION; they are not present by default. Queries that reference them without configuring `file_metadata` fail with a missing-column error.
Predicate pushdown on Parquet depends on row-group min/max statistics; if the writer produced files with one giant row group per file (common for small dumps like the 40-row quarters here), WHERE clauses cannot skip row groups and the scan reads every value.
Column pruning only helps when the query actually omits columns. `SELECT *` forces DeltaForge to materialise every column chunk, for wide tables, project the columns you need to get the real Parquet win.
Parquet dictionary encoding can inflate apparent column count in stats but does not affect the logical schema; do not infer storage size from column count alone.
Always use `DROP EXTERNAL TABLE ... WITH FILES` in cleanup when you want the registered metadata removed, plain `DROP TABLE` targets DataFusion in-memory tables and will not match an external-table registration.

Parquet Flight Delays: Airline Delay Analysis

Syntax

Description

Pitfalls

See Also