Tick Data Storage and Cleaning

A canonical tick schema, one timestamp policy, a partitioned columnar store with tested backups, written cleaning rules for bad ticks, gaps and corrections, and queries researchers can trust.

Free · Mac (Apple silicon) and Windows · v1.0.5 · Other platforms

Then open the Marketplace in the app, search for Tick Data Storage and Cleaning, and click Install. It goes into your own files, and works offline from then on.

Before you start: this plan is general information to help you organise decisions about money. It is not financial, investment, tax or legal advice, and it does not recommend any product, investment or deal. Rules differ between countries, so check decisions that matter with a qualified, licensed adviser in yours. Investing and trading can lose money, and raising or managing other people's money is regulated in most countries.

What it installs.

1

Pillar

1

Area

50

Projects

200

Starter tasks

🧮 Quantitative Finance & Trading

Tick Data Storage and Cleaning

Storing and cleaning high-frequency data: file formats and databases, bad ticks, timestamps and time zones, compression and fast research queries.

  1. Inventory of tick data sets and their sources
  2. Canonical schema for trades, quotes and book updates
  3. Timestamp and time zone policy
  4. Partitioning scheme for the tick store
  5. Choosing a file format for tick archives
  6. Choosing a time-series database for tick queries
  7. Raw, cleaned and derived data layers
  8. Data dictionary for every tick field
  9. Storage capacity and cost baseline
  10. Backup and restore plan for the tick archive
  11. Daily ingest completeness check
  12. Bad tick filter rule set
  13. Weekly data quality scorecard
  14. Exchange calendar and session hours table
  15. Instrument identifier map in the tick store
  16. Monthly restore drill
  17. Storage growth, compaction and query speed review
  18. Capture host clock synchronisation log
  19. Data incident log and post-mortems
  20. Quarterly audit of cleaned data against raw
  21. Columnar storage internals study
  22. Compression techniques for tick data
  23. Order book reconstruction from incremental feeds
  24. As-of joins between trades and quotes
  25. Reading exchange feed specifications
  26. Time zone, daylight saving and leap second drills
  27. Fast vectorised cleaning in Python
  28. Statistical outlier filters for ticks
  29. Duplicate and out-of-sequence tick detection
  30. Gap detection and backfill procedure
  31. Crossed and locked quote handling policy
  32. Trade corrections, cancels and condition codes
  33. Auction and off-exchange prints flagging
  34. Reproducible bar building from cleaned ticks
  35. Migrating CSV archives to columnar files
  36. Hot, warm and cold storage tiers
  37. Query helpers for researchers
  38. Feed format change cutover
  39. Daylight saving transition checks
  40. Annual archive integrity audit
  41. Loading a multi-year history backfill
  42. Market stress day data review
  43. Tick store on a single workstation
  44. Recording your own crypto exchange feed
  45. First 90 days as a market data engineer
  46. Handing over a tick pipeline when you leave
  47. Data request intake for researchers
  48. Cross-venue timestamp alignment
  49. Full depth order book storage at scale
  50. Bitemporal store for late corrections

Where it starts.

Each project arrives with its purpose, milestones and first tasks written out. Here is how the first three read.

Inventory of tick data sets and their sources

Most tick stores grow by accident: a vendor dump on one drive, a capture process on another, a colleague's cleaned copy somewhere in between. Listing every data set with its source, venues, date range, size, format and owner shows what you actually hold, which copies are duplicates and which ones research quietly depends on.

First tasks

  • List every drive, bucket and database that holds tick data
  • Record source, venues, date range, size and format for each data set
  • Ask colleagues which data sets their current research reads
  • Mark duplicate and orphaned copies for a consolidation decision

Canonical schema for trades, quotes and book updates

Every vendor and exchange names fields differently, scales prices differently and packs flags in its own way, so research code fills up with per-source special cases. A single canonical schema for trades, top-of-book quotes and book updates, with types, units and nullability fixed, lets every loader translate once and every query read the same columns.

First tasks

  • Collect the field lists from every current source side by side
  • Decide the price representation and how tick size is stored
  • Write the trade, quote and book update layouts with types and units
  • Map each source's fields to the canonical columns in one table

Timestamp and time zone policy

A tick can carry three or four times: the exchange's matching time, the feed's send time, your receive time and a vendor's own stamp, each at a different precision and often in local exchange time. Writing down which one is primary, storing it in UTC at nanosecond resolution, and keeping the others as named columns removes the most common silent error in intraday research.

First tasks

  • List each source's timestamps with their meaning and precision
  • Choose the primary event time and write down why
  • Name the secondary time columns and their units
  • Add the policy to the schema document and share it with researchers

Make it yours.

Install Tick Data Storage and Cleaning, then tell Aurum the date you're aiming for. It checks whether the plan fits the weeks you actually have.

Free · Mac (Apple silicon) and Windows · v1.0.5 · Other platforms

Blueprints are general information to help you organise your own work. They are not medical, financial, legal or other professional advice. See the Terms.