--- title: "1.4.7 Storage: CSV, Parquet, DuckDB and databases" description: "Storing market data: CSV versus Parquet, compression and encoding, a partitioned lake, DuckDB, PostgreSQL with TimescaleDB and ArcticDB." url: "https://pythonparatrading.com/en/python-trading-course/level-1/storing-market-data-parquet-duckdb-timeseries/" lang: "en" image: "https://pythonparatrading.com/og/en/python-trading-course/level-1/storing-market-data-parquet-duckdb-timeseries.png" --- # 1.4.7 · Storage: CSV, Parquet, partitioning, DuckDB, TimescaleDB and ArcticDB > Market data is read far more often than it is written, almost always by date ranges and a few columns. This lesson stores two years of NOVA bars in seven formats, measures how much space each takes and what each preserves, sets up a partitioned Parquet lake that DuckDB queries without opening what it does not need, and places databases for when files fall short. - Course: [Level 1: Financial data, markets and analysis](https://pythonparatrading.com/en/python-trading-course/level-1/) - Module: 1.4 · Financial data engineering - Duration: 55 min - Prerequisites: [0.5.5 · DuckDB and essential SQL: SELECT, JOIN, GROUP BY and window functions on Parquet](https://pythonparatrading.com/en/python-trading-course/level-0/duckdb-sql-on-parquet/), [0.4.4 · Files (CSV, JSON, YAML, TOML) and dates with time zones](https://pythonparatrading.com/en/python-trading-course/level-0/python-files-pathlib-time-zones/) ## By the end of this lesson - Compare CSV and Parquet by size, preserved types and columns read, with figures measured on the same data. - Choose a Parquet file's compression and encoding knowing where each column's weight is. - Write a Parquet lake partitioned by year and month, read one partition and add new data without rewriting the old. - Query the lake with DuckDB and check in the plan which files it reads. - Decide when a database (PostgreSQL with TimescaleDB, ArcticDB) is worth it over files. ## 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. ```python 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 ``` ```output 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): ```python 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) ``` ```output 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: ```python 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}) ``` ```output 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: ```python pq.read_schema("barras_zstd.parquet").remove_metadata() ``` ```output 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: ```python 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) ``` ```output 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 ``` *Fig. — Where a Parquet file's weight is: compressed kilobytes per column of NOVA's bars with zstd, and the timestamp with delta encoding.* Horizontal bars: the ts timestamp takes 312.1 KB, volume 178.2 and each price column about 65. With delta encoding (DELTA_BINARY_PACKED) the same ts column takes 2.9 KB (highlighted): five-minute stamps are almost all the previous one plus 300 seconds. 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: ```python 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%})") ``` ```output 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: ```mermaid 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: ```python 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) ``` ```output 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: ```python junio = pd.read_parquet("lago/barras_5min", filters=[("anio", "=", 2025), ("mes", "=", 6)]) print(len(junio), "bars in June 2025") junio.dtypes[["ts", "anio", "mes"]] ``` ```output 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: ```python 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) ``` ```output ┌───────┬────────┬───────────┬────────┐ │ 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: ```python 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]}") ``` ```output 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: ```python 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") ``` ```output 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: ```python 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() ``` ```output (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: ```python skip title="timescale.py" 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 `DataFrame`s, 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 license[^1]. 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: ```python skip title="arctic.py" 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. [^1]: ArcticDB repository and license: , 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. ## What to use and when | Need | CSV | Parquet | Partitioned lake + DuckDB | TimescaleDB | ArcticDB | |---|---|---|---|---|---| | Exchange with another person or a spreadsheet | Yes | With tools | No | No | No | | Preserve types and time zone | No | Yes | Yes | Yes | Yes | | Read only columns or dates | No | Yes | Yes, with partition filters | Yes, with indexes | Yes | | Add data without rewriting | Append at the end | Rewrite the file | Yes, by partition | Yes | Yes (`append`, `update`) | | Several processes writing at once | No | No | With care | Yes, with transactions | Yes | | Data versions | No | No | By hand | By hand | Yes, built in | | Server to maintain | No | No | No | Yes | No (or an S3) | 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? **Solution.** ```python 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) ``` ```output 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 ``` The CSV takes 3,357 KB and the Parquet file 340 KB, ten times less. The heaviest column is again the timestamp (234.6 KB): these are irregular microseconds, without the bars' pattern, but increasing, and with delta encoding they would drop a lot (the course's `nova_operaciones.parquet`, written that way, takes 215 KB). The text columns barely weigh anything: `centro` has five possible values, `agresor` two and `condicion` four, and the dictionary reduces them to indexes of a few bits (13.7, 6.4 and 0.3 KB). In the CSV, each of those words is written in full in each of the 50,946 rows. 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. **Solution.** ```python 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 """) ``` ```output ┌────────────┬──────────┐ │ sesion │ volumen │ │ date │ int128 │ ├────────────┼──────────┤ │ 2024-10-25 │ 51399629 │ │ 2024-07-26 │ 47635706 │ │ 2024-01-26 │ 43678960 │ └────────────┴──────────┘ ``` `WHERE anio = 2024` filters on the partition column, so DuckDB opens only the 12 files for 2024. The session is computed by converting the UTC timestamp to New York time before keeping the date: in this case UTC would give the same (the session ends at 21:00 UTC at the latest), but with markets that cross UTC midnight it would not. The three sessions with the most volume are October 25, July 26 and January 26, 2024: all three are the Fridays after an earnings release (fourth Thursday of January, April, July and October, after the close), when the price opens with a gap and much more is traded. 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. **Solution.** Parquet instead of CSV, with timestamps in UTC (or with their zone stored in the schema): readers get time-zone-aware dates and correct types without guessing, and the zone is not lost at Madrid's clock changes, which do not coincide with New York's. A lake partitioned by date (year/month, or day with 500 stocks in minutes) and perhaps by market, with `anio=…/mes=…` in the path: each reader filters what they need and DuckDB opens only those files. The daily load writes a new partition without touching the previous ones, after passing some quality tests (lesson 1.4.8), and the raw data is stored separately in case it has to be rebuilt. If several processes had to write at once or you needed to know which version of the data each backtest used, then a database (TimescaleDB) or ArcticDB would pay off. ## Self-assessment 1. A CSV of bars was saved with New York times and their offset (`-05:00` in winter, `-04:00` in summer). What does `pd.read_csv(..., parse_dates=["ts"])` return? - A. A text column: with mixed offsets pandas cannot give it a single datetime type, and the time zone was lost when writing - B. A datetime column with the `America/New_York` zone - C. A datetime column in UTC - D. An error *Answer A: A text column: with mixed offsets pandas cannot give it a single datetime type, and the time zone was lost when writing.* The CSV only stores the text `2025-06-02 09:30:00-04:00`: neither the zone's name nor its daylight saving rules. In the lesson, the column comes back as `str`; the Parquet file returns it with its `America/New_York` zone. 2. In the zstd Parquet file of bars, the `ts` column takes 312.1 KB, and with delta encoding 2.9 KB. Why such a difference? - A. Because delta encoding loses precision - B. Because zstd does not compress dates - C. Because delta removes repeated rows - D. Because it stores the difference from the previous value, and in 5-minute bars almost every difference is 300 seconds: an almost constant sequence that takes very little space *Answer D: Because it stores the difference from the previous value, and in 5-minute bars almost every difference is 300 seconds: an almost constant sequence that takes very little space.* Timestamps are increasing, regular integers (microseconds). Stored as is, each one is a different 8-byte number; as differences, they are almost all the same. Encoding is a lossless transformation that compression then takes advantage of. 3. What advantage does a lake partitioned by `anio=` and `mes=` have for a query on April-June 2025? - A. That the files take less space than a single one - B. That it allows using CSV - C. That the reader discards the folders that do not match the filter without opening them: in the lesson, DuckDB reads 3 of 24 files - D. None: DuckDB reads everything anyway *Answer C: That the reader discards the folders that do not match the filter without opening them: in the lesson, DuckDB reads 3 of 24 files.* The partition value is in the path. DuckDB's plan shows it: `File Filters: (anio = 2025)(mes BETWEEN 4 AND 6)` and `Scanning Files: 3/24`. In exchange, 24 small files take more space (623 KB) than one large file (445 KB). 4. When does it make sense to move from Parquet files to PostgreSQL with TimescaleDB? - A. Whenever there are more than a million rows - B. When several processes write and read at the same time, and you need transactions, row-by-row updates or concurrent queries from several users - C. So that the data takes less space - D. Never: Parquet does it all *Answer B: When several processes write and read at the same time, and you need transactions, row-by-row updates or concurrent queries from several users.* A Parquet lake is ideal for a researcher who writes in batches and reads a lot. A database provides what files lack: safe concurrent writes, transactions, indexes and a server that several clients connect to. ## Key points - CSV is text: it loses types (categories, nullable integers, time zone) and takes five times more space than a well-written Parquet file with the same data. - Parquet stores the schema, compresses by column and lets you read only the columns and row groups you ask for. - Encoding matters as much as the codec: the bars' timestamp goes from 312.1 KB to 2.9 KB with delta encoding. - A partitioned lake (`anio=2025/mes=6/`) lets you read only what you need and add new months without touching the old ones; DuckDB queries it with SQL and its plan says how many files it opens. - Databases (PostgreSQL with TimescaleDB, ArcticDB) pay off with concurrent writes, updates, versions or several users; for solo research, Parquet and DuckDB are almost always enough. ## 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 - [Apache Parquet: encodings (DELTA_BINARY_PACKED, RLE_DICTIONARY…)](https://parquet.apache.org/docs/file-format/data-pages/encodings/) · parquet.apache.org - [PyArrow: pyarrow.dataset.write_dataset](https://arrow.apache.org/docs/python/generated/pyarrow.dataset.write_dataset.html) · arrow.apache.org - [DuckDB: Hive partitioning](https://duckdb.org/docs/current/data/partitioning/hive_partitioning.html) · duckdb.org - [Tiger Data (TimescaleDB): hypertables with CREATE TABLE … WITH (tsdb.hypertable)](https://www.tigerdata.com/docs/api/latest/hypertable/create_hypertable) · tigerdata.com - [ArcticDB: Library API (write, append, update, read)](https://docs.arcticdb.io/latest/api/library/) · docs.arcticdb.io ### Data - [Level 1 data: NOVA 5-minute bars, 2024-2025 (Parquet)](https://pythonparatrading.com/curso/datos/nivel-1/nova_barras_5min.parquet) · Download · nova_barras_5min.parquet Previous lesson: [1.4.6 · Continuous futures series: roll rules, difference adjustment and ratio adjustment](https://pythonparatrading.com/en/python-trading-course/level-1/continuous-futures-series-and-roll-adjustment/) Next lesson: [1.4.8 · Reproducible pipeline: download, validation, incremental updates and quality tests](https://pythonparatrading.com/en/python-trading-course/level-1/reproducible-market-data-pipeline/)