3. From a CSV to a database: when you need one and when it is just for show
Level 5: Storing, updating and scaling
There comes a moment in every project when somebody says this now calls for a proper database. Sometimes they are right. Far more often they are describing a problem they do not have, and the price of building it is paid in maintenance hours that come out of the budget for researching strategies. This module is the criterion for deciding it without embarrassing yourself in either direction.
What a database does that files do not
A database is not a place to store tables; files are already that. What it adds is a set of guarantees that with files you would have to program yourself:
- Safe concurrent writes. Several processes writing at once without one trampling the file another is rewriting. With partitions and full rewrites this can be handled by hand up to a point, and beyond that it hurts.
- Transactions. Either the whole batch goes in or none of it does. If your process dies halfway through the load, the store is not left in an intermediate state nobody knows how to interpret.
- Integrity constraints. A unique key on symbol and date physically prevents the duplicate that in the previous module you had to avoid through discipline.
- Access control. Different users with different permissions, and a record of who touched what. As soon as there is more than one person, this stops being bureaucracy.
- Optimized complex queries. Joins across several tables, combined filters, window aggregations, and a planner that decides how to execute them.
The real signs that files are no longer enough
None of these signs is the size of the data, and that is the most expensive misunderstanding of all:
- More than one process writes to the same store at the same time.
- More than one person reads, and they should not all see the same thing.
- Your queries join different tables in non-trivial ways: prices against corporate actions against fundamentals against your order log.
- You need a failure halfway through a write not to leave data half-finished.
- You have to answer an arbitrary question in under a second, not inside a script you already know.
Now the flip side. If you are a single user, with daily data for a few thousand stocks, a process that runs once a day and a store of tens of megabytes, you have none of those signs. You have well-organized files, and that is a correct architecture, not a provisional phase.
SQL without a database: the middle ground almost nobody uses
The argument you hear most in favor of a database is that SQL is more expressive than chaining pandas operations together. That is true, and it does not imply what people think it implies, because there are embedded analytical engines that run SQL directly over your columnar files, with no server, no users and no process to maintain.
import duckdb
con = duckdb.connect() # in memory: there is no server and no database file
top = con.execute("""
SELECT simbolo, avg(volumen) AS vol_medio, count(*) AS sesiones
FROM read_parquet('almacen/precios/**/*.parquet', hive_partitioning = true)
WHERE anio BETWEEN 2023 AND 2024 AND volumen > 0
GROUP BY simbolo
HAVING count(*) > 400
ORDER BY vol_medio DESC
LIMIT 10
""").df()Look at what that SELECT does: the filter on anio is pushed down to the file layer and only the 2023 and 2024 folders are opened, and of the columns only the three that appear in the query are read. You get the expressiveness of SQL over the store from module one, without having installed a server. For the vast majority of personal projects, the conversation ends here.
Time series or relational: what really changes
If a database really is needed, the next choice is not a matter of fashion either:
- A classic relational database shines when the data has relationships: portfolio, orders, executions, commissions, instruments, issuers. There you want foreign keys, transactions and constraints that prevent an orphaned order.
- A time series database shines when the pattern is writing a lot and always at the end, and reading by windows: the last two hours, an average per minute, resampling, automatic retention of old data. With 1,200 instruments at one point per second you have some 103 million points a day, and at that point continuous ingestion and resampling stop being details.
import sqlite3
con = sqlite3.connect("almacen/precios.db")
con.execute("""
CREATE TABLE IF NOT EXISTS precios (
simbolo TEXT NOT NULL,
fecha TEXT NOT NULL,
cierre REAL NOT NULL,
volumen INTEGER NOT NULL,
descargado_en TEXT NOT NULL,
PRIMARY KEY (simbolo, fecha)
)
""")
# The ON CONFLICT clause needs that key or a unique index to exist:
# without the PRIMARY KEY above, this statement does not run, it fails
con.executemany("""
INSERT INTO precios (simbolo, fecha, cierre, volumen, descargado_en)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(simbolo, fecha) DO UPDATE SET
cierre = excluded.cierre,
volumen = excluded.volumen,
descargado_en = excluded.descargado_en
""", filas)
con.commit()A case: the project that set up a server for nothing
A typical and perfectly recognizable case. A single user, 500 stocks, daily data, twenty-five years: 3.1 million rows, tens of megabytes in columnar format. The process runs once a day and takes a couple of minutes. Setting up a database server there adds a service that has to be started, updated, backed up and monitored, and a connection layer that can fall over at three in the morning.
What do you get in exchange? With a single writer, no concurrency guarantee you actually need. With known queries launched from your own script, no planner to save you. With a single user, no access control.
The honest criterion is this: most personal projects never need more than well-organized files plus an embedded SQL engine when an odd query comes along. The day two writers, a second person or an integrity requirement appears, you migrate, and by then you will know exactly which table and which constraints you need. Migrating late costs you a while; migrating early costs you the whole project.