Design a Centralized Metrics Layer

WHAT THIS TESTS: Your understanding of data architecture beyond simple ETL. It assesses your ability to design a system that enforces consistency, promotes data governance, and scales organizationally. The interviewer wants to see if you can separate business logic from raw data storage and visualization, creating a 'single source of truth' for key business metrics.

A GOOD ANSWER COVERS: Four key components of a metrics layer, which sits between the data warehouse (like Snowflake or BigQuery) and consumption tools (like Tableau or Looker). First, a definition layer, where metrics are defined as code (e.g., in YAML or Python) and managed in Git. This makes changes reviewable and auditable. Second, a centralized metric catalog or registry that serves as a discoverable inventory of all metrics. Third, a query generation engine that translates a request for a metric (e.g., 'MAU') into the correct, optimized SQL for the underlying warehouse. Fourth, consumption interfaces, like REST/GraphQL APIs or JDBC/ODBC connectors, allowing any tool to query metrics by name, not by writing raw SQL.

COMMON WRONG ANSWERS: Proposing to build a single 'golden dashboard.' This just moves the problem; it doesn't solve the underlying inconsistency in the logic used to populate that dashboard. Another red flag is suggesting only to create a new, pre-aggregated table (a data mart). This is inflexible to new dimensions (e.g., 'show me MAU by country') and doesn't create a reusable definition. Finally, just naming a tool like dbt or Looker without explaining the architectural principles is a weak answer. The key is the decoupling of the metric definition from its consumption.

LIKELY FOLLOW-UPS: How would you handle slowly changing dimensions, like a user changing their country? How would you manage backfills if a metric definition changes? How would you drive adoption of this new layer across different teams? What's the performance trade-off of generating queries on the fly versus pre-calculating everything?

ONE CONCRETE EXAMPLE: To define a 'Monthly Active User' (MAU), a team would commit a YAML file to a Git repository. It might look like: metric: mau; type: count_distinct; sql: user_id; filters: [event_timestamp > now() - 30d]; dimensions: [country, device_type]. When a dashboard requests 'MAU by country,' it calls the metrics layer API. The layer finds the mau definition, generates the SQL SELECT country, COUNT(DISTINCT user_id) FROM events WHERE event_timestamp > NOW() - INTERVAL '30 day' GROUP BY 1;, runs it against the warehouse, and returns the result. The dashboard never contains any SQL.