← TikTok Interview Insights

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

Senior
May 2026

Summary

TikTok data engineering interview with a single deep-dive design question covering basically every corner of data warehouse architecture at once. No SQL required but the scope was massive and I felt like I was juggling five problems simultaneously.

Questions Asked (1)

Q1

Given an e-commerce marketplace with buyers, sellers, orders, order items, payments, and page view events, design a data warehouse model to support: daily session-to-purchase conversion rate, average order value, 7-day buyer retention by signup cohort, cancellation rate by seller, and daily GMV by category. Cover fact and dimension tables, grain, key columns, data types, partitioning and clustering, late-arriving events, deduplication, anonymous users, and slowly changing seller attributes. Justify your schema style and surrogate key choices.

Data ModelingSystem DesignTechnical Trade-offs
Author's notes

This was a lot to hold in your head at once.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the business metrics and their required grains, then design a star schema with fact tables at the appropriate grains (e.g., order items for GMV, sessions for conversion) and conformed dimensions. Address data quality issues like late-arriving events, deduplication, and anonymous users by incorporating them into the ETL design and surrogate key strategy. Justify your choices by balancing query performance, storage, and maintainability.

Pro tip: Emphasize how you handle slowly changing dimensions (SCD) for sellers, especially Type 2, to track historical attribute changes and ensure accurate seller-level metrics over time. Also, mention partitioning by date and clustering by high-cardinality columns like category or seller_id to optimize query performance.

1. Clarify Metrics and Grain

Identify each metric's calculation and the finest grain needed (e.g., order item for GMV, session for conversion). This determines the fact table design and avoids unnecessary joins.

2. Design Fact Tables

Create fact tables for orders (order_id grain), order items (order_item_id grain), sessions (session_id grain), and page views (event_id grain). Include measures like quantity, price, and flags for cancellations.

3. Design Dimension Tables

Build dimensions for buyer, seller, product, category, date, and session. Use surrogate keys and handle SCD Type 2 for seller attributes to track changes over time.

4. Address Data Quality and Scalability

Implement deduplication using event_id or natural keys, handle late-arriving events with partitioning and incremental loads, and manage anonymous users via a guest buyer dimension or session stitching.

5. Optimize Storage and Query Performance

Partition fact tables by date and cluster by high-cardinality columns (e.g., category, seller_id). Choose surrogate keys (e.g., monotonically increasing integers) for efficiency and join performance.

Key Points to Mention

  • Star schema vs. snowflake schema: justify star schema for simplicity and query performance.
  • Surrogate keys: use integers for dimensions to handle SCD and avoid natural key changes.
  • Partitioning by date and clustering by category/seller_id for efficient filtering and aggregation.
  • Late-arriving events: use event timestamps and incremental processing with watermarks.
  • Deduplication: use unique event IDs or natural keys with dedup logic in ETL.
  • Anonymous users: track via session IDs and map to buyers upon signup or login.

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