Governed Data Service for Downstream Consumers

0) Problem Restatement

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.

1) Requirements

• 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.

2) Architecture

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 --> EXP

3) Key Design Points

• Contracts in a catalog: each dataset has a versioned schema, field descriptions, owner, freshness SLA and allowed filters. /v1/datasets/creator_daily_stats has a fixed shape. Breaking changes create v2, and v1 is supported for a deprecation period.
• Serving store, not the raw warehouse: publish curated tables into a store built for fast filtered reads (e.g., an OLAP store like ClickHouse or Pinot, or a key-value store for point lookups), refreshed on schedule. Interactive API calls hit this, not the heavy warehouse.
• Access policies: authenticate the consumer (a service identity), then apply row-level filters (e.g., advertiser_id IN (allowed list)) and column masking (hide or hash PII) inside the service, so consumers can't bypass them.
• Query shape limits: only allow the filters and sorts the contract supports (indexed ones), require a time range, and cap page sizes. No arbitrary SQL through the API, which protects cost and performance.
• Pagination: cursor-based (next_cursor encodes the last key), stable while data is refreshed (use a snapshot/version ID within one pagination session).
• Bulk export: for millions of rows, an async export job writes files (Parquet/CSV) to object storage and returns a short-lived download link. Much cheaper than paging.
• Freshness: every response includes data_as_of and dataset_version.
• Caching: cache popular queries (same dataset, filters and version) and invalidate on new publish.

4) 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).

5) Wrap-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.