| Column | Data Type | Usage Count | Logic Count |
|---|---|---|---|
trip_distance | FLOAT | 13 times | 1 time |
| Model Name | Impact Level | How trip_distance is Used | Affected Calculations | Business Domain |
|---|---|---|---|---|
revenue_per_distance | 🔴 HIGH | • sum(trip_distance) as total_distance• Used in revenue_per_mile calculation | • Revenue per mile • Total distance aggregations | Finance/Operations |
trip_distance_breakdown | 🔴 HIGH | • Distance categorization logic • sum(trip_distance) as total_distance• avg(trip_distance) as avg_distance | • Distance categories (Under 1 mile, 1-3 miles, etc.) • Average distance per trip • Distance-based percentages • Revenue per mile | Operations/Analytics |
financial_statement | 🟡 MEDIUM | • sum(trip_distance) as total_distance | • Average trip distance • Total distance metrics | Finance |
vendor_results | 🟡 MEDIUM | • sum(trip_distance) as total_distance | • Average trip distance per vendor • Vendor performance metrics | Finance/Operations |
airport_popularity | 🟢 NONE | Not used | None | Marketing |
borough_popularity | 🟢 NONE | Not used | None | Marketing |
cost_breakdown | 🟢 NONE | Not used | None | Finance |
zone_popularity | 🟢 NONE | Not used | None | Marketing |
| Test Name | Test Type | Impact | Description |
|---|---|---|---|
not_null_revenue_per_distance_revenue_per_mile | Nullness | 🔴 Direct | Validates revenue per mile is not null |
expression_is_true_revenue_per_distance_revenue_per_mile > 0 | Range | 🔴 Direct | Ensures revenue per mile is positive |
expression_is_true_revenue_per_distance_revenue_per_mile < 1000 | Range | 🔴 Direct | Ensures revenue per mile is reasonable |
not_null_revenue_per_distance_total_distance | Nullness | 🔴 Direct | Validates total distance is not null |
expression_is_true_revenue_per_distance_total_distance > 0 | Range | 🔴 Direct | Ensures total distance is positive |
| Model | Field Created | Calculation | Business Purpose |
|---|---|---|---|
revenue_per_distance | total_distance | sum(trip_distance) | Monthly distance aggregation by location/vendor |
revenue_per_distance | revenue_per_mile | total_revenue / total_distance | Key efficiency metric |
trip_distance_breakdown | distance_category | CASE statement on trip_distance | Trip categorization for analysis |
trip_distance_breakdown | total_distance | sum(trip_distance) | Category-level distance totals |
trip_distance_breakdown | avg_distance | avg(trip_distance) | Average trip length by category |
trip_distance_breakdown | revenue_per_mile | total_revenue / total_distance | Revenue efficiency by category |
financial_statement | total_distance | sum(trip_distance) | Monthly operational metric |
financial_statement | avg_trip_distance | total_distance / total_trips | Average trip length |
vendor_results | total_distance | sum(trip_distance) | Daily vendor distance totals |
vendor_results | avg_trip_distance | total_distance / total_trips | Vendor performance comparison |
| Monitor/Alert | Entity | Description | Impact |
|---|---|---|---|
| "top vendor revenue [daily]" | vendor_results | Tracks vendor revenue performance | 🟡 Indirect (if distance affects revenue calculations) |
| Risk Category | Level | Affected Data Products | Description |
|---|---|---|---|
| Financial Reporting | 🔴 High | revenue_per_distance, financial_statement | Revenue per mile calculations and monthly financial metrics |
| Data Quality | 🔴 High | revenue_per_distance (5 tests) | Tests validating distance-derived fields may fail |
| Analytics | 🔴 High | trip_distance_breakdown | Distance categorization and trip pattern analysis |
| Operational Reporting | 🟡 Medium | vendor_results, financial_statement | Vendor performance and operational distance metrics |
| Marketing Analytics | 🟢 Low | airport_popularity, borough_popularity, zone_popularity | These models are unaffected by distance changes |
| Priority | Action | Reason |
|---|---|---|
| 1 | Test revenue_per_distance model | Contains most critical distance calculations |
| 2 | Review trip_distance_breakdown logic | Distance categorization may need adjustment |
| 3 | Validate 5 data quality tests | Tests may fail with data type/value changes |
| 4 | Check downstream dashboards | Business users rely on revenue per mile metrics |
| 5 | Update documentation | Distance field changes affect multiple domains |