← All lessons
Warehouses and pipelines · Lesson 3 of 7

Staging and layers

Warehouses are easier to trust when data moves through layers, each with one job. The names vary (raw, staging, marts; or bronze, silver, gold), but the idea is the same.

Raw (or landing): the data exactly as it arrived, never edited, with when it was loaded. If something goes wrong later, this is what you rebuild from.

Staging: one cleaned-up table or view per source table. Rename columns consistently, cast types, trim and lower-case keys, convert units and time zones, remove exact duplicates. No joins and no business logic yet: a staging model should still look like its source.

CREATE VIEW stg_signups AS
SELECT id AS signup_id,
       LOWER(TRIM(email)) AS email,
       name AS full_name,
       DATE(signed_up_at) AS signup_date
FROM signups;

SELECT * FROM stg_signups ORDER BY signup_id LIMIT 3;
Returns
signup_id | email           | full_name | signup_date
----------+-----------------+-----------+------------
1         | ana@example.com | Ana       | 2024-09-01
2         | ben@example.com | Ben       | 2024-09-01
3         | ana@example.com | Ana       | 2024-09-02
(3 rows)

Intermediate: reusable steps that join or reshape staging models, like one row per customer with their first and latest order.

Marts: the tables people and dashboards use, organised around the business: fact and dimension tables, and agreed definitions of metrics such as revenue or active customers. A dashboard should read from a mart, never from raw.

Layers give every rule one home. If emails need lower-casing, that happens once, in staging, and everything downstream gets it for free. If a source renames a column, you fix one staging model.

Practice

Put the layers in the order data flows through them.

  1. Tap the items below in order.
More info