Storage: CSV, Parquet, partitioning, DuckDB, TimescaleDB and ArcticDB.

How market data is used

Before choosing where to store data, it is worth looking at how it is used. The data of a trading system has a very marked pattern:

• It is written in batches and almost never modified. A new session arrives every day; yesterday's does not change (except for vendor corrections, lesson 1.4.8).
• It is read many times, by date ranges. A backtest reads years; an analysis, a quarter; a chart, a day.
• It is read by columns. A moving average needs the close; a liquidity study, the volume. Almost never all the columns at once.
• It comes in long, narrow tables: millions of rows with half a dozen numeric columns and a timestamp.

This pattern favors columnar formats (Parquet) over row formats (CSV), and files organized by date over a single huge table. Databases come in when needs appear that files do not cover. The lesson uses NOVA's 5-minute bars for 2024 and 2025: 38,940 rows, the size of two years of one asset.

import os
from pathlib import Path

import pandas as pd
import pyarrow.parquet as pq

b = pd.read_parquet("nova_barras_5min.parquet")
print(len(b), "5-minute bars,",
      b["ts"].dt.date.nunique(), "sessions")
print(f"in memory: {b.memory_usage(deep=True).sum() / 1e6:.2f} MB")
b.dtypes

38940 5-minute bars, 502 sessions
in memory: 1.87 MB
ts          datetime64[us, UTC]
apertura                float64
maximo                  float64
minimo                  float64
cierre                  float64
volumen                   int64
dtype: object

Six 8-byte columns per row: 38,940 × 48 = 1.87 MB in memory. It is the reference for what follows.

CSV versus Parquet

The same DataFrame, saved in six ways. The first five are one line of pandas each; the sixth writes with PyArrow to choose each column's encoding (explained further down):

b.to_csv("barras.csv", index=False)
b.to_csv("barras.csv.gz", index=False, compression="gzip")
for codec in ["none", "snappy", "zstd"]:
    b.to_parquet(f"barras_{codec}.parquet", compression=codec)

precios = ["apertura", "maximo", "minimo", "cierre", "volumen"]
pq.write_table(
    pq.read_table("barras_zstd.parquet"), "barras_zstd_delta.parquet",
    compression="zstd", use_dictionary=precios,
    column_encoding={"ts": "DELTA_BINARY_PACKED"})

ficheros = sorted(Path(".").glob("barras*"))
tamanos = pd.Series({f.name: f.stat().st_size / 1e3 for f in ficheros})
tamanos.sort_values(ascending=False).round(0)

barras.csv                   2188.0
barras_none.parquet          1073.0
barras_snappy.parquet         841.0
barras_zstd.parquet           754.0
barras.csv.gz                 503.0
barras_zstd_delta.parquet     445.0
dtype: float64

The figure at the top plots them. Sizes are in kilobytes and do not depend on the machine: they are the same with the same versions of pandas and PyArrow. What they say:

• The CSV takes more space than the data in memory (2,188 KB versus 1.87 MB): each timestamp is a 25-character text (2024-01-02 14:30:00+00:00) and each price, several characters plus a comma.
• Uncompressed Parquet already takes half the space of the CSV, because it stores numbers in binary.
• Compression (snappy, zstd) removes another 20-30%. Snappy is faster; zstd, more compact.
• Compressing the CSV with gzip leaves it at 503 KB, less than the default zstd Parquet. But it is still text: it has to be decompressed and read in full for any query, and it still loses types.
• zstd Parquet with delta encoding on the timestamp is the smallest: 445 KB, 20% of the CSV. It is how the nova_barras_5min.parquet you download from the course is written.

Read and write times do not appear because they depend on the machine; in general, Parquet reads several times faster than CSV, because there is no text to convert into numbers.

What the CSV forgets

Size is the least of it. The serious problem is that a CSV does not store types: everything is text, and the reader has to guess. Three rows with a New York timestamp, a category and an integer with a missing value are enough to see it:

x = b.iloc[[0, 27504, -1]][["ts", "cierre"]].copy()
x["ts"] = x["ts"].dt.tz_convert("America/New_York")
x["centro"] = pd.Categorical(["PRINCIPAL", "MTF_A", "OSCURO"])
x["ordenes"] = pd.array([12, 7, None], dtype="Int64")
x.to_csv("x.csv", index=False)
x.to_parquet("x.parquet")

