Data Engineering Interview: How do BI, notebooks, and APIs share a governed semantic layer?

Prompt and scope

A company finds that “active customer,” “revenue,” and “retention” use different SQL in different dashboards. Design a semantic layer shared by BI, notebooks, APIs, and future automation agents. It must support dimensions, time grains, filters, permissions, historical versions, and near-real-time data. Explain how to avoid building another untestable reporting platform.

What the interviewer is testing

Separate entities, dimensions, measures, aggregation, and metric semantics before discussing tools. Then handle join grain, duplicate counting, time zones, and late data. Treat a metric as a versioned product contract with definition, owner, source, supported grain, quality state, and compatibility—not as a directory of SQL snippets.

Clarifications before answering

1. What is the fact grain? Mixing orders, order lines, and events can double-count revenue.
2. Which dimensions and time grains are needed? Not every combination is safe; publish a support matrix.
3. What are freshness and consistency targets? Streaming, daily batch, and backfill data have different visibility states.
4. Who may publish definitions and read sensitive dimensions? Metric permissions must not bypass row- or column-level security.
5. Must historical definitions be reproducible, or is only the current definition needed? This determines version routing, snapshots, and recomputation cost.

Recommended design and derivation

Create an immutable metric-definition entity: name, description, measure expression, default aggregation, dimensions, time semantics, filters, source, owner, version, state, and quality SLO. A query references a metric ID, dimensions, and time window; a compiler generates SQL or routes to a pre-aggregated table.

Before compilation, check that join paths are unique, aggregation matches grain, filters can be pushed down, and the user has column access. For distinct counts, ratios, and window metrics, record denominator, deduplication key, and null policy instead of letting each tool guess.

metric: active_customers
version: 3
owner: growth-data
source: mart_customer_daily
measure: count_distinct(customer_id)
dimensions: [plan, region]
time_grain: [day, week, month]
freshness_slo: 2h
status: published

Release in two tracks: compare a new version with the old in shadow queries, then expose it to a small set of workspaces; block promotion when the difference exceeds a threshold. Cache keys must include metric version, dimensions, filters, and data watermark, or a version switch can read an old result. Put budgets, timeouts, and pre-aggregation fallbacks around expensive queries.

Alternatives and trade-offs

Embedding logic in each BI tool ships quickly but forks definitions. One physical curated dataset is simple but cannot express every grain or permission. A central semantic layer gives consistency and reusable APIs at the cost of a compiler, version governance, permission mapping, and debugging. A small team can begin with a few core metrics and one consumer before opening multi-tool access.

Failure modes, boundaries, and counterexamples

• Defining revenue as sum(amount) while ignoring refunds, taxes, currencies, and duplicate order joins.
• Letting a metric read arbitrary raw tables, bypassing quality gates and column permissions.
• Changing a metric’s meaning without a new version, silently changing historical dashboards.
• Treating cache hit rate as correctness while ignoring watermarks, late events, and backfill visibility.
• Generating a Cartesian-product query for every dimension combination; publish unsupported combinations and offer a safer aggregate instead.

Tests and verification checklist

Keep a golden query and small fixed dataset for each metric. Test aggregation, denominator, time zone, nulls, duplicate joins, late events, and version differences. Add schema, permission, compiler, and cache contract tests; compare results and cost against production samples. Release gates should check definition completeness, source freshness, quality SLO, permission mapping, and old-versus-new differences.

Follow-up questions

How do you handle a breaking metric-definition change?

Publish a new version, retain routing for the old version, mark a deprecation date, notify dependents, and remove it only after migration. Historical reports must select the old version for reproducible recomputation; never reuse a version number.

How can the layer serve near-real-time and batch data?

Make source and watermark part of the definition, and return freshness and completeness state with every result. A streaming source may provide a provisional result that batch later reconciles; both paths must share semantics and deduplication rules.

How do you stop natural-language agents from misusing metrics?

Expose only published metrics, supported dimensions, and authorized scopes, returning an explainable query plan and definition version. Reject requests that cannot prove grain, permission, or freshness; do not let an agent freely compose raw-table SQL.