Land raw data unchanged and build tables in layers

How to do it

This order is called ELT, for extract, load and then transform inside the warehouse. A raw copy lets you rebuild every later table when a transformation was wrong. Use three layers, such as raw, staging for renamed and typed columns, and marts for the tables dashboards read. Each layer reads only from the one before it. Add the load time and the source name to every raw row. Raw personal data stays only as long as Expire warehouse data by partition allows.

Examples

Python example

import re
from pathlib import Path

ALLOWED_SOURCES = {"staging": {"raw"}, "marts": {"staging", "marts"}}

def test_layers_read_only_the_layer_before():
    for model in Path("models").glob("*/*.sql"):
        layer = model.parent.name
        read = set(re.findall(r"\b(raw|staging|marts)\.", model.read_text()))
        assert read <= ALLOWED_SOURCES[layer], f"{model} reads {read - ALLOWED_SOURCES[layer]}"

JavaScript example

import { readdirSync, readFileSync } from 'node:fs';

const ALLOWED_SOURCES = { staging: ['raw'], marts: ['staging', 'marts'] };

for (const layer of Object.keys(ALLOWED_SOURCES)) {
  for (const file of readdirSync(`models/${layer}`)) {
    const read = [...readFileSync(`models/${layer}/${file}`, 'utf8').matchAll(/\b(raw|staging|marts)\./g)].map(match => match[1]);
    const wrong = read.filter(source => !ALLOWED_SOURCES[layer].includes(source));
    if (wrong.length) throw new Error(`${layer}/${file} reads ${wrong.join(', ')}`);
  }
}

How to check

A script reads every model and fails when a marts model reads raw directly. Only the loader's account can write to raw. Rebuilding every model from raw on staging gives the same row counts as production.

Sources

• dbt How we structure our dbt projects

Related rules

• DEN-26 Expire warehouse data by partition in Data engineering