Start by clarifying the schema and KPI definitions, then design each KPI query as an idempotent INSERT ... ON CONFLICT DO UPDATE into a rollup table keyed by date. Use CTEs to compute daily aggregates from raw tables, and handle follower distribution percentiles with window functions or percentile_cont, ensuring the target date parameter is applied consistently.
Pro tip: Mention that idempotency can be achieved by making the rollup table's primary key (date, metric) and using upserts, and that you'd wrap the ETL in a transaction to avoid partial updates. Also, consider using a staging table or MERGE for complex distributions to simplify reruns.
Confirm the definitions of each KPI (e.g., user growth = new users per day, DAU = distinct active users per day), the grain of rollup tables, and the exact columns in raw tables. Ask about edge cases like time zones and late-arriving data.
Define rollup tables with a composite primary key (e.g., date, metric_name) to enable idempotent upserts. Include columns for the metric value and any dimensions (e.g., follower bucket).
For each KPI, write a query that computes the daily aggregate for the target date using CTEs, then uses INSERT ... ON CONFLICT DO UPDATE to upsert into the rollup table. For follower distribution, compute median and p90 using percentile_cont or window functions.
Wrap all upserts in a single transaction so that reruns either fully succeed or roll back. Use DELETE+INSERT or MERGE if the rollup table has multiple rows per date (e.g., follower buckets).
Test the queries with sample data, including rerunning for the same date to confirm no duplicates. Check performance and consider indexing strategies for large tables.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.