Master data engineering and AI like a big tech engineer

OpenAI

Amazon

Netflix

Tesla

Salesforce

Robinhood

Microsoft

Real Time Formula 1 Analytics

Big Bag Data

Chess.com Analytics

Trading Strategy for Crypto Currency - Analytics Engineering Capstone Submission

BetFlow - Real Time Sports Betting App

Showcase of student projects

Capstone Projects

Book a discovery call with Zach

Discovery call

Our students have gone on to work at companies like Meta, Airbnb and Amazon. As well as achieve 100% raises!

Fast Track Your Career

Immediate free cloud access to Databricks, AWS, Snowflake, Astronomer, and more!

Free Cloud Access with tons of hands on exercises

Weekly Guest Speaker Sessions

Why Choose DataExpert.io Academy?

Anthropic

Databricks

Discord

Snowflake

GitHub

A five-round, escalating deep-dive on dimensional modeling. Start with basic star schemas, progress through Slowly Changing Dimensions and multi-valued relationships, design petabyte-scale facts, and finish with SQL queries against your model. Calibrated for analytics engineers and data architects. ~2h 40min total.

Data Modeling Gauntlet

Clear star schema with fact_order_lines at line-item grain, surrogate keys (dim_customer_key, etc.) with natural keys noted, FK relationships correctly drawn, measures labeled as additive, dimensions denormalized (no subdimensions), clear cardinality annotations, grain statement written explicitly.

E-Commerce Order Star Schema

Clear SCD2 implementation on dim_customer with valid_from/valid_to, fact_streaming_session with session grain, surrogate keys in fact table pointing to dim_customer_key + customer_plan_key (to capture which plan was active), separate dim_content, clear FK relationships, notes on how to handle content hierarchy (denormalize or reference), example query using SCD2 join conditions (fact date between valid_from and valid_to).

Subscription Service with SCD Type 2

Clear bridge_listing_attributes table with FK to fact and dim_attribute, surrogate keys in fact_transaction pointing to bridge_key, separate dim_supplier and dim_consumer with SCD2 on dim_supplier only, dim_date used 3+ times with role annotations, dim_geography with hierarchy, degenerate dims in fact table (order_number, transaction_id), clear notation of M:M vs 1:M relationships.

Two-Sided Marketplace with Bridge Tables

Atomic fact_events with fine grain, clear partitioning by date/region/event_type, separate fact_events_late or late_date_key handling, materialized fact_events_daily_summary with surrogate key to dim_date, denormalization into fact vs dimension decision (e.g., frequent attributes in fact, slow-moving in dimension), explicit snapshot/transaction classification, SCD2 on dimensions with efficient joins via effective_date, notes on incremental load strategy, acknowledgment of storage-query-latency tradeoffs.

Petabyte-Scale Event Fact Design

Given the pre-built star schema below, write a SQL query that computes total revenue by customer segment as it was on 2026-02-15.

The dim_customer table uses SCD Type 2 with valid_from and valid_to dates. A customer can have multiple rows (one per segment change). On any given date, a customer had exactly one active segment (where date between valid_from and valid_to).

Tables:

  dim_date (
    date_id INTEGER PRIMARY KEY,
    date_value TEXT,
    year INTEGER,
    month INTEGER,
    day INTEGER
  )

  dim_customer (
    customer_key INTEGER PRIMARY KEY,
    customer_id INTEGER,
    segment TEXT,
    valid_from TEXT,  -- ISO 8601 date
    valid_to TEXT      -- ISO 8601 date ('9999-12-31' for current)
  )

  dim_product (
    product_key INTEGER PRIMARY KEY,
    product_name TEXT,
    category TEXT
  )

  fact_orders (
    order_key INTEGER PRIMARY KEY,
    customer_key INTEGER,
    product_key INTEGER,
    date_key INTEGER,
    order_date TEXT,    -- ISO 8601 date when order was placed
    quantity INTEGER,
    revenue REAL,
    FOREIGN KEY(customer_key) REFERENCES dim_customer(customer_key),
    FOREIGN KEY(product_key) REFERENCES dim_product(product_key),
    FOREIGN KEY(date_key) REFERENCES dim_date(date_id)
  )

Write a query that returns: segment, total_revenue for orders placed on 2026-02-15 or earlier, 
grouped by the customer's segment AS IT WAS on 2026-02-15.

**Expected Output:**

| Column | Type |
|--------|------|
| segment | TEXT |
| total_revenue | REAL |

Order by total_revenue DESC.

Important: If a customer was "Free" on 2026-02-15 but later changed to "Premium", you should 
still count their earlier orders using the "Free" segment.

Query SCD2 Customer Revenue as-of Date

Given the same star schema (dim_date, dim_customer with SCD2, dim_product, fact_orders), 
compute Month-over-Month (MoM) revenue cohort analysis.

Cohort definition: the first month in which a customer placed an order is their cohort_month.

Task: Return one row per cohort, showing revenue for months 0, 1, 2, 3 after cohort entry.
- cohort_month (YYYY-MM)
- m0_revenue (revenue in month customer first ordered)
- m1_revenue (revenue one month after first order)
- m2_revenue (revenue two months after first order)
- m3_revenue (revenue three months after first order)

Note: When joining to dim_customer (SCD2), use the customer's CURRENT segment, not the segment 
at the time of order. Ignore the valid_from/valid_to for this query — treat it as a point-in-time 
lookup: use the dim_customer row where valid_to = '9999-12-31'.

Order by cohort_month ASC.

Data Modeling Gauntlet

In order. Round N+1 unlocks when Round N passes.