Raw market data is a mess. A CSV export from a brokerage's API will have split-adjusted close prices that don't match your reference terminal. A websocket stream will send timestamps in UTC while your local system expects America/New_York. A corporate action will retroactively alter last month's prices by 30% with no warning. For retail investors pulling data for a weekend project, these discrepancies are an annoyance. For quant researchers building a multi-year backtest, they are a career-limiting error waiting to happen.

This article dissects the engineering inside TickDB's historical data pipeline. We examine five dimensions where raw data diverges from cleaned, aligned data: corporate action adjustments, timestamp normalization, outlier detection, volume integrity, and continuity validation. For each dimension, we show what the raw data looks like, what the cleaned output delivers, and how the transformation works in practice. We assume you are evaluating a market data vendor or building a backtesting pipeline that depends on data quality at the line level.

Why Data Cleaning Is Not Optional for Backtesting

A backtest on uncleaned data produces results that cannot be replicated in live trading. The causes are structural, not incidental. Corporate actions such as stock splits, dividends, and spin-offs retroactively alter the historical price series. A 4-for-1 split on a stock trading at $800 per share creates a historical series where every pre-split price must be divided by four to be comparable to today's price. If your backtest uses raw, unadjusted closes, your strategy's historical performance will appear four times larger than it actually was. Commission and slippage adjustments cannot compensate for this error because the base PnL figure is wrong.

Timestamp misalignment introduces a different class of errors. A strategy that enters on the open of a specific bar assumes that the bar's open price reflects the first traded price after the market session began. If the data vendor timestamps bars using the exchange's native timezone while your execution system operates in UTC, your entry signals will be offset by the exact duration of the timezone difference. For US equity markets, this is a 5-hour offset during standard time and 4 hours during daylight saving time. A mean-reversion strategy with a 15-minute holding window loses 33% of its effective holding period to timestamp drift. This is not a edge case. It is a systematic bias that appears in every backtest run on misaligned data.

TickDB's pipeline addresses these failure modes at ingestion time. The goal is a dataset where every OHLCV candle is timestamped in a consistent reference frame, price-adjusted to be comparable across corporate actions, stripped of obvious data artifacts, and validated for structural continuity. The sections below walk through each dimension of that pipeline.

Dimension 1: Corporate Action Adjustments

The Problem with Raw Price Data

Stock splits, stock dividends, rights offerings, and spin-offs all alter the per-share price without changing the total market capitalization of the position. A 3-for-2 stock split triples the share count and halves the per-share price. A $10,000 position in that stock before the split is still worth $10,000 after the split — but the raw historical price series will show a 33.3% drop on the split date if unadjusted. Any strategy that backtests across that date on raw data will register a catastrophic loss that never actually occurred.

Raw data from many exchanges and brokerage APIs arrives with minimal or no adjustment. Some vendors provide split-adjusted close data as an optional field but leave dividend adjustments to the consumer. Others provide neither. The result is a dataset that appears internally consistent within a single trading day but becomes progressively more unreliable over multi-year backtest horizons.

TickDB's Adjustment Standard

TickDB applies split-adjusted and dividend-adjusted corrections to all US equity OHLCV data. The adjustment logic follows the industry-standard methodology used by CRSP and Bloomberg:

  1. Split adjustment: All historical prices are backward-adjusted to the current share count. Pre-split prices are divided by the split ratio. Post-split prices are left unchanged.

  2. Dividend adjustment: Historical prices are adjusted for the dividend yield impact. The adjustment factor accounts for the dividend payment date and the dividend per share relative to the pre-dividend price. This is a multiplicative adjustment applied to all pre-dividend close prices.

  3. Spin-off and rights adjustments: Corporate actions involving subsidiary spin-offs or rights offerings receive special handling. The parent company's price series is adjusted to reflect the pro-rata value transferred to the subsidiary. The subsidiary's price series begins on the distribution date.

Practical Impact: A Real Example

