These walkthroughs include schema limitations and operational design because a
correct SELECT is only part of a staff-level answer.
09 · Staff55 min
Attribute incidents to releases
Situation: Engineering leadership wants release-level change-failure metrics without claiming every incident was caused by a deployment.
Question: Attribute each incident to the closest preceding deployment in the same project within 48 hours. Then report attributed incident rate and median time-to-detect by deployer while preserving unattributed incidents for audit.
Read the data at the right grain
Attribution is a nearest-prior temporal join. LATERAL lets each incident perform an indexed top-one search in its own project and time window.
Tables: incidents → deployments → engineers
Construct the query
- Preserve every incident with LEFT JOIN LATERAL.
- Search preceding production deployments within 48 hours and order newest-first.
- Count all production deployments per deployer for the rate denominator.
- Aggregate attributed incidents and median detection delay by deployer.
Show the runnable worked query
WITH attribution AS (
SELECT i.incident_id,
i.detected_at,
candidate.deployment_id,
candidate.deployed_at,
candidate.deployed_by
FROM incidents i
LEFT JOIN LATERAL (
SELECT d.deployment_id, d.deployed_at, d.deployed_by
FROM deployments d
WHERE d.project_id = i.project_id
AND d.environment = 'production'
AND d.deployed_at <= i.detected_at
AND d.deployed_at >= i.detected_at - interval '48 hours'
ORDER BY d.deployed_at DESC, d.deployment_id DESC
LIMIT 1
) AS candidate ON true
), deploy_counts AS (
SELECT deployed_by, count(*) AS deployment_count
FROM deployments
WHERE environment = 'production'
GROUP BY deployed_by
)
SELECT e.handle AS deployer,
dc.deployment_count,
count(a.incident_id) FILTER (WHERE a.deployment_id IS NOT NULL) AS attributed_incidents,
count(a.incident_id) FILTER (WHERE a.deployment_id IS NOT NULL)::numeric
/ nullif(dc.deployment_count, 0) AS incidents_per_deployment,
percentile_cont(0.5) WITHIN GROUP (
ORDER BY extract(epoch FROM a.detected_at - a.deployed_at) / 3600.0
) FILTER (WHERE a.deployment_id IS NOT NULL) AS median_detect_hours
FROM deploy_counts dc
JOIN engineers e ON e.engineer_id = dc.deployed_by
LEFT JOIN attribution a ON a.deployed_by = dc.deployed_by
GROUP BY e.handle, dc.deployment_count
ORDER BY incidents_per_deployment DESC NULLS LAST, e.handle;
How to read the result
The aggregate compares deployers while the attribution CTE remains auditable; incidents with no candidate stay unattributed.
What makes this a senior/staff answer
Temporal proximity is not causality. Validate the heuristic against service/component metadata and report the unattributed population.
Interview traps and follow-ups
- Temporal proximity is attribution, not causation
- Use the total deployment count as the rate denominator
- How would you validate attribution quality?
- What if several services deploy together?
10 · Staff60 min
Point-in-time feature reconstruction
Situation: A reproducibility audit needs the feature state available when each artifact was created.
Question: For each model version and each non-deprecated feature in its project, select the latest successful materialization completed on or before model creation. Surface features with no eligible materialization.
Read the data at the right grain
Point-in-time correctness chooses the latest eligible past row for every model-version-feature pair, while preserving pairs with no eligible history.
Tables: models → model_versions → feature_definitions → feature_materializations
Construct the query
- Expand model versions to features in the same project that were not yet deprecated.
- For each pair, search successful materializations completed by model creation.
- Order eligible rows newest-first and LIMIT 1 in a lateral subquery.
- Use LEFT JOIN so a missing materialization becomes evidence rather than disappearing.
Show the runnable worked query
SELECT mv.model_version_id,
m.model_name,
mv.version_no AS model_version,
f.feature_id,
f.feature_name,
chosen.materialization_id,
chosen.dataset_version_id,
chosen.completed_at,
chosen.materialization_id IS NULL AS missing_at_model_creation
FROM model_versions mv
JOIN models m USING (model_id)
JOIN feature_definitions f
ON f.project_id = m.project_id
AND (f.deprecated_at IS NULL OR f.deprecated_at > mv.created_at)
LEFT JOIN LATERAL (
SELECT fm.materialization_id,
fm.dataset_version_id,
fm.completed_at
FROM feature_materializations fm
WHERE fm.feature_id = f.feature_id
AND fm.status = 'succeeded'
AND fm.completed_at <= mv.created_at
ORDER BY fm.completed_at DESC, fm.materialization_id DESC
LIMIT 1
) AS chosen ON true
ORDER BY mv.model_version_id, f.feature_id;
How to read the result
One row per model-version-feature identifies the reconstructed materialization or explicitly marks it missing.
What makes this a senior/staff answer
The schema does not prove that training actually consumed every selected feature materialization. Immutable training manifests should record exact feature/version IDs.
Interview traps and follow-ups
- A current latest value leaks future data
- LEFT/LATERAL semantics preserve missing features
- What additional lineage key is missing?
- How would you make reconstruction immutable?
11 · Staff50 min
Audit deployment governance
Situation: Compliance asks whether every production artifact had a valid approval before release.
Question: Find production deployments for which no approved production governance decision existed before deployed_at. Then identify why the current approval schema cannot prove which artifact digest was reviewed and propose corrective DDL.
Read the data at the right grain
Compliance is an anti-join: start from production deployments and retain those for which no qualifying prior approval exists.
Tables: deployments → model_versions → governance_approvals
Construct the query
- Join the deployed model version and immutable artifact digest.
- LEFT JOIN only production approvals that are approved and decided before deployment.
- Filter where the qualifying approval remains NULL.
- Explain why approving model_version_id alone does not attest the artifact bytes.
Show the runnable worked query
SELECT d.deployment_id,
p.project_name,
d.model_version_id,
mv.artifact_digest,
d.deployed_at,
ga.approval_id,
ga.decided_at,
CASE
WHEN ga.approval_id IS NULL THEN 'no prior approved production decision'
END AS violation
FROM deployments d
JOIN projects p USING (project_id)
JOIN model_versions mv USING (model_version_id)
LEFT JOIN governance_approvals ga
ON ga.model_version_id = d.model_version_id
AND ga.environment = 'production'
AND ga.decision = 'approved'
AND ga.decided_at <= d.deployed_at
WHERE d.environment = 'production'
AND ga.approval_id IS NULL
ORDER BY d.deployed_at;
-- Production DDL direction: attest the immutable digest, not only the mutable
-- model-version row. Add artifact_digest to an append-only approval_decisions
-- table and enforce a foreign key or trigger-backed deployment policy.
How to read the result
Every returned deployment lacks evidence of a valid prior production decision under the available schema.
What makes this a senior/staff answer
Use append-only decisions that store the reviewed artifact digest and policy version. Add a break-glass path with expiration and mandatory retrospective review.
Interview traps and follow-ups
- A later approval does not make an earlier deployment compliant
- Model version identity is weaker than digest attestation
- Would an approval be mutable?
- How would emergency break-glass releases work?
12 · Staff60 min
Measure drift impact
Situation: Hundreds of drift alerts fire, but on-call needs the few correlated with customer harm.
Question: For drift windows whose score exceeds threshold, compare error rate and MAE inside the window against the immediately preceding equal-length window. Rank features by estimated impact without double-counting prediction traffic when multiple features drift together.
Read the data at the right grain
Each drift signal defines two equal temporal windows. Join only the deployment's events in the combined range, then conditionally aggregate baseline and drift periods.
Tables: drift_signals → prediction_events → deployments
Construct the query
- Filter breached signals and derive window width.
- Range-join events once across prior+current windows.
- Use FILTER to calculate error rate and labeled MAE for each side.
- Subtract baseline from drift metrics and rank impact.
Show the runnable worked query
WITH breached AS (
SELECT ds.*,
ds.window_end - ds.window_start AS window_width
FROM drift_signals ds
WHERE ds.score > ds.threshold
), event_metrics AS (
SELECT b.drift_signal_id,
b.deployment_id,
b.feature_name,
b.score,
b.threshold,
avg((pe.http_status >= 500)::int) FILTER (
WHERE pe.predicted_at >= b.window_start
) AS drift_error_rate,
avg(abs(pe.prediction - pe.ground_truth)) FILTER (
WHERE pe.predicted_at >= b.window_start AND pe.ground_truth IS NOT NULL
) AS drift_mae,
avg((pe.http_status >= 500)::int) FILTER (
WHERE pe.predicted_at < b.window_start
) AS baseline_error_rate,
avg(abs(pe.prediction - pe.ground_truth)) FILTER (
WHERE pe.predicted_at < b.window_start AND pe.ground_truth IS NOT NULL
) AS baseline_mae
FROM breached b
LEFT JOIN prediction_events pe
ON pe.deployment_id = b.deployment_id
AND pe.predicted_at >= b.window_start - b.window_width
AND pe.predicted_at < b.window_end
GROUP BY b.drift_signal_id, b.deployment_id, b.feature_name, b.score, b.threshold
)
SELECT *,
drift_error_rate - baseline_error_rate AS error_rate_delta,
drift_mae - baseline_mae AS mae_delta
FROM event_metrics
ORDER BY greatest(
coalesce(drift_error_rate - baseline_error_rate, 0),
coalesce(drift_mae - baseline_mae, 0)
) DESC, drift_signal_id;
-- Compute the event metrics once per deployment/window before joining feature
-- names when several feature signals share a window; that prevents traffic from
-- being counted multiple times in an endpoint-level impact total.
How to read the result
Each signal gets comparable before/after metrics, with NULL MAE when labels are absent.
What makes this a senior/staff answer
Several features can share one endpoint window. Compute customer-impact metrics once per deployment/window before attaching features, or endpoint totals will multiply traffic. Correlation is not root cause.
Interview traps and follow-ups
- Joining signals directly to events multiplies traffic
- MAE coverage differs between windows
- Can correlation establish root cause?
- How would you control alert multiplicity?
13 · Staff60 min
Multi-window SLO burn rate
Situation: Serving owns a 99.5% success SLO and needs a low-noise page based on fast and slow burn.
Question: Produce one row per production endpoint-hour with one-hour and six-hour error-budget burn rates. Flag only hours where both exceed 14.4x and 6x respectively.
Read the data at the right grain
Burn rate is observed error rate divided by allowed error rate. For a 99.5% SLO the allowed rate is 0.005.
Tables: deployments → prediction_events
Construct the query
- Aggregate production traffic to endpoint-hour first.
- Use RANGE windows for trailing one-hour and six-hour sums.
- Divide errors/requests by 0.005 for each window.
- Page only when both fast and slow thresholds are breached.
Show the runnable worked query
WITH hourly AS (
SELECT d.endpoint_name,
date_trunc('hour', pe.predicted_at) AS hour_start,
count(*) AS requests,
count(*) FILTER (WHERE pe.http_status >= 500) AS errors
FROM prediction_events pe
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
GROUP BY d.endpoint_name, date_trunc('hour', pe.predicted_at)
), burn AS (
SELECT *,
sum(errors) OVER endpoint_1h::numeric
/ nullif(sum(requests) OVER endpoint_1h, 0) / 0.005 AS burn_1h,
sum(errors) OVER endpoint_6h::numeric
/ nullif(sum(requests) OVER endpoint_6h, 0) / 0.005 AS burn_6h
FROM hourly
WINDOW endpoint_1h AS (
PARTITION BY endpoint_name ORDER BY hour_start
RANGE BETWEEN interval '59 minutes' PRECEDING AND CURRENT ROW
),
endpoint_6h AS (
PARTITION BY endpoint_name ORDER BY hour_start
RANGE BETWEEN interval '5 hours 59 minutes' PRECEDING AND CURRENT ROW
)
)
SELECT *, burn_1h > 14.4 AND burn_6h > 6.0 AS should_page
FROM burn
ORDER BY hour_start, endpoint_name;
How to read the result
One row per observed endpoint-hour carries both burn rates and a boolean page decision.
What makes this a senior/staff answer
Generate an hour spine if zero-traffic hours must appear. Add minimum-event policy, ingestion-delay handling, and alert state deduplication.
Interview traps and follow-ups
- Allowed error rate is 0.5%
- Sparse hours and zero traffic need an explicit policy
- Why use two windows?
- How would late events revise a page decision?
14 · Staff55 min
Quality failure blast radius
Situation: A failed dataset check requires identifying every potentially affected production endpoint.
Question: Trace failed quality-check results through dataset versions, model versions, and production deployments. Return the affected endpoint blast radius and propose a generic lineage-edge table that supports arbitrary depth.
Read the data at the right grain
The current schema supplies a direct chain: failed check → dataset version → model version → deployment. Use ordinary joins for the known path; recursion is a schema-design follow-up.
Tables: quality_check_results → dataset_versions → model_versions → deployments
Construct the query
- Start only from failed quality results.
- Join the checked dataset version.
- Find model versions trained from that version.
- Restrict downstream deployments to active production endpoints.
Show the runnable worked query
SELECT qcr.result_id,
qcd.check_name,
dv.dataset_version_id,
mv.model_version_id,
d.deployment_id,
d.endpoint_name
FROM quality_check_results qcr
JOIN quality_check_definitions qcd USING (check_id)
JOIN dataset_versions dv USING (dataset_version_id)
JOIN model_versions mv
ON mv.training_dataset_version_id = dv.dataset_version_id
JOIN deployments d USING (model_version_id)
WHERE NOT qcr.passed
AND d.environment = 'production'
AND d.status = 'active'
ORDER BY qcr.result_id, d.endpoint_name;
-- For arbitrary lineage, model edges as (upstream_type, upstream_id,
-- downstream_type, downstream_id, valid_from, valid_to), then traverse with
-- WITH RECURSIVE while carrying a visited-node array to stop cycles.
How to read the result
Rows preserve the full failed-check-to-endpoint path, from which distinct endpoint blast radius can be derived.
What makes this a senior/staff answer
A generic edge table needs typed nodes, validity, cycle prevention, and immutable evidence. Recursive queries should carry a visited path and a depth guard.
Interview traps and follow-ups
- Current schema supports a direct path, not arbitrary recursion
- Deduplicate endpoints after preserving paths
- How would you handle cycles?
- Where would feature lineage enter the graph?
15 · Staff50 min
Repair a pathological query plan
Situation: A dashboard query scans excessive partitions and returns inflated request counts.
Question: Repair the intentionally poor query in database/exercises.sql: make the timestamp predicate sargable, eliminate drift-signal row multiplication, and propose the smallest useful index. Compare plans with EXPLAIN (ANALYZE, BUFFERS).
Read the data at the right grain
Fix correctness before performance. The original join multiplies every prediction by every drift signal for its deployment and wraps the partition key in date_trunc.
Tables: projects → deployments → prediction_events → drift_signals
Construct the query
- Reduce drift_signals to one deployment key before joining.
- Drive from prediction_events and use half-open raw timestamp predicates.
- Join dimensions only after event grain is stable.
- Compare EXPLAIN ANALYZE buffers and pruned partitions.
Show the runnable worked query
WITH drifted_deployments AS (
SELECT DISTINCT deployment_id
FROM drift_signals
WHERE score > threshold
)
SELECT p.project_name,
count(*) AS requests,
avg(pe.latency_ms) AS avg_latency_ms
FROM prediction_events pe
JOIN drifted_deployments dd USING (deployment_id)
JOIN deployments d USING (deployment_id)
JOIN projects p USING (project_id)
WHERE pe.predicted_at >= timestamptz '2026-07-15 00:00+00'
AND pe.predicted_at < timestamptz '2026-07-16 00:00+00'
GROUP BY p.project_name
ORDER BY p.project_name;
How to read the result
Request counts are no longer inflated, and only the July partition/day range is scanned.
What makes this a senior/staff answer
The existing deployment-time index may be enough. Add an index only after plan evidence; every additional serving index increases ingestion cost.
Interview traps and follow-ups
- Functions on predicted_at defeat pruning
- DISTINCT after a bad join may hide, not fix, incorrectness
- When is a sequential scan correct?
- Why might a new index be rejected?
16 · Staff60 min
Design an idempotent health backfill
Situation: A metric bug requires recomputing a week of serving health while dashboards remain online.
Question: Recompute daily deployment health for 2026-07-01 through 2026-07-07 so retries are safe and readers never observe partial results. Defend a table, materialized-view refresh, or partition-swap design.
Read the data at the right grain
Separate compute from publish. A deterministic recomputation SELECT creates the desired state; a transaction or partition swap makes publication atomic and retry-safe.
Tables: prediction_events → daily_deployment_health
Construct the query
- Use half-open July 1–8 bounds so all seven days are included once.
- Aggregate at the target key deployment_id + day.
- Load into run-scoped staging and validate counts/checksums.
- Publish with one transaction and idempotent ON CONFLICT semantics.
Show the runnable worked query
WITH recomputed AS (
SELECT deployment_id,
predicted_at::date AS day,
count(*) AS request_count,
count(*) FILTER (WHERE http_status >= 500) AS error_count,
percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_latency_ms,
avg(abs(prediction - ground_truth))
FILTER (WHERE ground_truth IS NOT NULL) AS mae
FROM prediction_events
WHERE predicted_at >= timestamptz '2026-07-01 00:00+00'
AND predicted_at < timestamptz '2026-07-08 00:00+00'
GROUP BY deployment_id, predicted_at::date
)
SELECT *
FROM recomputed
ORDER BY day, deployment_id;
-- Production pattern: load recomputed rows into a run-scoped staging table,
-- validate counts and checksums, then publish in one transaction with
-- INSERT ... ON CONFLICT (deployment_id, day) DO UPDATE. A retry uses the same
-- deterministic key and readers never observe the staging table.
How to read the result
The shown query produces the replacement rows without mutating the materialized view.
What makes this a senior/staff answer
The current target is a materialized view, so choose full concurrent refresh or introduce a table designed for partial upsert. Never expose DELETE and INSERT as separate commits.
Interview traps and follow-ups
- The existing materialized view cannot be partially refreshed
- DELETE then INSERT is unsafe without one transaction
- How do you bound locks?
- How do you verify before publishing?
20 · Staff55 min
Subgroup quality guardrail
Situation: A model promotion requires evidence that quality is not materially worse for a device or country subgroup.
Question: For each production deployment, compare subgroup MAE by country and device family with its overall MAE. Flag a subgroup only when degradation exceeds 15%, label coverage is at least 60%, and sample size is at least 100.
Read the data at the right grain
Compute the overall baseline directly from events, not as an average of subgroup averages. Then compare each subgroup with adequate evidence.
Tables: prediction_events → deployments
Construct the query
- Create one event-level absolute-error relation.
- Aggregate overall MAE per deployment.
- Aggregate requests, labels, and MAE per country-device subgroup.
- Join and apply degradation, coverage, and labeled-sample gates together.
Show the runnable worked query
WITH base AS (
SELECT pe.deployment_id,
pe.country_code,
pe.device_family,
abs(pe.prediction - pe.ground_truth) AS absolute_error,
pe.ground_truth IS NOT NULL AS is_labeled
FROM prediction_events pe
JOIN deployments d USING (deployment_id)
WHERE d.environment = 'production'
), overall AS (
SELECT deployment_id,
avg(absolute_error) FILTER (WHERE is_labeled) AS overall_mae
FROM base
GROUP BY deployment_id
), subgroup AS (
SELECT deployment_id,
country_code,
device_family,
count(*) AS requests,
count(*) FILTER (WHERE is_labeled) AS labeled_samples,
avg(absolute_error) FILTER (WHERE is_labeled) AS subgroup_mae
FROM base
GROUP BY deployment_id, country_code, device_family
)
SELECT s.*,
o.overall_mae,
s.labeled_samples::numeric / nullif(s.requests, 0) AS label_coverage,
s.subgroup_mae / nullif(o.overall_mae, 0) - 1 AS relative_degradation,
s.subgroup_mae > o.overall_mae * 1.15
AND s.labeled_samples::numeric / nullif(s.requests, 0) >= 0.60
AND s.labeled_samples >= 100 AS guardrail_failed
FROM subgroup s
JOIN overall o USING (deployment_id)
ORDER BY guardrail_failed DESC, relative_degradation DESC NULLS LAST;
How to read the result
Each subgroup shows its baseline, relative degradation, evidence size, and guardrail decision.
What makes this a senior/staff answer
Add uncertainty and multiple-comparison control. Missing labels can differ by subgroup, and whether country is an allowed governance dimension is a policy decision.
Interview traps and follow-ups
- Overall baseline must not be an unweighted average of subgroup MAEs
- Missing labels may be non-random
- How would you correct for multiple testing?
- Is country a permissible governance dimension?
21 · Staff50 min
Safe data-retention deletion plan
Situation: Dataset retention policies conflict with reproducibility requirements for deployed models.
Question: Identify dataset versions past retention as of 2026-08-17, classify which are still referenced by model versions or feature materializations, and produce a deletion plan that preserves auditability.
Read the data at the right grain
Retention eligibility is not deletion safety. First calculate age-based candidates, then independently classify every form of live lineage reference.
Tables: datasets → dataset_versions → model_versions → feature_materializations → deployments
Construct the query
- Compute expires_at from version creation plus dataset retention_days.
- Filter against the explicit 2026-08-17 as-of time.
- Use EXISTS for feature, model, and live-deployment references without multiplying rows.
- Map reference combinations to an ordered disposition.
Show the runnable worked query
WITH candidates AS (
SELECT dv.dataset_version_id,
ds.dataset_name,
dv.created_at,
ds.retention_days,
dv.created_at + ds.retention_days * interval '1 day' AS expires_at
FROM dataset_versions dv
JOIN datasets ds USING (dataset_id)
WHERE dv.created_at + ds.retention_days * interval '1 day'
< timestamptz '2026-08-17 00:00+00'
), usage_refs AS (
SELECT c.*,
EXISTS (
SELECT 1 FROM feature_materializations fm
WHERE fm.dataset_version_id = c.dataset_version_id
) AS used_by_feature,
EXISTS (
SELECT 1 FROM model_versions mv
WHERE mv.training_dataset_version_id = c.dataset_version_id
) AS used_by_model,
EXISTS (
SELECT 1
FROM model_versions mv
JOIN deployments d USING (model_version_id)
WHERE mv.training_dataset_version_id = c.dataset_version_id
AND d.environment = 'production'
AND d.status = 'active'
) AS used_by_live_deployment
FROM candidates c
)
SELECT *,
CASE
WHEN used_by_live_deployment THEN 'retain: live production dependency'
WHEN used_by_model OR used_by_feature THEN 'archive bytes; retain metadata and lineage'
ELSE 'eligible for deletion after legal-hold check'
END AS disposition
FROM usage_refs
ORDER BY used_by_live_deployment DESC, used_by_model DESC, expires_at;
How to read the result
Every expired version is classified as retain, archive-with-lineage, or deletion candidate.
What makes this a senior/staff answer
Keep metadata after byte deletion, check legal holds, make object-store deletion asynchronous and auditable, and prove deployed artifacts remain reproducible.
Interview traps and follow-ups
- Retention begins from a clearly chosen timestamp
- A foreign key prevents deletion but does not define archival policy
- Would you retain metadata after deleting bytes?
- How do legal holds override retention?
22 · Staff55 min
Deduplicate serving events safely
Situation: Client retries can duplicate request IDs and inflate both SLO and quality metrics.
Question: Audit prediction_events for duplicate logical requests, define the correct uniqueness scope, select deterministic survivors, and design an online constraint migration that works with monthly partitioning.
Read the data at the right grain
Define the logical request key before deduplicating. In this dataset deployment_id + request_id finds the 50 seeded duplicate groups.
Tables: prediction_events
Construct the query
- Group by the proposed uniqueness scope and HAVING count > 1.
- Choose a deterministic survivor such as minimum prediction_id.
- Measure first/last occurrence to understand late duplicates.
- Separate cleanup SQL from the online uniqueness design.
Show the runnable worked query
WITH duplicates AS (
SELECT deployment_id,
request_id,
count(*) AS copies,
min(prediction_id) AS survivor_prediction_id,
min(predicted_at) AS first_seen,
max(predicted_at) AS last_seen
FROM prediction_events
GROUP BY deployment_id, request_id
HAVING count(*) > 1
)
SELECT *
FROM duplicates
ORDER BY copies DESC, deployment_id, request_id;
-- PostgreSQL 14 requires a partitioned unique constraint to contain the
-- partition key. A strict (deployment_id, request_id) key therefore needs a
-- non-partitioned request-id registry, hash partitioning by the logical key, or
-- application idempotency; merely adding predicted_at weakens the guarantee.
How to read the result
The audit returns each duplicate group, copy count, survivor, and occurrence span.
What makes this a senior/staff answer
PostgreSQL 14 partitioned uniqueness must include predicted_at, which weakens cross-partition request uniqueness. Use a request registry, compatible hash partitioning, or producer idempotency.
Interview traps and follow-ups
- request_id may only be unique within an endpoint or tenant
- A partitioned unique constraint must include the partition key in PostgreSQL 14
- How would producers become idempotent?
- What happens to late cross-partition duplicates?
24 · Staff60 min
Reconstruct the production control plane
Situation: During a postmortem, responders need to know exactly what model, traffic, config, and approval state existed at an arbitrary timestamp.
Question: Return a production control-plane snapshot as of 2026-07-15 12:00 UTC: endpoint, model artifact, effective traffic, deployment config, approval state, and open incidents. Then identify which fields cannot be reconstructed reliably with the current schema.
Read the data at the right grain
Build an as-of base deployment set, then independently as-of join traffic and approval state. Aggregate incidents laterally so one-to-many incidents do not duplicate the snapshot row.
Tables: deployments → deployment_traffic → model_versions → governance_approvals → incidents
Construct the query
- Select production deployments whose observed lifetime covers the timestamp.
- Join immutable artifact identity.
- Lateral-select latest traffic and approval facts at or before the timestamp.
- Lateral-aggregate incidents open at that instant.
Show the runnable worked query
WITH as_of_deployments AS (
SELECT d.*
FROM deployments d
WHERE d.environment = 'production'
AND d.deployed_at <= timestamptz '2026-07-15 12:00+00'
AND (d.retired_at IS NULL OR d.retired_at > timestamptz '2026-07-15 12:00+00')
)
SELECT p.project_name,
d.endpoint_name,
d.deployment_id,
mv.artifact_digest,
traffic.traffic_percent,
traffic.effective_at AS traffic_effective_at,
d.config,
approval.decision AS approval_state,
approval.decided_at,
incident.open_incident_ids
FROM as_of_deployments d
JOIN projects p USING (project_id)
JOIN model_versions mv USING (model_version_id)
LEFT JOIN LATERAL (
SELECT dt.traffic_percent, dt.effective_at
FROM deployment_traffic dt
WHERE dt.deployment_id = d.deployment_id
AND dt.effective_at <= timestamptz '2026-07-15 12:00+00'
ORDER BY dt.effective_at DESC
LIMIT 1
) AS traffic ON true
LEFT JOIN LATERAL (
SELECT ga.decision, ga.decided_at
FROM governance_approvals ga
WHERE ga.model_version_id = d.model_version_id
AND ga.environment = 'production'
AND ga.decided_at <= timestamptz '2026-07-15 12:00+00'
ORDER BY ga.decided_at DESC
LIMIT 1
) AS approval ON true
LEFT JOIN LATERAL (
SELECT array_agg(i.incident_id ORDER BY i.detected_at) AS open_incident_ids
FROM incidents i
WHERE i.project_id = d.project_id
AND i.detected_at <= timestamptz '2026-07-15 12:00+00'
AND (i.resolved_at IS NULL OR i.resolved_at > timestamptz '2026-07-15 12:00+00')
) AS incident ON true
ORDER BY p.project_name, d.endpoint_name;
-- Current deployment status and config are not bitemporal. retired_at helps,
-- but arbitrary status/config history cannot be proven without valid-time and
-- transaction-time history tables.
How to read the result
One snapshot row per historical production deployment includes traffic, artifact, config, approval, and open incident IDs.
What makes this a senior/staff answer
Current status and config are overwritten facts, so parts of this answer are inference. Use valid-time plus transaction-time control-plane histories and immutable decision events.
Interview traps and follow-ups
- Current rows are not necessarily historical truth
- Approval and config changes need valid-time history
- Differentiate event time, valid time, and transaction time
- How would you test snapshot completeness?
30 · Staff50 min
Dataset schema blast radius
Situation: A source schema changed and platform owners need the affected artifact and endpoint set.
Question: Detect schema_hash changes and list model versions and active production endpoints trained from changed versions.
Read the data at the right grain
Detect changes within each dataset's ordered version history, then traverse optional lineage outward with LEFT JOINs.
Tables: dataset_versions → datasets → model_versions → models → deployments
Construct the query
- LAG schema_hash by dataset version.
- Keep non-initial changed hashes.
- Join models trained from the changed version.
- Restrict endpoint impact to active production while preserving unconsumed changes.
Show the runnable worked query
WITH version_history AS (
SELECT dv.*,
lag(dv.schema_hash) OVER (
PARTITION BY dv.dataset_id
ORDER BY dv.version_no, dv.dataset_version_id
) AS previous_schema_hash
FROM dataset_versions dv
), schema_changes AS (
SELECT *
FROM version_history
WHERE previous_schema_hash IS NOT NULL
AND schema_hash <> previous_schema_hash
)
SELECT ds.dataset_name,
sc.version_no AS changed_version,
sc.previous_schema_hash,
sc.schema_hash,
mv.model_version_id,
m.model_name,
d.deployment_id,
d.endpoint_name
FROM schema_changes sc
JOIN datasets ds USING (dataset_id)
LEFT JOIN model_versions mv
ON mv.training_dataset_version_id = sc.dataset_version_id
LEFT JOIN models m USING (model_id)
LEFT JOIN deployments d
ON d.model_version_id = mv.model_version_id
AND d.environment = 'production'
AND d.status = 'active'
ORDER BY ds.dataset_name, sc.version_no, mv.model_version_id;
How to read the result
The output separates schema changes from the subset with registered or currently serving impact.
What makes this a senior/staff answer
Store structured schemas and compatibility results, not only hashes. Trigger contract checks before training and deployment rather than discovering blast radius afterward.
Interview traps and follow-ups
- Partition LAG by dataset
- Preserve changes with no downstream model
- A hash says changed, not compatible—what is missing?
- Where does feature lineage enter?
31 · Staff55 min
Deduplicate drift alert episodes
Situation: Weekly drift signals for several features generate a storm of overlapping pages.
Question: Merge overlapping breached windows per deployment and summarize each alert episode.
Read the data at the right grain
Interval islands start only when the next start is beyond every earlier end in the current partition. A running maximum handles nested overlaps.
Tables: drift_signals
Construct the query
- Filter breached signals.
- Compute the maximum prior window_end per deployment.
- Mark a new island when window_start exceeds that maximum.
- Cumulatively number and aggregate episodes.
Show the runnable worked query
WITH breached AS (
SELECT ds.*,
max(ds.window_end) OVER (
PARTITION BY ds.deployment_id
ORDER BY ds.window_start, ds.window_end, ds.drift_signal_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS previous_max_end
FROM drift_signals ds
WHERE ds.score > ds.threshold
), marked AS (
SELECT *,
(previous_max_end IS NULL OR window_start > previous_max_end)::int AS starts_episode
FROM breached
), numbered AS (
SELECT *,
sum(starts_episode) OVER (
PARTITION BY deployment_id
ORDER BY window_start, window_end, drift_signal_id
) AS episode_id
FROM marked
)
SELECT deployment_id,
episode_id,
min(window_start) AS episode_start,
max(window_end) AS episode_end,
max(window_end) - min(window_start) AS duration,
count(*) AS signal_count,
count(DISTINCT feature_name) AS feature_count,
max(score / nullif(threshold, 0)) AS max_threshold_ratio
FROM numbered
GROUP BY deployment_id, episode_id
ORDER BY episode_start, deployment_id;
How to read the result
Many feature-level signals collapse into fewer operational episodes with severity and breadth context.
What makes this a senior/staff answer
Persist alert fingerprints and state transitions; SQL dedup alone does not implement acknowledgement, cooldown, escalation, or late-signal behavior.
Interview traps and follow-ups
- Compare with the running maximum end, not only the immediately previous row
- Do not merge across deployments
- Would touching intervals merge?
- How do you reopen an episode?
32 · Staff60 min
Rollback candidate and evidence
Situation: An incident commander needs a fast but defensible rollback option for each live endpoint.
Question: Find the closest prior production deployment, verify approval, and compare seven-day health evidence.
Read the data at the right grain
Candidate selection, compliance evidence, and health evidence are independent lateral lookups joined back to the active deployment.
Tables: deployments → governance_approvals → prediction_events → projects
Construct the query
- Anchor active production deployments.
- Lateral-select the closest earlier release in the same project.
- Use EXISTS for a prior approval without multiplying rows.
- Aggregate bounded current and historical health windows separately.
Show the runnable worked query
WITH active AS (
SELECT d.*
FROM deployments d
WHERE d.environment = 'production' AND d.status = 'active'
), candidates AS (
SELECT cur.*,
previous.deployment_id AS rollback_deployment_id,
previous.model_version_id AS rollback_model_version_id
FROM active cur
LEFT JOIN LATERAL (
SELECT d.deployment_id, d.model_version_id
FROM deployments d
WHERE d.project_id = cur.project_id
AND d.environment = 'production'
AND d.deployed_at < cur.deployed_at
ORDER BY d.deployed_at DESC, d.deployment_id DESC
LIMIT 1
) AS previous ON true
), evidence AS (
SELECT c.*,
EXISTS (
SELECT 1
FROM governance_approvals ga
WHERE ga.model_version_id = c.rollback_model_version_id
AND ga.environment = 'production'
AND ga.decision = 'approved'
AND ga.decided_at <= c.deployed_at
) AS rollback_was_approved,
current_health.error_rate AS current_error_rate,
current_health.p95_latency_ms AS current_p95_latency_ms,
previous_health.error_rate AS previous_error_rate,
previous_health.p95_latency_ms AS previous_p95_latency_ms
FROM candidates c
LEFT JOIN LATERAL (
SELECT avg((pe.http_status >= 500)::int) AS error_rate,
percentile_cont(0.95) WITHIN GROUP (ORDER BY pe.latency_ms) AS p95_latency_ms
FROM prediction_events pe
WHERE pe.deployment_id = c.deployment_id
AND pe.predicted_at >= timestamptz '2026-08-10 00:00+00'
AND pe.predicted_at < timestamptz '2026-08-17 00:00+00'
) AS current_health ON true
LEFT JOIN LATERAL (
SELECT avg((pe.http_status >= 500)::int) AS error_rate,
percentile_cont(0.95) WITHIN GROUP (ORDER BY pe.latency_ms) AS p95_latency_ms
FROM prediction_events pe
WHERE pe.deployment_id = c.rollback_deployment_id
AND pe.predicted_at >= c.deployed_at - interval '7 days'
AND pe.predicted_at < c.deployed_at
) AS previous_health ON true
)
SELECT p.project_name,
e.deployment_id,
e.rollback_deployment_id,
e.rollback_was_approved,
e.current_error_rate,
e.previous_error_rate,
e.current_p95_latency_ms,
e.previous_p95_latency_ms,
e.rollback_was_approved
AND e.previous_error_rate IS NOT NULL
AND e.previous_p95_latency_ms IS NOT NULL AS has_minimum_rollback_evidence
FROM evidence e
JOIN projects p USING (project_id)
ORDER BY p.project_name;
How to read the result
Each endpoint gets a candidate and an explicit minimum-evidence flag rather than an unconditional rollback recommendation.
What makes this a senior/staff answer
A real rollback gate also verifies artifact availability, feature/schema compatibility, config, capacity, security revocation, and a rehearsed traffic-switch procedure.
Interview traps and follow-ups
- Previous does not mean safe
- Historical traffic cohorts may differ
- What if the prior artifact is incompatible with today's features?
- How do you precompute rollback readiness?