print(Path("x.csv").read_text())
desde_csv = pd.read_csv("x.csv", parse_dates=["ts"])
desde_pq = pd.read_parquet("x.parquet")
pd.DataFrame({"original": x.dtypes, "csv": desde_csv.dtypes,
              "parquet": desde_pq.dtypes})

ts,cierre,centro,ordenes
2024-01-02 09:30:00-05:00,43.46,PRINCIPAL,12
2025-06-02 09:30:00-04:00,53.46,MTF_A,7
2025-12-31 15:55:00-05:00,68.19,OSCURO,

                                 original      csv                           parquet
ts       datetime64[us, America/New_York]      str  datetime64[us, America/New_York]
cierre                            float64  float64                           float64
centro                           category      str                          category
ordenes                             Int64  float64                             Int64

Three losses in three columns:

• The time zone. The CSV stores 09:30:00-05:00 in January and 09:30:00-04:00 in June: the offset of each instant, not the America/New_York zone with its daylight saving rules. With different offsets, pandas cannot give the column a single datetime type and returns it as text (str), even if you ask for parse_dates. It is the problem of lesson 0.4.4, now in storage.
• The category comes back as text: the possible categories and their order are lost.
• The integer with a missing value comes back as float64: the CSV writes an empty cell, and the reader can only represent it as NaN, which does not exist in classic NumPy integers.

The Parquet file returns the three columns with their exact type, because the schema travels inside the file:

pq.read_schema("barras_zstd.parquet").remove_metadata()

ts: timestamp[us, tz=UTC]
apertura: double
maximo: double
minimo: double
cierre: double
volumen: int64

timestamp[us, tz=UTC]: microseconds with a UTC zone. remove_metadata() only hides the pandas metadata that is also stored (the metadata that rebuilds indexes and categories).

Inside a Parquet file: columns, compression and encoding

A Parquet file stores each column separately, in chunks called row groups, with statistics (minimum and maximum) for each chunk. That allows three things a CSV cannot do: read only some columns, skip row groups that do not match a filter and choose how each column is compressed. The file's metadata says how much each one takes:

def bytes_por_columna(ruta):
    rg = pq.ParquetFile(ruta).metadata.row_group(0)
    return pd.Series({rg.column(i).path_in_schema:
                      rg.column(i).total_compressed_size
                      for i in range(rg.num_columns)})

por_col = pd.DataFrame({
    "zstd": bytes_por_columna("barras_zstd.parquet"),
    "zstd_delta": bytes_por_columna("barras_zstd_delta.parquet"),
}) / 1e3
por_col.round(1)

           zstd  zstd_delta
ts        312.1         2.9
apertura   64.9        64.9
maximo     64.8        64.8
minimo     64.8        64.8
cierre     64.9        64.9
volumen   178.2       178.2

With the default settings, the timestamp is the heaviest column: 312.1 KB, more than the four price columns together. Prices compress well with a dictionary (NOVA has few distinct prices, multiples of one cent, and each value is stored as a short index into a table); timestamps do not, because they are all different. Delta encoding (DELTA_BINARY_PACKED) stores the difference between each value and the previous one. In 5-minute bars that difference is 300 seconds in 77 out of every 78 rows, and an almost constant sequence takes almost nothing: 2.9 KB. Encoding does not lose information; it prepares the data so that compression works.

Reading a column means reading only its bytes:

solo_cierre = pq.read_table("barras_zstd_delta.parquet",
                            columns=["cierre"])
total = por_col["zstd_delta"].sum()
cierre_kb = por_col.loc["cierre", "zstd_delta"]
print(solo_cierre.num_rows, "rows,", solo_cierre.num_columns,
      "column")
print(f"column bytes: {cierre_kb:.1f} KB of {total:.1f} KB"
      f" ({cierre_kb / total:.0%})")

38940 rows, 1 column
column bytes: 64.9 KB of 440.6 KB (15%)

To compute a moving average of the close, the reader touches 15% of the file. With a CSV you would have to read and convert every row and every column.

A partitioned lake

One file per asset works until the history grows and almost every query asks for a slice. The usual solution is to partition: split the data into folders by the value of one or more columns, named column=value (Hive format). The layout of a market data lake usually looks something like this:

flowchart TB
  P[Vendor] --> C[crudo/]
  C --> V{Passes the tests?}
  V -- no --> Q[cuarentena/]
  V -- yes --> L[limpio/ Parquet]
  L --> A[anio=/mes=/]
  A --> D[DuckDB and pandas]
  A --> B[Backtests]

