# Design Data Aggregation, Query, and One-Time Secret Systems Address three independent system-design exercises. For each part, establish requirements, propose APIs and data models, trace the critical path, and analyze failures. ### Clarifying Questions to Ask - Which correctness guarantees are mandatory and which results may be delayed? - What is the trust boundary for data sources, query clients, and secret recipients? - Which operations need idempotency or atomic state transitions? - What workload shape determines partitioning and caching? ### Part 1: Aggregate Data from Multiple Sources Design a service that ingests records from several independent pipelines and exposes a unified, queryable view. Sources can retry, arrive out of order, and temporarily stop. #### What This Part Should Cover - Source identity, offsets, schema versions, and idempotent ingestion - Normalization, deduplication, late data, and backfill - Durable buffering, partitioning, and consumer recovery - Completeness indicators and observability ### Part 2: Design a SQL Query Engine Design the high-level components of a SQL query engine, from parsing a query through planning, execution, and returning results. State whether the first version targets one process or distributed workers. #### What This Part Should Cover - Parser, semantic analysis, catalog, logical and physical plans - Cost or rule-based choices and operator execution - Memory limits, spilling, cancellation, and partial failure - Isolation, authorization, and result streaming ### Part 3: Share a Secret That Can Be Read Once A user submits a secret and receives a URL. A recipient who presents that URL may retrieve the secret at most once. Multiple users may store identical secret text, and one user may create several independent shares of the same text. #### What This Part Should Cover - Random unguessable share IDs and independent records - Atomic consume-if-unread semantics under concurrent requests - Expiration, deletion, audit metadata, and minimal logging - Encryption, key management boundaries, and replay resistance ### What a Strong Answer Covers A strong answer treats these as three different systems, names the central invariant in each, and gives enough detail to show how state changes remain correct during retries, concurrency, and component failure. ### Follow-up Questions - How would you backfill one source without duplicating aggregate records? - Which query operators are hardest to distribute? - Can a one-time secret service guarantee that a recipient actually saw the plaintext?