Consider Apple (AAPL) between 2017 and 2020. During this period, Apple executed a 4-for-1 stock split in August 2020 and paid quarterly dividends throughout. A raw price extract would show the following discontinuity on the split date:

Date Raw Close (USD) Adjusted Close (USD) Adjustment Factor
Aug 27, 2020 (pre-split) 499.23 124.81 ÷4
Aug 31, 2020 (post-split) 129.04 129.04 ÷1

For a backtest running from January 2019 to December 2021, the raw data would overstate all pre-split returns by a factor of four. A strategy that bought AAPL at $75 in January 2019 and sold at $120 in March 2021 would appear to have a 60% gross return on raw data but a 15% return on adjusted data. The strategy itself is unchanged. Only the data representation differs.

TickDB's adjusted close series is returned by default on the /v1/market/kline endpoint. The adjustment flag is documented in the endpoint schema but is set to the correct default for backtesting use cases.

Dimension 2: Timestamp Normalization

The Problem with Raw Timestamps

Market data arrives from multiple sources with non-standard timestamp conventions. US equity exchanges report trades in the exchange's local timezone (America/New_York). Crypto exchanges typically report in UTC. Forex data may use server time or exchange time depending on the venue. A quant researcher aggregating data across asset classes — equities, crypto, and forex — will encounter timestamp mismatches that are invisible until a strategy fails to execute at the intended time.

Beyond timezone differences, there is the question of bar boundary alignment. A 1-minute bar can be defined as the interval starting at hh:00:00 and ending at hh:00:59.999, or as the interval ending at hh:01:00 (the "last-tick" convention). Different data vendors use different conventions, and a strategy that assumes one convention while the data uses another will experience systematic entry and exit timing errors.

TickDB's Timestamp Standard

TickDB normalizes all timestamps to UTC as the reference frame. Every OHLCV candle returned by the /v1/market/kline endpoint carries a UTC timestamp that reflects the bar's closing time according to the exchange's official session schedule.

For US equity markets specifically, TickDB aligns bar boundaries to the regular trading session (RTS) schedule: 9:30 AM ET to 4:00 PM ET for the standard session, with pre-market (4:00 AM to 9:30 AM ET) and after-hours (4:00 PM to 8:00 PM ET) available as separate sessions. All timestamps are converted from America/New_York to UTC before storage.

The bar convention used is closing-time alignment: a 1-minute bar spanning 9:30:00 to 9:30:59 ET carries the closing timestamp 9:30:59 ET (or 14:30:59 UTC). This matches the convention used by major terminal providers and ensures that strategies written against other standard data sources produce consistent results on TickDB data.

Handling Daylight Saving Time Transitions

A common source of silent errors is the DST transition. On the spring forward date, the 2:00 AM hour is skipped entirely in local time. A bar that should cover 1:59 to 2:00 AM local time on that date simply does not exist in the data. If a strategy expects that bar to exist and fills it by forward-filling the previous bar's close, the strategy will experience a phantom price at a time when no trade occurred. Conversely, on the fall back date, the 1:00 AM to 2:00 AM hour occurs twice.

TickDB's pipeline explicitly handles DST transitions. Bars are generated using a timezone-aware datetime library that correctly skips or duplicates the DST boundary hours according to the America/New_York timezone definition. The pipeline flags DST transition dates in the metadata so that downstream systems can apply appropriate logic if needed.

Dimension 3: Outlier Detection

The Problem with Raw Data Artifacts

Exchange data feeds are not pristine. Erroneous prints appear as a result of fat-fingered orders, test transactions, or exchange system errors. A single trade reported at $0.01 on a $100 stock will corrupt a moving average calculation if included without screening. A volume print of 100x the average daily volume on a single minute bar will distort any volume-weighted strategy. These artifacts are rare — typically less than 0.1% of total records — but their impact on statistical calculations is disproportionate.

Common outlier types in raw market data include:

  • Price spikes: Trades at prices more than 5 standard deviations from the rolling mean of the security's price distribution.
  • Zero prints: Trades reported at $0.00 or at a negative price, which are clearly system errors.
  • Volume anomalies: Minute bars where volume exceeds 20x the rolling median volume for that time-of-day slot.
  • Stale data: Records with timestamps that fall outside the exchange's published session hours, indicating a delayed or erroneous report.

