LEARN. PRACTICE. GROW.Contract for the delivered result
A fictional funds support team is preparing two products: a processing-exception dashboard and a procedure search for operators. The dashboard must identify batches still needing intervention. Search must find documents applicable to the operator’s tenant and the current procedure version. A fast response using old data or another tenant’s document can lead to an incorrect action. The consumption contract should therefore identify the population, reference time, authorization and usefulness criteria. Write an acceptance record with the business owner. For the dashboard, record the counting unit, maximum acceptable delay and allowed action when data is late. For search, record the identity used, document origin, version rule and evidence expected in the answer. Also define behavior when no eligible results exist: explaining the absence and escalating the search is preferable to filling the answer with an invalid procedure. These are requirements of this example, not internal rules of a real bank. Keep the acceptance record beside the test evidence so that a later performance improvement cannot silently change the meaning of an accepted result.
Compare execution with execution
A trial report shows 18 seconds for the first run and 0.4 seconds for the second. Before attributing the improvement to a SQL change, inspect cacheHit in the job statistics. If results were reused, the two measurements answered different questions. To compare execution, disable result reuse and keep input data, parameters, identity and capacity conditions consistent. Record cost and result correctness too; lower elapsed time alone is insufficient. In the example, prepare three runs per variant and retain the job identifiers. If timings vary substantially, describe the spread and investigate concurrency before choosing a winner. A dashboard query using CURRENT_TIMESTAMP may not use cached results even without visible data changes. Do not remove the function merely to obtain a better timing if it represents the business-required observation time. To feed a dependent job with shared results, use a destination table with an explicit lifecycle instead of treating an anonymous cache table as a stable interface. The performance report should identify whether it measures fresh computation or repeated consumption.
Refresh, staleness and dashboard meaning
The operations owner proposes refresh_interval_minutes=30 and writes an SLA saying results will always be current within 30 minutes. The option caps automatic refresh frequency; it does not guarantee when each refresh starts or finishes. Distinguish materialized state, query output and source-data time. Without a special staleness allowance, lack of a recent refresh can increase the work needed to return current results. With max_staleness, a direct query of the materialized view accepts staleness within the configured interval. A four-hour allowance does not by itself satisfy a fifteen-minute requirement. Prepare a trial with a known movement, its arrival time and the time it appears on the dashboard. Decide what to present when the source misses its deadline: a delay indicator, a blocked decision or manual escalation. A technically successful refresh does not prove that the source sent every batch. Keep these two checks separate in the acceptance record and assign different owners where appropriate. Review the actual consumer query because an optimization setting has meaning only in the path that uses it.
Filter documents and measure usefulness
In search, the candidate list is ordered by an external mechanism. This lesson’s exercise does not calculate that order. For tenant t1 at time 60, the first documents are b and c: b belongs to t2 and c expired at time 50. Selecting two documents first and then applying eligibility returns an empty list. Scanning eligible candidates before limiting to two finds a and g farther down. Neither option should deliver b or c to the consumer. In BigQuery, filter behavior depends on its position and the columns stored in the index. A predicate in the base subquery is insufficient to guarantee pre-filtering when it uses a non-stored column. Inspect the configuration and executed plan; do not infer behavior solely from SQL appearance. Even pre-filtering can return few results in a highly selective approximate search. The exercise illustrates operation order over a finite supplied list. It does not reproduce partition selection, vector distances or actual index coverage. The observed improvement is evidence about this fixture, not a promise that changing filter placement always fills every result slot.
Run the local eligibility contract
Save the code as run.py and use case.json from the pde-retrieval-gate lab: python3 run.py < case.json. Input contains documents and queries. Each document has an identifier, tenant, embedded version, current version, deletion flag and validity interval. Each query contains time, tenant, k, an eligible reference and ordered candidates. Names are fictional. No documents, credentials or customer data are loaded. The rule accepts availableAt <= at < validUntil, requires equal versions and tenant, and requires deleted=false. Availability has an inclusive boundary; expiration has an exclusive boundary. The reference contains between one and k eligible documents without duplicates. An invalid reference rejects the input instead of silently changing the reference to improve the metric. In the fixture, a and g form the reference. postFilterHits is zero and preFilterHits is two, both over referenceCount=2. The report retains exclusion reasons for each candidate. In production, even those identifiers and reasons would require access control; here they are synthetic learning data. Read the counts before interpreting any ratio and retain the fixture used to produce them.
Counterexamples before accepting an improvement
Change the candidate list to h,a,g while keeping k=2. All three documents are eligible, but h is outside the reference. The output selected before truncation is h,a, with one hit out of two. Correct filtering does not make a ranking useful. If g is absent from the supplied list, this code cannot discover it either. Evaluating a real engine would require a representative query set and a reference built with consistent population and relevance criteria. Next, change f’s availableAt to 60: it becomes eligible exactly at query time. Change c’s validUntil to 60: it remains excluded. Correct d’s embedded version to the current version and observe how its rank can occupy a slot before g. Removing an invalid document and recovering a relevant document are different properties. Tests permute registry order without changing results, but do not treat candidate order as irrelevant. That order is part of the contract. The program leaves authorizationEnforced and embeddingQualityProven false because it validates supplied metadata without authenticating tenants or measuring model quality.
Publish for the correct identity
An authorized view can expose selected data without granting the consumer direct read access to source tables. Design the trial around the identity running the job and the project where that job is created. Do not approve sharing using only the data owner’s account. Also confirm compatible dataset locations. When VPC Service Controls applies, check the path through both view and source projects even when the consumer does not require direct source IAM permissions. In the fictional case, the operator uses project ops, the view is in reporting and the source is in funds. A perimeter error does not justify granting broad access to funds. Capture the error, identify the failing stage and resolve the specific missing authorization with the control owner. Test an allowed tenant and an excluded tenant, recording expected outcomes before execution. For column-access changes, also consider previously cached results: revoking the Fine-Grained Reader role associated with a policy tag does not automatically invalidate those earlier results. Do not generalize this behavior to every kind of access revocation.
Decision to enter operation
Prepare an evidence table for the operational handover meeting. For the dashboard: business request, actual SQL, data interval, cacheHit, time, cost and a reconciled example. For search: eligible population, versions, candidates, reference, hits and exclusion cases. For sharing: identity, job project, project path and positive and negative test results. Assign every failure an owner and an action whose outcome can be checked. The sponsor requests a demonstration today. Demonstrating the exercise with synthetic documents and explaining its output is acceptable while real integration remains pending. Presenting two local hits as proof of customer isolation or generated-answer quality is not. In the final decision, distinguish available evidence from assumptions: the lab demonstrates execution of its bounded contract; the real service needs its own trials. Give support procedures for missing results, source delay and access failure, with escalation criteria. Complete the lesson by reproducing one correct result and one counterexample, and explain the operational impact of each in English. Preserve both outputs with their inputs so that another engineer can reproduce the decision.
"""Original offline ranking/filter-order model. No embeddings or access enforcement."""
import json
import re
import sys
def require(ok, message):
if not ok:
raise ValueError(message)
def integer(v, low, high):
return type(v) is int and low <= v <= high
def identifier(v):
return type(v) is str and re.fullmatch(r'[A-Za-z0-9_-]{1,48}', v) is not None
def keys(v, expected):
require(type(v) is dict and set(v) == set(expected.split), 'Unexpected object fields')
def blocked(doc, query):
reasons = []
if doc['tenant']!= query['tenant']:
reasons.append('tenant-mismatch')
if doc['deleted']:
reasons.append('deleted')
if doc['availableAt'] > query['at']:
reasons.append('not-yet-available')
if doc['validUntil'] <= query['at']:
reasons.append('expired')
if doc['embeddedVersion']!= doc['currentVersion']:
reasons.append('stale-version')
return reasons
def evaluate(payload):
keys(payload, 'documents queries')
documents, queries = payload['documents'], payload['queries']
require(type(documents) is list and 1 <= len(documents) <= 100, 'Need 1..100 documents')
require(type(queries) is list and 1 <= len(queries) <= 20, 'Need 1..20 queries')
registry = {}
for d in documents:
keys(d, 'id tenant embeddedVersion currentVersion deleted availableAt validUntil')
require(identifier(d['id']) and identifier(d['tenant']), 'Invalid document identifier')
require(d['id'] not in registry, 'Duplicate document id')
require(all(integer(d[k], 1, 1000000) for k in ['embeddedVersion', 'currentVersion']), 'Invalid version')
require(type(d['deleted']) is bool, 'deleted must be boolean')
require(all(integer(d[k], 0, 1000000000) for k in ['availableAt', 'validUntil']), 'Invalid document time')
require(d['availableAt'] < d['validUntil'], 'Invalid validity interval')
registry[d['id']] = d
ids = set
result = []
for q in queries:
keys(q, 'id tenant at k reference candidate')
require(identifier(q['id']) and identifier(q['tenant']), 'Invalid query identifier')
require(q['id'] not in ids, 'Duplicate query id')
ids.add(q['id'])
require(integer(q['at'], 0, 1000000000) and integer(q['k'], 1, 10), 'Invalid query time or k')
for name, low, high in [('reference', 1, q['k']), ('candidate', 0, 100)]:
values = q[name]
require(type(values) is list and low <= len(values) <= high, 'Invalid '+name+' length')
require(all(identifier(v) and v in registry for v in values), 'Unknown '+name+' document')
require(len(set(values)) == len(values), 'Duplicate '+name+' document')
require(all(not blocked(registry[v], q) for v in q['reference']), 'Reference contains ineligible document')
eligible = lambda v: not blocked(registry[v], q)
post = [v for v in q['candidate'][:q['k']] if eligible(v)]
pre = [v for v in q['candidate'] if eligible(v)][:q['k']]
reference = set(q['reference'])
result.append({'id': q['id'], 'referenceCount': len(reference),
'postFilterIds': post, 'preFilterIds': pre,
'postFilterHits': len(reference.intersection(post)),
'preFilterHits': len(reference.intersection(pre)),
'blockedCandidates': [{'id': v, 'reasons': blocked(registry[v], q)}
for v in q['candidate'] if not eligible(v)]})
return {'queries': sorted(result, key=lambda x: x['id']),
'rankingExhaustive': False, 'authorizationEnforced': False,
'embeddingQualityProven': False, 'productionApproval': False}
def main:
raw = sys.stdin.read(1000001)
require(len(raw) <= 1000000, 'Input too large')
print(json.dumps(evaluate(json.loads(raw)), sort_keys=True))
if __name__ == '__main__':
try:
main
except (ValueError, TypeError, RecursionError):
print('Invalid retrieval fixture', file=sys.stderr)
sys.exit(2)
With k=2 and candidates b,c,a,d,e,f,g,h, the fixture returns zero hits after truncation and two hits when filtering before truncation. The eligible reference is a,g.
Common pitfalls
Confusing cache with optimization, refresh interval with an SLA, eligibility with relevance, a local tenant filter with real authentication, or owner-account tests with consumer access.
Related topics: Preparing data for analysis · Contracts and changes · Pipeline observability
Deliver results whose population, validity, usefulness and consumer identity have been explicitly checked.
Reference: Professional Data Engineer standard exam guide · Current linked standard guide (document title v4.2); edition date unconfirmed (2026-09-30 inspection)