Sources and staging

The declaration is short. _bazaar__sources.yml names one source called bazaar, points it at schema: raw, and lists the eight tables. From then on, every reference in the project is {{ source('bazaar', 'orders') }} and never raw.orders. Three consequences, in ascending order of how much they matter:

• A loader rename is a one-file edit. Fivetran restructures a schema, someone renames transactions to payment_attempts, the warehouse team moves raw to landing. You change the yml. Every model keeps compiling, because no model ever knew the physical name.
• Sources become nodes in the graph. Once declared, a source appears in lineage, so "what depends on this raw table" is answerable by walking edges. That is why b4's graph has 32 nodes rather than 23 - sources are in it, and everything is downstream of one.
• You get somewhere to put tests and documentation about data you do not own. This is the real prize, and it is the subject of the next card.

The naming convention for the file itself is worth copying: _bazaar__sources.yml, leading underscore so it sorts to the top of the folder, source name in the middle, double underscore as the separator. Its sibling _bazaar__models.yml holds the staging models' descriptions and tests. Two files, one obvious place to look for each.

Source tests are ordinary dbt tests attached to a source column instead of a model column, and dbt build runs them before anything downstream is built. Three of them do most of the work in this project:

• unique on users.user_id, merchants.merchant_id, products.product_id, carts.cart_id, transactions.txn_id, plus users.email and transactions.psp_ref. A duplicated primary key in a source is the single most destructive input a warehouse can receive, because it silently multiplies rows in every join above it.
• not_null on those same keys, with one deliberate exception: transactions.psp_ref is declared unique only, because the payment provider does not always return a reference on a failed attempt. A null key does not multiply rows, it loses them - which is worse than a duplicate, because a missing row does not look like an error, it looks like a slow week.
• relationships from products.merchant_id to merchants.merchant_id, and from orders.user_id to users.user_id. This is referential integrity the source system may or may not be enforcing, checked on the data you actually received rather than on the ERD somebody drew.

Why at the boundary rather than on the staging models? Because the point of a test is to stop work as early as possible. A duplicate txn_id caught on the source costs one failing test and a skipped subtree. The same duplicate caught in a mart costs a rebuilt star schema, a wrong revenue number, and a conversation with somebody who already presented it. This project runs 100 data tests in total, and the ones at the boundary are the cheapest of them.

One deliberate asymmetry: cart_items and order_items have no source-level column tests. Their grain is a composite - cart plus product, order plus line - and there is no single column to declare unique. That check lives one layer up as a unique_combination test on the staging model, which is the right place for it. Session b7 takes the whole test suite apart.

Every test above answers "is this data correct". Freshness answers a completely different and often more urgent question: "is this data there". A source can be perfectly valid and eleven days stale, and no unique or not_null test will ever notice.

You declare it on the source with a timestamp column dbt can look at:

• loaded_at_field - the column that tells dbt when a row arrived. This is the requirement people trip on: freshness needs a load timestamp, and an OLTP created_ts is not the same thing. If your loader does not stamp arrival time, freshness cannot be computed and the honest fix is to ask the loader to add the column.
• warn_after and error_after - thresholds in hours or days. Warn when it is late, error when it is broken, and the two numbers are a business decision rather than a technical one.
• dbt source freshness - the command that checks them. Note that it is its own command: dbt build does not run freshness for you.

This project does not enable freshness, and the reason is worth being straight about: the raw data is a fixed 90-day extract loaded by python/load_raw.py, so a freshness check would either always pass trivially or always fail. Teaching it against fake staleness would be theatre. What is real is the shape of the decision, which is why it is named here and why the threshold conversation belongs on your own project.

Freshness is also the seam where this course stops and two others start. Whether the loader ran is monitoring, which is learn-dataops. Why the loader is built the way it is, and how raw got populated at all, is learn-data-engineering. dbt transforms what is already there and has no way to fetch a missing batch.

Consistency is the low bar. The useful version is naming that tells you something you would otherwise have to look up:

• stg_<source>__<entity> with a double underscore: stg_bazaar__order_items. The double underscore separates source from entity, which matters the day a second source system also has an orders table. stg_bazaar__orders and stg_shopify__orders coexist without argument.
• *_at or *_ts for timestamps, *_date or *_on for dates. Here: ordered_at, signed_up_at, added_at, attempted_at are timestamps; joined_on and launched_on are dates. Reading a mart, you know whether a column has a time component without checking the schema - which is exactly the mistake that produces off-by-one-day reports.
• is_* for booleans, and only for booleans. is_settled, is_guest, is_approved. Never is_status, never an is_* column holding 'Y'.
• Meaning-carrying names over source-faithful ones. joined_on beats joined_date because it reads as a fact about a merchant. order_status beats status because status in a mart is a question rather than a column.
• One primary key name per entity, everywhere. order_id is order_id in every model that touches it. The moment it becomes orderid in one place, every join in the project needs a human to check it.

Staging is also the only layer where you get this for free. Rename in staging and the whole project above it inherits the good name. Rename in a mart and you have two names for one thing, forever, plus a conversation about which one the dashboard uses.

