Using Cube’s Analytic Agent For NYC Taxi Trip Data Analysis

TL;DR

I’ve been following Joe Reis’ Substack and saw him write about his experience using Cube’s analytic agent (see ‘I stress-tested Cube’s New AI Analytics Agent’). I naturally thought about giving Cube a try since I just talked about my experience using the Hex notebook agent. In this post, I’m replicating (as much as I can) the same analysis on the same dataset and comparing the process and experience to what I did with Hex.

Photo by Nitin Shivaprasad on Unsplash

Raw Data

To compare the two solutions, I’ll use the same dataset from my previous post. The raw data can be found here: https://github.com/muharismrgn/NYC-Taxi-Trip-Data-Analysis/tree/main/data/raw.

Importing Data

Cube’s free trial plan doesn’t allow direct CSV imports. No problem. I connected Cube to MotherDuck (the cloud-based SaaS version of DuckDB) as my data source, which supports CSV imports and also offers a free trial.

Here’s what it looks like after importing the CSV file into MotherDuck. The dataset contains 68,211 records.

Coming back to Cube side, after configuring DuckDB as the data source, Cube quickly ran its own magic analyzing the data (¯_(ツ)_/¯). I then asked it to do a quick quality check. Cube also reports 68211 records. We are off to a good start.

Compared to what Hex agent produced (shown below), Cube’s result shows more context beyond simply showing statistics.

Then I asked Cube to investigate possible duplicate records and came up with some strategies.

Looks great. It covers more cases than I can think of. In order to use the same strategy I asked Cube to use the same columns that I used before

lpep_pickup_datetime
lpep_dropoff_datetime
RatecodeID
PULocationID
DOLocationID
passenger_count
trip_distance

Cube also reports 174 duplicate records matching the same number reported by Hex. The summary provides richer context than what Hex did. Nice!

Data Cleaning

Let’s do some clean up before moving forward using data.

Removing Duplicates

I asked Cube to use same columns mentioned above to analyze the records before attempting to delete them.

Oh No!

I discovered a truth that I didn’t know about Cube before.

Photo by Justus Menke on Unsplash

Unfortunately, I discovered that Cube is not designed to modify the underlying data

That’s a bummer, isn’t it?

Given that Cube doesn’t allow modifying the underlying data, I’ll proceed as if the data quality is fine. 🫠

Visualization

I am going to create the same charts that were created by Hex before.

Total Trip Distribution on January

First, we plot the daily trip count over the month of January 2023.

Interestingly, I was able to give Cube the Python code that the Hex agent had generated, and it worked. After a few iterations, Cube produced a chart similar to what Hex had generated.

Average Trip By Weekday

The average is calculated by:

1. Grouping trips by actual calendar date + day of week (e.g., Jan 2, Jan 9, Jan 16, Jan 23, Jan 30 are all Mondays)
2. Counting total trips for each specific date
3. Averaging those daily counts by day of week

Total Trip By Hour

Total Trips By Pickup Borough and Pickup Zone

Final Thoughts

What is Cube great about

1 Cube creates a semantic data model. In our example, we have one table containing trip data, which Cube uses to automatically generate a data model.