TickDB's Outlier Detection Pipeline

TickDB applies a three-stage outlier detection pipeline to each ingested OHLCV record:

Stage 1 — Rule-based screening. A set of deterministic rules catches the most obvious errors. Any bar with a close price of zero, a negative price, a volume of zero on a non-zero tick count, or a timestamp outside the allowed session window is flagged as invalid.

Stage 2 — Statistical screening. For each security, the pipeline computes a rolling mean and standard deviation of close prices over a 20-day window. Any bar whose close price falls outside ±5 standard deviations from the rolling mean is flagged as a potential outlier. This threshold was selected empirically to balance false positive rate against missed detection. Securities with high idiosyncratic volatility (e.g., low-price stocks with wide spreads) may have wider statistical bands, preventing legitimate high-variance bars from being flagged.

Stage 3 — Volume correlation screening. The pipeline cross-references each bar's volume against the security's typical volume profile by time-of-day. A bar whose volume exceeds 20x the median volume for that 5-minute slot on that day-of-week is flagged for review. This catches anomalous volume prints that are not price异常的.

Flagged bars are not automatically deleted. The pipeline routes them to a review queue where automated rules determine the correction strategy:

Outlier type Correction strategy
Zero or negative price Replace with previous bar's close
Volume spike Set volume to the median volume for that time slot
Price spike (verified as error) Replace with previous bar's close
Price spike (legitimate move) Retain; flag with anomaly_flag: true metadata
Stale timestamp Reject; do not include in dataset

Bars that are corrected carry a metadata flag adjusted: true in the response payload, allowing downstream systems to exclude or weight them differently in calculations.

Dimension 4: Volume Integrity

The Problem with Raw Volume Data

Volume data is less glamorous than price data, but it is equally critical for strategy performance. Two categories of volume errors appear in raw exchange data: double-counting and missing prints. Double-counting occurs when a trade is reported through multiple exchange channels and a consolidated feed includes both instances. Missing prints occur during high-volatility periods when exchange systems fall behind and consolidate delayed reports, creating gaps in the minute-by-minute volume series.

For strategies that rely on volume-weighted indicators — VWAP, OBV, volume-profile analysis — these errors introduce systematic biases. A double-counted minute bar will inflate the volume denominator in a VWAP calculation, making the indicator appear less volatile than it actually is. A missing volume bar will create a false gap in the volume profile, potentially triggering a breakout signal on a non-existent price move.

TickDB's Volume Validation Logic

TickDB's pipeline applies two validation checks to volume data before committing records to the storage layer:

Cross-venue reconciliation for US equities. US equity volume is distributed across 13 distinct exchanges (the consolidated tape). Raw consolidated tape data may include double-counted prints where a trade is reported through both FINRA's OTC venue and a national exchange. TickDB reconciles volume against the SEC's consolidated volume statistics, which are published daily. Any bar whose volume exceeds 102% of the corresponding consolidated tape volume for that security and time interval is flagged for investigation.

Fill-forward validation. For any time gap in the OHLCV series that is not attributable to a market holiday or scheduled session closure, the pipeline applies a fill-forward validation. The volume for the missing bar(s) is estimated as the average of the preceding and following bars' volumes, with a flag indicating that the value is interpolated. For backtesting purposes, interpolated volume bars are clearly labeled so that strategies can apply appropriate weighting.

Dimension 5: Continuity Validation

The Problem with Data Gaps

A backtester that encounters a missing bar in the middle of a strategy's holding period will either crash or introduce a forward-looking bias if it fills the gap with the last known price. Data gaps occur for three reasons: scheduled market closures (holidays, early closes), unscheduled closures (emergency halts, system failures), and ingestion failures in the data vendor's pipeline.

The first category is benign and predictable. The second category is analytically significant — a trading halt during a news event is not a data gap; it is a market signal that should appear in the dataset. The third category is a data quality failure that should be visible to the consumer.