"Rename, recast, clean, no joins" would make staging pure plumbing. Two additions earn their place, and both are about defining something once instead of everywhere.

• A boolean defined once. order_status in ('paid', 'refunded') as is_settled. Which statuses count as money is a business rule, and the alternative to writing it here is writing it in every mart, every ad hoc query and every dashboard filter - where it will eventually be written differently. In this dataset that is 920 paid plus 37 refunded, so 957 settled orders out of 985, and every number in this course that says 957 traces back to this one line. A refunded order still represents a sale that happened, which is a decision made in learn-data-modeling and honoured here.
• Pre-split additive measures. In stg_bazaar__transactions: case when status = 'approved' then amount else 0 end as approved_amount, and the same for declined. Plus the flag versions, is_approved and is_declined, as 1 and 0. It looks redundant. It means every rate downstream has an additive numerator and an additive denominator, so a decline rate can be summed up from hour to day to month and stay correct.

That second point is the one that saves a project. Never store a ratio in a mart. Store the parts and divide at read time, because a ratio cannot be aggregated: the average of daily decline rates is not the monthly decline rate unless every day had identical volume, and no day does. Pre-splitting at staging means no downstream model is ever tempted, because the additive columns are already sitting there. The 6.2% decline rate in this course is computed from 66 declines over 1,060 attempts every single time it appears.

price_tier is in stg_bazaar__products and nowhere in raw.products. The source has list_price, a number. The business thinks in bands: budget, mid, premium. Somebody has to convert one into the other, and the only real question is where.

• In the dashboard. Fastest to write, worst outcome. Three dashboards, three sets of thresholds, and a meeting where two people disagree about how many premium products exist. Nobody can find the definition because it is in a filter expression.
• In each mart that needs it. Better, still wrong. The rule is now in two or three models and will be updated in some of them.
• In staging, once. A three-branch case expression, one place, with an accepted_values test asserting the output is exactly budget, mid or premium. Every model above it inherits the definition, and changing the bands is one diff a reviewer can see.

The principle generalises: define a derived attribute as low in the stack as it can correctly live. Low enough that everything above inherits it, and no lower than the layer that has all the information needed to compute it. price_tier needs only list_price, so staging can do it. Something that needs an order and a payment cannot be computed in staging at all, because staging is not allowed to join - so it belongs in intermediate, which is session b5.

Where staging must stop: anything that needs another table, anything that changes the grain, and anything genuinely contested. If two departments disagree about what "premium" means, no case statement fixes that, and putting one in staging just hides the disagreement under a column name. That is a governance conversation, and pretending otherwise is how a warehouse loses trust.

SELECT count(*)                                     AS attempts,
       sum(is_approved)                              AS approvals,
       sum(is_declined)                              AS declines,
       round(100.0 * sum(is_declined) / count(*), 1) AS decline_rate_pct,
       round(sum(approved_amount), 2)                AS approved_amount,
       round(sum(declined_amount), 2)                AS declined_amount
FROM main_staging.stg_bazaar__transactions;

Every column after the first two is additive, which is the whole point. Add a GROUP BY date_trunc('day', attempted_at) and the daily rates are correct; sum those days back up and the monthly rate is still correct, because you are re-dividing sums rather than averaging averages. Then notice the last two numbers: approved payments total 111,906.48, which is exactly the settled net revenue the order lines produce. Two independent paths through the warehouse agreeing to the cent is what a reconciliation test looks like, and this project has one as a singular test.

SELECT s.price_tier,
       count(*)                    AS products,
       round(min(s.list_price), 2) AS cheapest,
       round(max(s.list_price), 2) AS dearest,
       (SELECT count(*) FROM information_schema.columns
         WHERE table_schema = 'raw' AND table_name = 'products'
           AND column_name  = 'price_tier') AS price_tier_columns_in_the_source
FROM main_staging.stg_bazaar__products s
GROUP BY 1
ORDER BY cheapest;

Now edit the thresholds in your head: move the premium boundary from 80 to 120 and nine products become mid. In this project that is one case expression in one model and every mart follows. In a project where the bands live in three dashboards, it is three tickets and one of them gets missed.

No SQL. Five questions about the source system you actually have. The answers are usually uncomfortable, which is what makes them worth writing down before b4.

• How many places in your estate is a raw table name written? If the answer is more than one file, a loader rename is currently an archaeology project.
• Which source columns would you bet money are unique and not null? Those are your first source tests. The ones you would not bet on are more interesting, because your models are probably already assuming them.
• Does your loader stamp an arrival time? If not, freshness is not available to you, and the fix is a conversation with whoever owns extraction rather than anything you can write in dbt.
• Is there a business rule written in more than one of your models? A settled flag, an active-customer definition, a tier band. That rule wants to be one column in one staging model.
• Is any ratio stored in a table anywhere? A conversion rate, an approval rate, a margin percentage. Find its numerator and denominator and store those instead, before somebody averages it.