• crudo/ (raw): what arrived from the vendor, as is, so everything can be rebuilt if the process changes.
• limpio/ (clean): the validated data (lesson 1.4.8), in Parquet, with correct types and time zone.
• Partitions by date (year and month, or day if there is a lot of volume) and, with many assets, by symbol or market.

PyArrow writes a partitioned dataset with write_dataset. The function below adds the anio (year) and mes (month) columns, which become folders, and fixes the file name (parte-0.parquet) so the result is reproducible:

import pyarrow as pa
import pyarrow.dataset as ds

def escribir_lago(df, ruta, comportamiento="error"):
    tabla = pa.Table.from_pandas(
        df.assign(anio=df["ts"].dt.year, mes=df["ts"].dt.month),
        preserve_index=False)
    formato = ds.ParquetFileFormat()
    ds.write_dataset(
        tabla, ruta, format=formato,
        file_options=formato.make_write_options(
            compression="zstd", use_dictionary=precios,
            column_encoding={"ts": "DELTA_BINARY_PACKED"}),
        partitioning=["anio", "mes"], partitioning_flavor="hive",
        basename_template="parte-{i}.parquet",
        existing_data_behavior=comportamiento)

escribir_lago(b, "lago/barras_5min")
partes = sorted(Path("lago").rglob("*.parquet"))
print(len(partes), "files,",
      f"{sum(p.stat().st_size for p in partes) / 1e3:.0f} KB")
for p in partes[:3]:
    print(p)

24 files, 623 KB
lago/barras_5min/anio=2024/mes=1/parte-0.parquet
lago/barras_5min/anio=2024/mes=10/parte-0.parquet
lago/barras_5min/anio=2024/mes=11/parte-0.parquet

Twenty-four files, one per month, taking 623 KB: more than the single file (445 KB), because each one carries its own schema and metadata and compresses with less context. It is the price of partitioning. With one stock's data in 5-minute bars, partitioning by month gives 25 KB files, too small to pay off; with minutes or ticks for hundreds of assets, the balance is the opposite. A rule of thumb: partitions of tens or hundreds of megabytes, not kilobytes.

The partition columns are not inside the files, but in the path. The reader rebuilds them and filters on them without opening anything:

junio = pd.read_parquet("lago/barras_5min",
                        filters=[("anio", "=", 2025), ("mes", "=", 6)])
print(len(junio), "bars in June 2025")
junio.dtypes[["ts", "anio", "mes"]]

1560 bars in June 2025
ts      datetime64[us, UTC]
anio               category
mes                category
dtype: object

June 2025 has 20 sessions × 78 bars = 1,560. The anio and mes columns come back as category: PyArrow reads them from the folder names as dictionary values. For calculations with them, convert them to integers.

DuckDB on the lake

DuckDB (lesson 0.5.5) queries the lake directly with SQL. hive_partitioning = true tells it to read anio and mes from the paths, and the ** wildcard walks every folder:

import duckdb

con = duckdb.connect()
consulta = """
    SELECT mes, count(*) AS barras, sum(volumen) AS volumen,
           round(max(maximo), 2) AS maximo
    FROM read_parquet('lago/barras_5min/**/*.parquet',
                      hive_partitioning = true)
    WHERE anio = 2025 AND mes BETWEEN 4 AND 6
    GROUP BY mes ORDER BY mes
"""
con.sql(consulta)

┌───────┬────────┬───────────┬────────┐
│  mes  │ barras │  volumen  │ maximo │
│ int64 │ int64  │  int128   │ double │
├───────┼────────┼───────────┼────────┤
│     4 │   1638 │ 302389399 │  55.31 │
│     5 │   1638 │ 216632338 │  55.71 │
│     6 │   1560 │ 216718377 │   57.9 │
└───────┴────────┴───────────┴────────┘

April and May have 21 sessions (1,638 bars) and June 20. To find out what DuckDB actually read, you ask for the plan with EXPLAIN. In JSON format it is easy to extract the Parquet reader's part:

import json

plan = json.loads(con.sql("EXPLAIN (FORMAT json) " + consulta)
                  .fetchone()[1])

def lector(nodo):
    if nodo["name"] == "READ_PARQUET":
        return nodo["extra_info"]
    return next(filter(None, map(lector, nodo["children"])), None)

info = lector(plan[0])
for clave in ["Projections", "File Filters", "Scanning Files"]:
    print(f"{clave}: {info[clave]}")