TickDB's Continuity Verification

TickDB's pipeline maintains a session calendar for each supported market. The calendar defines regular trading hours, early closures, and market holidays for every exchange. At ingestion time, the pipeline verifies that the OHLCV series for each security is continuous within each scheduled session.

Gaps are classified into three types:

Gap type Cause Handling
Scheduled closure Market holiday, early close No action; gap is expected and documented in metadata
Unscheduled halt Trading halt, circuit breaker Retained as a zero-volume bar with halt: true flag; included in dataset
Ingestion failure Vendor pipeline error Flagged; data is backfilled from the redundant source if available; marked with backfill: true

For US equities, the pipeline cross-references gaps against exchange-published halt records from FINRA and the primary listing exchange. This ensures that an unscheduled halt appears in the dataset as a distinct event rather than vanishing into a gap.

The continuity metadata is accessible via the /v1/market/kline endpoint through the gaps parameter, which returns a list of all identified gaps in the requested time range with their classification and handling status.

Comparing Raw and Cleaned Data Side by Side

The following table summarizes the five dimensions and the practical impact of each cleaning step on a representative US equity dataset:

Dimension Raw data behavior TickDB cleaned data Strategy impact without cleaning
Corporate actions Unadjusted close prices; split dates show phantom jumps Split + dividend adjusted; pre-action prices scaled to current share count Gross return overstatement; incorrect position sizing across corporate action dates
Timestamps Exchange-local timezone; DST-unaware bar generation UTC normalized; DST-aware bar boundaries Systematic entry/exit timing drift; phantom bars at DST transitions
Outliers Includes erroneous prints, zero prices, volume spikes Rule-based + statistical screening; corrected bars flagged with metadata Corrupted moving averages; false breakout signals; distorted VWAP
Volume Double-counted prints possible; missing bars not flagged Cross-venue reconciliation; interpolated gaps flagged Inflated or deflated volume indicators; false volume-breakout signals
Continuity Gaps treated as missing data; no classification Scheduled closures vs. unscheduled halts vs. ingestion failures classified Forward-looking bias from gap filling; missed halt events treated as data loss

Code: Retrieving Adjusted OHLCV Data with Metadata Flags

The following example demonstrates how to retrieve cleaned, adjusted OHLCV data from TickDB using the /v1/market/kline endpoint. The code is production-ready and includes all required engineering elements: timeout configuration, environment-variable-based authentication, and error handling.

import os
import time
import requests
from datetime import datetime, timedelta

# Configuration
API_KEY = os.environ.get("TICKDB_API_KEY")
BASE_URL = "https://api.tickdb.ai/v1/market/kline"

if not API_KEY:
    raise ValueError("TICKDB_API_KEY environment variable is not set")

def fetch_adjusted_klines(symbol: str, interval: str, start_ts: int, end_ts: int, limit: int = 500):
    """
    Fetch adjusted OHLCV candles from TickDB for a given symbol and time range.
    Returns cleaned, split-adjusted, and dividend-adjusted data with continuity metadata.
    
    Args:
        symbol: Exchange symbol (e.g., "AAPL.US")
        interval: Candle interval (e.g., "1m", "1h", "1d")
        start_ts: Start timestamp in milliseconds (UTC)
        end_ts: End timestamp in milliseconds (UTC)
        limit: Maximum bars per request (max 1000)
    
    Returns:
        List of OHLCV dictionaries with metadata flags
    """
    headers = {"X-API-Key": API_KEY}
    params = {
        "symbol": symbol,
        "interval": interval,
        "start": start_ts,
        "end": end_ts,
        "limit": limit,
        "adjust": "split,div"  # Request split and dividend adjustments
    }
    
    retries = 0
    max_retries = 3
    
    while retries < max_retries:
        try:
            response = requests.get(
                BASE_URL,
                headers=headers,
                params=params,
                timeout=(3.05, 10)  # (connect_timeout, read_timeout)
            )
            data = response.json()
            
            # Handle rate limiting
            if data.get("code") == 3001:
                retry_after = int(response.headers.get("Retry-After", 5))
                print(f"Rate limited. Retrying after {retry_after} seconds.")
                time.sleep(retry_after)
                continue
            
            # Handle authentication errors
            if data.get("code") in (1001, 1002):
                raise ValueError("Invalid API key — check TICKDB_API_KEY environment variable")
            
            # Handle symbol not found
            if data.get("code") == 2002:
                raise KeyError(f"Symbol {symbol} not found")
            
            # Return the cleaned data
            if data.get("code") == 0:
                return data.get("data", [])
            
            raise RuntimeError(f"Unexpected API response: {data}")
            
        except requests.exceptions.Timeout:
            retries += 1
            delay = min(2 ** retries + 0.1, 8)  # Exponential backoff with cap
            print(f"Request timed out. Retry {retries}/{max_retries} in {delay:.1f}s")
            time.sleep(delay)
        except requests.exceptions.RequestException as e:
            raise RuntimeError(f"Network error: {e}")

