Axon·Software Engineer·Onsite - System Design / Architecture·Senior

SeniorPrefer not to say

May 2026

Summary

System design round at Axon for a software engineer role. One meaty question about building an analytical database for user event tracking, with a focus on schema design and query performance. Not the most grueling interview but it required more thought than I expected going in.

Questions Asked (1)

Design an analytical database to track user actions like search, view item, login, add to cart, purchase, and view order. The system needs to efficiently answer pre-written queries like 'how many users from Asia viewed item A this month?' or 'what are the three highest-value orders right now?' Walk through the schema, event model, and data flow.

System DesignData ModelingTechnical Trade-offs

Author's notes

I started with a flat event table and the interviewer pushed back pretty fast.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying requirements and access patterns, then propose a columnar analytical database (e.g., Redshift, BigQuery, ClickHouse) with an event-based schema. Walk through the data flow from ingestion to query, highlighting partitioning, sorting, and pre-aggregation to meet performance needs.

Pro tip: Emphasize that the schema should be optimized for the known queries, not for transactional writes—this shows you understand the difference between OLTP and OLAP. Also, mention that you'd validate the design with a cost model and consider materialized views for the top queries.

1. Clarify Requirements and Access Patterns

Ask about data volume, query frequency, latency requirements, and whether the queries are truly pre-written or ad-hoc. Confirm the need for near real-time vs. batch updates.

2. Design the Event Schema

Propose a fact table with one row per event, including user_id, event_type, timestamp, and a JSON payload for event-specific properties. Add dimension tables for users, items, and orders.

3. Choose Storage and Partitioning Strategy

Select a columnar store and partition by date (e.g., day) and cluster by user_id or item_id. Explain how this speeds up time-range and entity-specific queries.

4. Outline Data Flow and Ingestion

Describe how events are collected (e.g., via Kafka), processed (e.g., Flink/Spark), and loaded into the analytical store. Mention batch vs. streaming trade-offs.

5. Optimize for Pre-written Queries

Propose pre-aggregated tables or materialized views for common queries (e.g., daily user views per item per region). Discuss indexing and query patterns.

Key Points to Mention

Use a columnar database for analytical workloads (e.g., Redshift, BigQuery, ClickHouse).
Event table with flexible schema (JSON) to accommodate different event types.
Partitioning by date and clustering by high-cardinality columns (user_id, item_id) for efficient filtering.
Pre-aggregation or materialized views to answer frequent queries quickly.
Data flow: ingestion (Kafka) -> processing (Flink/Spark) -> storage (columnar DB).
Trade-offs: storage cost vs. query performance, real-time vs. batch, normalization vs. denormalization.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.