LEARN. PRACTICE. GROW.1. Select from consumer decisions
Start with access that the data product must support. A portfolio history lookup, a transactional update of two records and analysis over months of movements have different patterns. Record grain, volume, frequency, acceptable latency, concurrency, consistency and recovery approach. Add what consumers consider a valid result. A service can respond quickly with data that has not passed reconciliation. Separate the storage decision from the decision about when to publish results for use. In a fictional APS project, the operational application updates instructions while reporting calculates consolidated positions. Do not choose one product merely to reduce the number of names in the diagram. Compare candidates against concrete access patterns. Spanner deserves evaluation for a distributed transactional relational model; Bigtable for access suited to its key design; BigQuery for analysis; and Cloud Storage for objects. This does not remove the need to measure latency, cost and limitations. For archives, include reading and retrieval in total cost. If a file moves from rare access to daily reconciliation, the economically suitable class can change. Initial approval should state which assumptions justify selection and when they need review. Keep these assumptions alongside the workload examples used during validation.
2. Design keys without losing required access
A physical key influences which records sit near each other and how they are found. Bigtable’s lexicographic order supports prefixes and ranges. An increasing timestamp at the start of a row key can concentrate recent writes. An appropriately distributed prefix can improve that pattern, but the decision must account for queries. Spreading data is insufficient if the dominant read then needs to search the entire set. Include small and large accounts, write bursts and concurrent recent-history queries in the exercise. Imagine telemetry from several fund applications. An application#time key can preserve locality per application, but one dominant application can still concentrate load. Record observed distribution rather than application count alone. If proposing several segments per application, document how many ranges each read combines and how the team diagnoses omissions. Hashing an entire key removes useful ordering of the original value. That trade can require another access path. At technical review, compare design, distribution and queries on the same data. More nodes do not prove a key hotspot has been resolved. The decision should explain the resulting write and read behavior. Keep evidence of skew and access fan-out so capacity assumptions can be revisited after workload changes.
3. Relate partitions to the business period
Occurrence date and ingestion date have different meanings. An ingestion-partitioned table physically organizes data arrival. A business-date report must include late movements that can reside in other partitions. Before restricting scanning, define the expected logical set. Only then find physical selection that preserves it. A margin of several days can pass a trial and remain incorrect without a guaranteed lateness bound. In that case, review layout, correction capture or reconciliation strategy. For BigQuery, use predicates that let the system determine relevant partitions and verify the concrete query. A filter comparing the partition column with another variable column can prevent pruning. Limiting returned rows is not equivalent to limiting data read. Requiring a partition filter helps avoid queries without suitable restrictions but does not check whether you selected the correct month. Record functional requirements, physical predicates and execution evidence separately. Compare IDs, counts and measures by period. Matching totals can hide a missing zero-value row, two offsetting omissions or incorrect portfolio attribution. Optimization must preserve the result contract. This is why a faster query is only a candidate for acceptance until its output has been reconciled against the agreed reference.
4. Control grain and internal organization
After partition selection, clustering can reduce blocks relevant to particular filters. Column order matters. If account_id is the dominant filter, a layout starting with another field deserves comparison against one aligned with that access. Do not promise the same improvement for every query. Collect metrics on a representative sample while holding correctness criteria constant. Physical design should follow the actual workload, including close-period and incident-investigation queries. When modeling, consider hierarchical relationships frequently queried together. An instruction with a bounded number of components can use an ARRAY of STRUCT. This preserves the relationship within a row, but consumers must respect grain. After UNNEST, the parent total appears on several rows. Summing it per component multiplies the measure. DISTINCT applied only to the amount can also merge legitimate instructions with equal values. Define the measure key before aggregating. Exercise zero, one and several components, plus two parents with equal amounts. Denormalization is not universal: a suitable star schema may not benefit from additional nesting. Measure and document the reason for selection. The model review should include both the storage representation and the actual query a reporting consumer will run.
5. Govern files and access as a product
A usable lake needs discovery, meaning, authorization and operations. Catalog entries help locate assets, owners and relationships, but their presence proves neither data access nor approval of every change. In federated models, domains remain accountable for their products and agree shared contracts. Define who accepts schema changes, what a period means, which consumers depend on the product and how incompatibilities are communicated. In the inspected documentation, Dataplex Universal Catalog has evolved into Knowledge Catalog; the exam guide retains Dataplex terminology. Preserve that relationship in references without inventing a new exam version. BigLake delegation separates table permissions from underlying storage permissions. Also inspect direct object paths, because an independent grant can bypass table controls. For lifecycle management, distinguish eligibility, execution and recovery. Lifecycle actions are asynchronous. A locked retention policy has irreversible consequences: it cannot be removed or shortened. The product owner should accept those conditions before locking. Record periods, exceptions, costs and recovery evidence. Do not use the existence of a rule as proof that every object has already been processed. Handover needs a way to observe outcomes and a named owner who can explain whether observed behavior matches the accepted contract.
6. Lab: fidelity before scan size
The original exercise content/labs/pde-partition-fidelity/run.py compares physical selection against a supplied logical reference result. Run python3 content/labs/pde-partition-fidelity/run.py < content/labs/pde-partition-fidelity/case.json from the project root. Each row has id, eventDate, ingestDate, account, amountCents and declaredBytes. Under this fictional contract, amounts are EUR cents with no currency conversion. Dates are explicit YYYY-MM-DD days, without time-zone calculations. IDs must be unique. Input accepts at most 10,000 rows and rejects unexpected fields, invalid dates, duplicates and out-of-range numbers. partitionBy selects eventDate or ingestDate. scan defines a half-open physical range, or null to read all rows. query defines the logical eventDate range and one account, or null for every account. The model filters partitions before applying the logical request. Separately, it calculates a reference over every supplied row. It reports IDs, amounts, scanned partitions, rows and declared bytes. matchesProvidedReference passes only when no eligible identity is missing. The program executes no SQL, simulates no BigQuery optimizer and estimates no billing. Bytes are a fictional value supplied by the learner, useful only for comparing cases within this model. Source completeness is explicitly outside the evidence it provides.
7. Compare alternatives using counterexamples
The example file contains four movements. A and B belong to October 1 and account F1, with 1,000 and 2,000 cents. A arrived that day; B arrived on October 3. C belongs to October 2 and D to October 3. Each row declares 100 bytes. The logical request selects F1 in [2026-10-01,2026-10-02). With ingestDate partitions and a scan over the same range, only A is read: 100 bytes, total 1,000 and missingIds=[B]. The reference contains A and B, total 3,000. The smaller result is incorrect for the request. Change partitionBy to eventDate: the same range reads A and B, 200 bytes, without omissions. Set scan=null: it reads 400 bytes and also preserves results. Now change B to zero. The narrow scan again produces the reference total but still fails because an identity is missing. Finally, widen the physical range through October 4. Comparison passes for these data without proving a universal bound for future delays. Write conclusions in a table covering fidelity, scope and declared bytes. Separate what the exercise establishes from assumptions requiring observation of the real system. Retain the failing cases when evaluating a later optimization.
8. Accept and operate the selected design
A storage decision ends with operating criteria. Hand over expected query patterns, growth limits, schema ownership, retention policy, recovery approach and metrics indicating changed behavior. For analytical tables, measure cost and latency of reconciled results. For key-based access, track distribution and concentration. For files, track volume, classes, access and lifecycle actions. A lower bill can result from a consumer no longer receiving data; always compare financial metrics with the delivered product. In the APS case, prepare a committee decision covering the earlier model, alternative, preserved requirements, known differences and return plan. Run close-period queries, ad hoc investigations and late-event reads on representative data. If a consumer still assumes the old grain, handle compatibility before promotion. Define who can accept a provisional version and who needs notification of corrections. The summary is straightforward: select storage from access patterns, preserve meaning when reorganizing data and validate guarantees at the right scope. The local exercise teaches construction of a counterexample; real acceptance still needs evidence about source, security, execution, capacity and costs in the actual environment. Preserve that distinction in both the runbook and the change decision so later teams can understand the limits of the original approval.
"""Original local scan model. Declared bytes are not BigQuery billing estimates."""
from datetime import date
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 day(value):
if type(value) is not str or not re.fullmatch(r'[0-9]{4}-[0-9]{2}-[0-9]{2}', value):
raise ValueError('date must be YYYY-MM-DD')
date.fromisoformat(value)
return value
def bounds(value):
exact(value, ['from', 'to'])
start, end = day(value['from']), day(value['to'])
if start >= end:
raise ValueError('range must be nonempty and increasing')
return start, end
def integer(value, low, high):
if type(value) is not int or not low <= value <= high:
raise ValueError('integer outside local contract')
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 evaluate(document):
exact(document, ['rows', 'partitionBy', 'scan', 'query'])
rows = document['rows']
if type(rows) is not list or len(rows) > 10000:
raise ValueError('rows must be a list with at most 10000 entries')
layout = document['partitionBy']
if layout not in ('eventDate', 'ingestDate'):
raise ValueError('unknown partition field')
exact(document['query'], ['from', 'to', 'account'])
query = document['query']
start, end = bounds({'from': query['from'], 'to': query['to']})
if query['account'] is not None:
identifier(query['account'])
scan = bounds(document['scan']) if document['scan'] is not None else None
seen = set
for row in rows:
exact(row, ['id', 'eventDate', 'ingestDate', 'account', 'amountCents', 'declaredBytes'])
identifier(row['id'])
identifier(row['account'])
day(row['eventDate'])
day(row['ingestDate'])
integer(row['amountCents'], -10**12, 10**12)
integer(row['declaredBytes'], 0, 10**9)
if row['id'] in seen:
raise ValueError('duplicate row id')
seen.add(row['id'])
def eligible(row):
return start <= row['eventDate'] < end and (query['account'] is None or row['account'] == query['account'])
scanned = [r for r in rows if scan is None or scan[0] <= r[layout] < scan[1]]
reference = sorted((r for r in rows if eligible(r)), key=lambda r: r['id'])
result = sorted((r for r in scanned if eligible(r)), key=lambda r: r['id'])
found = {r['id'] for r in result}
missing = [r['id'] for r in reference if r['id'] not in found]
return {'partitionBy': layout, 'scannedPartitions': sorted({r[layout] for r in scanned}),
'scannedRowCount': len(scanned), 'scannedDeclaredBytes': sum(r['declaredBytes'] for r in scanned),
'referenceIds': [r['id'] for r in reference], 'resultIds': [r['id'] for r in result],
'referenceAmountCents': sum(r['amountCents'] for r in reference),
'resultAmountCents': sum(r['amountCents'] for r in result),
'missingIds': missing, 'matchesProvidedReference': not missing,
'sourceCompletenessProven': False, 'cloudPruningMeasured': False, 'billingEstimate': 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(3_000_001)
if len(raw) > 3_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)
Reading 100 bytes omits B; reading 200 by eventDate preserves A and B. Bytes are fictional.
Common pitfalls
Confuse ingestion with occurrence; sum parent totals after UNNEST; treat metadata as authorization; infer billing from the local model.
Related topics: Late events and replay · Preparation for analysis · Costs and recovery
An optimization is an acceptance candidate when it preserves results and has measured benefit on the real workload.
Reference: Professional Data Engineer standard exam guide · Current linked standard guide (document title v4.2); edition date unconfirmed (2026-09-30 inspection)