Start Where You Are

In the previous labs, we defined our strategy and identified the necessary tables. Now, we move to the "engine room" of business analytics: Extract, Transform, and Load (ETL). Raw data is rarely ready for a dashboard because it is optimized for recording transactions, not for high-level analysis.

To make this data useful, we must filter out superfluous information—technical clutter like last_update timestamps or internal address_id keys—and focus on the "signal" that drives business decisions.

The Medallion Architecture

Modern analytics often uses a "Medallion" framework to refine data through three distinct stages:

• Bronze (Raw): A direct copy of your source tables (e.g., rental, payment) exactly as they exist in the operational database. This data is usually collected in a form that is built for operational speed and simplicity.
• Silver (Cleaned): Data that has been joined and filtered. Here, we drop columns that don't add business value and ensure data types are consistent.
• Gold (Curated): Final Data Marts formatted for specific analysis. This is where we create specific views like "Customer History".

Technical Spotlight: Choosing Your Join

When moving from Bronze to Silver, the type of "Join" you choose determines which rows are included in your final analysis. Think of these as different ways to handle missing or unmatched data.

• Inner Join: Only includes rows where there is a match in both tables.
  • Example: If you join customer and rental, you will only see customers who have actually rented at least one movie.
• Left (Outer) Join: Includes all rows from the "Left" (first) table, and only matching rows from the "Right" table.
  • Example: This is the most common for business reports. If you Left Join customer to rental, you will see every customer in your database—even those who have never rented a movie (their rental columns will just appear as "Null").
• Right (Outer) Join: The opposite of a Left Join; it includes all rows from the second table.
  • Example: If you Right Join customer to rental, you would see every rental record, even if—hypothetically—a rental was somehow processed without a valid customer ID.
• Full Outer Join: Includes all rows from both tables, regardless of whether there is a match. Use this sparingly, as it can create massive, messy datasets.

Evolution of a "Row Volumes"

A key part of ETL is understanding how the "grain" (what one row represents) changes as it moves toward the Gold layer. I like to always emphasize the importance of being absolutely clear about what each row of your represents from each table you are reading it from.

<aside> 💡

Pro-Tip: The Doctrine of Volumetric Integrity

As you build your ETL, you must track your row counts at every single step. This practice, which I call the Doctrine of Volumetric Integrity, is your primary defense against "garbage in, garbage out."

Imagine you have 100 films, 500 customers, and 4,000 rentals.

• The Expected Outcome: When you join customers and films via the rentals table, your final joined table should ideally have 4,000 rows (one for every rental event).
• The Red Flag (Clipping): If your output only has 500 rows, you likely "clipped" your data by accidentally filtering for only one rental per customer.
• The Red Flag (The "Omnibus" Error): If you end up with 400,000 rows, you likely missed a join condition, resulting in a Cartesian product where every rental is incorrectly joined to every customer.

If you enter a meeting and your numbers differ from a colleague’s, your ability to demonstrate Volumetric Integrity—showing exactly how and why your row counts changed at each stage—is what proves your analysis is the "source of truth."

</aside>

1. The Customer History Table

• Source (Bronze): One row is a single interaction, such as one payment event in the payment table.