Fleet Telematics Partitioning and Ingestion Architecture Specification

Role: Principal Data Architect specialising in high-scale IoT and fleet telematics data infrastructure.

Context

• Telematics System: {{telematics_platform_name}}
• Daily Data Volume: {{daily_ingestion_volume_gb}}
• SQL Engine / Dialect: {{target_sql_dialect}}
• Fleet Composition: {{fleet_size_and_asset_types}}
• Workload Patterns: {{primary_query_access_patterns}}
• Retention Mandate: {{compliance_retention_window_months}}

Task

Author a comprehensive database architecture specification detailing physical storage partitioning, indexing strategy, staging layers, and core analytical SQL transformations for the fleet telemetry streaming pipeline to ensure sub-second query performance and cost-controlled retention.

Method

1. Define the raw Bronze staging schema with explicit column types, micro-batch ingestion buffers, and JSON payload unpacking logic.
2. Establish the physical clustering and partitioning key hierarchy based on timestamp granularities and asset identifiers.
3. Design geospatial data type representations (points, linestrings) and spatial indexing methods supported natively in {{target_sql_dialect}}.
4. Construct the Silver curated sessionization SQL model using window functions to assemble raw pings into discrete vehicle trip legs.
5. Specify an incremental aggregation SQL pattern for vehicle health telemetry (engine RPM, fuel flow, harsh braking) into hourly and daily summary tables.
6. Detail lifecycle lifecycle policies, automated table pruning, and cold-tier archive partitioning satisfying the {{compliance_retention_window_months}} requirement.
7. Provide concrete DDL statements and representative production-ready SQL queries for the specified {{primary_query_access_patterns}}.

Constraints

• MUST include explicit DDL syntax tailored strictly to {{target_sql_dialect}}.
• MUST NOT recommend query patterns requiring full table scans on historical partitions.
• All timestamp fields must be specified with explicit timezone offsets (UTC default).
• Include explicit memory and compute sizing guidelines for micro-batch execution.

Output format

• Section 1: Physical Ingestion & Schema Specification (DDL, Data Types, Nullability)
• Section 2: Partitioning, Clustering, and Retention Lifecycle Plan
• Section 3: Silver-Layer Sessionization & Aggregation SQL Models
• Section 4: Query Optimization Benchmarks & Benchmark Scenarios (3 Core Access Queries) Total deliverable must be between 800 and 1200 words.

Self-review

• Verify every SQL snippet compiles cleanly according to {{target_sql_dialect}} syntax standards.
• Confirm that the partitioning strategy directly optimizes the documented {{primary_query_access_patterns}}.
• Ensure cold storage transition criteria fully cover the {{compliance_retention_window_months}} boundary.