cubes:
  - name: nyc_trips_cube
    sql_table: main.nyctlctriprecord
    description: NYC TLC green taxi trip records with pickup/dropoff times, locations, fares, and passenger information

    dimensions:
      - name: vendor_id
        sql: VendorID
        type: number
        description: Vendor ID (1=Creative Mobile Technologies, 2=VeriFone Inc.)

      - name: pickup_datetime
        sql: lpep_pickup_datetime
        type: time
        description: Date and time when the meter was engaged

      - name: dropoff_datetime
        sql: lpep_dropoff_datetime
        type: time
        description: Date and time when the meter was disengaged

      - name: store_and_fwd_flag
        sql: store_and_fwd_flag
        type: string
        description: Trip record held in vehicle memory before sending (Y=store and forward, N=not stored)

      - name: rate_code_id
        sql: RatecodeID
        type: number
        description: Rate code (1=Standard, 2=JFK, 3=Newark, 4=Nassau/Westchester, 5=Negotiated, 6=Group ride)

      - name: pickup_location_id
        sql: PULocationID
        type: number
        description: TLC Taxi Zone where the meter was engaged

      - name: dropoff_location_id
        sql: DOLocationID
        type: number
        description: TLC Taxi Zone where the meter was disengaged

      - name: passenger_count
        sql: passenger_count
        type: number
        description: Number of passengers in the vehicle (driver entered value)

      - name: trip_distance
        sql: trip_distance
        type: number
        description: Trip distance in miles

      - name: payment_type
        sql: payment_type
        type: number
        description: Payment method (1=Credit card, 2=Cash, 3=No charge, 4=Dispute, 5=Unknown, 6=Voided)

      - name: trip_type
        sql: trip_type
        type: number
        description: Trip type (1=Street-hail, 2=Dispatch)

      - name: fare_amount
        sql: fare_amount
        type: number
        description: Time-and-distance fare calculated by the meter

      - name: extra
        sql: extra
        type: number
        description: Miscellaneous extras and surcharges

      - name: mta_tax
        sql: mta_tax
        type: number
        description: MTA tax automatically triggered based on metered rate

      - name: tip_amount
        sql: tip_amount
        type: number
        description: Tip amount (automatically populated for credit card tips)

      - name: tolls_amount
        sql: tolls_amount
        type: number
        description: Total amount of all tolls paid in trip

      - name: improvement_surcharge
        sql: improvement_surcharge
        type: number
        description: Improvement surcharge assessed on hailed trips

      - name: total_amount
        sql: total_amount
        type: number
        description: Total amount charged to passengers (does not include cash tips)

      - name: congestion_surcharge
        sql: congestion_surcharge
        type: number
        description: Congestion surcharge for trips in congestion zone

      - name: trip_duration_minutes
        sql: EXTRACT(EPOCH FROM (lpep_dropoff_datetime - lpep_pickup_datetime)) / 60
        type: number
        description: Trip duration in minutes

      - name: avg_speed_mph
        sql: >
          CASE 
            WHEN EXTRACT(EPOCH FROM (lpep_dropoff_datetime - lpep_pickup_datetime)) > 0 
            THEN trip_distance / (EXTRACT(EPOCH FROM (lpep_dropoff_datetime - lpep_pickup_datetime)) / 3600)
            ELSE 0 
          END
        type: number
        description: Average speed in miles per hour

      - name: tip_percentage
        sql: >
          CASE 
            WHEN fare_amount > 0 
            THEN (tip_amount / fare_amount) * 100
            ELSE 0 
          END
        type: number
        description: Tip as percentage of fare amount

      - name: pickup_borough
        sql: |
          CASE 
            WHEN PULocationID IN (4, 12, 13, 24, 41, 42, 43, 45, 48, 50, 68, 74, 75, 79, 87, 88, 90, 100, 103, 104, 105, 107, 113, 114, 116, 120, 125, 127, 128, 137, 140, 141, 142, 143, 144, 148, 151, 152, 153, 158, 161, 162, 163, 164, 166, 170, 186, 194, 202, 209, 211, 224, 229, 230, 231, 232, 233, 234, 236, 237, 238, 239, 243, 244, 246, 249, 261, 262, 263) THEN 'Manhattan'
            WHEN PULocationID IN (14, 17, 21, 22, 25, 26, 29, 33, 34, 35, 36, 37, 39, 40, 49, 52, 54, 55, 61, 62, 63, 65, 66, 67, 71, 72, 76, 77, 80, 85, 89, 91, 97, 106, 108, 111, 112, 123, 133, 149, 150, 154, 155, 165, 177, 178, 181, 188, 189, 190, 195, 197, 198, 203, 210, 217, 218, 222, 225, 227, 228, 250, 252, 253, 254, 255, 256, 257, 258, 259, 260) THEN 'Brooklyn'
            WHEN PULocationID IN (2, 7, 8, 9, 10, 11, 15, 16, 19, 27, 28, 30, 38, 53, 56, 57, 58, 64, 70, 73, 82, 83, 86, 92, 93, 95, 96, 98, 101, 102, 117, 121, 122, 124, 126, 129, 130, 131, 132, 134, 135, 138, 139, 145, 146, 157, 160, 167, 168, 169, 171, 173, 175, 179, 180, 191, 192, 193, 196, 199, 200, 201, 205, 207, 208, 215, 216, 219, 223, 226, 242, 247, 248, 251) THEN 'Queens'
            WHEN PULocationID IN (3, 18, 20, 31, 32, 46, 47, 51, 58, 59, 60, 69, 78, 81, 94, 119, 126, 136, 147, 159, 167, 168, 169, 174, 182, 183, 184, 185, 199, 200, 208, 212, 213, 220, 235, 240, 241, 242, 247, 248, 251) THEN 'Bronx'
            WHEN PULocationID IN (5, 6, 23, 110, 115, 118, 156, 172, 176, 187, 204, 206, 214, 221, 245) THEN 'Staten Island'
            WHEN PULocationID = 1 THEN 'EWR'
            ELSE 'Unknown'
          END
        type: string
        description: Borough where the trip pickup occurred

    measures:
      - name: count
        type: count
        description: Total number of trips

      - name: total_trips
        type: count
        description: Total number of trips

      - name: total_revenue
        type: sum
        sql: total_amount
        description: Total revenue from all trips

      - name: avg_trip_distance
        type: avg
        sql: trip_distance
        description: Average trip distance in miles

      - name: avg_fare_amount
        type: avg
        sql: fare_amount
        description: Average fare amount

      - name: avg_tip_amount
        type: avg
        sql: tip_amount
        description: Average tip amount

      - name: total_trip_distance
        type: sum
        sql: trip_distance
        description: Total distance traveled across all trips

      - name: total_passengers
        type: sum
        sql: passenger_count
        description: Total number of passengers across all trips

      - name: avg_passengers
        type: avg
        sql: passenger_count
        description: Average number of passengers per trip

      - name: avg_trip_duration
        type: avg
        sql: EXTRACT(EPOCH FROM (lpep_dropoff_datetime - lpep_pickup_datetime)) / 60
        description: Average trip duration in minutes

      - name: total_tip_amount
        type: sum
        sql: tip_amount
        description: Total tips collected across all trips

