Work like a mid-level data analyst: define KPIs, forecast, run and analyse A/B tests, model analytics tables and turn stakeholder requests into recommendations.

Data Analytics in Practice

## Why it matters

Application databases are designed for running a product: many small, normalised tables, updated constantly. Raw exports are the opposite: one wide file with everything repeated. Neither is pleasant to analyse. Analysts who query them directly write long, fragile joins, and two people answering the same question get different numbers.

**Dimensional modelling**, popularised by Ralph Kimball, organises analytics data into **fact tables** and **dimension tables**. The result, often called a **star schema**, is the shape most BI tools (Power BI, Looker, Tableau) work best with, and it is a core skill for analysts moving towards analytics engineering.

## Facts and dimensions

- A **fact table** records events or measurements: orders, page views, payments, support tickets. Each row is one event at a defined **grain**, with numeric **measures** (quantity, revenue, duration) and **foreign keys** to dimensions.
- A **dimension table** describes the things involved: customers, products, dates, stores. Each row is one entity with descriptive **attributes** (name, category, country) used to filter and group.

Draw the fact table in the middle and the dimensions around it, and you get a star:

```text
              dim_date
                 |
dim_customer - fact_sales - dim_product
                 |
             dim_store
```

## Step 1: Declare the grain

The grain is the single most important decision: **what does one row of the fact table represent?** "One row per order line" (a product within an order) is different from "one row per order". Mixing grains in one table leads to double counting. Write the grain in the table's documentation.

## Step 2: Build dimensions with surrogate keys

A **surrogate key** is a simple integer assigned by the warehouse (`customer_key = 1, 2, 3...`) instead of the source system's natural key (email, SKU). Surrogate keys keep joins fast, protect you when source keys change (a customer changes their email), and allow history tracking.

```sql
CREATE TABLE dim_customer (
  customer_key   INTEGER PRIMARY KEY,
  customer_email TEXT UNIQUE,
  customer_name  TEXT,
  country        TEXT
);
INSERT INTO dim_customer (customer_email, customer_name, country)
SELECT DISTINCT customer_email, customer_name, country
FROM raw_orders
ORDER BY customer_email;
```

In SQLite, an `INTEGER PRIMARY KEY` column is filled automatically with 1, 2, 3... in insert order. Warehouses use sequences, identity columns or hashes of the natural key.

## Step 3: Build the fact table

The fact table looks up each dimension's key and keeps the measures:

```sql
CREATE TABLE fact_sales AS
SELECT r.order_id,
       r.order_date,
       c.customer_key,
       p.product_key,
       r.quantity,
       r.quantity * r.unit_price AS revenue
FROM raw_orders r
JOIN dim_customer c ON c.customer_email = r.customer_email
JOIN dim_product  p ON p.product_sku    = r.product_sku;
```

Now an analysis query is short and hard to get wrong:

```sql
SELECT p.category, c.country, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product  p USING (product_key)
JOIN dim_customer c USING (customer_key)
GROUP BY p.category, c.country;
```

## A date dimension

Most warehouses include `dim_date`: one row per calendar day with columns such as year, quarter, month name, week number, weekday, holiday flag and fiscal period. It saves every analyst rewriting date logic and keeps fiscal calendars consistent.

## Slowly changing dimensions

Attributes change: a customer moves country, a product changes category. You must decide:

