Driving Impact and Delivering Results Questions

Situation: Designing storage strategies for a petabyte-scale Delta Lake containing event-level telemetry used by analysts and ML teams.

Partition keys:

• Choose coarse-grained, high-cardinality-safe keys. Primary: ingestion_date (yyyy-MM-dd or yyyy-MM) to scope most time-based queries and retention. Secondary: event_type or topic when a small number (~10–50) of stable categories heavily drive queries.
• Avoid high-cardinality user_id/session_id as partition keys — causes many small files and metadata explosion.
• Use multi-level partitioning: yyyy/MM for month-based backfill and yyyy/MM/dd for recent hot data (or dynamic partitioning where tail is daily, older data monthly).

Clustering (Z-order / data skipping):

• Use Z-order on columns frequently filtered together (e.g., user_id hashed bucket, event_type, timestamp) to colocate related rows within files and improve data skipping.
• Consider Spark bucketing or hash partitioning for join keys used by ML (user_id) combined with Z-order on timestamp.

File sizes:

• Target Parquet file size ~256–512 MB (read-optimized) for HDFS/S3; 128–256 MB if many small queries. This balances IO throughput and parallelism; avoids too many small files.

Compaction cadence:

• Nearline compaction for recent data: run small-file compaction hourly or every few hours to merge micro-batches into target file size (streaming ingestion).
• Weekly full compaction on older partitions (monthly partitions) and re-Z-ordering for cold data.
• Trigger compaction based on thresholds: number of files in partition > N (e.g., >100), average file size < target/2.

Measuring impact:

• Track query latency (P99/P50) and scan bytes per query before/after.
• Monitor number of files per partition, average file size, and Delta table transaction log size.
• Measure job runtimes for common ETL and ML training workloads and cluster CPU utilization and shuffle/read throughput.
• Use cost metrics (S3 egress, EMR/Azure Databricks compute hours) and SLA adherence for data freshness.
• A/B test: run queries against compacted vs non-compacted snapshots and compare read bytes, query time, and success rates.

Trade-offs:

• More aggressive compaction reduces read overhead but increases write/compute cost and latency for freshness.
• Z-order improves selective reads but is expensive at rewrite; schedule during low-usage windows.

This strategy balances query performance, metadata scalability, and operational cost while keeping fresh data queryable and ML pipelines efficient.

Requirements & constraints:

• Support analytics and ML: unified customer profile, identity resolution, event enrichment.
• Low-latency reads for features; batch and streaming ingestion.
• Preserve auditability and provenance.

Canonical model (core entities):

• Customer (canonical_customer): customer_id (surrogate), ext_ids {crm_id, billing_id, device_ids}, name, primary_contact, lifecycle_status, created_at, updated_at, source_origin.
• Account/BillingAccount: account_id, canonical_customer_id, billing_status, billing_cycle, currency, last_payment_date.
• Product / Subscription: subscription_id, canonical_customer_id, sku, plan, start_date, end_date, status.
• CustomerEvent (event-level): event_id, canonical_customer_id (nullable until resolved), event_type, event_ts, properties (JSON), source_system, ingestion_ts.
• IdentityMapping: ext_id_type, ext_id, canonical_customer_id, confidence, last_seen.
• Metadata tables: schema_version, change_log, provenance.

Migration plan for producers:

1. Define v1 schema + protobuf/Avro spec, publish in schema registry.
2. Run dual-write: producers write original payload + canonical envelope (or event with ext_ids). Use streaming connector (Kafka) with topics per source.
3. Build streaming enrichment job (Spark/Beam) that resolves ext_ids via IdentityMapping and emits canonical records to canonical topics and to downstream warehouse.
4. Monitor drift and data quality; iterate.

Backward compatibility:

• Use schema evolution (Avro/Protobuf) with compatible additions (optional fields), avoid breaking renames.
• Keep source_id and raw_payload for replay.
• Support nullable canonical_customer_id until ETL resolves identities.
• Version fields: schema_version on every record; provide transformation adapters for older versions.

Governance:

• Central data contract team: approves schema changes, maintains registry, enforces semantic catalog.
• Automated CI: schema linting, compatibility checks, contract tests for producers/consumers.
• SLAs, onboarding docs, change approval flow (RFC), and a deprecation policy (staggered: notify > dual-write > migrate > retire).
• Observability: lineage (data catalog), DQ metrics, identity match rates, and alerting.

Trade-offs:

• Early identity resolution increases latency but improves downstream quality; keep both raw + resolved to support experimentation.
• Use surrogate canonical_customer for stable joins.

This approach balances incremental migration, backward compatibility, and strong governance to keep the canonical model consistent for analytics and ML.