Apache Parquet Historical Time-Series Storage

📦 Apache Parquet Historical Time-Series Storage

Storing millions of historical price ticks and daily exchange rates directly inside a transactional SQLite database causes database bloat, degrades backup speeds, and degrades OLTP write throughput.

Kryptofolio delegates historical time-series storage to Apache Parquet files organized in a Hive-partitioned directory hierarchy.

---

🗂️ Hive Partitioning Directory Structure

Historical pricing data downloaded from market providers is persisted to disk under the following layout:

data/
└── historical/
    └── prices/
        ├── year=2024/
        │   ├── month=01/
        │   │   ├── btc_eur.parquet
        │   │   └── eth_eur.parquet
        │   └── month=12/
        └── year=2025/
            └── month=06/
                └── sol_eur.parquet

Why Hive Partitioning?

• Partition Pruning: When an analytical query requests portfolio valuations for 2025, DuckDB automatically skips reading folders for 2023 and 2024 without scanning the disk.
• Extreme Compression: Columnar storage with Snappy/ZSTD compression reduces financial time-series disk footprints by over 90% compared to traditional SQL tables or raw CSV/JSON files.

---

🚀 Federated Queries in DuckDB

DuckDB natively reads Parquet files directly from disk without requiring any import or loading step:

-- Direct federated scan with predicate pushdown:
SELECT
    price_timestamp,
    price_eur,
    volume_24h
FROM read_parquet('data/historical/prices/year=2025/**/*.parquet')
WHERE asset_symbol = 'BTC'
ORDER BY price_timestamp ASC;

---

🔄 Zero-Copy ASOF Joins with SQLite

During daily portfolio valuation, DuckDB federates data across both storage engines simultaneously:

SELECT
    tx.timestamp,
    tx.amount_in,
    p.price_eur,
    (CAST(tx.amount_in AS DECIMAL(38,18)) * p.price_eur) AS cost_basis_eur
FROM ledger.spot_transactions tx
ASOF JOIN read_parquet('data/historical/prices/**/*.parquet') p
    ON tx.asset_id = p.asset_id
    AND tx.timestamp >= p.price_timestamp;

• ledger.spot_transactions is queried live from SQLite via zero-copy attachment.
• historical_prices is streamed directly from Parquet files on disk.
• DuckDB’s vectorized query optimizer executes the join in a single pipeline without loading either dataset fully into RAM.