Katkat
Back

Design an Online SQL Query Service over Apache Iceberg Tables

AmperitySoftware Engineer · Onsite
HardSoftware EngineerOnsiteSystem Design

Last updated

Design an online service that runs user-submitted SQL statements against data stored in Apache Iceberg tables. Covers the query lifecycle and API, metadata-driven planning with snapshots and manifests, distributed execution, multi-tenant admission control, commits for writes, and table maintenance.

Design an online service where users submit SQL statements and get results back, with all of the data stored in Apache Iceberg tables. Users work through a web UI or an API; the service runs each statement against the Iceberg tables and returns the result.

Clarifying Questions

  • Is the service read-only (SELECT), or must it also run INSERT, UPDATE, DELETE and MERGE against the tables?
  • Are queries interactive (an analyst waiting at a UI), scheduled batch jobs, or both, and what latency does each expect?
  • Is the service multi-tenant, and must tenants be isolated from each other's load and data?
  • Should we build the query engine, or put a service around an existing distributed engine?
  • How many concurrent queries, how much data per table, and how large can a result be?
  • Which catalog tracks the Iceberg tables, and who else writes to them?

Hints

Hint 1

Metadata before data: An Iceberg table carries its own metadata about which files hold which data. Think about how much of a query's work can be decided from that metadata before a single data file is opened.

Hint 2

Queries outlive requests: Some statements finish in a second, others run for many minutes or return very large results. Think about what the client holds on to while a statement runs, and where its results live.

Follow-up questions

  • A table receives small streaming appends every minute, and its queries get slower each week. Why, and what do you do?
  • How would you support time-travel queries against an older snapshot, and what does keeping old snapshots cost?
  • How would you cache query results so that a cached answer is never stale?
  • Two users run `MERGE` against the same table at the same moment. What happens?