--- title: "6.6.4 Time series databases and ArcticDB" description: "ArcticDB run for real (versions, snapshots, append, update, QueryBuilder, dynamic schema) and TimescaleDB, QuestDB, ClickHouse and kdb+ from their docs." url: "https://pythonparatrading.com/en/python-trading-course/level-6/time-series-databases-and-arcticdb/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-6/time-series-databases-and-arcticdb.png" --- # 6.6.4 · Time series databases: TimescaleDB, QuestDB, ClickHouse, ArcticDB and kdb+ > Market data is written at the end, read by symbol and date, aggregated into intervals and joined on the previous timestamp. This lesson stores bars for 50 stocks in ArcticDB, fixes a vendor error without losing the version a research study used, queries inside the store and joins trades with quotes; then it compares TimescaleDB, QuestDB, ClickHouse and kdb+ using their documentation, without marketing figures. - Course: [Level 6: Generative AI, LLMs, agents, big data and alternative data](https://pythonparatrading.com/en/python-trading-course/level-6/) - Module: 6.6 · Big data for trading - Duration: 65 min - Prerequisites: [1.4.7 · Storage: CSV, Parquet, partitioning, DuckDB, TimescaleDB and ArcticDB](https://pythonparatrading.com/en/python-trading-course/level-1/storing-market-data-parquet-duckdb-timeseries/), [1.1.1 · Market participants and trading venues](https://pythonparatrading.com/en/python-trading-course/level-1/market-participants-and-trading-venues/), [6.6.2 · Parquet, Arrow, Polars streaming and out-of-core DuckDB](https://pythonparatrading.com/en/python-trading-course/level-6/parquet-arrow-polars-streaming-duckdb/) ## By the end of this lesson - Distinguish row-based from columnar storage and recognize the four access patterns of market data: append at the end, read by symbol and date, aggregate into intervals and join on the previous timestamp. - Write, read by range and columns, append and correct series in ArcticDB, and reproduce a research study with versions and snapshots. - Query inside the store with QueryBuilder (filters, fixed-frequency resampling, groupby) and know its limits and those of the dynamic schema. - Perform an as-of join without look-ahead and place its equivalent in kdb+, QuestDB and ClickHouse. - Choose between ArcticDB, TimescaleDB, QuestDB, ClickHouse, kdb+ and a Parquet lake according to the shape of the work, not vendor benchmarks. ## What is special about market data A market data table does not look like the table of a business application. It is almost always written **at the end** (the next minute arrives, yesterday's is not modified), it is read by **symbol and date range**, it is aggregated into **time intervals** (5-minute, hourly, daily bars) and it is joined with other series on the **closest previous timestamp**, not on exact equality (a trade with the quote in force at that instant). Time series databases are built around those four patterns. Lesson 1.4.7 introduced two (TimescaleDB and ArcticDB) in one line each; this one compares them with QuestDB, ClickHouse and kdb+ based on their documentation, and runs ArcticDB, which is the only one that works as a Python library without a server. The first difference between them is how they store rows. A **row-based** database (PostgreSQL) stores all the columns of each row together: it is good for inserting and reading individual rows. A **columnar** database (ClickHouse, QuestDB, kdb+, ArcticDB and the compressed part of TimescaleDB) stores the values of each column together, like Parquet (lesson 6.6.2): it is good for reading few columns of many rows and compresses much better. ```mermaid flowchart LR F[Row-based] --> F1[ts, sim, precio, vol] F1 --> F2[ts, sim, precio, vol] C[Columnar] --> C1[ts ts ts ts] C --> C2[precio precio precio] C --> C3[vol vol vol vol] ``` ## ArcticDB: libraries, symbols and versions ArcticDB, from Man Group, stores pandas `DataFrame`s in object storage (S3, Azure) or on local disk with LMDB, without a server: the Python library talks directly to the storage[^1]. Its model has four levels: ```mermaid flowchart TB A[Arctic: store] --> L[Library] L --> S1[Symbol BAGO] L --> S2[Symbol BAKA] S1 --> V0[Version 0] S1 --> V1[Version 1] V1 --> SG[Segments] ``` A **store** (`Arctic`) is opened with a URI (`lmdb://folder`, `s3://…`); it contains **libraries**, which group **symbols** (each one a `DataFrame` with a date index); each write creates a new **version** of the symbol, and each version is stored split into **segments** of rows and columns. ArcticDB is in the course's `zipline` environment because, up to its version 6.28.1, it requires pandas 2; its license is the Business Source License 1.1, which according to its repository converts each version to Apache 2.0 two years after its release and does not allow offering it as a database service to third parties[^2]. The lesson uses an LMDB store in a temporary folder: ```python import tempfile import arcticdb as adb import exchange_calendars as xcals import numpy as np import pandas as pd from arcticdb.config import set_log_level set_log_level("OFF") # no traces with time and process in the output ruta = tempfile.mkdtemp(prefix="arctic_", dir=".") ac = adb.Arctic(f"lmdb://{ruta}") lib = ac.create_library("barras_1min") print(adb.__version__, pd.__version__, ac.list_libraries()) ``` ```output 6.28.1 2.3.3 ['barras_1min'] ``` `set_log_level("OFF")` turns off ArcticDB's logging, which writes errors to the output with the time and the process number before raising the exception. In production it is better to leave it on. The data: synthetic 1-minute bars (close and volume) for 50 stocks from the level 5 panel over the 60 sessions of the first quarter of 2025, with a daily volatility of 2%. So that the lesson tells a realistic story, the “vendor” first sends January and February, with an error: three BAGO minutes on February 14 with the price multiplied by ten. `write_batch` writes the 50 symbols in one call. ```python tickers = sorted(pd.read_csv("panel5_activos.csv")["ticker"])[:50] cal = xcals.get_calendar("XNYS") minutos = cal.minutes_in_range( cal.session_open("2025-01-02"), cal.session_close("2025-03-31") - pd.Timedelta("1min")) rng = np.random.default_rng(664) def barras(n): r = rng.normal(0, 0.02 / np.sqrt(390), n) # 2% daily return pd.DataFrame({ "cierre": (50 * np.exp(np.cumsum(r))).round(2), "volumen": rng.integers(100, 5000, n)}, index=minutos) datos = {s: barras(len(minutos)) for s in tickers} corte = pd.Timestamp("2025-03-01", tz="UTC") # the vendor sends three wrong BAGO minutes on February 14 malos = slice("2025-02-14 15:00Z", "2025-02-14 15:02Z") v0 = {s: d.loc[:corte].copy() for s, d in datos.items()} v0["BAGO"].loc[malos, "cierre"] *= 10 r = lib.write_batch([adb.WritePayload(s, d) for s, d in v0.items()]) print(len(r), "symbols; BAGO version:", r[0].version) print(len(minutos), "minutes in the quarter;", len(v0["BAGO"]), "written through February") print(lib.get_description("BAGO").date_range) ``` ```output 50 symbols; BAGO version: 0 23400 minutes in the quarter; 15210 written through February (Timestamp('2025-01-02 14:30:00+0000', tz='UTC'), Timestamp('2025-02-28 20:59:00+0000', tz='UTC')) ``` 50 symbols in their version 0. Each symbol has 15,210 minutes (39 sessions × 390) of the quarter's 23,400. `get_description` reads only the metadata: columns, types, number of rows and the date range, from January 2 at 14:30 UTC to February 28 at 20:59 UTC (the last bar of the winter-time session). Reading accepts a range and some columns, like Parquet filters; ArcticDB only downloads the segments that fall in the range: ```python tramo = lib.read("BAGO", date_range=(pd.Timestamp("2025-02-14 14:58Z"), pd.Timestamp("2025-02-14 15:03Z")), columns=["cierre"]) print(tramo.symbol, "version", tramo.version) tramo.data ``` ```output BAGO version 0 cierre 2025-02-14 14:58:00+00:00 53.29 2025-02-14 14:59:00+00:00 53.25 2025-02-14 15:00:00+00:00 533.20 2025-02-14 15:01:00+00:00 532.70 2025-02-14 15:02:00+00:00 532.00 2025-02-14 15:03:00+00:00 53.17 ``` There is the error: from 53.25 to 533.20 and back to 53.17. A backtest that uses this version will see a 900% jump in BAGO. Before fixing it, a **snapshot** is saved: a named picture of the version of each symbol in the library, so that what a research study done with this data saw can be reproduced exactly. ## Versions, corrections and snapshots Then the March data arrives (`append`, which adds at the end) and the vendor's correction (`update`, which replaces the date range covered by the new data). Each operation is a version, and `as_of` reads any of them: ```python lib.snapshot("investigacion-2025-03-01") # picture of the library for s, d in datos.items(): lib.append(s, d.loc[corte:]) # version 1 lib.update("BAGO", datos["BAGO"].loc[malos]) # version 2 print("versions:", sorted(k.version for k in lib.list_versions("BAGO"))) def minuto_15h(**kw): d = lib.read("BAGO", date_range=(pd.Timestamp("2025-02-14 15:00Z"), pd.Timestamp("2025-02-14 15:00Z")), **kw) return d.version, len(lib.read("BAGO", **kw).data), \ float(d.data["cierre"].iloc[0]) pd.DataFrame([minuto_15h(as_of=v) for v in (0, 1, 2)] + [minuto_15h(as_of="investigacion-2025-03-01")], columns=["versión", "filas", "cierre 15:00"], index=["as_of=0", "as_of=1", "as_of=2", "snapshot"]) ``` ```output versions: [0, 1, 2] versión filas cierre 15:00 as_of=0 0 15210 533.20 as_of=1 1 23400 533.20 as_of=2 2 23400 53.32 snapshot 0 15210 533.20 ``` The figure in the header plots BAGO's history: - **Version 0**: January and February (15,210 rows), with the error. - **Version 1**: the March `append`; 23,400 rows and the error is still there. - **Version 2**: the `update` of the three minutes; the rows do not change and the 15:00 close becomes 53.32. - **The snapshot** points to version 0: whoever wants to repeat the March 1 research study reads exactly the data it had, error included. It is the same idea as *point-in-time* fundamental data (lesson 1.4.4) applied to the store itself: what matters is not only what the data says, but what it said on the day it was used. A backtest that saves the snapshot name (or the version number) alongside its results is reproducible; one that reads “the latest” is not, because the latest changes. `append` requires the new data to start **after** the end of what is there. Appending two minutes that already exist fails: ```python raises lib.append("BAGO", datos["BAGO"].loc["2025-03-31 19:58Z":]) ``` ```output arcticdb_ext.exceptions.InternalException: E_ASSERTION_FAILURE Can't append dataframe with start index 2025-03-31 19:58:00.000000000 to existing sequence ending at 2025-03-31 19:59:00.000000001 ``` To rewrite an existing range there is `update`, and to delete it, `delete_data_in_range`. The reason for the rule is the segment structure: appending at the end only creates new segments; inserting in the middle would require rewriting the existing ones, which is what `update` does explicitly. Versions take up space: each `update` and each `append` stores new segments and the previous version keeps its own. `prune_previous_versions` deletes old versions, except those kept by some snapshot: ```python lib.prune_previous_versions("BAGO") vivas = lib.list_versions("BAGO") print(sorted(k.version for k in vivas)) print(lib.read("BAGO", as_of="investigacion-2025-03-01").version) ``` ```output [0, 2] 0 ``` Version 2 (the latest) and version 0, protected by the snapshot, remain; version 1 has been deleted. The usual policy: prune intermediate versions regularly and keep, with named snapshots, those used by research studies or models in production. ## Queries inside the store: QueryBuilder Reading an entire symbol to filter afterwards in pandas downloads everything. `QueryBuilder` describes filters, computed columns, groupings and resamplings that ArcticDB runs in its engine (C++) before returning the result[^3]: ```python q = adb.QueryBuilder() q = q[(q["volumen"] > 4900) & (q["cierre"] > 50)] grandes = lib.read("BAGO", query_builder=q).data print(len(grandes), "minutes with volume > 4900 and close > 50") q = adb.QueryBuilder().resample("1h").agg( {"cierre": "last", "volumen": "sum"}) horas = lib.read("BAGO", query_builder=q, date_range=(pd.Timestamp("2025-03-03", tz="UTC"), pd.Timestamp("2025-03-04", tz="UTC"))).data horas[["cierre", "volumen"]] ``` ```output 244 minutes with volume > 4900 and close > 50 cierre volumen 2025-03-03 14:00:00+00:00 51.93 83768 2025-03-03 15:00:00+00:00 51.25 161355 2025-03-03 16:00:00+00:00 50.97 167040 2025-03-03 17:00:00+00:00 50.01 159946 2025-03-03 18:00:00+00:00 50.46 141475 2025-03-03 19:00:00+00:00 50.45 148767 2025-03-03 20:00:00+00:00 50.97 159973 ``` 244 BAGO minutes with volume above 4,900 and a close above 50. The 1-hour resampling of March 3 labels each bar by its start, like pandas: the 14:00 UTC one contains only half an hour (the session opens at 14:30 UTC in winter), and that is why its volume is half that of the others. Resampling has a limit worth knowing: it only supports fixed frequencies. ```python raises q = adb.QueryBuilder().resample("ME").agg({"cierre": "last"}) lib.read("BAGO", query_builder=q) ``` ```output arcticdb.exceptions.ArcticDbNotYetImplemented: Frequency string 'ME' not yet supported. Valid frequency strings are ns, us, ms, s, min, h, D, and multiples/combinations thereof such as 1h30min ``` Neither months (`ME`) nor weeks (`W`): a month has no fixed duration. For monthly bars, resample to days in the store and to months in pandas, or read and resample everything in pandas. A different pattern is to store many assets in a single symbol, in long format (a `simbolo` column), like a table in a relational database. That is what `groupby` and membership filters are for: ```python marzo = pd.concat( [d.loc[corte:].assign(simbolo=s) for s, d in datos.items()] ).sort_index(kind="stable") lib.write("panel_marzo", marzo) q = adb.QueryBuilder() q = q[q["simbolo"].isin(["BAGO", "BAKA", "BAKU"])] q = q.groupby("simbolo").agg({"volumen": "sum", "cierre": "max"}) print(len(marzo), "rows in the long table") lib.read("panel_marzo", query_builder=q).data.sort_index() ``` ```output 409500 rows in the long table cierre volumen simbolo BAGO 54.21 21174687 BAKA 48.76 21086537 BAKU 48.72 20773982 ``` 409,500 rows (50 symbols × 8,190 March minutes) and the total volume and maximum close of three of them, computed in the store. The order of the groups ArcticDB returns is not guaranteed; hence `sort_index()`. One symbol per asset or a long table? One symbol per asset allows independent versions and `append` per asset and direct reads per asset; a long table simplifies cross-sections (all assets in one minute) at the cost of rewriting more when one changes. With ArcticDB the usual approach is one symbol per asset and `read_batch` to read several. ## Static and dynamic schema By default, all versions of a symbol have the same columns and types. Adding a new column (for example, the number of trades per minute, which the vendor starts sending in April) fails with `StreamDescriptorMismatch`. A library with `dynamic_schema=True` allows it: ```python nuevo = pd.DataFrame( {"cierre": [51.0], "volumen": [100], "operaciones": [7]}, index=[pd.Timestamp("2025-04-01 13:30Z")]) try: lib.append("BAGO", nuevo) except adb.exceptions.ArcticException as e: print(type(e).__name__) dinamica = ac.create_library( "dinamica", library_options=adb.LibraryOptions(dynamic_schema=True)) dinamica.write("BAGO", datos["BAGO"].iloc[-2:]) dinamica.append("BAGO", nuevo) dinamica.read("BAGO").data ``` ```output StreamDescriptorMismatch cierre volumen operaciones 2025-03-31 19:58:00+00:00 52.27 4416 0 2025-03-31 19:59:00+00:00 52.20 626 0 2025-04-01 13:30:00+00:00 51.00 100 7 ``` The dynamic library accepts the new column, but watch what it puts in the old rows: **0**, not a null. The two March bars appear with `operaciones = 0`, which is false data (there were not zero trades; nobody knew how many there were). With floating-point columns it fills with `NaN`; with integers, with zero. A model that uses that column would learn from invented zeros. The practical rule: new columns of type `float`, or a separate column that says since when the data exists. ## As-of joins: the query that defines a time series database The most characteristic operation on market data is to join each trade with the quote in force at that instant: the last previous one, not one with the same timestamp (lesson 1.1.1). It is called an *as-of join*, and it is the reason for being of a good part of the databases in this field: kdb+ has it as `aj`, QuestDB and ClickHouse as `ASOF JOIN`, and DuckDB too. In pandas it is `merge_asof`. The cell generates 120 quote changes and 25 trades in one minute and joins each trade with the previous quote: ```python rng_q = np.random.default_rng(6640) t0 = pd.Timestamp("2025-03-14 15:00Z") ts_q = t0 + pd.to_timedelta( np.sort(rng_q.choice(60_000, 120, replace=False)), unit="ms") medio = 50 + np.cumsum(rng_q.choice([-0.01, 0, 0.01], 120)) cot = pd.DataFrame({"ts": ts_q, "bid": medio - 0.01, "ask": medio + 0.01}) ts_t = t0 + pd.to_timedelta( np.sort(rng_q.choice(60_000, 25, replace=False)), unit="ms") ops = pd.DataFrame({"ts": ts_t, "precio": 0.0}) unidas = pd.merge_asof(ops, cot.assign(ts_cot=cot["ts"]), on="ts", direction="backward", allow_exact_matches=False) unidas["precio"] = np.where(rng_q.random(25) < 0.5, unidas["bid"], unidas["ask"]).round(2) unidas["retraso_ms"] = (unidas["ts"] - unidas["ts_cot"]) \ .dt.total_seconds().mul(1000).round() print(unidas["retraso_ms"].describe()[["mean", "50%", "max"]] .round(1).to_dict()) unidas[["ts", "ts_cot", "bid", "ask", "precio"]].head(4) ``` ```output {'mean': 564.8, '50%': 368.0, 'max': 1667.0} ts ts_cot bid ask precio 0 2025-03-14 15:00:02.080000+00:00 2025-03-14 15:00:01.712000+00:00 49.99 50.01 49.99 1 2025-03-14 15:00:05.046000+00:00 2025-03-14 15:00:04.027000+00:00 49.99 50.01 50.01 2 2025-03-14 15:00:08.231000+00:00 2025-03-14 15:00:07.575000+00:00 49.98 50.00 50.00 3 2025-03-14 15:00:09.762000+00:00 2025-03-14 15:00:08.806000+00:00 49.96 49.98 49.96 ``` *Fig. — As-of join over the first 12 seconds: each trade is joined to the last earlier quote, never to one at the same instant.* Two lanes on a 0 to 12 second axis. Top, 20 quote changes; bottom, 6 trades. A line links each trade to the closest earlier quote; the one at 9.762 s, highlighted, links to the quote at 8.806 s, 956 milliseconds earlier. Each trade gets the closest previous quote (`ts_cot`): on average, 565 milliseconds earlier, with a maximum of 1.7 seconds. `allow_exact_matches=False` excludes a quote with the same timestamp as the trade, which could be the one that trade has just caused. In a database with a native *as-of join* this query runs over billions of rows, per symbol, without bringing the data into Python. ## TimescaleDB, QuestDB, ClickHouse and kdb+ All four need a server and are not run in the course. What follows comes from their official documentation, accessed on October 23, 2026; the code has been checked against it, not run. **TimescaleDB** (from Tiger Data) is a PostgreSQL extension. **Hypertables** are PostgreSQL tables that are automatically split into *chunks* by time; its Hypercore engine stores recent data by rows and later converts it to compressed columns; `time_bucket` aggregates into arbitrary intervals, and **continuous aggregates** are materialized views that are refreshed in the background when data arrives[^4]. Lesson 1.4.7 showed how to create a hypertable; a continuous aggregate of hourly bars: ```python skip title="timescale_agregado.py" import psycopg SQL = [ """ CREATE MATERIALIZED VIEW barras_1h WITH (timescaledb.continuous) AS SELECT simbolo, time_bucket(INTERVAL '1 hour', ts) AS hora, last(cierre, ts) AS cierre, sum(volumen) AS volumen FROM barras_1min GROUP BY simbolo, hora """, """ SELECT add_continuous_aggregate_policy('barras_1h', start_offset => INTERVAL '1 week', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '30 minutes') """, ] with psycopg.connect("postgresql://localhost/mercado", autocommit=True) as cx: for sentencia in SQL: cx.execute(sentencia) ``` TimescaleDB's strength is that it is PostgreSQL: full SQL, transactions, indexes, permissions and its whole ecosystem. Its weakness, that it is an adapted general-purpose database: for columnar analysis of billions of rows, natively columnar databases usually need less tuning. **QuestDB** is a columnar database with one **designated timestamp** per table, which enables time partitioning, `SAMPLE BY` (aggregation into intervals), `LATEST ON` (the last value per key) and `ASOF JOIN`[^5]. Its Python client 5.x writes complete `DataFrame`s and reads results as Arrow or pandas; the documentation describes the InfluxDB protocol (ILP) as a compatibility protocol and recommends its own protocol (QWP) for new installations: ```python skip title="questdb_asof.py" import questdb # pip install -U questdb with questdb.connect("ws::addr=localhost:9000;") as db: db.dataframe(ops, table_name="operaciones", symbols=["simbolo"], at="ts") db.dataframe(cot, table_name="cotizaciones", symbols=["simbolo"], at="ts") unidas = db.query(""" SELECT o.ts, o.precio, c.bid, c.ask FROM operaciones o ASOF JOIN cotizaciones c ON (simbolo) """).to_pandas() horas = db.query(""" SELECT ts, simbolo, last(cierre), sum(volumen) FROM barras_1min SAMPLE BY 1h """).to_pandas() ``` **ClickHouse** is a general-purpose columnar analytics (OLAP) database. Its `MergeTree` engine sorts each table by the `ORDER BY` key, which acts as a **sparse** primary key: it does not point to rows but to blocks of 8,192 rows (*granules*), and discards blocks like Parquet statistics do. It has `ASOF JOIN` with one equality column and one inequality column[^6]: ```python skip title="clickhouse_asof.py" import clickhouse_connect # pip install clickhouse-connect cliente = clickhouse_connect.get_client(host="localhost") cliente.command(""" CREATE TABLE IF NOT EXISTS cotizaciones ( simbolo LowCardinality(String), ts DateTime64(3, 'UTC'), bid Float64, ask Float64 ) ENGINE = MergeTree ORDER BY (simbolo, ts) """) unidas = cliente.query_df(""" SELECT o.ts, o.precio, c.bid, c.ask FROM operaciones o ASOF JOIN cotizaciones c ON o.simbolo = c.simbolo AND o.ts >= c.ts """) ``` The `ORDER BY (simbolo, ts)` is the same decision as the write order in lesson 6.6.2: queries by symbol and time range discard almost all blocks. **kdb+** (from KX) is the historical reference of banks and funds for market data: an in-memory and on-disk columnar database with its own language, q. On disk, a *splayed* table stores each column in a file and a *partitioned* table spreads the *splayed* tables across folders (usually by date): the same design as a Parquet lake, decades earlier[^7]. Its `aj` is the canonical *as-of join*. From Python it is used with PyKX (now “KDB-X Python”); KX offers a free Community edition for personal and commercial use with limits (16 GB of RAM for q, 24 cores, one instance)[^7]: ```python skip title="kdb_aj.py" import pykx as kx # pip install pykx (requires a KDB-X license) kx.q["t"] = ops # pandas to a q table kx.q["q"] = cot unidas = kx.q("aj[`simbolo`ts; t; q]").pd() ``` `aj[`simbolo`ts; t; q]` joins each row of `t` with the last row of `q` with the same `simbolo` whose `ts` is less than or equal. ## Comparison of models | | Storage | Server | Writes | As-of join | Versions | Language | |---|---|---|---|---|---|---| | ArcticDB | Columns, segments in object storage or LMDB | No (library) | `write`, `append`, `update` | No (pandas after reading) | Yes, with snapshots | Python | | TimescaleDB | Rows and compressed columns (Hypercore) | PostgreSQL | SQL, transactions | Not native | No | SQL | | QuestDB | Columns, time partitioning | Yes | Own protocol, ILP, SQL | `ASOF JOIN` | No | SQL | | ClickHouse | Columns, `MergeTree` | Yes | Batch SQL | `ASOF JOIN` | No | SQL | | kdb+ | Columns, *splayed* and partitioned | Yes (q process) | q | `aj` | No | q | | Parquet + DuckDB (6.6.2) | Columns in files | No | Batch files | `ASOF JOIN` | By hand (or lakehouse, 6.6.5) | SQL | The table summarizes what each one's documentation says, not its performance: the speed comparisons published by the vendors themselves measure what suits them and are not reproduced here. The choice depends more on the shape of the work than on microseconds. A researcher or a small team with DataFrames and a need for reproducible versions fits ArcticDB; a system that already lives in PostgreSQL, TimescaleDB; continuous tick ingestion with live SQL queries, QuestDB or ClickHouse; and a team with q experience and budget, kdb+. For research on your own, the Parquet lake with DuckDB from lesson 6.6.2 is still hard to beat. [^1]: ArcticDB, introduction and `Library` and `Arctic` API (LMDB, `LibraryOptions`): and , accessed October 23, 2026. [^2]: ArcticDB license (Business Source License 1.1): , accessed October 23, 2026. [^3]: ArcticDB, “DataFrame Processing Operations API”: , accessed October 23, 2026. [^4]: Tiger Data, hypertables, `time_bucket`, continuous aggregates and Hypercore: , accessed October 23, 2026. [^5]: QuestDB, designated timestamp, `SAMPLE BY`, `ASOF JOIN` and Python client: and , accessed October 23, 2026. [^6]: ClickHouse, `MergeTree` and sparse primary indexes, `ASOF JOIN` and Python client: , accessed October 23, 2026. [^7]: KX, `aj`, *splayed* and partitioned tables, KDB-X Python and Community license: and , accessed October 23, 2026. ## Exercises 1. Read with `read_batch` the March closes of BAGO, BAKA and BAKU and compute the correlation of their 1-minute log returns. What value do you expect, knowing how they were generated? **Solution.** ```python marzo_ini = pd.Timestamp("2025-03-03", tz="UTC") lotes = lib.read_batch( [adb.ReadRequest(s, date_range=(marzo_ini, None), columns=["cierre"]) for s in ["BAGO", "BAKA", "BAKU"]]) cierres = pd.DataFrame({r.symbol: r.data["cierre"] for r in lotes}) rend = np.log(cierres).diff().dropna() print([r.version for r in lotes], len(rend), "returns") rend.corr().round(3) ``` ```output [2, 1, 1] 8189 returns BAGO BAKA BAKU BAGO 1.000 -0.007 0.009 BAKA -0.007 1.000 -0.006 BAKU 0.009 -0.006 1.000 ``` `read_batch` returns one read per symbol with its version (2 for BAGO, because of the correction; 1 for the other two). The correlations are between −0.007 and 0.009: zero, apart from sampling noise, because each series was generated with its own independent random walk. With 8,189 returns, the standard error of a null correlation is 1/√8,189 ≈ 0.011, so none can be distinguished from zero. With real data, stocks in the same market have positive 1-minute correlations, although lower than daily ones because of microstructure noise (the Epps effect). 2. Compute BAGO's daily volume in March with `QueryBuilder` (`resample("1D")`) and check it with pandas on the data read. **Solution.** ```python q = adb.QueryBuilder().resample("1D").agg({"volumen": "sum"}) en_almacen = lib.read("BAGO", query_builder=q, date_range=(marzo_ini, None)).data["volumen"] leido = lib.read("BAGO", date_range=(marzo_ini, None)).data en_pandas = leido["volumen"].resample("1D").sum() en_pandas = en_pandas[en_pandas > 0] # no weekends print(len(en_almacen), "sessions;", "equal:", en_almacen.equals(en_pandas)) en_almacen.head(3) ``` ```output 21 sessions; equal: True 2025-03-03 00:00:00+00:00 1022324 2025-03-04 00:00:00+00:00 984597 2025-03-05 00:00:00+00:00 998865 Name: volumen, dtype: int64 ``` The 21 March sessions match. pandas' daily resampling also creates Saturdays and Sundays with zero volume, which have to be removed; ArcticDB only returns the days with data. The first bar, for March 3, adds up to 1,022,324 shares in 390 minutes. The labels are 00:00 UTC of each day: for markets that cross UTC midnight (futures, currencies, crypto), a UTC day is not a session, and it is better to resample by session (lesson 1.1.2) rather than by calendar day. ```python import shutil from pathlib import Path del lib, dinamica, ac shutil.rmtree(ruta) print(sorted(p.name for p in Path(".").glob("arctic_*"))) ``` ```output [] ``` 3. A small fund currently stores the ticks of 300 stocks in daily CSV files. It wants three things: that analysts can read bars for any period from Python, to be able to repeat a backtest a year from now with the exact data it used, and to join trades with quotes. What do you propose? **Solution.** For the first two points, ArcticDB with one symbol per stock and 1-minute bars (and, if needed, the ticks in another library), on S3 or compatible storage: analysts read from Python with no server to maintain, the daily ingestion is one `append` per symbol and the vendor's corrections, an `update` that leaves the previous version intact. Each backtest saves the name of a snapshot created when it is launched, and a year from now `as_of` with that name returns the exact data; intermediate versions are pruned regularly. To join trades with quotes, `merge_asof` on the data read is enough at this scale (or DuckDB's `ASOF JOIN` over Parquet). If the volume grows to billions of ticks and the joins are done daily, a database with a native *as-of join* (QuestDB, ClickHouse or kdb+) would pay off, at the cost of a server. Before anything else, review the ArcticDB license for the intended use and move the CSV files to a format with types and time zones (lesson 1.4.7). ## Self-assessment 1. A research study used BAGO on March 1, with three wrong minutes. Later March was appended and the error was fixed with `update`. How do you reproduce its exact data? - A. By reading the latest version, which is the correct one - B. By reading `as_of` the snapshot created on March 1 (or version 0): it returns the 15,210 rows with the error, even if the intermediate versions have been pruned - C. You cannot: `update` overwrites the data - D. By asking the vendor for the data again *Answer B: By reading `as_of` the snapshot created on March 1 (or version 0): it returns the 15,210 rows with the error, even if the intermediate versions have been pruned.* Each write creates a version and the snapshot pins the version of each symbol. `prune_previous_versions` deleted version 1, but not version 0, protected by the snapshot. 2. `lib.append("BAGO", ...)` with two minutes that are already in the symbol fails. Why? - A. Because ArcticDB does not allow duplicate data under any circumstances - B. Because the library has a static schema - C. Because the data has a time zone - D. Because `append` only adds after the end of what is there; to rewrite an existing range there is `update` *Answer D: Because `append` only adds after the end of what is there; to rewrite an existing range there is `update`.* Appending at the end only creates new segments. Changing an intermediate range requires rewriting segments, and ArcticDB requires doing it explicitly with `update` (or deleting with `delete_data_in_range`). 3. `QueryBuilder().resample("ME")` raises `ArcticDbNotYetImplemented`. What is the correct way to get monthly bars? - A. Resample to days (`1D`) in the store and to months in pandas, or read and resample in pandas: ArcticDB resampling only supports fixed frequencies - B. Use `resample("30D")`, which is the same - C. Switch to a library with a dynamic schema - D. Use `resample("W")` and group *Answer A: Resample to days (`1D`) in the store and to months in pandas, or read and resample in pandas: ArcticDB resampling only supports fixed frequencies.* A month has no fixed duration; 30 days do not match calendar months. ArcticDB accepts ns, us, ms, s, min, h, D and their multiples; neither months nor weeks. 4. In a library with `dynamic_schema=True`, an integer column `operaciones` (number of trades) is added from April. What value do the March rows have? - A. NaN, as in pandas - B. The library rejects the write - C. 0: an invented value that a model would take as real; for new columns use `float` (NaN) or record since when the data exists - D. The last known value *Answer C: 0: an invented value that a model would take as real; for new columns use `float` (NaN) or record since when the data exists.* With integers, ArcticDB fills the rows that did not have the column with zero; with floating point, with NaN. Zero trades is not the same as “unknown”. 5. What does `allow_exact_matches=False` do in the as-of join of trades with quotes? - A. It speeds up the join - B. It excludes the quote with the same timestamp as the trade, which may be the one that trade caused: using it would be information from the next instant - C. It requires the quote and the trade to be from the same venue - D. It removes trades without a quote *Answer B: It excludes the quote with the same timestamp as the trade, which may be the one that trade caused: using it would be information from the next instant.* It is the rule from lesson 1.1.1. In the cell, each trade gets the previous quote: on average 565 ms earlier and at most 1.7 s. kdb+ (`aj`), QuestDB and ClickHouse (`ASOF JOIN`) do the same inside the database. ## Key points - Market data is written at the end, read by symbol and date, aggregated into intervals and joined on the previous timestamp: time series databases are designed for that, almost all of them columnar. - ArcticDB stores DataFrames without a server (LMDB or S3) with versions: write, append (only at the end), update (fixes a range) and named snapshots that make a research study reproducible even if the data is later corrected or pruned. - QueryBuilder filters, groups and resamples in the store, but only with fixed frequencies (neither months nor weeks); the dynamic schema fills new integer columns with 0. - The as-of join is the query that defines the field: `merge_asof` in pandas, `aj` in kdb+, `ASOF JOIN` in QuestDB, ClickHouse and DuckDB. - TimescaleDB is PostgreSQL with hypertables and continuous aggregates; QuestDB and ClickHouse, columnar databases with SQL and as-of join; kdb+, the historical reference with q. Choose by the shape of the work, not by vendor benchmarks. ## Resources ### Research - [Big Data Management 3 - Building a Datalake on the Nasdaq with ArcticDB.](https://pythonparatrading.com/en/big-data-with-arcticdb/) · Research-0021 · 5 min ### Documentation - [ArcticDB: Library API (write, append, update, read, snapshot, versions)](https://docs.arcticdb.io/latest/api/library/) · docs.arcticdb.io - [ArcticDB: QueryBuilder and processing operations](https://docs.arcticdb.io/latest/api/processing/) · docs.arcticdb.io - [Tiger Data (TimescaleDB): continuous aggregates](https://docs.tigerdata.com/use-timescale/latest/continuous-aggregates/) · docs.tigerdata.com - [QuestDB: ASOF JOIN](https://questdb.com/docs/reference/sql/asof-join/) · questdb.com - [ClickHouse: MergeTree engine and sparse primary index](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) · clickhouse.com - [KX: aj (as-of join) in q](https://code.kx.com/q/ref/aj/) · code.kx.com Previous lesson: [6.6.3 · Dask, Spark and Ray: from one machine to a cluster](https://pythonparatrading.com/en/python-trading-course/level-6/dask-spark-and-ray/) Next lesson: [6.6.5 · Cloud storage, lakehouse (Delta, Iceberg) and GPU (RAPIDS, cuDF)](https://pythonparatrading.com/en/python-trading-course/level-6/cloud-storage-lakehouse-and-gpu/)