NYC Taxi Analytics

The project uses the TLC Trip Record Data, which tracks the trips of New York City's yellow taxis. These taxis, also known as medallion taxis, can pick up passengers in all five boroughs but operate mostly in Manhattan.

I downloaded the data available for 2024 and loaded it into a Fabric lakehouse, which acts as the storage layer where these files land.

• →Yellow taxi trips are stored in Parquet format, one file per month.
• →A taxi zone lookup table is stored as CSV and holds static reference data about taxi zones.

2.png3.png

Once the files are in the lakehouse, a Data Factory copy activity ingests them into a staging table of the data warehouse. The taxi zone lookup is also loaded into staging as a static table, processed once and left unchanged.

4.1.png4.2.png

The trip file path is generated dynamically from the v_date variable, so the pipeline automatically picks up each new month of data:

@concat('yellow_tripdata_', variables('v_date'), '.parquet')

15.1.png15.2.png

• →The staging layer contains the main table stg.nyctaxi_yellow, which stores the taxi trips.
• →It also includes the static lookup table stg.taxi_zone_lookup, which describes NYC taxi zones.

6.3.png6.2.png

At this stage I cleanse and process the data before moving it to the presentation layer.

Out-of-period cleanup

1. 01A stored procedure dynamically deletes data outside the current processing period. It takes two parameters: @start_date (start of the period) and @end_date (end of the period).
2. 02A Set Variable activity defines v_end_date as v_date plus one month, so the pipeline processes the correct time range.
3. 03A Stored Procedure activity calls stg.data_cleaning_stg, mapping v_date to @start_date and v_end_date to @end_date.
4. 04For consistency, the Copy to Staging activity empties stg.nyctaxi_yellow before each run, so only data for the current period is processed.

Metadata logging

I created a metadata schema with a processing_log table that records every pipeline execution. After cleaning and transformation, a record is inserted with:

• →pipeline_run_id — unique identifier of the run.
• →table_processed — name of the processed table.
• →rows_processed — number of rows processed.
• →latest_processed_pickup — latest pickup datetime in the batch.
• →processed_datetime — execution timestamp.

The insertion logic is wrapped in a stored procedure so that logging runs systematically as part of the pipeline.

8.1.png8.2.png

Self-advancing processing window

To always process the next available month, v_date is updated from the last processed data. A Script activity, Latest_Processed_Data, fetches the most recent tpep_pickup_datetime (when the meter was engaged) from metadata.processing_log, then a Set Variable activity adds one month to it.

9.1.png9.2.png

The resulting pipeline, pl_stg_processing_nyctaxi, automates ingestion, cleansing and tracking: it adjusts the processing period, loads staging, removes out-of-period records and updates the metadata table for full traceability.

10.png

Enrichment with Dataflow Gen2

A Dataflow Gen2 moves the cleaned data from staging to the presentation layer. It left-joins nyctaxi_yellow with taxi_zone_lookup to add borough and zone details for both pickup and drop-off, removes unnecessary columns, categorises values and loads the result into dbo.nyctaxi_yellow.

11.3.png11.2-1.png11.1.png

The pl_processing_nyctaxi pipeline runs this dataflow, then calls the insert_presentation_metadata stored procedure to log the number of rows, the latest pickup timestamp and the execution time for dbo.nyctaxi_yellow.

16.png12.1.png12.2.png

Master pipeline

Finally, a master pipeline orchestrates pl_stg_processing_nyctaxi then pl_processing_nyctaxi sequentially: staging runs first, followed by transformation and loading into the presentation layer. This guarantees data consistency and end-to-end automation.

13.png

With dbo.nyctaxi_yellow finalised, I built a semantic model that structures and optimises the data for Power BI, with relationships and calculated measures for efficient querying.

14.1.png14.2.png

The report analyses yellow-cab performance with key metrics such as total revenue, number of trips and passengers. Interactive filters let you explore trends by payment method, vendor and pickup/drop-off borough.

ff.png