Sefism.Data engineering: AWS

Tauseef Fayyaz

Chapter 02

Analytical SQL and data modelling

The SQL patterns pipelines rely on and the dimensional modelling ideas that turn raw tables into a warehouse people can trust.

Common table expression

Also called CTE, WITH clause

What: A named subquery defined with WITH at the start of a query, so a long transformation reads as a series of steps.
Why: Readable, testable steps are how dbt models are written, and they make reviewing complex logic far easier than nested subqueries.

SQL

WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
),
daily AS (
  SELECT order_date, sum(amount) AS revenue
  FROM paid GROUP BY order_date
)
SELECT * FROM daily ORDER BY order_date;

Watch out

Selecting every column in every step, which hides what each step actually needs.

Join fan-out

Also called row explosion, duplicate rows after join

What: When a join matches one row to several rows on the other side, multiplying rows and inflating sums.
Why: It is the most common cause of wrong totals in analytics. Checking row counts before and after each join catches it.

Watch out

Hiding the duplicates with DISTINCT instead of fixing the join key or aggregating first.

Window functions

Also called ROW_NUMBER, LAG, LEAD, running total, OVER

What: Functions that compute a value for each row from a related set of rows, defined with OVER (PARTITION BY ... ORDER BY ...), without collapsing rows like GROUP BY.
Why: They power deduplication, running totals, moving averages, first and last events per user and sessionisation.

SQL

SELECT customer_id, order_date, amount,
       sum(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS running_total,
       lag(order_date) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) AS previous_order
FROM orders;

Watch out

An ORDER BY with ties, so the result changes between runs.

Deduplication

Also called latest record per key, dedup

What: Keeping one row per business key, usually the latest version, by numbering rows per key and keeping row 1.
Why: Sources send duplicates and multiple versions of the same record; the silver layer must hold one clean row per entity.

SQL

SELECT *
FROM (
  SELECT *, row_number() OVER (
    PARTITION BY order_id
    ORDER BY updated_at DESC, ingested_at DESC
  ) AS rn
  FROM raw_orders
) t
WHERE rn = 1;

Watch out

No tie-breaker in the ORDER BY, so two rows with the same updated_at swap between runs.

OLTP and OLAP

Also called transactional database, analytical database, OLTP, OLAP

What: OLTP systems run applications with many small reads and writes on normalised tables. OLAP systems answer analytical questions over many rows, usually with columnar storage.
Why: It explains why data is copied from app databases into a warehouse and why warehouse models look different from app schemas.

Watch out

Running heavy reports on the production database and slowing it down for real users.

Fact table

Also called facts, measures

What: A table of events or measurements at a declared grain, holding foreign keys to dimensions and numeric measures such as quantity and amount.
Why: Facts are what people sum, count and average. A clean fact table makes most business questions a simple query.
Example: fct_order_lines: one row per product line of an order, with order_date_key, customer_key, product_key, quantity and net_amount.

Watch out

Storing ratios such as margin percentage as measures, which cannot be summed correctly.

Dimension table

Also called dimensions, conformed dimension, date dimension

What: A table describing the context of facts: who, what, where and when, such as customer, product, store and date, with descriptive attributes.
Why: Dimensions are how people filter and group facts. Conformed dimensions shared across fact tables keep numbers consistent between reports.

Watch out

Skipping a proper date dimension and recalculating fiscal periods and holidays in every query.

Grain

Also called granularity, level of detail

What: Exactly what one row of a fact table represents, stated in one sentence before any columns are chosen.
Why: Every measure and dimension must fit the grain. A clear grain prevents double counting and settles design arguments.
Example: One row per ride booking, not per ride event and not per day per driver.

Watch out

Mixing order-level totals and line-level rows in one table, which makes every sum wrong.

Star and snowflake schemas

Also called star schema, snowflake schema, dimensional model

What: A star schema has one fact table joined directly to denormalised dimension tables. A snowflake schema normalises dimensions into further tables.
Why: Stars are simple for analysts and fast in columnar warehouses, so most teams prefer them; snowflaking saves little storage and adds joins.

Watch out

Copying the application's normalised schema into the warehouse and calling it a model.

Surrogate key

Also called SK, natural key, business key

What: A meaningless key, an integer or a hash, created in the warehouse to identify a dimension row, separate from the source system's natural key.
Why: Source IDs can change, repeat across systems or be reused. Surrogate keys also identify each version of a Type 2 dimension row.

Watch out

Hashing columns without a separator, so different values produce the same concatenated string and the same key.

Slowly changing dimension

Also called SCD, SCD Type 1, SCD Type 2, history table

What: Ways to handle dimension attributes that change. Type 1 overwrites; Type 2 adds a new row with valid_from, valid_to and a current flag; Type 3 keeps a previous-value column.
Why: Type 2 lets each fact join to the version that was true when it happened, so historical reports stay correct.

SQL

SELECT f.order_id, c.city
FROM fct_orders f
JOIN dim_customer c
  ON c.customer_id = f.customer_id
 AND f.ordered_at >= c.valid_from
 AND f.ordered_at <  c.valid_to;

Watch out

Joining facts on the natural key alone to a Type 2 table, which matches every version and double counts.

Kimball, Inmon and data vault

Also called data vault 2.0, hubs links satellites, one big table, OBT, data vault, Kimball, Inmon

What: Modelling styles: Kimball builds star schemas per business process; Inmon builds a normalised enterprise warehouse first; data vault splits data into hubs, links and satellites; one big table is a wide denormalised table per use case.
Why: Recognising them helps you read existing platforms and choose sensibly. Kimball stars suit most teams; data vault suits many changing sources and strict audit needs.

Watch out

Adopting data vault for a small team with five sources and drowning in tables.

Go deeper