Three shelves: raw → stg → DDS → mart
O que você vai aprender
- to see four shelves between the source and the report, not one arrow
- to say, for each shelf, what one of its rows means, and to check that with a query
- to answer "how many orders were there" so that the answer depends on the meaning of the shelf, not on a lucky
COUNT(*) - to read other people's layer names: raw/stg/DDS/mart, bronze/silver/gold, ODS/CDM — different words often stand for one and the same warehouse
Ticket #01, part two. "Is the data itself intact?"
09:20. You answered the Academy: the 14th is under-counted in the mart, the 13th is counted twice. A minute later a new question arrives:
"Understood. And is the data itself intact? Are yesterday's orders anywhere on the station at all, or is the night lost and the has to be caught again?"
The mart alone cannot answer that. The mart is the end of the belt: what lies here is the RESULT of a count, not the source data. If a day has no rows, there can be several reasons: the source sent nothing, the data arrived but got stuck on the way, or only the last step failed to make it.
To tell those cases apart you have to walk back along the belt and check the shelves in between.
Between the intake port and the mart stand four shelves. One and the same order looks different on each of them, because the meaning of a row changes.
Today you walk the warehouse from the end to the beginning and answer the same question for every shelf: what counts as one row here.
rows_about_order column shows how many rows on each shelf are about IT. The numbers differ, and that is not a loading error: on each shelf one row means something of its own. Focus on the label for the row's meaning and the two rightmost columns; the mart condition merely finds the order's city, and you do not need to unpack that machinery here.stg_orders, 3 in dds_fact_order_item, 1 in the mart. What changes is not the cargo but the meaning of one row.A shelf is an obligation, not a folder
Zero, two, three, one. And all of it is one and the same order.
The difference did not appear because data got lost or multiplied. Each shelf simply holds its own stage of the processing, so each has its own meaning for a row.
That is why you put two questions to every table in this course first: what one row means and what this shelf promises to keep or to fix.
raw_orders — the receiving tray. What the port handed over lies here, word for word: a line of , the time of receipt, the intake run it came in on. There are zero rows about the March order, because this layer stores THE NIGHT, not history: today's tray is taken apart by morning and cleared.
The obligation of this shelf is simple: "we lost nothing and rewrote nothing". So nobody fixes types here, deletes bad records or collapses duplicates. Start "improving" the raw layer and, when something breaks, there will be nothing left to compare the later stages against.
stg_orders — the sorting bench. Here the same order is already taken apart into columns: a number has become a number, a date a date. There are two rows, because one row here means not the order itself but a load of the order: during the night it arrived twice, first as paid, then as delivered.
Staging's obligation is a different one: "types are cast, bad records are set aside in quarantine, but the history of loads is kept as it came". We can already work with the fields as normal data, but we do not yet pretend that the duplicates and the repeated versions never happened.
dds_fact_order_item — the rack you count from. Here one row is an order line, so there are three rows: three goods in the order. Repeated loads and bad records no longer multiply the facts: each order line is represented by one row.
DDS's obligation: "one row = one fact that can be analysed in different breakdowns". This is exactly where you go when a new analytical question turns up that no ready-made mart answers.
dm_daily_revenue — the mart. Here one row is day × city. Our order is no longer visible on its own: it has dissolved into a single aggregated row with the other orders from that city on that day.
The mart's obligation is narrow: "I answer one question, known in advance, and I answer it fast". That is convenient for a report, but the price of the convenience is lost detail. When the question changes, you usually go back to DDS.
Hence the access rule too: reports use the mart and DDS, while only those who maintain the load get access to the raw layer. The issue is not the name raw_* but the quality of the data. Duplicates, bad records and junk sit there on purpose — everything the later shelves are supposed to take apart and filter out.
A report built on raw_* inherits every problem the source had that night and walks around the very rules the warehouse exists for.
QUERY: Hand the raw layer outside and somebody will most certainly build a report on it. Then the Academy will ask where the report got the thing we swore never to let through, and you are the one who goes and explains.
Now let's check the meaning of a row with a query rather than a description. We put one and the same question to three shelves and see why an identical COUNT(*) gives answers that mean different things.
rows_in_day column shows what a naive COUNT() over each shelf returns. The orders column is the correct number of orders. Compare the last two columns: where one row does not mean an order, COUNT() is already answering a different question.A glossary of layers. Once only in this course — after this we use them, we do not explain them.
| Here | Synonyms you will meet in job ads and other people's code |
|---|---|
raw_* — as it arrived | raw, landing, bronze, "the raw layer" |
stg_* — parsed and typed | staging, ODS, silver, "the operational layer" |
dds_* — facts and dimensions | DDS, core, detail, "the warehouse core" |
dm_* — marts built for a question | , CDM, gold, "the reporting layer" |
There are many names, and the meaning of the shelves changes far more rarely. So do not try to work out what a layer is for from the words bronze, silver, ODS or CDM alone. Find out its obligation and the meaning of one of its rows first.
At interview it is usually enough to show that you recognise these names and understand the borders between neighbouring layers: where data is still stored as it arrived, where it is taken apart, where the facts sit and where the ready answers do.
Warehouses vary in the number of layers — our belt uses four. An extra layer is often added on top for a particular team so that its changes do not touch the shared core.
Where the sandbox differs from . In a real warehouse the shelves are usually separated by schemas, or even by databases: raw.orders, stg.orders, dds.fact_order_item. Grants can then be given on a whole schema, so the raw layer can be closed to analysts technically rather than by agreement.
In our sandbox CREATE SCHEMA is forbidden by the validator: otherwise a student could walk out of their isolated schema into somebody else's. So a layer here is marked by a PREFIX in the table name: stg_orders instead of stg.orders.
The mechanics are simpler, the meaning is the same. Everything the course says about the meaning of a row, about the obligations of a layer and about access carries over to schemas in production word for word.
Interview question
How this is asked at interview. "What do you need staging for? Why not load straight from the source into the mart?"
A good answer is not built around "that's how it's done" but around three practical problems of a night-time load.
The first is the re-run. If parsing and building the mart are fused into one step, a repeat goes back to the source — and the source may have changed in the meantime. One and the same yesterday then gets recounted from different input data on a retry.
The second is investigating a failure. When the mart lies, you need to know where the data went bad: did it arrive that way, or did the error appear inside the warehouse? For that you compare the raw layer against staging. Without the intermediate shelf the border of the error is much harder to find.
The third is cost. It is cheaper to read and parse the source once and then build several marts from the prepared data.
After that the next question is often what one row of your fact table means. Here the answer should be short: one row is an order line, which is why orders are counted with COUNT(DISTINCT order_id) and not COUNT(*).
There are two English terms worth recognising straight away. What one row of a table means is called grain in the industry. The "as it arrived" shelf, where data is kept exactly as the source handed it over, is the raw layer.
You can answer in your own words. What matters is not the term but what stands behind it: what a row means and what obligation the layer carries.
stg_orders holds 1382 rows for 12 March, and 1329 distinct order_ids. Each identifier appears no more than twice. What does that mean?dm_daily_revenue mart has no categories. Which shelf do you go to, and why?- One-to-one, one-to-manyEASY
- Row fan-out after a JOINEASY
- Contagem de linhas de um DISTINCT sobre uma agregação GROUP BYEASY
Principais pontos
- Between the port and the report there are four shelves:
raw_*— as it arrived,stg_*— parsed,dds_*— facts and dimensions,dm_*— a mart built for one question. - A shelf is defined not by a folder and not by a name but by its obligation and the meaning of one row. Order No. 115725 takes up 0 rows in the raw layer (only tonight lives there), 2 in staging (two loads), 3 in DDS (three lines), 1 of the mart's 1,980 rows.
- One and the same question, "how many orders on 12 March", gives 1382, 3100 and 22 under a naive
COUNT(*). If you count orders rather than rows, all three shelves give 1329. - So a
COUNT(*)only means anything after you have answered the question: what does one row mean here. - Reports use the mart and DDS. Only those who maintain the load get access to the raw layer: it deliberately holds what the later shelves are supposed to take apart, filter out or collapse.
- Other people's names do not change the principle: bronze/silver/gold, ODS, CDM are other words for shelves you already know.
The layout of the warehouse is clear now. Further down the belt is the first section you will build yourself: the intake port. And it starts with an awkward fact — the source does not hand over a table.