3. Automated checks: the data validates itself

Level 4: Cleaning and validation

Everything above has one practical problem: it gets done once, on the day you set the project up, and after that nobody looks at it again. Six months later the vendor changes the format of a column, a new public holiday enters the calendar, or the overnight download brings half the universe, and nothing notices. The solution is not more personal discipline: it is turning the cleaning into code that runs before every use and that stops the process when something does not add up.

A contract, not an eyeball review

An automated check is an explicit contract about what the data has to look like for the rest of the system to have permission to use it. The minimum list, which covers the overwhelming majority of real incidents:

• Structure. The expected columns exist, with the expected type. A price column loaded as text breaks everything downstream.
• Order and uniqueness. The date index is strictly increasing and repeats no date.
• Ranges. No negative or zero prices, no negative volumes, no returns below minus 100%.
• Consistency between columns. High, low, open and close fit together, and the volume is consistent with there having been trading.
• Coverage. Every session carries data for a minimum percentage of the universe. This is the check that catches half-finished downloads, which are far more frequent than failed downloads.
• Historical continuity. What was worth X yesterday is still worth X today, barring a justified revision.

Writing it so that it fails loudly

class DatosInvalidos(Exception):
    pass


def validar(df, universo_esperado, cobertura_min=0.95):
    fallos = []

    faltan = {"apertura", "maximo", "minimo", "cierre", "volumen"} - set(df.columns)
    if faltan:
        fallos.append("faltan columnas: " + ", ".join(sorted(faltan)))

    if not df.index.is_monotonic_increasing:
        fallos.append("el indice de fechas no esta ordenado")
    if not df.index.is_unique:
        repes = df.index[df.index.duplicated()].unique()[:5]
        fallos.append("fechas duplicadas: " + str(list(repes)))

    if (df[["apertura", "maximo", "minimo", "cierre"]] <= 0).any().any():
        fallos.append("hay precios nulos o negativos")

    cobertura = df.groupby(level=0)["cierre"].count() / universo_esperado
    flojas = cobertura[cobertura < cobertura_min]
    if len(flojas):
        fallos.append("cobertura baja en " + str(len(flojas)) + " sesiones, "
                      "minima " + str(round(flojas.min(), 3)))

    if fallos:
        raise DatosInvalidos(" | ".join(fallos))
    return df

Three design decisions that are not cosmetic. First, every failure is accumulated and they are raised together: if you abort on the first one, you fix it, run again and discover the next, and so on six times over. Second, the message carries the specific data, which dates are duplicated and what the minimum coverage is, because an error that only says something is wrong forces you to repeat the whole investigation. And third, an exception is raised, not a warning emitted. A warning gets lost in the overnight job's output and everything carries on as if nothing had happened, delivering bad data that looks good.

The history should not change, and you have to check it

The most neglected check is also the one that catches the most silent incidents: comparing today's download with yesterday's over the stretch they share. That stretch is the past; barring a legitimate vendor revision, it has to be identical.

def comparar_con_anterior(nuevo, anterior, tolerancia=1e-9):
    comun = nuevo.index.intersection(anterior.index)
    a, b = nuevo.loc[comun, "cierre"], anterior.loc[comun, "cierre"]

    cambiadas = comun[(a - b).abs() > tolerancia * b.abs().clip(lower=1)]
    desaparecidas = anterior.index.difference(nuevo.index)

    if len(cambiadas) or len(desaparecidas):
        raise DatosInvalidos(
            "el historico ha cambiado: " + str(len(cambiadas)) + " cierres "
            "distintos y " + str(len(desaparecidas)) + " filas desaparecidas"
        )

This check firing does not always mean there is an error: vendors revise data for legitimate reasons, and companies restate accounts with the regulator. It means somebody has to decide, and that the decision has to be written down. What is unacceptable is for the past to change and nobody to notice, because then two identical backtests give different results and weeks get lost hunting for the fault in the strategy.

Where these checks hook in

They go at the exact point where the data crosses from one stage to the next: when the download finishes, before writing to the store, and again when loading it for a backtest. Duplicating the validation on load looks redundant and is not: it protects you from somebody having touched the files outside the process, which is a far more common incident than anybody admits.

The command that orchestrates the download must finish with a non-zero exit code when the validation fails, so that the task scheduler treats it as what it is, a failure, and raises the alarm. And there is a design decision worth taking early: when a check fires, the new data does not replace the old. It is written to one side, in quarantine, and the system carries on running on the last valid version until somebody resolves the incident. It is the difference between one bad night and a week trading blind.

A word of warning about the temptation to loosen thresholds: when a check fires often for legitimate reasons, the instinctive reaction is to raise the tolerance until it stops being a nuisance. That turns the check into decoration. The right move is to split it in two, a strict one that stops the process and an informational one that only counts, and to review the informational one from time to time.

A worked example: the coverage that falls slowly

A universe of 480 stocks with a 95% threshold requires at least 456 stocks with data every session. One day 447 arrive, and the check fails by nine stocks. Taken one at a time, the incident looks minor. Taken as a series, it is something else: if coverage has been falling from 99% to 93% over three weeks, it is not a one-off incident, it is a leak, typically identifiers that have stopped resolving after a name change. The check that runs every day catches the trend; the manual review, which happens when somebody has the time, never catches it.