Projections: ['mes', 'volumen', 'maximo']
File Filters: (anio = 2025)(mes BETWEEN 4 AND 6)
Scanning Files: 3/24

Three items in the plan, all three important:

• Projections: it only reads the mes, volumen and maximo columns. Neither the open, low and close prices nor the timestamp.
• File Filters: the query's filter is applied to the paths, before opening any file.
• Scanning Files: 3/24: of the lake's 24 files, it opens three. With ten years of minutes for a thousand assets, that ratio is the difference between a one-second query and one of several minutes.

The filter can only discard files if it is written on the partition columns. WHERE ts >= '2025-04-01' returns the same, but DuckDB has to look at each file's statistics to discard it; that is why your usual queries should match the way you partition.

Adding data without rewriting

The biggest practical advantage of partitioning is incremental updating: new data goes into new folders, and the old data is not touched. With existing_data_behavior="overwrite_or_ignore", PyArrow writes the new files and leaves the others as they are:

import shutil

shutil.rmtree("lago")
hasta_nov = b[b["ts"] < "2025-12-01"]
escribir_lago(hasta_nov, "lago/barras_5min")
antes = len(list(Path("lago").rglob("*.parquet")))

diciembre = b[b["ts"] >= "2025-12-01"]
escribir_lago(diciembre, "lago/barras_5min",
              comportamiento="overwrite_or_ignore")
despues = len(list(Path("lago").rglob("*.parquet")))
print(f"files: {antes} → {despues}")
n = con.sql("SELECT count(*) FROM read_parquet("
            "'lago/barras_5min/**/*.parquet')").fetchone()[0]
print(n, "bars in the lake")

files: 23 → 24
38940 bars in the lake

DuckDB can also store the data in its own format, a single database file with tables, indexes and transactions. It is useful when several queries share intermediate tables or when you want named views:

db = duckdb.connect("mercado.duckdb")
db.sql("""
    CREATE OR REPLACE TABLE barras AS
    SELECT * FROM read_parquet('lago/barras_5min/**/*.parquet',
                               hive_partitioning = true)
""")
print(db.sql("SELECT count(*), min(ts)::VARCHAR, max(ts)::VARCHAR"
             " FROM barras").fetchone())
db.close()

(38940, '2024-01-02 14:30:00+00', '2025-12-31 20:55:00+00')

The timestamps come out as text with ::VARCHAR so as not to depend on how DuckDB converts time-zone-aware dates into Python objects. The first bar is the one at 14:30 UTC on January 2, 2024 (9:30 in New York, on winter time) and the last one at 20:55 UTC on December 31, 2025.

Databases: PostgreSQL with TimescaleDB and ArcticDB

Parquet files and DuckDB cover almost all the work of a researcher who writes in batches and reads a lot. Databases provide what files lack: a server that several processes connect to at once, safe concurrent writes, transactions, row-by-row updates and access control. Two common options in trading, with very different approaches.

PostgreSQL with TimescaleDB. PostgreSQL is the reference open source relational database; TimescaleDB is an extension that turns a table into a hypertable, automatically split into chunks by time, with columnar compression and functions such as time_bucket to aggregate by intervals. According to its documentation, since version 2.23.0 CREATE TABLE … WITH (tsdb.hypertable) is enough, partitioning by the first timestamp column; earlier versions use the create_hypertable function. The code below needs a server with the extension installed, so it does not run in the course:

import psycopg  # pip install "psycopg[binary]"

DDL = """
CREATE TABLE IF NOT EXISTS barras_5min (
    ts       TIMESTAMPTZ      NOT NULL,
    simbolo  TEXT             NOT NULL,
    apertura DOUBLE PRECISION, maximo DOUBLE PRECISION,
    minimo   DOUBLE PRECISION, cierre DOUBLE PRECISION,
    volumen  BIGINT,
    PRIMARY KEY (simbolo, ts)
) WITH (tsdb.hypertable, tsdb.segmentby = 'simbolo',
        tsdb.orderby = 'ts DESC');
"""
with psycopg.connect("postgresql://usuario@localhost/mercado") as cx:
    cx.execute(DDL)
    with cx.cursor().copy(
        "COPY barras_5min (ts, simbolo, apertura, maximo, minimo,"
        " cierre, volumen) FROM STDIN"
    ) as copia:
        for fila in b.assign(simbolo="NOVA")[
                ["ts", "simbolo", *precios]].itertuples(index=False):
            copia.write_row(fila)
    horas = cx.execute("""
        SELECT time_bucket('1 hour', ts) AS hora, sum(volumen)
        FROM barras_5min WHERE simbolo = 'NOVA'
        GROUP BY hora ORDER BY hora LIMIT 3
    """).fetchall()

