Raw Layer

Transforming data on the way in saves storage and produces tidier tables. The problem appears later, when the transformation rule turns out to be wrong. With no original to return to, there's nothing to do but keep calculating on top of incorrect values.

Pagedeck lived this. Records from the payment provider were transformed and written only into a payments table, and refunds — which arrive as negative amounts — were being stored as positive ones. Two months later, finance noticed monthly revenue was reading high. Fixing it needed the originals, and all that remained were the transformed values. The provider's retention window was 90 days, so the two months before that couldn't be recovered at all.

Keeping what arrived in one untouched layer turns that incident into a re-run. Transformation rules live in code and code can be corrected; an original, once discarded, cannot be recreated at any price.

The rule for this layer is that things stay as they arrived. It's the place where you resist the urge to tidy.

1. Keep column names and formats as the source sent them. If the provider's column names mix capitals and underscores, leave them; if a date arrives as a string, store a string. Rename anything and you lose the ability to reconcile against the source.
2. Append only. Don't edit or delete rows already loaded — stack new arrivals underneath. When a corrected version of a payment arrives, both rows stay, and which one is authoritative gets decided in the next layer.
3. Record arrival time and origin on every row. Which file it came from, when it was loaded, what source timestamp it claims. Re-running a single date's load later depends on those columns existing, and so does answering the question of why yesterday's numbers changed overnight.
4. Don't run analysis queries here. Column names vary and formats aren't normalised, so every query has to reinterpret the data, and when that interpretation differs by person the same metric name diverges. Two dashboards built off raw will disagree within a quarter.
5. Set a long retention period, and store it cheaply. Partitioned by date and compressed, three years is not a burden, and the partitioning is also what makes re-running one day possible without touching the rest.
6. Keep access narrow. Nothing has been masked yet, so personal data sits here in its original form; masking belongs to the next layer and this one stays with the data team, with access logged.

The next layer is the staging layer, which holds the intermediate cleaned form. Six things have to be in place before moving.

1. Names and types get normalised. Source column names become the team's naming convention, and dates and amounts that arrived as strings become dates and numbers. Anything that fails to convert gets recorded rather than dropped.
2. Pick the column that identifies a duplicate. For payments it's the provider's transaction id; when two rows share it, the later arrival wins, which is a decision worth writing down because the opposite choice is equally plausible.
3. Unify the time basis. Sources use different time zones, and the moment an event happened differs from the moment it reached you. Decide which one aggregations use, write the rule down, and keep the other column rather than discarding it.
4. Reconcile row counts. Compare what the source says it sent against what landed, and reload any date that disagrees. This comparison is item one on a data quality checklist.
5. Make transformations repeatable. Build new tables rather than editing originals, and processing the same date twice produces the same result. That property is what lets you re-run without hesitation during an incident.
6. Manage transformation rules as code. Loading first and transforming inside the warehouse is called ELT, and that order is only possible because the originals are still there.

After that comes the mart layer, shaped for direct use in analysis. The reason for three layers is to narrow down where a fix belongs: when a number is wrong, you can check whether the source arrived wrong, the transformation is wrong, or the final aggregation is wrong, one layer at a time. Without the separation, every wrong number is a search through the entire pipeline.

"The originals are there, so we can just re-run it."

Past values can be recalculated after correcting the rule.

"Leave that column name as the source has it. Renaming it here breaks reconciliation."

Restating the rule that nothing gets tidied in this layer.

"Don't query raw directly — go through staging."

Building metrics on an unnormalised layer makes the values person-dependent, and the disagreement surfaces weeks later in a meeting.

After the payments incident, Theo rebuilt the data pipeline as three layers: provider responses land in raw as received, staging holds the rule that refunds are negative, and the mart aggregates monthly revenue. Three months later a change in tax treatment meant correcting the rule again — this time one line of code and 40 minutes to re-run eighteen months. Whether the originals still exist is what decides the size of the incident, and that was the whole difference between the two events.

The most common reading is that keeping originals wastes storage. The same content repeats across layers, so it looks that way. But storage is cheap and re-acquisition is expensive or impossible. Once a provider's 90-day window closes, no amount of money retrieves the records, and user behaviour logs have no mechanism for recreating a moment that has passed. Partitioned by date and compressed, three years often costs less than one month of one person's salary.

The other belief is that having this layer means the data is correct. An original feels authoritative. What the layer actually guarantees is that you can go back. If the provider sent wrong values from the start, the raw layer faithfully preserves wrong values. That's why reconciling the count the source claims against the count you received is a separate check, and why the value of this layer is recalculability rather than correctness.