Building a payments lakehouse alone: what it forces you to settle

NOVAPAY is a payments and fraud platform I built end to end, on Databricks and GCP: incremental ingestion of about 70,000 transactions a day, quality controls with a reconcilable quarantine, historisation of the customer reference data, risk scoring with configurable thresholds, BigQuery delivery. This piece is about the architecture decisions, including the ones I would make differently.

Why build a complete project alongside the engagements

Ten years in banking and insurance is ten years of work behind a confidentiality clause. You do not show a client architecture, you do not publish a code excerpt, you do not detail a volume. That leaves a concrete difficulty: how do you demonstrate a way of working that you are not allowed to expose?

A complete personal project answers that — provided it really is complete. A demo notebook proves nothing: what distinguishes a platform from a prototype is what happens when data is bad, when a run fails halfway, when a provider changes its format without warning. Those situations only appear if you keep the project alive over time.

Bronze is not corrected

The bronze layer receives transactions as they arrive, with two added columns: the source file and the ingestion timestamp. No transformation, no correction, no opinion.

The temptation to fix things in bronze is strong, because the error is visible and the fix is one line. A misspelled currency, an amount in cents rather than euros: you see it, you repair it, you move on. That is exactly what makes a platform unauditable. Six months later, nobody can tell what arrived from what was repaired, and the question « why did this total change » no longer has an answer.

(spark.readStream.format("cloudFiles")
   .option("cloudFiles.format", "json")
   .option("cloudFiles.schemaLocation", f"{path}/_schema")
   .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
   .load(source)
   .withColumn("_file",        input_file_name())
   .withColumn("_ingested_at", current_timestamp())
   .writeStream
   .option("checkpointLocation", f"{path}/_checkpoint")
   .trigger(availableNow=True)
   .toTable("bronze.transactions"))

Two options carry all the meaning here. schemaEvolutionMode set to addNewColumns: a payment provider adds a field without warning, and I would rather the chain welcome it than stop — or worse, ignore it silently. And the checkpointLocation, which makes ingestion resumable: a failure at two in the morning does not cost a day of catch-up.

Five rules, not thirty

The quality engine applies five rules, with deliberately distinct behaviours. The opposite temptation — writing thirty rules to cover every imaginable case — produces a system nobody can reason about and whose rejections are no longer interpretable.

The central property is not the number of rules: it is that a rule routes, it never filters. A non-compliant row goes to quarantine with the code of the rule that set it aside. It is never deleted.

RULES = [
    Rule("R01_UNKNOWN_CURRENCY", ~col("currency").isin(*KNOWN_CURRENCIES)),
    Rule("R02_AMOUNT_MISSING",    col("amount").isNull()),
    Rule("R03_AMOUNT_NEGATIVE",   col("amount") < 0),
    Rule("R04_UNKNOWN_MERCHANT",  col("merchant_name").isNull()),
    Rule("R05_FUTURE_TIMESTAMP",  col("transaction_date") > current_timestamp()),
]

marked = reduce(
    lambda df, r: df.withColumn(r.code, r.condition),
    RULES, enriched,
)
in_breach = reduce(lambda a, b: a | b, [col(r.code) for r in RULES])

compliant  = marked.filter(~in_breach)
quarantine = marked.filter(in_breach).withColumn(
    "rules_violated",
    array_compact(array(*[when(col(r.code), lit(r.code)) for r in RULES])),
)

And the check that makes the whole class of defects impossible, run on every run:

received = bronze_today.count()
processed, quarantined = compliant.count(), quarantine.count()
assert received == processed + quarantined, \
    f"silent loss: {received - processed - quarantined} rows"

What I would do differently: from day one, an alert on the age of the quarantine backlog, not only its size. A quarantine nothing ever leaves is a bin with a better name, and you only notice by looking at the exit rate.

Currency conversion, the quiet trap

This is the decision whose consequences surprised me most. Converting amounts at the processing day's rate seems natural — and makes every amount irreproducible the next day. A report regenerated a week later no longer gives the same figures, without any source data having changed.

An amount converted without its rate and its date is not data, it is a snapshot. The rate has to be historised just like the reference data.

The shape that holds: a historised rate table, joined on the transaction date rather than the processing date, with the rate kept in the output row.

silver = (transactions.alias("t")
    .join(rates.alias("x"),
          (col("t.currency") == col("x.currency")) &
          (to_date(col("t.transaction_date")) == col("x.rate_date")),
          "left")
    .withColumn("applied_rate", col("x.rate"))          # kept, not only used
    .withColumn("amount_eur",   col("t.amount") * col("x.rate")))

The join is deliberately outer: a missing rate is an exception to route, not a row to lose.

The customer reference data follows the same logic, with SCD 2 historisation: you must be able to answer « what did we know about this customer at the time of the transaction ». Without that, a replayed scoring gives a different result from the one that raised the alert — and an alert you cannot reproduce cannot be defended.

What detection latency actually measures

The figure I keep from this project is not a compute time: it is the delay between a suspicious transaction and the moment someone can act. It went from twenty-six hours to under fifteen minutes.

The interesting part is where those twenty-six hours came from. Almost none of it was computation. Most of it was cadence: a daily job launched at night, on the previous day's data cut off at midnight. A transaction at ten in the morning therefore waited fourteen hours before even entering the chain, then the batch, then publication.

The gain came from moving to incremental ingestion triggered by file arrival, not from a Spark optimisation. That is a general lesson: on a detection chain, compute time is rarely the dominant term. Before optimising a job, measure the end-to-end delay — from event to possible action — and look at where the hours actually went.

One last, less technical point. Building alone forces you to settle things a team could defer. None of these decisions is new, and none is specific to payments. What the project adds is that they are visible: the code is open, the architecture is publishable, and each choice can be argued on evidence rather than on claim.