13 Project: Finding the fastest way to get around NYC

The chapter turns prior Airflow concepts into a practical, end-to-end project that answers a simple question: if you travel between two NYC neighborhoods right now, is a taxi or a Citi Bike faster? It assembles a reproducible environment using Docker Compose that includes mock data sources for taxis and bikes, an S3-compatible object store (MinIO), a PostgreSQL database, and a lightweight web app. The objective is to design a robust pipeline that continuously ingests, transforms, and serves results so they can be queried and visualized.

Two live-like feeds drive the use case: Yellow Cab CSV files exposed by a basic file server in rolling 15-minute batches, and Citi Bike rides exposed by an authenticated REST API with configurable lookback windows. Because taxi files expire after an hour, the pipeline persists “raw” snapshots to MinIO for reproducibility and later reprocessing, aligning both sources on 15-minute intervals and scheduling the DAG accordingly. Instead of relying on generic operators, custom Python tasks use requests and the S3Hook, credentials are managed via Airflow connections, and idempotency is built in by safely skipping objects that already exist. The chapter advocates a clear data layout—separating raw from processed—to enable repeatable runs and shared consumption.

For transformation, Citi Bike station coordinates are mapped to taxi zones to make the two sources comparable, then standardized and written as Parquet to a processed area. To avoid code duplication, the chapter introduces a reusable PandasOperator that composes three callables—read, transform, write—around a DataFrame, making it easy to swap formats and steps. When publishing to the database, idempotency is enforced by overwriting processed outputs and adding an execution-date column, paired with a transactional delete-then-insert to prevent duplicate rows. The result is a 15-minute cadence pipeline that updates PostgreSQL and powers a web app showing, by neighborhood pair and time of day, whether taxis or Citi Bikes are faster on average.

Our Docker Compose file creates several services. Our task is to load data from the REST API and file share and transform it to eventually view the fastest method of transportation on the resulting web page.

NYC Yellow Cab zones plotted with Citi Bike station locations

Mapping Citi Bike stations (dots) to Yellow Cab zones enables accurate comparison but neglects the fact that rides within one zone can vary in distance. Ride A is obviously shorter than ride B. By averaging all ride times from Greenwich Village South to East Village, you lose such information.

A custom S3 hostname can be set, but not where you would expect it.

Screenshot of the MinIO interface showing a file written to /datalake/raw/citibike, and the filename templated with data_interval_start.

First two tasks of the NYC transportation DAG downloading data

Data exported to the MinIO storage. We have MinIO under our own control and can always refer to these files later.

We cannot control the structure of data in external systems. In our own systems, it’s logical to store data according to the life cycle of data. For example, unedited data is stored in Raw, derived and transformed data is stored in Processed, and data sets ready for transfer are stored in Export.

Web application displaying results stored in the PostgreSQL database, continuously updated by the Airflow DAG

• We cannot control the structure of data in external systems. In our own systems, it’s logical to store data according to the life cycle of data.
• Many organizations apply fine-grained separations between the stages of data. No one structure suits all; your project and the project’s requirements will determine how to best structure the movement of data.
• When an operator’s functionality does not fulfill a need, you must resort to calling a function with a PythonOperator or implement your own operator.
• We generalized the execution of Pandas transformations in a custom written PandasOperator for reusability and code deduplication
• The tasks using the PandasOperator can provide a specific callable for reading into a pandas Dataframe and writing that Dataframe to a specific output format

FAQ