Prompt and context
DAU, revenue, and conversion rate disagree across reports. Design a governed semantic layer so analysts, BI, applications, and automated jobs share metric definitions.
Discuss metric contracts, entity grain, dimensions, join graphs, time semantics, versions, permissions, caching, quality tests, and migration. Do not assume dbt or Looker; they are implementation options, not the answer. The inconsistencies are a fictional practice scenario.
What the interviewer is testing
Consistent definitions
Turn a metric name into numerator, denominator, filters, time window, deduplication, and default dimensions instead of another copied SQL query.
Grain and joins
Identify fact-table grain, prevent many-to-many duplication, and reject combinations that are not semantically composable.
Governance and change
Definitions need version control, review, deprecation, and compatibility windows. BI users should not create a second source of truth.
Usability
A semantic layer needs machine-readable contracts and human documentation with owners, examples, and quality status.
Questions to clarify first
- What exactly do DAU, revenue, and conversion mean?
- Do consumers need SQL, an API, a BI explorer, or embedded charts?
- What are source grains and time zones?
- Can near-real-time and final revised values coexist?
- Are permissions at dataset, row, column, or dimension level?
- How long should legacy reports remain compatible?
A 30-second answer
“I would define a versioned contract containing name, description, numerator, denominator, filters, time grain, timezone, entity grain, dimensions, owner, sensitivity, and quality status. The query engine uses a declared join graph and rejects unsafe many-to-many or incompatible-grain paths.
Definitions are reviewed in version control, with an old-version and deprecation window. SQL APIs and BI adapters return the definition version and freshness. Tests cover fixtures, reconciliation, duplicate joins, latency, permissions, and historical regressions.”
Step-by-step deep answer
Step 1: Inventory use cases
List the queries, latency, and precision required by reports, alerts, products, and experiments. Pilot two or three high-value metrics before migrating every SQL file.
Step 2: Write the contract
Record metric name, business meaning, measure, filters, time window, timezone, entity, dimensions, owner, sensitivity, version, and freshness SLO.
Step 3: Model grain and joins
Declare each model’s primary key and grain. Open only joins whose cardinality and aggregation direction are safe; pre-aggregate, bridge, or reject many-to-many paths.
Step 4: Handle time and revisions
Define event time, processing time, timezone, late data, and final revision rules. Near-real-time results should expose freshness and final status.
Step 5: Release and authorize
Store definitions in version control and publish after owner and data-quality review. Authorize datasets, rows, columns, and dimensions; audit access to sensitive metrics. Set a migration deadline for old versions.
Step 6: Test and serve
Use fixtures, reconciliation samples, duplicate-join checks, freshness, null, and distribution tests. Return values through SQL, BI, or embedded APIs together with version, timezone, and quality status.
Model answer
“I would pilot DAU and revenue. Each definition records numerator, denominator, filters, event time, timezone, entity grain, allowed dimensions, owner, sensitivity, version, and freshness. DAU must say whether it deduplicates people or devices; revenue must define recognition, refunds, and taxes.
The layer owns model grain and a join graph. Many-to-many paths are pre-aggregated or rejected. Definitions are reviewed in Git and tested before release; old versions remain available during a migration window, and responses include version and freshness. Permissions cover datasets and dimensions, with access audits.
Validation includes reconciliation samples, duplicate counts, late data, timezone, permissions, freshness, and historical regression. Migrate high-value reports first and explain differences between old and new results rather than rewriting every SQL query at once.”
Common mistakes
- Storing only a metric name without numerator, denominator, and filters.
- Using a wide table to hide different fact grains.
- Allowing arbitrary joins that duplicate facts.
- Mixing event time, processing time, and timezone.
- Omitting versions, owners, deprecation, and migration windows.
- Testing query success without result reconciliation or freshness.
- Building only a BI plug-in without a machine-readable contract.
- Protecting the dashboard but not underlying dimensions or query audit.
Follow-up questions
Follow-up 1: How do you prevent revenue duplication?
Declare the revenue grain and unique key, pre-aggregate to the target entity before joins, reject unsafe many-to-many paths, and reconcile against known totals.
Follow-up 2: Can a definition change break downstream users?
Publish a new version or compatible field, keep the old version until a deadline, include the version in responses, and require owner and consumer approval.
Follow-up 3: How can near-real-time and final values coexist?
Return the value with freshness, watermark, and final status. Consumers choose acceptable latency and revision semantics instead of treating a provisional value as final finance data.
Follow-up 4: How do you authorize metrics?
Combine dataset, row, column, and dimension policies with least privilege. Record the requester, definition version, and export, then audit regularly.
Follow-up 5: How do you prove the layer creates value?
Compare definition conflicts, duplicate SQL, reconciliation differences, query success, freshness, adoption, and incidents before and after migration, while checking that users can explain results.