- **Type 1:** overwrite the old value. Simple, but history is restated (last year's sales now appear under the new country).
- **Type 2:** add a new row with a new surrogate key and `valid_from` / `valid_to` dates, so old facts keep pointing at the old version. More work, but history stays accurate.

Choose per attribute, based on whether historical reports should change.

## Common mistakes

- **Undeclared or mixed grain**, causing double counting when joining.
- **Measures in dimensions** (putting total revenue on the customer row) or descriptive text in facts.
- **Joining facts to facts directly** at different grains. Aggregate each to a common grain first.
- **Natural keys everywhere**, which break when the source changes.
- **Dimensions with duplicate keys**, which silently multiply fact rows in joins. Test uniqueness (lesson 3).

## Quick recap

- Facts are events with measures at a declared grain; dimensions describe entities.
- Dimensions get surrogate keys; facts store those keys plus numeric measures.
- A star schema makes analysis queries short and consistent and suits BI tools.
- Decide how to handle changing attributes (Type 1 vs Type 2).

## Try it

In the **Do** tab, turn a wide raw export into a product dimension and a fact table that uses surrogate keys.


Design fact and dimension tables (a star schema) so analysis queries are simple, fast and consistent.

Modelling analytics tables

What is STAR schema | Star vs Snowflake Schema | Fact vs Dimension Table

Data Modeling Tutorial: Star Schema (aka Kimball Approach)

## Why it matters

In many teams, the SQL that builds reporting tables lives in scheduled queries, BI tool views and analysts' laptops. Nobody knows which version is current, changes break dashboards without warning, and there are no tests. **Analytics engineering** applies software engineering habits to this work: version control, code review, automated tests, documentation and repeatable builds. **dbt** (data build tool, from dbt Labs) is the most widely used tool for it, and "dbt" now appears in many analyst and analytics engineer job postings. The same ideas appear in alternatives such as SQLMesh and Dataform.

## The core idea: a model is a SELECT

In dbt, every transformation is a **model**: a `.sql` file that contains one `SELECT` statement. dbt wraps it in the `CREATE TABLE` or `CREATE VIEW` for you and runs it in your warehouse (Snowflake, BigQuery, Redshift, Databricks, PostgreSQL, DuckDB and others).

```sql
-- models/marts/fct_sales.sql
select
    o.order_id,
    o.order_date,
    c.customer_key,
    p.product_key,
    o.quantity,
    o.quantity * o.unit_price as revenue
from {{ ref('stg_orders') }} as o
join {{ ref('dim_customer') }} as c on c.customer_email = o.customer_email
join {{ ref('dim_product') }}  as p on p.product_sku    = o.product_sku
```

`{{ ref('stg_orders') }}` is a **Jinja** template function that refers to another model. dbt uses these references to build a **dependency graph (DAG)**, so it always builds `stg_orders` before `fct_sales`, and it resolves the right schema for development or production.

## Layers

A common project structure:

- **Sources:** raw tables loaded by an ingestion tool, declared in YAML (`{{ source('shop', 'orders') }}`).
- **Staging models (`stg_`):** one per source table; rename columns, cast types, light cleaning. No joins.
- **Intermediate models (`int_`):** reusable joins and logic.
- **Marts (`fct_`, `dim_`):** the fact and dimension tables (previous lesson) that BI tools and analysts query.

Each layer has one job, so problems are easier to find.

## Tests live next to the code

Tests are declared in YAML and run with `dbt test` (or `dbt build`, which runs and tests models in dependency order):

```yaml
# models/marts/schema.yml
models:
  - name: fct_sales
    description: One row per order line.
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_key
        tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_key
  - name: dim_customer
    columns:
      - name: customer_key
        tests: [unique, not_null]
```

The four built-in "generic" tests are `unique`, `not_null`, `accepted_values` and `relationships` (referential integrity). Each compiles to a SQL query that returns failing rows; zero rows means pass. Packages such as `dbt_utils` and `dbt-expectations` add more, and you can write custom tests in SQL. Newer dbt versions also support unit tests on model logic with fixed input rows.

## Documentation and lineage

`description` fields in YAML plus `dbt docs generate` produce a browsable site showing every model, its columns, tests and a **lineage graph** of what feeds what. When a stakeholder asks "where does this revenue number come from?", you can show them.

## The workflow

1. Create a git branch.
2. Edit or add a model and its tests.
3. Run `dbt build --select fct_sales+` against your development schema (the `+` includes downstream models).
4. Open a pull request; a teammate reviews the SQL; CI runs the build and tests.
5. Merge; the production job runs on a schedule (dbt Cloud, Airflow, Dagster, GitHub Actions...).

**Materialisations** control how each model is built: `view`, `table`, `incremental` (only process new rows, for big event tables) or `ephemeral` (inlined as a CTE).

## Why analysts should care

- Metric logic is written once and reused, which ends the "two different numbers" problem from Module 1.
- Broken assumptions are caught by tests before a stakeholder sees a wrong dashboard.
- Changes are reviewed and reversible.

You can try dbt for free with dbt Core (open source, command line) and DuckDB on your own laptop, with no cloud account needed.

## Common mistakes

- Hard-coding table names instead of using `ref()` and `source()`, which breaks the DAG.
- Putting all logic in one giant model instead of layers.
- No tests on primary keys, so duplicates silently inflate metrics.
- Treating dbt as a scheduler for everything; it transforms data already in the warehouse, and ingestion is a separate tool.

## Quick recap

- A dbt model is a SELECT in a file; `ref()` links models into a DAG.
- Layers: sources, staging, intermediate, marts.
- Tests (`unique`, `not_null`, `accepted_values`, `relationships`) and docs live with the code.
- Git, review and CI make transformations reliable.


Explain how dbt-style projects turn SQL transformations into version-controlled, tested, documented models.

Transformations as code

What Is DBT and Why Is It So Popular -  Intro To Data Infrastructure Part 3

Why so many companies use dbt - data build tool explained

## Why it matters

In SQL for Analysis you wrote one-off queries to find duplicates and nulls. At mid-level, the job changes: you **own** tables and dashboards that other people rely on, and you are responsible when they are wrong. Bad data that reaches a dashboard costs trust that takes months to rebuild. "Data quality ownership" means problems are caught automatically **before** stakeholders see them, and when something does slip through, everyone knows who fixes it and how.

## Dimensions of data quality

Most checks fall into a few families:

| Dimension | Question | Example check |
|---|---|---|
| **Uniqueness** | Is each key present once? | No duplicate `order_id` |
| **Completeness** | Are required fields filled? | `customer_id` is not null |
| **Validity** | Do values follow the rules? | `amount >= 0`; status in an allowed list |
| **Referential integrity** | Do references point to something real? | Every `customer_id` exists in `customers` |
| **Timeliness / freshness** | Is the data recent enough? | Latest `order_date` within 1 day of the load |
| **Volume** | Is the row count plausible? | Today's rows within ±30% of the 4-week average |
| **Consistency** | Do numbers agree across systems? | Warehouse revenue within 0.5% of the finance ledger |

The first four are rules about individual rows. Freshness, volume and consistency catch pipeline failures, such as a load that silently delivered half the data.

## Write checks as queries that return failures

A useful convention (used by dbt tests and most data quality tools): **each check is a query that counts or returns failing rows; zero means pass**. Combining them gives a single report a pipeline can act on:

```sql
WITH checks AS (
  SELECT 'null_customer_id' AS check_name,
         (SELECT COUNT(*) FROM orders WHERE customer_id IS NULL) AS failing_rows
  UNION ALL
  SELECT 'orphan_customer_id',
         (SELECT COUNT(*) FROM orders o
          WHERE o.customer_id IS NOT NULL
            AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id))
)
SELECT check_name, failing_rows,
       CASE WHEN failing_rows = 0 THEN 'pass' ELSE 'fail' END AS status
FROM checks;
```

Note the use of `NOT EXISTS` rather than `NOT IN`, which misbehaves when the subquery contains NULLs.

## Severity and thresholds

Not every failure should stop the pipeline:

- **Error (block):** duplicate primary keys, a load with zero rows, revenue off by more than 5% from finance. Stop publishing and alert the owner; it is better to show yesterday's data with a "delayed" banner than wrong data.
- **Warn:** a handful of orphan rows, a volume dip of 15%. Publish, but alert and investigate.

Some checks need a **tolerance**: "fewer than 0.1% of rows with a null country" is a realistic rule when a few nulls are expected. dbt supports this with `severity`, `warn_if` and `error_if` settings.

## Where checks run

1. **At ingestion:** schema checks (expected columns and types).
2. **After each transformation:** uniqueness, nulls, relationships on the models you build.
3. **On the final tables BI reads:** freshness, volume, reconciliation against a trusted source.

Tools include dbt tests, Great Expectations, Soda and SQL assertions in an orchestrator such as Airflow or Dagster. **Data observability** platforms (Monte Carlo, Bigeye, and features built into some warehouses) learn normal patterns and alert on anomalies in freshness, volume and distributions without a hand-written rule for each.

## Ownership and incidents

Technology is half of it. The other half is people:

- **A named owner** for each important table and dashboard, listed in the documentation.
- **A data contract** with upstream teams: which columns they promise, what they will announce before changing.
- **An incident routine:** detect, tell affected users quickly ("Revenue dashboard is delayed; we are investigating"), fix, backfill, and write a short blameless review with a new check so the same failure is caught next time.
- **Visible status:** a freshness timestamp and known-issues note on dashboards.

## Common mistakes

- Checks that nobody receives alerts from.
- Alerting on everything, so people ignore the noise. Tune thresholds.
- Only checking row-level rules and missing a half-empty load.
- Fixing data by hand in the dashboard instead of upstream.
- Silent fixes. Tell users when published numbers changed.

## Quick recap

- Quality dimensions: uniqueness, completeness, validity, referential integrity, freshness, volume, consistency.
- Write each check as a query that returns failures; zero means pass.
- Decide severity: block on errors, warn on smaller issues, with tolerances.
- Run checks at every stage, name owners, and handle incidents openly.

## Try it

In the **Do** tab, write one SQL query that runs five quality checks on a load and reports pass or fail for each.


Own the quality of the tables you publish with automated checks, thresholds, alerts and clear incident handling.

Data quality ownership

What is Data Observability?

SQL Data Quality Checks Every Data Engineer Must Know

Model clean fact and dimension tables, treat transformations as tested code, and own the quality of the data you publish.