LEARN. PRACTICE. GROW.1. Define the measure before accelerating the dashboard
A dashboard should answer a business question using a defined measure. Record unit, grain, period, included population and correction handling. An incident count might mean incidents currently open, created during the period or resolved during the period; those results are not interchangeable. In a fictional APS service, management wants mean resolution time per incident. If preparation stores only team averages, a simple average of those averages can give equal weight to teams with different volumes. Retain sums and counts so the measure can be recomposed correctly. For example, one team has sum 100 and count 10; another has sum 900 and count 30. The per-record mean is 1000/40=25. Simply averaging 10 and 30 would give 20 and answer a different question. Put this counterexample in the report acceptance test. Add empty groups, reopened incidents and incomplete periods. Also define when output is provisional and who accepts corrections. A faster chart remains wrong if preparation changes measure meaning. Examples in this lesson are fictional and do not describe BNP Paribas procedures. Measure definitions should travel with the data delivered to each consumer.
2. Connect optimization to execution evidence
Begin latency investigation with representative jobs and execution plans. Distinguish query time, waiting, transfer and client rendering. Look for repeated work, joins that expand grain and expensive transformations applied more often than necessary. A CTE improves query organization but does not guarantee one materialization. If the plan shows repetition, compare a temporary table or another suitable intermediate result, including the cost of producing and retaining it. Validate results before comparing duration alone. Materialized views and BI Engine address specific problems. Materialized view type affects capabilities; a non-incremental view does not support smart tuning. Do not generalize one feature across all variants. For BI Engine, inspect job statistics and reasons to understand actual acceleration. An existing reservation does not establish benefit for every query. Prepare comparison using the same data, parameters and identities, noting cache and concurrency. For daily close, include peak usage rather than measuring only one isolated execution. Record which requirements improved and which remain unverified, such as freshness, consumer compatibility and behavior after data updates. The decision should connect each proposed change to evidence from the query that motivated it.
3. Validate security on the consumption path
A control’s result depends on the identity and path used. A full-read account does not represent a consumer meant to receive masked values. Run representative queries using the consumer’s effective permissions, including protected columns and filter combinations used by the dashboard. Also record export-job identity. A screen showing masked data does not establish that another account produces a masked file. Acceptance should observe actual content leaving through each authorized path. In a fictional case, the dashboard uses a limited account while the distribution batch uses an older account with full access. The team validated only the dashboard and is about to distribute the file. Challenge the conclusion that both paths are equivalent and compare configuration, permissions and output. If the export needs narrower scope, apply and test that contract on the export itself. Do not change permissions merely to make a test pass. Retain evidence that the authorized consumer can perform required work and that excluded fields do not appear in its intended output. The owner should accept any change in shared scope. Keep the test identity in the evidence so a later reviewer can reproduce the conclusion.
4. Reconstruct what was known at decision time
For ML preparation, distinguish when information was effective from when it became available. A correction can describe yesterday and arrive tomorrow. If the objective is evaluating a decision made today, using that correction gives the model knowledge the service lacked. Define the availability boundary, entity, transformation version and version-selection rule. If history cannot reconstruct these elements, record the limitation. Merely using a timestamp does not establish absence of information leakage. In our APS example, a model prioritizes incidents using a feature representing known operational state. Prepare training data according to that contract. Evaluation of future use must respect the relevant time sequence and avoid inappropriate reuse of examples. Also compare offline transformation with production transformation. Replacing NULL with zero in training while rejecting NULL in the service produces different behavior for the same input. Keep absences and conflicts observable; do not silently fill them using future information to improve a metric. Correct preparation does not itself establish predictive quality, but it is necessary for evaluation to have useful meaning. Record which conditions were observed and which timestamps were merely supplied by an unverified source.
5. Prepare documents for useful retrieval
Embeddings and vector search also need a preparation contract. Record source, document version, segmentation, model, normalization and content-access criteria. A vector with the expected number of components can belong to a representation incompatible with the index. Before changing model or preparation, measure retrieval on representative questions with identified relevant documents. Successful HTTP responses from embedding generation do not establish that useful results remain retrievable. An approximate index can reduce latency while missing some relevant neighbors. Compare recall and duration together, including difficult questions and recently updated documents. In a fictional RUN support example, a user seeks the procedure applicable to the current middleware version. Quickly finding an old procedure can worsen the decision. Retain date, version and original-document link in the result. Test updates and removal from the searchable set, plus consumer access scope. Selection of useful results must consider operational requirements and available evidence; a high similarity value proves neither that an instruction is current nor that the user may read it. Keep a reviewed reference set so a preparation change can be compared against the same questions.
6. Lab: historical selection using two times
The original program content/labs/pde-feature-history/run.py uses only local Python. Run python3 content/labs/pde-feature-history/run.py < content/labs/pde-feature-history/case.json from the project root. features contains id, entity, effectiveAt, availableAt and value. decisions contains id, entity and at. Times are integers on a fictional scale; value is an integer without financial or clinical meaning. Each collection has unique IDs and at most 1,000 entries. The program rejects unexpected fields, invalid identifiers, out-of-range numbers and repeated JSON fields. For each decision, asKnownThen considers the same entity and requires effectiveAt <= at and availableAt <= at. It ranks first by effectiveAt and then by availableAt, selecting the greatest eligible values. If several rows tie and declare the same value, all IDs are retained as provenance. If values differ, it returns ambiguous with no value. Without candidates it returns missing. ignoringAvailability ignores the second condition to construct a counterexample. The program compares selections, including IDs and status; a provenance change can be flagged even when values match. It neither trains models nor simulates a managed feature store. Both time conditions are explicit local rules rather than claims about every cloud product.
7. Predict results and test boundaries
In the example, old has times 10/10 and value 5. known has 20/40 and value 6. correction has 20/120 and value 9. future has 110/80 and value 11. Decision D1 occurs at 100: known is the selected eligible version with value 6; ignoring availability selects correction with value 9. future was already available but not yet effective and is excluded. D2 occurs at 30 and selects old; D3 belongs to another entity and returns missing. Trace these selections manually before running the file. Then add a row with the same times as known and value 7. Historical selection becomes ambiguous. Change row order and confirm the result stays the same. If the tied row also has value 6, selection retains both IDs. Move the decision to 40 to observe the inclusive availability boundary; move it to 110 to observe the effective-time boundary. At 130, future still wins because the rule prioritizes effective time. Explain each result. None of these tests authenticates source timestamps or establishes quality of a model that might consume the feature. Keep the counterexamples as regression fixtures when changing the selection contract.
8. Share and hand over with meaning preserved
Sharing needs a contract that consumers can continue to understand. A BigQuery sharing linked dataset is a read-only reference to shared data; subscribing does not create an independent editable copy. If a publisher changes amount units from cents to euros while retaining the name, consumers may continue dividing by 100. Identify dependencies, version breaking changes and validate migration. Do not confuse listing discovery with approval for any use or future change. At RUN handover, collect measure definitions, preparation version, performance evidence, authorized identities and handling of missing, late or conflicting data. Define who responds when a metric changes through historical correction and when the change must be communicated. For an ML or document-retrieval product, retain evaluation criteria and known limitations. The team should explain what was measured, over which population, with what information and for which consumer. This lesson connects preparation to decisions: a fast query, high metric or accessible shared dataset is useful only when content, time and access scope match the accepted contract. Treat unresolved conflicts as visible work with an owner rather than silently choosing whichever value produces a better result.
"""Original offline feature-history contract; not a managed feature store."""
import json
import re
import sys
def exact(value, keys):
if type(value) is not dict or set(value)!= set(keys):
raise ValueError('unexpected or missing fields')
def identifier(value):
if type(value) is not str or not re.fullmatch(r'[A-Za-z0-9_-]{1,64}', value):
raise ValueError('invalid identifier')
def integer(value, low, high):
if type(value) is not int or not low <= value <= high:
raise ValueError('integer outside local contract')
def select(candidates):
if not candidates:
return {'status': 'missing', 'ids': [], 'value': None}
rank = max((r['effectiveAt'], r['availableAt']) for r in candidates)
latest = [r for r in candidates if (r['effectiveAt'], r['availableAt']) == rank]
values = {r['value'] for r in latest}
return {'status': 'selected' if len(values) == 1 else 'ambiguous',
'ids': sorted(r['id'] for r in latest),
'value': latest[0]['value'] if len(values) == 1 else None}
def evaluate(document):
exact(document, ['features', 'decisions'])
features, decisions = document['features'], document['decisions']
if type(features) is not list or len(features) > 1000:
raise ValueError('at most 1000 feature rows required')
if type(decisions) is not list or len(decisions) > 1000:
raise ValueError('at most 1000 decisions required')
for rows, keys in [(features, ['id','entity','effectiveAt','availableAt','value']),
(decisions, ['id','entity','at'])]:
seen = set
for row in rows:
exact(row, keys)
identifier(row['id'])
identifier(row['entity'])
if row['id'] in seen:
raise ValueError('duplicate id within collection')
seen.add(row['id'])
for key in ['effectiveAt','availableAt'] if rows is features else ['at']:
integer(row[key], 0, 10**9)
if rows is features:
integer(row['value'], -10**6, 10**6)
results = []
for decision in sorted(decisions, key=lambda r: r['id']):
candidates = [r for r in features if r['entity'] == decision['entity'] and r['effectiveAt'] <= decision['at']]
available = [r for r in candidates if r['availableAt'] <= decision['at']]
safe, naive = select(available), select(candidates)
results.append({'decisionId': decision['id'], 'at': decision['at'],
'asKnownThen': safe, 'ignoringAvailability': naive,
'excludedUnavailableIds': sorted(r['id'] for r in candidates if r['availableAt'] > decision['at']),
'selectionDiffers': safe!= naive})
return {'results': results, 'contract': 'latest effectiveAt, then latest availableAt, both <= decision time',
'sourceTimestampAuthenticityProven': False, 'modelQualityProven': False,
'cloudExecutionValidated': False, 'productionApproval': False}
def unique_object(pairs):
result = {}
for key, value in pairs:
if key in result:
raise ValueError('duplicate JSON field')
result[key] = value
return result
if __name__ == '__main__':
try:
raw = sys.stdin.read(1_000_001)
if len(raw) > 1_000_000:
raise ValueError('input exceeds local limit')
print(json.dumps(evaluate(json.loads(raw, object_pairs_hook=unique_object)), sort_keys=True))
except (ValueError, TypeError, RecursionError) as error:
print(json.dumps({'error': str(error)}), file=sys.stderr)
sys.exit(2)
At decision 100, the available version is 6; ignoring availableAt selects a later correction worth 9.
Common pitfalls
Unweighted averages of averages; reservation as acceleration proof; future corrections in training; masking tests under another identity.
Related topics: Storage fidelity · Operations and observability · Data governance
Preparation must preserve meaning, availability time and access scope before evaluating performance or quality.
Reference: Professional Data Engineer standard exam guide · Current linked standard guide (document title v4.2); edition date unconfirmed (2026-09-30 inspection)