Datanalysis0: Designing a Multi-Source Telemetry & Earnings Pipeline Where One Source Has No Export

Executive Summary & Context

Operational telemetry and financial earnings across multi-platform mobility operations sit in incompatible, siloed formats: high-frequency vehicle telemetry CSVs (Tessie), weekly platform PDF statements, rider invoice CSVs, and a fourth platform (Lyft) that provides no bulk data export at all—only mobile screenshots.

Datanalysis0 was engineered as a phased Python and SQLite data pipeline to solve this heterogeneous ingestion problem using transferable data-engineering discipline: data-dictionary-first discovery, strict relational normalization, generated columns for deterministic business logic, and a living architectural decision log (causality.md).

---

Architecture & Technical Execution

+-------------------+  +-------------------+  +-------------------+  +--------------------+
| Vehicle Telemetry |  | Platform Earnings |  | Rider Invoices    |  | No-Export Platform |
| (Tessie CSVs)     |  | (Weekly PDFs)     |  | (Trip CSVs)       |  | (Screenshots Only) |
+-------------------+  +-------------------+  +-------------------+  +--------------------+
          |                      |                      |                       |
          +----------------------+----------------------+                       |
                                 |                                              v
                                 v                                    +--------------------+
                    +-------------------------+                       |   lyft_shim.py     |
                    |       ingest.py         |                       | (CLI Manual-Entry  |
                    | (Automated ETL Parsers) |                       |  & Validation Shim)|
                    +-------------------------+                       +--------------------+
                                 \                                             /
                                  +---------------------+---------------------+
                                                        |
                                                        v
                                 +---------------------------------------------+
                                 |          SQLite Relational Store            |
                                 |  - data_dictionary.md (Field-Level Spec)    |
                                 |  - schema.sql (8 Tables, 3 Analytical Views)|
                                 |  - Generated Columns for Utilization Logic  |
                                 |  - causality.md (Condition/Decision/Effect) |
                                 +---------------------------------------------+

1. Data-Dictionary-First Discovery (data_dictionary.md)

Before writing a single CREATE TABLE statement, every source field across all four platforms was catalogued with its data type, unit, origin, and nullability constraints. Defining the data dictionary first surfaced the missing-export constraint early—preventing the relational schema from locking in assumptions that one platform could not satisfy.

2. Relational Schema & Generated Columns (schema.sql)

• 8 Normalized Tables & 3 Views: Validated cleanly in SQLite (chosen for zero-daemon portability and direct conceptual parity with PostgreSQL).
• Deterministic Business Logic: Computed metrics use SQL generated columns and views rather than scattered application-layer formulas.
• Authoritative Utilization Classifier: Domain analysis revealed that raw odometer deltas contaminate commercial utilization calculations with personal driving kilometers. The schema designates the telemetry tag field (Personal vs. Rideshare) as the authoritative classifier for denominator filtering.

3. First-Class Handling of Missing Exports (lyft_shim.py)

Rather than ignoring the platform with no CSV/API export, lyft_shim.py was built as an interactive, validation-enforcing CLI shim that prompts for required trip attributes, normalizes timestamps and currency fields, and inserts directly into the unified SQLite schema (with 11 initial rides seeded and verified, and an OCR ingestion path scoped for a future phase).

4. Decision Traceability (causality.md)

Every structural choice across the pipeline is logged as a triad—Condition → Decision → Effect—providing an auditable engineering record across working sessions. Similarly, a companion portfolio utility—the Synthetic Support-Ticket Data Generator—applies the same schema-invariant philosophy using Python and Faker to validate conditional null fields, chronological timestamp ordering, and category distributions for ITSM testing without exposing customer PII.

---

Measurable Impact & Results

• Unified 4-Source Ingestion: Consolidated incompatible CSVs, PDF statements, and non-exportable mobile records into a single 8-table, 3-view relational schema.
• Data Integrity at Entry: Eliminated utilization denominator contamination by isolating tagged telemetry segments and enforcing CLI validation on manual-shim records.
• Full Architectural Traceability: Every schema constraint and ingestion trade-off is documented in data_dictionary.md and causality.md.