Data Warehouse

Querying the Warehouse

This is the reference for querying the warehouse. It documents the tables your read-only credentials can access — in the dwh schema — and shows how to join and filter them. If you are connecting a BI tool or writing SQL, start here.

Note: Read-only credentials can query the dwh schema only; everything else is internal. See Connecting to the Warehouse for how to get credentials and connect.

How the Schema Is Organised

The warehouse uses a star schema: numeric fact tables in the centre, surrounded by descriptive dimension tables you join to. Facts hold the measures (counts, events); dimensions hold the attributes you slice and filter by (dates, programmes, subjects, states). Joins are always on surrogate keys (columns ending in _sk).

There are two stars:

  • Journeys star — fact_journeys describes how many profiles sit in each state (prospect, applicant, student, alumnus…) for each class/course, captured as a point-in-time snapshot.
  • Applications star — fact_application_events records application lifecycle events (created, started, submitted, unsubmitted) at the moment they happen.

Fact Tables

dwh.fact_journeys

One row per date × time × context × journey state — a snapshot of journey counts.

Column Meaning
date_sk Join to dim_date.date_sk (snapshot date, YYYYMMDD)
time_sk Join to dim_time.time_sk (hour the snapshot was taken)
context_sk Join to dim_context.context_sk (the class/course)
journey_states_sk Join to dim_journey_states.journey_states_sk
total Profiles in this context/state
withdrawn Of which withdrawn
not_withdrawn Of which not withdrawn

Warning: Each date_sk is an independent point-in-time count — do not SUM across dates. To see a value over time, select one row per date (ORDER BY full_date); to see a single moment, filter to one date_sk. Summing across days double-counts the same profiles.

dwh.fact_application_events

One row per application lifecycle event.

Column Meaning
event_sk Primary key
application_sk Join to dim_application.application_sk
event_type created, started, submitted, unsubmitted
occurred_at When the event happened
event_date_sk Join to dim_date.date_sk

Dimension Tables

Join from a fact on the surrogate key (*_sk); filter and group by the descriptive attributes. The business key (*_id) ties a row back to the source record in the product.

  • dwh.dim_date — date_sk (YYYYMMDD), full_date, day_name, iso_week, month_number, month_name, quarter, year, is_weekend. Pre-seeded for 2000–2050.
  • dwh.dim_time — time_sk, hour (0–23).
  • dwh.dim_context — the class/course a journey belongs to. context_sk, context_id, context_name, programme_sk, subject_sk, is_current.
  • dwh.dim_journey_states — journey_states_sk, state (e.g. prospect, applicant, student, alumnus), substate, is_registration.
  • dwh.dim_programme — programme_sk, programme_id, programme_name.
  • dwh.dim_subject — subject_sk, subject_id, subject_name, subject_code.
  • dwh.dim_application — application_sk, application_id, template_sk.
  • dwh.dim_template — template_sk, template_id, template_name, programme_sk.

Tables That Keep History

dim_context, dim_programme, dim_subject, dim_application, and dim_template keep history. Each version carries effective_date, end_date (open versions use 9999-12-31), and is_current. For current-state reporting, filter is_current = TRUE; for point-in-time accuracy, match the fact's date between effective_date and end_date.

dwh.mv_application_snapshots

A convenience view holding a pre-aggregated application funnel by template and month — the simplest starting point for application reporting. Columns: template_id, template_sk, year, month_number, created_count, started_count, submitted_count, unsubmitted_count.

Example Queries

Current journey counts by programme and state (latest snapshot)

SELECT dp.programme_name, djs.state, f.total
FROM dwh.fact_journeys f
JOIN dwh.dim_date dd             ON dd.date_sk = f.date_sk
JOIN dwh.dim_journey_states djs  ON djs.journey_states_sk = f.journey_states_sk
JOIN dwh.dim_context dc          ON dc.context_sk = f.context_sk
LEFT JOIN dwh.dim_programme dp   ON dp.programme_sk = dc.programme_sk
WHERE dd.full_date = (SELECT MAX(full_date)
                      FROM dwh.fact_journeys ff
                      JOIN dwh.dim_date d ON d.date_sk = ff.date_sk)
  AND f.total > 0
ORDER BY dp.programme_name, djs.state;

Applicant trend over time for one programme (respects snapshot grain)

SELECT dd.full_date, f.total AS applicants
FROM dwh.fact_journeys f
JOIN dwh.dim_date dd             ON dd.date_sk = f.date_sk
JOIN dwh.dim_journey_states djs  ON djs.journey_states_sk = f.journey_states_sk
JOIN dwh.dim_context dc          ON dc.context_sk = f.context_sk
JOIN dwh.dim_programme dp        ON dp.programme_sk = dc.programme_sk
WHERE dp.programme_name = 'MBA'
  AND djs.state = 'applicant'
ORDER BY dd.full_date;

Application funnel by month (from the convenience view)

SELECT template_id, year, month_number,
       created_count, started_count, submitted_count, unsubmitted_count
FROM dwh.mv_application_snapshots
ORDER BY year, month_number, template_id;

Conventions

  • Surrogate keys end in _sk (used for all joins); business keys end in _id (tie back to the product).
  • date_sk is the integer YYYYMMDD form of a date; time_sk is the hour of day.
  • Always filter is_current = TRUE on history-keeping dimensions unless you specifically want past versions.
  • Tables also carry a loaded_at timestamp recording when the row was loaded — an internal audit column, safe to ignore when querying.
Previous
Connecting to the Warehouse
Next
Overview