Lesson 18.4

Stage, Clean, and Model

Split a long query into layers - staging, model, reporting - so each step has one job and can be checked on its own.

A report that goes from raw tables to a final number in one query is hard to trust, because there is nowhere to look when the number is wrong. Splitting it into named layers gives you somewhere to look. Each layer does one job and can be queried on its own.

The layers are conventional: raw is what arrived and is never edited. Staging renames and cleans it without changing what it means. Model is the cleaned result you store. Reporting is what people actually query.

Staging: rename and clean, nothing else

A staging layer is where the raw column names become the names you want to live with, text is normalised, and lookup ids are resolved into labels. It is a view rather than a table, because it holds no decisions worth storing - it should always reflect whatever raw currently says.

The LEFT JOIN to campaigns matters here. 14 of the 30 sessions have no campaign, and an inner join would silently drop them. COALESCE turns those nulls into a label a reader can group on.

Step 1: staging view

create or replace view stg_sessions as
select s.session_id,
       s.session_date,
       lower(trim(s.channel)) as channel,
       coalesce(c.campaign_name, 'unattributed') as campaign_name,
       s.duration_seconds
from sessions s
left join campaigns c on c.campaign_id = s.campaign_id

Model: store the decisions

The model layer is where filters that reflect a business rule get applied and the result is written down with CREATE OR REPLACE TABLE ... AS SELECT. Storing it is a deliberate trade: the reports get faster and stop moving under you mid-analysis, and in exchange the table is a snapshot that only refreshes when you rerun the script.

Check the counts between layers

After each step, count. Staging should not lose rows - if it does, a join is dropping them. The model layer should lose exactly the rows your filter was meant to remove, and no more. A count per layer is the cheapest check in the pipeline and catches the majority of accidents.

Rows per layer

select 'sessions' as step,
       count(*) as rows
from sessions
union all
select 'stg_sessions',
       count(*)
from stg_sessions

Schema · Website AnalyticsTable · sessions7 columns · 30 rows

Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows

session_id intuser_id intsession_date datechannel textcampaign_id intdevice textduration_seconds int

Your task

The staging view is already written for you. Add step 2, a tablesessions_model holding the staged sessions lasting at least 120 seconds, and step 3, a view campaign_sessions withcampaign_name, sessions, andavg_seconds (average duration rounded to 1 decimal). Finish by selecting all three columns from the view, sorted by sessions descending then campaign name.

SQL Workbench

query.sqlwebsite_analytics · SQL engine loading

⌘↵ to run

Expected answer

Columns: campaign_name, sessions, avg_seconds.
Rows: 5, one per campaign that still has a session after the filter.
unattributed is the largest group with 13 sessions; Retargeting drops out entirely.

30 raw sessions became 30 staged rows and 27 modelled rows, and Retargeting disappeared because none of its sessions cleared the filter. Both facts are visible because the layers are separate. The next lesson makes the whole script safe to run again tomorrow.

Previous · 18.3 Build a semantic view

Next · 18.5 Write a rerunnable script →

Lesson

Build a Semantic View

Hide the joins and fix one definition of a metric.

Reference

SQL Views

Save a query under a name and reuse it like a table.

Reference

SQL CREATE TABLE AS SELECT

Store a query result as a table you can query again.

Reference