# Market Data Engineering Construct point-in-time datasets with documented transformations. Use this workbook alongside the course. Write your answers before opening the solutions. Practical work is self-reviewed; scored knowledge checks are in the Academy. ## 1. Ingestion ### Timestamps Ingestion should retain raw provider messages or files with source identifiers and receipt times. A normalized database is useful, but it should not be the only record when transformations may need investigation. ### Time zones Define a schema for symbol, price type, quantity, timestamp and source. Reject or quarantine records that do not satisfy required fields. A schema change should not silently reinterpret old values. ### Missing data Track event time and receipt time separately. Delayed messages can arrive out of order. A research pipeline needs an explicit policy for late observations rather than assuming arrival order equals market order. ### Duplicate records Make repeated ingestion idempotent using stable identifiers or documented deduplication rules. Reprocessing the same file should not double volume or P&L. Preserve evidence of rejected duplicates and genuine repeated observations. ### Worked example A provider resends yesterday's file after a reconnect. An append-only importer without identity checks doubles the rows. The correct replay should leave the normalized dataset unchanged for already stored records. ### Independent exercise Design an ingestion key and describe what happens when the same key arrives with corrected content. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## 2. Transformations ### Resampling Transformations should state inputs, outputs and assumptions. Unit conversion, resampling and adjustments are different operations and should remain distinguishable in the lineage of a derived series. ### Corporate/fund changes Timezone normalization needs date-aware rules. Preserve original timestamps and timezone information, especially around daylight-saving transitions or ambiguous local times. UTC storage does not repair an incorrectly interpreted source timestamp. ### Futures rolls Bar aggregation must define interval boundaries, price stream and empty-interval behavior. An inclusive endpoint used twice can double-count an event across adjacent bars. Test boundary timestamps explicitly. ### Quote versus trade feeds Continuous futures construction requires a roll and adjustment policy. Keep raw expiry contracts and transformation metadata so execution-sensitive studies can recover actual traded prices rather than only adjusted history. ### Worked example An event exactly at 10:05 is included in both the 10:00–10:05 and 10:05–10:10 bars. A half-open interval convention, consistently applied, prevents double inclusion. ### Independent exercise Specify a bar-boundary convention and test observations just before, exactly at and just after the boundary. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## 3. Quality ### Outlier policies Quality checks should distinguish impossible values, suspicious values and valid extremes. Automatically deleting every large move can remove the very events needed for risk analysis. Quarantine and investigate rather than rewriting history for smoothness. ### Staleness Missingness should be characterized by source, interval and cause where known. A feed outage differs from a market closure. The downstream policy should follow the reason and study requirements. ### Provenance Cross-source comparisons require matched instruments, timestamps and price conventions. Disagreement can reveal a defect, but it can also reflect genuine venue differences. Avoid declaring one source wrong solely because another differs. ### Reproducible storage Quality reports should include counts, affected periods and impact on derived results. A pass/fail badge without details is insufficient when the remaining gaps affect a strategy's trading window. ### Worked example A dataset has complete daily closes but missing ticks during major releases. It may support a daily study while being unsuitable for measuring first-second event execution. ### Independent exercise Write a fitness-for-purpose note for both uses of the dataset. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## 4. Point-in-time data laboratory ### Reconstruct an as-of dataset An as-of dataset contains only information available by a specified decision time. Observation dates alone are insufficient for revised economic data or corrected market records. Record publication and revision timing. ### Track vendor corrections Vendor corrections should be versioned so a study can distinguish original observations from later cleaned data. Both can be useful for different questions, but they should not be silently interchanged. ### Reconcile overlapping feeds Reconcile overlapping feeds with explicit priority and conflict rules. A merge that chooses the best-looking price after seeing outcomes can create artificial performance. Preserve source attribution for each retained observation. ### Document licensing and retention constraints Licensing, access and retention terms constrain how data can be used and shared. A reproducibility package may need hashes, schemas and retrieval instructions rather than redistribution of restricted raw files. ### Worked example A monthly observation released on the tenth is stored with the first day of the month. Joining on observation date would let a strategy use it nine days before publication. ### Independent exercise Define an as-of join that avoids this error and explain how later revisions should be represented. My inputs and assumptions: My calculation or decision: Evidence that would change my conclusion: ## Course project Deliver a versioned dataset and a quality report. ### Self-review rubric - Concepts and reasoning: 25% - Calculations, data and evidence: 30% - Process and risk controls: 25% - Limitations and communication: 20% Record one correction and one next practice task. This rubric is not automatically graded. ## Worked solutions ### Exercise 1 Use source and stable event identity where available, with version or correction handling. A changed record should be retained as a documented revision, not silently ignored as identical or added as an unrelated event. ### Exercise 2 For example, use start-inclusive and end-exclusive intervals. The exact-boundary event belongs to the later bar; neighboring observations belong according to their timestamps. Document the convention for all consumers. ### Exercise 3 State the available resolution and gaps. Daily-close analysis may remain possible with appropriate checks, while fine event-execution claims are unsupported. Quality depends on the question, not one universal dataset label. ### Exercise 4 Join on availability time at or before the decision, retaining the original observation period. Store later revisions as separate versions with their own availability times so historical information sets can be reconstructed. ## Further reading - https://docs.python.org/3/library/datetime.html - https://www.itl.nist.gov/div898/handbook/