Summary
System design round at Axon for a software engineer role. The whole thing was basically one big question about building an analytics warehouse for an e-commerce platform, and they wanted you to cover everything from schema design to pipeline architecture to SCD handling. Dense.
Questions Asked(7)
This question is massive.
Suggested Approach
Start by clarifying the query patterns and data volume requirements, then design a layered warehouse architecture (ingestion, storage, serving) optimized for both batch and real-time analytics. Ground every design decision in the specific pre-authored queries provided, showing how the schema and indexing strategy directly enable those queries efficiently.
Clarify Requirements & Query Patterns
Identify the read/write ratio, acceptable query latency (real-time vs. hourly/daily), data retention period, and approximate event volume per day. Confirm whether 'real time' for the top-3 orders query means sub-second or sub-minute, as this drives technology choices significantly.
Define the Event Schema & Data Model
Design a fact-and-dimension star schema: a central Events fact table (event_id, user_id, item_id, order_id, event_type, timestamp, geo_region, amount) joined to dimension tables for Users (region, country), Items, and Orders. Ensure the schema directly supports the pre-authored queries without expensive joins at query time.
Design the Ingestion Pipeline
Use an event streaming layer (e.g., Kafka) to capture clickstream events from all services (search, detail views, login, cart, checkout, orders) and route them to both a real-time processing engine (e.g., Apache Flink or Spark Streaming) and a batch landing zone (e.g., S3/GCS). This dual-path approach supports both latency tiers.
Choose Storage & Serving Layers
Use a columnar OLAP store (e.g., BigQuery, Snowflake, or ClickHouse) for batch/historical queries like the monthly unique-user count, leveraging partitioning by month and clustering by region and item_id. For the real-time top-3 orders query, maintain a continuously updated materialized view or a Redis sorted set updated by the stream processor.
Address Trade-offs, Scalability & Reliability
Discuss partitioning and indexing strategies (e.g., partition Events by date, cluster by region) to minimize query scan costs, and explain idempotent event ingestion to handle duplicates. Mention monitoring query performance, schema evolution strategies (e.g., backward-compatible Avro/Protobuf schemas), and access control for sensitive user data.
Key Points to Mention
Went with a pretty standard approach: event_id as primary key, user_id (nullable for anonymous), session_id, event_type enum, timestamp, item_id, and a metadata blob for event-specific stuff.
Suggested Approach
Start by clarifying the scope and use cases for the event schema (e.g., clickstream, audit logs, IoT sensor data) before diving into field definitions and data types. Structure your answer around core entities, their relationships, and the trade-offs between normalization and query performance. Tie your design decisions back to Axon's domain — public safety technology — where auditability, scalability, and data integrity are critical.
Clarify Requirements & Use Cases
Ask clarifying questions to understand who consumes the events (analytics pipelines, audit systems, real-time dashboards) and the expected volume and retention needs. This scopes the design and signals that you think before you build.
Define Core Fields & Data Types
Identify universal fields every event must carry: event_id (UUID), event_type (enum/string), user_id (UUID/foreign key), timestamp (ISO 8601 / epoch ms), session_id, device_id, and a payload or properties object. Justify each data type choice (e.g., UUID for global uniqueness, epoch ms for timezone-agnostic sorting).
Model Key Relationships
Describe how the event entity relates to Users, Sessions, Devices, and Resources (e.g., a video clip or case file in Axon's context). Decide whether to embed related data denormalized in the event record or reference it via foreign keys, explaining the read-vs-write trade-off.
Address Partitioning & Indexing Strategy
Explain how you would partition the data (e.g., by timestamp or user_id) for efficient querying at scale, and which fields warrant indexes (event_type, user_id, timestamp). Mention append-only patterns common in event stores to preserve immutability.
Discuss Trade-offs & Schema Evolution
Acknowledge trade-offs such as strict schema vs. flexible JSON payloads, storage costs of denormalization, and how to handle schema versioning (e.g., a schema_version field, Avro/Protobuf for serialization). Highlight how your design supports auditability and compliance, which is especially relevant for Axon.
Key Points to Mention
I said client-generated UUID on first touch, stored in a cookie or local storage, passed with every event.
Suggested Approach
Start by defining what a session represents in your system and the lifecycle it must support, then walk through ID generation strategies for both anonymous and authenticated states. Emphasize how you handle the critical transition point when an anonymous user authenticates, ensuring continuity of session data without security vulnerabilities.
Define Session Scope and Lifecycle
Clarify what a session represents — a bounded period of user activity — and outline its states: creation, active, idle timeout, hard expiry, and termination. Distinguish between anonymous sessions (pre-login) and authenticated sessions and explain why both need tracking.
ID Generation Strategy
Describe generating cryptographically secure, globally unique session IDs using a CSPRNG (e.g., UUID v4 or a 128-bit random token encoded in base64). Explain why sequential or predictable IDs are a security risk and how entropy requirements differ between anonymous and authenticated sessions.
Anonymous-to-Authenticated Transition
Walk through the session upgrade flow: when a user authenticates, issue a brand-new session ID (invalidating the old one) while migrating relevant anonymous session data — such as cart contents or behavioral context — to the new authenticated session. This prevents session fixation attacks.
Storage, Expiry, and Invalidation
Discuss where session state is stored (e.g., Redis with TTL for distributed systems, or signed JWTs for stateless approaches) and how expiry policies differ — shorter TTLs for anonymous sessions, sliding expiry for authenticated ones. Cover explicit invalidation on logout and concurrent session management.
Scalability, Security Hardening, and Observability
Address cross-service session propagation (e.g., passing session context via headers in microservices), binding sessions to additional signals like IP or user-agent for anomaly detection, and logging session events for auditability — especially important in regulated or law-enforcement-adjacent environments like Axon.
Key Points to Mention
Dedup via event_id with an upsert pattern or a seen-IDs bloom filter at ingestion.
Suggested Approach
Frame your answer around event-driven architecture principles, specifically addressing idempotency at the consumer level and deduplication at both the ingestion and processing layers. Connect your strategy to real-world trade-offs such as storage cost vs. correctness guarantees, and tie it back to Axon's domain of CQRS and Event Sourcing where event ordering and exactly-once semantics are critical. Demonstrate awareness that no single solution fits all scenarios and that the right approach depends on consistency requirements and system scale.
Define the Problem Scope
Clarify the delivery guarantees of the messaging system in use (at-least-once, at-most-once, exactly-once) and identify where duplicates and out-of-order events can originate, such as retries, network partitions, or producer restarts. This sets the foundation for why deduplication and idempotency are necessary.
Establish Idempotent Consumers
Design event handlers to be naturally idempotent by ensuring that processing the same event multiple times produces the same outcome, using techniques like upserts, conditional writes, or state checks before applying changes. Mention using unique event IDs or aggregate version numbers as idempotency keys.
Implement Deduplication Mechanisms
Introduce a deduplication store — such as a Redis set, database table, or bloom filter — that tracks recently processed event IDs with a TTL aligned to your retry window. Discuss the trade-off between strict deduplication (exact match with persistent storage) and probabilistic approaches (bloom filters) based on throughput and accuracy needs.
Handle Late-Arriving and Out-of-Order Events
Use sequence numbers, timestamps, or vector clocks to detect ordering violations, and define a grace period or watermark strategy to decide when to accept or reject late events. Describe options such as buffering events in a reorder buffer, using event sourcing replay capabilities, or applying compensating events for corrections.
Monitor, Alert, and Tune
Instrument the pipeline with metrics tracking duplicate rates, late-arrival counts, and processing lag to continuously validate that your strategy is working and to surface anomalies early. Emphasize that deduplication windows and watermarks should be tunable based on observed system behavior rather than fixed at design time.
Key Points to Mention
Partitioned by event_date and region for the geo/time queries.
Suggested Approach
Ground your answer in the specific query patterns the platform needs to support (e.g., time-range scans, device/agency lookups, aggregations) before proposing any partitioning or indexing strategy. Tie each technical decision back to a concrete performance or cost trade-off, demonstrating that you design for real workloads rather than theoretical ideals.
Identify Query Patterns First
Start by articulating the dominant access patterns: time-range queries on event data, lookups by device or agency ID, and aggregation roll-ups. This justifies every subsequent decision and shows you design data models around usage, not intuition.
Choose a Partitioning Strategy
Propose range partitioning on a time column (e.g., event_timestamp by month or day) as the primary partition key, since most forensic and analytics queries filter on time windows. Discuss composite partitioning (time + agency_id or device_id) if cardinality and data volume justify it.
Define Clustering and Sort Keys
Within each partition, define a clustering or sort key on the most selective filter column (e.g., device_id or officer_id) to minimize data scanned per query. Explain how this complements partitioning by reducing I/O at the block level.
Layer in Secondary Indexes Selectively
Add secondary indexes only for high-frequency, low-cardinality lookup patterns (e.g., status, incident_type) where full partition scans would be too costly. Acknowledge the write-amplification trade-off and explain when you would avoid indexes in favor of materialized views or pre-aggregated tables.
Address Maintenance and Evolution
Discuss partition management over time — archiving cold partitions, handling partition skew if one agency generates disproportionate data, and how the strategy evolves as query patterns change. This signals operational maturity beyond initial design.
Key Points to Mention
Type 2 SCD for users and items where history matters, type 1 for stuff like item price if you only care about current state.
Suggested Approach
Start by briefly explaining the concept of Slowly Changing Dimensions (SCDs) and why they matter for historical accuracy, then walk through the specific SCD types best suited for users, items, and geography with concrete justifications. Tie it together by recommending an overall dimensional modeling approach (e.g., Kimball star schema) and explain how it aligns with Axon's likely analytical and operational needs.
Define SCDs and Their Relevance
Briefly explain that SCDs handle attributes that change over time and that choosing the right type ensures historical accuracy for reporting and analytics. Anchor this to why it matters at Axon — e.g., tracking device ownership, user role changes, or jurisdiction boundaries for law enforcement data.
Model the User Dimension
Recommend SCD Type 2 for users, as attributes like role, department, or account status change and historical context is critical for auditing and compliance. Explain the use of surrogate keys, effective_start_date, effective_end_date, and an is_current flag to manage row versioning.
Model the Item Dimension
Suggest SCD Type 1 or Type 2 depending on the item attribute — overwrite non-critical attributes (e.g., description) with Type 1, but use Type 2 for attributes like price, category, or ownership that affect historical fact accuracy. Mention hybrid approaches (SCD Type 3 for limited history) if only one prior value is needed.
Model the Geography Dimension
Recommend SCD Type 2 for geography since jurisdictional boundaries, district assignments, or region classifications can change and must be preserved for historical reporting. Note that geography dimensions are often slowly changing but high-impact when they do change, making full versioning essential.
Recommend an Overall Dimensional Modeling Approach
Advocate for the Kimball star schema as the primary approach — fact tables surrounded by conformed dimensions — for its query simplicity, BI tool compatibility, and scalability. Mention conformed dimensions across subject areas (users, devices, incidents) to enable cross-functional analysis, and optionally note where a Data Vault could complement for raw ingestion before serving the star schema.
Key Points to Mention
Streaming via a message queue into a processing layer for low-latency use cases like the top-3 orders query.
Suggested Approach
Start by clarifying the data characteristics and business requirements (volume, latency SLAs, query patterns) before proposing an architecture, since these constraints directly drive the batch vs. streaming decision. Structure your answer around a layered pipeline—ingestion, processing, storage, and serving—and explicitly articulate the trade-offs at each layer in terms of cost, complexity, and scalability.
Clarify Requirements & Constraints
Ask about data volume (GB/day vs. TB/day), acceptable latency (seconds vs. hours), downstream consumers, and budget envelope. This scoping prevents over-engineering and shows structured thinking.
Design the Ingestion Layer
Propose a durable, scalable message bus (e.g., Kafka or Kinesis) as the entry point to decouple producers from consumers and handle bursty traffic. Discuss partitioning strategy, retention policies, and back-pressure handling.
Evaluate Batch vs. Streaming Processing
Walk through the trade-off matrix: streaming (Flink, Spark Structured Streaming) delivers low latency but higher operational cost and complexity, while batch (Spark, dbt on S3/data lake) is cheaper and simpler for non-time-sensitive analytics. Recommend a Lambda or Kappa architecture depending on whether both views are needed.
Define Storage & Serving Strategy
Choose storage tiers aligned to access patterns—hot storage (e.g., DynamoDB, Cassandra) for real-time lookups, warm/cold storage (S3 + Parquet + columnar query engine like Athena or Redshift) for historical analytics. Highlight partitioning and compaction to control cost at scale.
Address Scaling, Cost, and Operational Concerns
Discuss auto-scaling consumers, spot/preemptible instances for batch jobs to cut costs, idempotent processing for exactly-once semantics, and observability (dead-letter queues, lag monitoring, data quality checks). Mention how you'd iterate from a simpler batch-first design toward streaming only when latency demands justify the cost.
Key Points to Mention
Discussion(7)
Sign in to join the discussion.
Your lambda architecture take is exactly right and I'd have said the same thing. The operational cost of maintaining two separate codebases that need to produce consistent results is brutal in practice, and most teams underestimate how often the batch and streaming layers quietly drift apart. Kappa architecture (just streaming, replay for historical) is usually the cleaner answer unless you have a genuinely compelling reason to run both.
The batch vs streaming split you described is solid. For something like a top-3 orders query, the latency requirement basically forces your hand toward streaming, but for anything aggregated over a day or longer, running Spark jobs on a schedule is so much cheaper and easier to reason about than keeping a stream processor warm around the clock. One thing that sometimes comes up in these Axon-style design rounds is where exactly you draw that latency boundary. I've been pushed on whether "low latency" means seconds or minutes, because that changes whether you reach for Kafka plus Flink versus something simpler like Kafka plus a consumer that writes directly to a fast OLAP store like Druid or ClickHouse.
Also worth having a position on backpressure and what happens when your processing layer falls behind. Interviewers at this level sometimes probe whether you've actually operated one of these pipelines or just read about them.
Over-partitioning in columnar stores is worth flagging even briefly. In BigQuery for example, having too many small partitions can actually hurt performance because the query planner has to open a lot of files. The sweet spot is usually coarse partitioning on date or month, then clustering on the fields you filter on most. Your instinct to keep the top-3 orders as a separate pre-aggregated structure is correct and I'd go further: that table should probably be maintained by the streaming pipeline directly, not derived from the main fact table on read.
Star schema with Type 2 SCD on users and items is the textbook answer and it's right. The one thing I'd add is that Type 2 gets painful fast if you don't have a clear surrogate key strategy. Natural keys (like user_id or item_id) can't be your dimension primary key once you have multiple versions of the same entity, so you need a surrogate key on the dimension table and your fact table foreign keys point to that surrogate. If you join on the natural key you'll accidentally pull multiple rows per entity and your aggregations will be wrong in ways that are hard to debug. Seen that bite a team badly in a data migration context.
The two example queries are doing a lot of work in this prompt and you clearly caught that, which is the right instinct. The region plus calendar month query is basically telling you 'partition on these two axes or your query costs will be embarrassing.' The real-time top-3 orders one is telling you that a full table scan on an orders fact table is never the answer, so you need a pre-aggregated structure that gets updated on every write, not on every read. Where I'd push back slightly on your approach: starting with event schema is defensible, but in a 45-60 minute design round, spending the first 15 minutes on field definitions before touching pipeline or serving layer is a real risk. I made that exact mistake in a similar round and the interviewer had to redirect me. The identity resolution piece you flagged is genuinely hard. The late-event-after-stitch problem is one where I'd just be upfront that you're accepting some inaccuracy unless you build a reprocessing job that re-evaluates stitching decisions periodically, but that adds complexity most teams don't want. Flagging the tradeoff explicitly is usually enough to satisfy the interviewer even if you don't have a clean solution.
Hybrid schema is the right call and arguing for it clearly is probably what saved you there. The failure mode I've seen is people going full JSON blob for 'flexibility' and then discovering that querying inside a JSON column in any columnar warehouse is painful and expensive at scale. Your instinct to normalize the high-cardinality query-critical fields and push the long tail into a metadata column is exactly right. On schema evolution, the answer I'd give is: the JSON column buys you evolution for the overflow stuff, but for the normalized columns you need a migration strategy. In practice that usually means additive changes only (new columns with nulls backfilled), and any breaking change goes through a versioned event_type rather than mutating the existing schema. Something like 'item_viewed_v2' is ugly but it keeps old data readable without a full backfill. Worth having that answer ready because it comes up constantly.
The 'beyond the watermark' question is one I've never had a fully satisfying answer to either. A correction table or a late-events partition is the honest answer, but then you have to explain how downstream consumers know to re-query or re-aggregate. In streaming systems like Flink you can configure allowed lateness and route anything beyond that to a side output, which at least makes the problem visible rather than silently dropping data. The correction then becomes a batch reprocessing job that runs periodically and patches affected partitions. It's operationally messy but it's what actually happens in production. The bloom filter approach for dedup is solid at ingestion scale but worth mentioning that it's probabilistic, so you're trading a small false-positive rate for memory efficiency, and whether that's acceptable depends on how much a duplicate event actually costs you downstream.
The multi-device pre-login case is where client-generated UUIDs per device start to break down and there's no clean deterministic answer. Probabilistic matching using device fingerprint signals (user agent, screen resolution, IP, timezone) can get you maybe 80-90% recall but you're introducing false positives and that has real downstream consequences for analytics accuracy. The more pragmatic answer for most e-commerce contexts is: accept that pre-login cross-device identity is fuzzy, treat each device session as its own identity until a login event creates a known link, and then do a best-effort retroactive stitch on the sessions that share a login within some lookback window. You won't get them all but you'll get the majority and the ones you miss are genuinely ambiguous anyway.