Cube also creates “View” which is built on top of cube(s).

views:
  - name: nyc_trips
    description: NYC TLC (Taxi and Limousine Commission) trip records for green taxis
    public: true

    cubes:
      - join_path: nyc_trips_cube
        includes:
          - vendor_id
          - pickup_datetime
          - dropoff_datetime
          - store_and_fwd_flag
          - rate_code_id
          - pickup_location_id
          - pickup_borough
          - dropoff_location_id
          - passenger_count
          - trip_distance
          - fare_amount
          - extra
          - mta_tax
          - tip_amount
          - tolls_amount
          - improvement_surcharge
          - total_amount
          - payment_type
          - trip_type
          - congestion_surcharge
          - trip_duration_minutes
          - avg_speed_mph
          - tip_percentage
          - count
          - total_trips
          - total_revenue
          - avg_trip_distance
          - avg_fare_amount
          - avg_tip_amount
          - total_trip_distance
          - total_passengers
          - avg_passengers
          - avg_trip_duration
          - total_tip_amount

It is interesting to see how Cube solves the problem of providing different views to users on top of the same semantic model and also at the same time applying different access control.

Being able to generate semantic data model is a huge deal at least for me. Not having to hand roll a model does save time especially when you are working with dataset that is not familiar to you.

2 The chat response feels more natural and provides deeper insights compared to what Hex produces.

3 Source Control. The git style source control gives user the ability to make increment changes and is essential for development.

What is Hex great about

1 Being able to provide users with the generated Python code is a huge win. While Cube is great at what it does, not being able to see how it generates results doesn’t necessarily give us confidence in the correctness of the solution.

2 Hex’s approach of loading data into Pandas DataFrames gives us flexibility to modify data as needed. On the other hand, Cube is kind of “view only” data analytic solution. User will have to find another way of transforming raw data first. In other words, using Hex we have the opportunity to configure ETL jobs. In my case, I had to use MotherDuck to load the raw data file and I didn’t want to spend the effort configuring ETL.

The opportunity of using dataframe shows its competitive advantage.

3 It feels like Hex is better at generating chart based on this experience.

Thinking Ahead

1 It should be possible to create a deep dbt integration with Cube given that dbt has strong community and existing integration with various external sources.

2 How does the semantic layer generated by Cube compare to what dbt offers ?

3 How does dbt agent compare to Hex and Cube?

Until next time~