# Example: Fetch 1-year of daily adjusted AAPL data
end_ts = int(datetime.utcnow().timestamp() * 1000)
start_ts = int((datetime.utcnow() - timedelta(days=365)).timestamp() * 1000)

try:
    candles = fetch_adjusted_klines("AAPL.US", "1d", start_ts, end_ts)
    print(f"Retrieved {len(candles)} daily candles")
    
    # Examine metadata flags on each candle
    adjusted_count = sum(1 for c in candles if c.get("adjusted") is True)
    gap_count = sum(1 for c in candles if c.get("halt") is True or c.get("backfill") is True)
    print(f"Candles with adjustment flags: {adjusted_count}")
    print(f"Candles with continuity flags (halts/backfills): {gap_count}")
    
except Exception as e:
    print(f"Error: {e}")

# ⚠️ Engineering note: For production backtesting workloads processing
# large datasets, implement pagination using the 'start' cursor returned
# in the API response. Batch requests to avoid rate limit (code 3001).
# For intrabar tick reconstruction, use the /v1/market/trades endpoint
# (available for HK and crypto markets; US equity tick data via
# consolidated tape vendors is recommended for high-frequency applications).

The code above retrieves data that has already passed through all five cleaning stages. The adjust parameter explicitly requests split and dividend adjustments. The returned candles include metadata fields (adjusted, halt, backfill) that allow downstream systems to apply appropriate weighting or exclusion logic for statistical calculations.

Verifying Data Quality Programmatically

For teams running systematic strategies, programmatic data quality verification should be part of the backtest initialization pipeline. The following utility function demonstrates a basic quality check that validates continuity, detects outlier-adjacent bars, and reports adjustment flags:

from typing import List, Dict, Any

