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.

One common layered pattern is: raw preserves what arrived, staging renames and cleans it while staying close to the source, model applies business-ready transformations, and reporting is what people actually query.

Staging: rename and clean, nothing else

A staging layer is where raw column names can become the names you want to live with, text can be normalized, and lookup ids can be resolved into labels. In this example it is a view so it reflects the current raw tables. Other pipelines may materialize staging when performance, replayability, or audit requirements make that useful.

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 as s
left join campaigns as 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. In this example staging is intended to preserve the session grain, so an unexpected row-count change is a reason to inspect the joins. The model layer should lose exactly the rows its filter was meant to remove, and no more. Row counts are a cheap reconciliation check, though they don't prove that every transformation is correct.

Rows per layer

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

Syntax reference

Need syntax details? See the CREATE TABLE AS SELECT reference

Schema · Website Analytics Tables · sessions, campaigns 2 tables

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 int user_id int session_date date channel text campaign_id int device text duration_seconds int

Table · campaigns

Marketing campaigns with their channel and UTM tags.

5 columns · 5 rows

campaign_id int campaign_name text channel text utm_source text utm_medium text

Your task

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

Website Analytics · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

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 modeled 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 →

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

SQL Common Table Expressions

The WITH clause, chained steps, and CTE vs subquery.

Lesson