Telemetry Lake with PII, Legal Holds, and a 35% Cost Target
A mobility company ingests 3.5 TB per day of vehicle telemetry and stores 480 TB in object storage. Nine fields contain personal data, including driver identifiers and precise location. Regulations require seven-year retention, and legal can place a hold on any vehicle. Dashboards query the last seven days and account for 80% of scans; monthly aggregates account for 15%; investigations account for 5% but often scan 18 months. The current lake writes thousands of small JSON files, has no catalog, and full-table scans cost more each month. Analysts copy extracts to personal buckets to join with marketing data. The company wants to reduce storage and query cost by 35% in two quarters while making PII access auditable. The data team has four engineers and can adopt an open table format but cannot rewrite all producers at once.
1. Recommend a storage layout and query strategy that cuts cost without breaking dashboards or investigations.
Read the worked answer
Reasoning: The 35% cost target cannot come from a single storage-class change while 80% of scans hit seven days of data and 5% scan 18 months. Small JSON files amplify metadata operations and prevent partition pruning. A catalog is required before analysts can be governed.
Recommendation: convert producers to write Parquet through an open table format, with a landing zone for raw JSON and a compaction job. Partition by ingest_date and region, not by vehicle_id, because vehicle_id has high cardinality. Target 128-512 MB files and compact small files daily. Keep the last 30 days in a hot query tier, 13 months in a warm tier, and older data in archive storage. Build pre-aggregated monthly tables for the 15% aggregate workload and a curated investigation table for the 5%. Register all datasets in a central catalog with schema, owner, and classification. Use partition and column pruning; ban full-table scans for dashboards. This removes most scan cost while keeping recent dashboards fast.
2. How should the nine PII fields be handled so investigations still work and access is auditable?
Read the worked answer
Reasoning: The nine PII fields are needed for investigations but not for every dashboard. Copying extracts to personal buckets bypasses access control and audit. Encryption alone does not prevent authorized misuse.
Recommendation: tokenize driver identifiers and precise location at ingest; keep the mapping in a separate restricted account with its own access path. Encrypt the token vault with a customer-managed key and require a purpose-bound role to detokenize. Store raw PII only in a restricted zone, and expose de-identified or aggregated views to analytics. Classify the nine fields in the catalog, tag downstream tables, and block unclassified copies. Replace personal buckets with governed workspaces that enforce row and column policies. Log every detokenization and export with user, purpose, and row count; alert on bulk exports. For legal holds, preserve tokenized and raw records with immutable retention, but keep hold metadata separate so deletion jobs cannot override it.
3. Design lifecycle and immutability rules for seven-year retention and legal holds.
Read the worked answer
Reasoning: Seven-year retention conflicts with cost reduction if all data stays hot. Legal holds must survive automated lifecycle rules, so hold state cannot live only in a spreadsheet.
Recommendation: define lifecycle by age and query pattern: 0-30 days hot, 31 days to 13 months warm, 13 months to seven years archive. Use object lock in compliance mode only for data under legal hold or regulatory minimum, not for every object, because it prevents early deletion and can increase cost. For normal data, use versioning and a retention policy with a delete gate that checks the hold register. Automate the hold register through the catalog and legal workflow, and test that a held vehicle cannot be deleted. Measure restore time for archive; if investigations need 18 months within hours, keep an indexed warm copy of the fields they use. Recalculate unit cost per TB per month and per query to prove the 35% target.