TIMESTAMPTZ stores instants in UTC and displays them in the session's zone: there is no time zone ambiguity. The primary key (simbolo, ts) prevents loading the same bar twice, something a file lake does not check on its own. COPY is the fast way to load many rows (inserting them one by one with INSERT is much slower), and the query would return the first three hours of 2024 with their summed volume.

ArcticDB. It is a time series database for DataFrames, developed by Man Group and used in the site's research series on big data (see the resources). Its code is public, but its license is the Business Source License 1.1: according to its repository, production use requires a paid license11Note 1 · ArcticDB repository and license: https://github.com/man-group/ArcticDB, accessed on October 21, 2026. The terms of use of a piece of software, like those of data (lesson 1.3.7), have to be read before building a system on it.. It is not relational and needs no server: it stores the data in object storage (S3 or compatible) or on local disk (LMDB), and each write creates a new version of the symbol, so you can read the data as it was before a correction. It is not installed in the course environment; this is the basic use of its API:

from arcticdb import Arctic  # pip install arcticdb

ac = Arctic("lmdb://datos/arctic")      # or s3://… in production
lib = ac.get_library("barras_5min", create_if_missing=True)

serie = b.set_index("ts")
lib.write("NOVA", serie.loc[:"2025-11"])   # version 0
lib.append("NOVA", serie.loc["2025-12"])   # version 1: at the end
junio = lib.read("NOVA", date_range=(pd.Timestamp("2025-06-01",
                 tz="UTC"), pd.Timestamp("2025-06-30", tz="UTC")),
                 columns=["cierre", "volumen"]).data
antes = lib.read("NOVA", as_of=0).data  # the previous version

write stores the history up to November and append adds December after it: what is appended has to start after the end of what is already there. To correct an intermediate stretch there is update, which replaces the dates covered by the new data. read returns an object with the data (.data) and the metadata; date_range and columns do the same as Parquet's filters and columns, and as_of=0 reads version 0, the one before December was appended.

What to use and when

For an individual researcher with dozens of assets, the combination of a partitioned Parquet lake and DuckDB is hard to beat: no server, fast and with open formats that any tool reads. TimescaleDB makes sense when a production system writes data in real time while other processes read it; ArcticDB, when there are many symbols, several people and you need to know which data was used at each moment.

Exercises

1. Save NOVA's trades (nova_operaciones.parquet) as CSV and as zstd Parquet. How much space does each take and which column weighs most in the Parquet file?

op = pd.read_parquet("nova_operaciones.parquet")
op.to_csv("operaciones.csv", index=False)
op.to_parquet("operaciones_zstd.parquet", compression="zstd")
for f in ["operaciones.csv", "operaciones_zstd.parquet"]:
    print(f"{f}: {os.path.getsize(f) / 1e3:.0f} KB")
(bytes_por_columna("operaciones_zstd.parquet") / 1e3).round(1)

operaciones.csv: 3357 KB
operaciones_zstd.parquet: 340 KB
ts           234.6
precio        25.7
tamano        55.5
agresor        6.4
centro        13.7
condicion      0.3
dtype: float64

2. With DuckDB and the lake, find the 2024 session with the most volume. Write the filter so that DuckDB only opens the 2024 files.

con.sql("""
    SELECT CAST(timezone('America/New_York', ts) AS DATE) AS sesion,
           sum(volumen) AS volumen
    FROM read_parquet('lago/barras_5min/**/*.parquet',
                      hive_partitioning = true)
    WHERE anio = 2024
    GROUP BY sesion ORDER BY volumen DESC LIMIT 3
""")

┌────────────┬──────────┐
│   sesion   │ volumen  │
│    date    │  int128  │
├────────────┼──────────┤
│ 2024-10-25 │ 51399629 │
│ 2024-07-26 │ 47635706 │
│ 2024-01-26 │ 43678960 │
└────────────┴──────────┘

3. A colleague saves a CSV every day with the 1-minute bars of 500 stocks, in Madrid time, and uploads it to a shared folder. Three others read that folder for their backtests. Propose a better way to store them and explain which problems it solves.