def quality_report(candles: List[Dict[str, Any]], interval_minutes: int) -> Dict[str, Any]:
    """
    Generate a data quality report for a list of OHLCV candles.
    
    Checks:
    1. Continuity — no unexpected gaps between bars
    2. Zero-price detection
    3. Negative return detection (large single-bar reversals)
    4. Adjustment flag summary
    
    Args:
        candles: List of OHLCV candle dicts from TickDB
        interval_minutes: Expected interval between candles in minutes
    
    Returns:
        Dictionary with quality metrics and flagged anomalies
    """
    if not candles:
        return {"status": "empty", "anomalies": []}
    
    anomalies = []
    interval_ms = interval_minutes * 60 * 1000
    adjusted_count = 0
    halt_count = 0
    backfill_count = 0
    
    for i, candle in enumerate(candles):
        # Check adjustment flags
        if candle.get("adjusted"):
            adjusted_count += 1
        if candle.get("halt"):
            halt_count += 1
        if candle.get("backfill"):
            backfill_count += 1
        
        # Check for zero or missing close price
        if not candle.get("close") or candle["close"] <= 0:
            anomalies.append({
                "type": "zero_price",
                "timestamp": candle.get("ts"),
                "candle_index": i
            })
        
        # Check for negative volume
        if candle.get("volume", 0) < 0:
            anomalies.append({
                "type": "negative_volume",
                "timestamp": candle.get("ts"),
                "candle_index": i
            })
        
        # Check continuity against previous candle
        if i > 0:
            expected_ts = candles[i - 1]["ts"] + interval_ms
            actual_ts = candle["ts"]
            if abs(actual_ts - expected_ts) > interval_ms:
                anomalies.append({
                    "type": "continuity_gap",
                    "expected_ts": expected_ts,
                    "actual_ts": actual_ts,
                    "gap_ms": actual_ts - expected_ts,
                    "candle_index": i
                })
    
    # Compute basic return statistics
    closes = [c["close"] for c in candles if c.get("close")]
    returns = [(closes[i] - closes[i-1]) / closes[i-1] for i in range(1, len(closes))]
    large_reversals = [r for r in returns if abs(r) > 0.10]  # Flag >10% single-bar moves
    
    return {
        "status": "complete",
        "total_candles": len(candles),
        "adjusted_candles": adjusted_count,
        "halt_candles": halt_count,
        "backfill_candles": backfill_count,
        "anomaly_count": len(anomalies),
        "large_reversals": len(large_reversals),
        "anomalies": anomalies[:10],  # Return first 10 for review
        "data_quality_score": max(0, 1 - len(anomalies) / len(candles))
    }

# Run quality report on the fetched data
report = quality_report(candles, interval_minutes=1440)  # Daily candles
print(f"Data quality score: {report['data_quality_score']:.2%}")
print(f"Adjusted candles: {report['adjusted_candles']}")
print(f"Continuity anomalies: {[a for a in report['anomalies'] if a['type'] == 'continuity_gap']}")

This utility function provides a sanity check before running a backtest. A data quality score below 0.95 warrants investigation. The large_reversals field identifies bars where the close-to-close return exceeds 10%, which may indicate either a genuine market event or a residual outlier that passed through the initial screening.

When to Use Cleaned Data vs. Raw Data

TickDB provides adjusted data as the default because the majority of quantitative strategies require price continuity across corporate actions. However, there are legitimate use cases where raw, unadjusted data is preferable:

Use adjusted data (default) for: Return-based strategies, portfolio optimization, factor model construction, risk attribution, performance attribution, benchmark comparison.

Use raw data for: Corporate action analysis (detecting the split date itself is a signal), dividend capture strategy research, options pricing model calibration where the actual unadjusted price path matters.

For strategies that require both, TickDB's /v1/market/kline endpoint supports an adjust parameter with three settings: "split,div" (default, both split and dividend adjusted), "split" (split-adjusted only), and "none" (raw, unadjusted prices).

Next Steps

If you are building a backtesting pipeline and need a data source that handles corporate action adjustments, timezone normalization, outlier detection, volume validation, and continuity verification at ingestion time, the friction of implementing these steps yourself is substantial. TickDB's cleaned OHLCV dataset covers 10+ years of US equity data with a consistent adjustment standard, available via the /v1/market/kline endpoint.

If you want to explore the data quality features firsthand, sign up at tickdb.ai to generate an API key (free tier available; no credit card required) and run the code examples above against your preferred symbol and time range.

If your strategy requires tick-level order flow data, note that TickDB's trades endpoint covers HK equities and crypto markets. For US equity tick data, the consolidated tape via dedicated high-frequency data vendors is the recommended path. The /v1/market/depth endpoint provides order book depth data for US equities (L1) and HK/crypto markets (up to L10), which is useful for liquidity and spread analysis on event-driven strategies.

If you are evaluating data quality across vendors, the quality report utility above can be adapted to compare datasets from multiple sources. Run the same symbol and time range through each vendor's API and compare the data_quality_score, adjusted_candles, and anomaly_count fields.

This article does not constitute investment advice. Markets involve risk; past performance does not guarantee future results. Data cleaning methodologies described reflect TickDB's pipeline as of the article date and are subject to change with advance notice.