2. Incremental downloads and late corrections

Level 5: Storing, updating and scaling

The store from the previous module is built and has twenty-five years inside it. Tomorrow there will be one more session, and the day after another. Re-downloading the complete history every day is absurd and, with paid vendors, expensive too. This is where the part of the craft begins that separates a project which lasts years from one that breaks the month you go on holiday.

Idempotent means you can run it twice

A process is idempotent when running it twice leaves the system exactly as running it once does. It is not an academic luxury: it is the only way to make sure that the cron job that fails halfway, the manual retry on Monday morning and the test run you launched without realizing do not leave your table with repeated rows.

The naive pattern is to look at the last date stored, ask from the following day onwards and append a file at the end. It fails in two places at once: if the process is cut off after writing and before recording its progress, you repeat; and if the vendor returns the same day with overlap, you duplicate. The version that holds up does three distinct things:

• It asks with deliberate overlap. The last day already stored is re-read, and even the last three or five. It is cheap and it captures recent corrections.
• It deduplicates on the business key. The key is the symbol-and-date pair, not the order of arrival.
• It rewrites the whole partition rather than appending a loose file. That way the final state depends on the content, not on how many times you have launched it.

import os
import pandas as pd
from pathlib import Path

ALMACEN = Path("almacen/precios")
CLAVE = os.environ.get("PROVEEDOR_API_KEY", "tu_clave_aqui")

def ultima_fecha(simbolo):
    """Last date already stored for the symbol, or None if there is nothing."""
    if not ALMACEN.exists():
        return None
    ya = pd.read_parquet(ALMACEN, columns=["simbolo", "fecha"],
                         filters=[("simbolo", "==", simbolo)])
    return None if ya.empty else ya["fecha"].max()

def actualizar(simbolo, descargar, solape_dias=5):
    desde = ultima_fecha(simbolo)
    if desde is not None:
        # Deliberate overlap: if the vendor corrected these days, the good version comes in
        desde = desde - pd.Timedelta(days=solape_dias)

    nuevo = descargar(simbolo, desde=desde, clave=CLAVE)
    if nuevo.empty:
        return 0

    nuevo["descargado_en"] = pd.Timestamp.now(tz="UTC")
    nuevo["anio"] = nuevo["fecha"].dt.year

    for anio, trozo in nuevo.groupby("anio"):
        viejo = leer_particion(simbolo, anio)          # empty DataFrame if it does not exist
        junto = pd.concat([viejo, trozo], ignore_index=True)
        junto = junto.sort_values("descargado_en")
        junto = junto.drop_duplicates(["simbolo", "fecha"], keep="last")
        escribir_particion(simbolo, anio, junto)       # rewrites the whole partition
    return len(nuevo)

The day the vendor corrects the past

Vendors do correct things. A provisional close that firms up hours later, a volume revised when off-exchange trades settle, a dividend adjustment that arrives late, a fundamental that gets restated. It is not a vendor failure: it is how markets and accounting work.

Faced with a correction you have exactly two honest policies, and the worst decision is not to choose:

• Overwrite with a log. You replace the old value with the new one and note in a separate file what changed, when and by how much. The store stays clean and small, and you have a trail to explain why a backtest from three months ago no longer comes out the same. What you cannot do is reproduce it.
• Store the correction as a new version. You delete nothing: you add a row with the same symbol-and-date pair and a later download stamp. The store grows, but you can rebuild any past state.

What never works is overwriting blindly and carrying on. That is the short road to the moment when you rerun an experiment, it comes out different, and you have no way of knowing whether your code changed or the data shifted under your feet.

Two timestamps: when it happened and when you knew it

Out of this comes the idea that organizes the whole module. Every row carries two different dates that must never be mixed up: the data date, which is the market day it refers to, and the download date, which is the instant that value landed on your disk.

With both you can answer the only question that matters if you are not to fool yourself: what did I know on March 15. You filter on download date at or before that instant and, within what is left, you keep the latest version of each symbol-and-date pair. What you get is the picture of the store exactly as it stood that day, later corrections included and discarded.

def vista_a_fecha(historial, conocido_hasta):
    """What we knew at 'conocido_hasta', ignoring later corrections."""
    visible = historial[historial["descargado_en"] <= conocido_hasta]
    visible = visible.sort_values("descargado_en")
    return visible.drop_duplicates(["simbolo", "fecha"], keep="last")

hoy = vista_a_fecha(historial, pd.Timestamp.now(tz="UTC"))
marzo = vista_a_fecha(historial, pd.Timestamp("2026-03-15", tz="UTC"))

# How many rows changed value between the two pictures
cambios = hoy.merge(marzo, on=["simbolo", "fecha"], suffixes=("_hoy", "_marzo"))
print((cambios["cierre_hoy"] != cambios["cierre_marzo"]).sum())

A case: what keeping the versions really costs

The usual fear about deleting nothing is that the table will explode. Put numbers on it instead of intuition. On the 3.1 million rows from the previous module, suppose the vendor corrects 0.3% of the rows a year, which is a reasonable order of magnitude for daily prices of liquid stocks and one you should measure with your own vendor before taking it as read.

That is some 9,300 new rows a year. Over five years, 46,500 rows: 1.5% more storage in exchange for being able to rebuild any past picture. The same calculation with fundamentals, which get restated far more often, gives a higher percentage but still nowhere near doubling anything.

The practical conclusion is an uncomfortable one for anyone in a hurry: not keeping the versions is almost never decided on space, it is decided on laziness, and the cost does not show up until the day a result changes and you do not know why.