Problem RestatementProblem
Design a service (asked at TikTok) that exposes governed data from the warehouse (modeled tables like "creator daily stats" or "ad performance") to reports and applications owned by other teams. Instead of every team writing their own SQL against raw tables, they call a stable API with clear contracts, correct access control, predictable performance, and known freshness.
RequirementsRequirements
- Stable, versioned data contracts (fields, types, meaning).
- Access control: which consumers can see which datasets, rows (e.g., only their region or advertiser) and columns (PII hidden).
- Pagination for interactive use, and bulk export for large pulls.
- Freshness metadata ("data as of 06:00 UTC").
- Protect the warehouse from expensive or runaway queries.
- Observability: who uses what, latency, errors.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
APPS["Consumer apps / reports"] --> GW["API Gateway - auth, quotas"]
GW --> DS["Data Service - contracts, policies"]
DS --> CAT[("Data catalog - contracts, owners, SLAs")]
DS --> POL["Policy engine - row/column rules"]
DS --> CACHE[("Result cache")]
DS --> SRV[("Serving store - materialized tables")]
DS -->|"bulk export"| EXP["Export jobs"]
EXP --> OS[("Object storage files")]
WH[("Warehouse / lakehouse")] -->|"scheduled publish"| SRV
WH --> EXPDeep Dive — Letting other teams use warehouse dataDeep dive
Teams want "creator daily stats" for their own reports and applications. How that access is granted decides whether the warehouse stays usable.
Give teams warehouse credentials
Hand out read access and let each team write its own queries.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
T1["Team A query"] --> WH[("Warehouse")]
T2["Team B dashboard - refreshes every 30 s"] --> WH
T3["Team C application"] --> WH
WH --> SLOW["Interactive apps issue warehouse-scale scans"]
WH --> COL["A column is renamed - every team's query breaks at once"]
WH --> ROW["No row filtering - any team can read any advertiser's data"]Three failures at once: a store built for analytical scans is serving interactive traffic, the physical schema has become everyone's API so no change is safe, and access is all-or-nothing.
Put an API in front of the warehouse
Expose endpoints that run queries on the teams' behalf.
Access control and query shape are now controllable, which is the important step. But the API still executes against the warehouse, so a popular endpoint issues warehouse queries at application traffic rates — slow, expensive, and subject to whatever else the warehouse is doing. Latency is inherited from a system tuned for throughput, not response time.
Contracts, a serving store, and per-consumer filters
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
WH[("Warehouse - modelled tables")] --> PUB["Scheduled publish"]
PUB --> SRV[("Serving store - OLAP or KV, built for filtered reads")]
CAT["Catalog: versioned schema, owner, freshness SLA, allowed filters"] --> API["Data API"]
SRV --> API
API --> AUTH["Authenticate the service identity"]
AUTH --> RLS["Row-level filter - advertiser_id in allowed set"]
RLS --> RESP["Response - plus the data's as-of timestamp"]
CAT --> V2["Breaking change -> v2, v1 supported through a deprecation window"]- The contract is the product, not the table. A versioned schema with descriptions, an owner, a freshness SLA and a declared set of allowed filters means consumers depend on something stable — and a breaking change becomes
v2alongside a supportedv1, instead of an outage. - Serve from a store built for it. Publish curated tables on a schedule into an OLAP store for filtered aggregates or a key-value store for point lookups. Interactive calls never touch the warehouse.
- Filter rows per consumer after authenticating the calling service, so one endpoint safely serves many tenants.
- Return the freshness. Every response says what it is as-of, so a consumer can decide whether an hour-old number is acceptable rather than assuming it is live.
The line to draw: the warehouse is where data is modelled; the serving store is where it is read. Conflating them is how a data platform becomes the thing everyone is waiting on.
Operations and Governance
- Quotas and rate limits per consumer, plus cost tracking per consumer.
- Lineage: record which warehouse tables feed each dataset, so owners know who is affected by upstream changes.
- Monitoring: publish delays vs SLA, error rates, slow queries, and usage per consumer (also used to find unused datasets to retire).
Wrap-UpWrap-up
Put a data service between the warehouse and consumers. Define versioned contracts with owners and freshness SLAs in a catalog, publish curated tables into a fast serving store, enforce consumer-specific row filters and column masking inside the service, allow only contract-approved query shapes with cursor pagination, and use async bulk exports for large pulls. Add caching, quotas, lineage and freshness metadata, so downstream teams get stable, safe and predictable data.