Thirty-Second Telematics, Six-Hour Reconciliation, 30% Cost Cut
FleetPulse tracks 9,000 vehicles. Telematics events peak at 40,000 per second and must appear on an operations dashboard within 30 seconds; the same events feed a finance reconciliation that must finish by 06:00 daily and be reproducible. Today, IDMC polls Kafka topics every 15 minutes and writes to Databricks. The operations team calls 15 minutes "close enough", but the dashboard is sometimes 40 minutes stale during peaks. The reconciliation is a chain of 240 IDMC mappings that takes 5 hours 40 minutes against a six-hour window. The Databricks bill is dominated by a 24/7 interactive cluster that is idle most of the night. The team knows IDMC well and has only basic Databricks SQL skills. Leadership wants one platform and a 30% cost reduction.
1. Which workloads should move to Databricks-native processing and which should stay in IDMC? Justify each choice against latency, volume and skills.
Read the worked answer
Split on mechanics, not preference.
Move to Databricks-native:
- The 40,000 events/second telematics path. IDMC is a batch and micro-batch engine with a polling agent; a 15-minute poll cannot produce 30-second freshness, and during peaks the agent queue is exactly what turned 15 minutes into 40. Kafka into Structured Streaming, or Auto Loader if events land as files, writes to bronze continuously and checkpoints offsets, so latency becomes a trigger interval rather than a schedule.
- The heavy set-based work inside the reconciliation, once profiled. 240 mappings in 5h40m usually means a handful dominate; the top 10-20 that are pure joins, aggregations and window functions belong in Databricks SQL or notebooks running beside the data.
- BI serving from a serverless SQL warehouse against gold.
Keep in IDMC:
- Connector-heavy extraction where IDMC has a mature supported connector and the team knows it - SAP, mainframe, SaaS APIs.
- Mappings whose business rules are undocumented. Rewriting undocumented logic under a deadline is the real risk, not licence cost.
- Scheduling and runbook-level monitoring of the remaining batch chain, if that is where operational knowledge lives.
The honest constraint is skills. A 30% cut that assumes a team learns Spark in a quarter is a plan with a hidden second phase. Move streaming first - it is bounded and it is where IDMC is structurally wrong - then migrate batch incrementally behind a runtime profile.
2. Design the freshness tiers and the orchestration between the two tools. Where does the finance tier get its reproducibility?
Read the worked answer
Three tiers, each with an owner.
- Hot, seconds: Kafka into Structured Streaming writing bronze Delta on a 10-30 second trigger, checkpoints on durable storage, partitioned by event date. Gold aggregates feeding the operations dashboard refresh on the same cadence. This meets the 30-second SLA with margin.
- Warm, 5-15 minutes: a scheduled Databricks Workflow building the curated silver and gold dimensions the dashboard joins to, keeping the streaming path cheap and simple.
- Cold, daily before 06:00: the finance reconciliation. Reproducibility comes from pinning input to a Delta table version or dated partition, removing non-determinism (no current_timestamp, no unseeded rand), and writing to a run-dated gold table so a restatement is a comparison rather than an argument. Size job clusters for the window and target 3h30m to leave retry headroom.
Orchestration. Choose one scheduler for the dependency graph. The cleaner option is Databricks Workflows calling the remaining IDMC taskflows through the IDMC REST API, since the streaming and batch majority already live in Databricks and the SLA is measured there. Two schedulers each believing they own the chain produce double runs and stale-state merges - the same class of failure already visible in the 40-minute dashboard lag.
Cost. Retire the 24/7 interactive cluster: job clusters with autoscaling for batch, an instance pool for the streaming cluster, serverless SQL for BI. If the dashboard is only watched 05:00-23:00, schedule the streaming cluster accordingly and absorb the morning start-up.
3. Rank the biggest risks in this design and specify what you would monitor to catch each one early.
Read the worked answer
1. Exactly-once assumptions in streaming. A checkpoint directory deleted or relocated during a redeploy causes duplicates or loss. Protect the checkpoint path like production data and never allow two writers to share it. Monitor Kafka consumer lag, watermark delay, and batch duration creeping past the trigger interval - that duration trend is the early warning before the dashboard goes stale.
2. Dual orchestration. A Databricks job and an IDMC taskflow both triggering the reconciliation will collide on the same Delta table. Name one owner and make every write an idempotent MERGE keyed on business key plus run date.
3. Reproducibility of finance numbers. Late-arriving telematics events restate yesterday. Freeze the input snapshot, record the Delta version, and publish restatements as new runs rather than mutating prior output.
4. Skills concentration. One person who understands both the IDMC mappings and the streaming job is a single point of failure. Pair during migration and write the runbooks while the knowledge is present.
5. Schema drift from the fleet. A new telematics field should land in bronze untouched and stay inert downstream until modelled - use Auto Loader schema evolution with a rescued-data column.
Monitor: consumer lag and watermark; streaming failures and dead-letter volume; reconciliation completion against 06:00; IDMC agent heartbeat and job success for whatever remains; and cost per pipeline reviewed weekly, because without that review the 30% target drifts back within two quarters.