DuckDB

DuckDB is a small SQL engine that runs on your laptop. It reads Parquet over HTTPS and fetches only the columns and row groups a query needs. Any version from 1.0 works; newer is faster.

Attach the database

ATTACH 'https://data.transitlab.nyc/v1/transitlab.duckdb' AS transitlab (READ_ONLY);
SHOW ALL TABLES;

The database file is tiny. It holds one view per table, and each view lists that table’s Parquet files by URL. So transitlab.<table> always reads every file without you listing them, and the views pick up new files when the database is rebuilt each night.

A first query

Which subway segments are slow right now, and how much time do they cost riders each weekday?

SELECT segment, routes, start_date,
       extra_s_median AS extra_seconds,
       riders_per_weekday,
       round(extra_s_median * riders_per_weekday / 3600) AS rider_hours_a_day
FROM transitlab.subway_slow_zones
WHERE kind = 'slow zone'
  AND end_date >= (SELECT max(end_date) FROM transitlab.subway_slow_zones) - INTERVAL 7 DAY
ORDER BY rider_hours_a_day DESC
LIMIT 10;

Read the files directly

Every file has a plain URL, and DuckDB can query it as if it were a table:

SELECT route_id, round(sum(meters) / sum(seconds) * 2.23694, 2) AS mph
FROM 'https://data.transitlab.nyc/v1/bus_route_speeds.parquet'
WHERE hour BETWEEN 7 AND 18
GROUP BY route_id
ORDER BY mph
LIMIT 10;

Large tables are one file a month (v1/<table>/year=2026/month=08/2026-08.parquet) or, for the feed archives, one file a day (…/month=10/2026-10-06.parquet). DuckDB can’t list a folder over HTTPS, so * in a URL won’t work. Name the files, or take their URLs from the catalog:

SELECT count(*) AS trips
FROM read_parquet([
  'https://data.transitlab.nyc/v1/taxi_yellow_trips/year=2026/month=07/2026-07.parquet',
  'https://data.transitlab.nyc/v1/taxi_yellow_trips/year=2026/month=08/2026-08.parquet'
]);

Keep queries fast

• Name your columns. SELECT * on a trip table pulls every column of every row group.
• Filter on time. Parquet files keep the smallest and largest value of each column for each block of rows, and taxi files are sorted by pickup time, so a date filter lets DuckDB skip most of a table.
• Copy what you’ll reuse. Pull a slice into a local table once, then work on that:

CREATE TABLE trips AS
SELECT pickup_datetime, pickup_zone, dropoff_zone, trip_miles, trip_minutes
FROM transitlab.taxi_yellow_trips
WHERE pickup_datetime >= '2026-08-01' AND pickup_datetime < '2026-09-01';

The catalog

catalog.json describes every table: name, description, source, license, rows, bytes, columns, and the URL of every file. DuckDB can read it too:

SELECT t.name, t.rows, t.bytes, len(t.files) AS files
FROM (
  SELECT unnest(tables) AS t
  FROM read_json('https://data.transitlab.nyc/v1/catalog.json')
);