Views and Materialized Views

Who this is for

Data Analysts who want faster queries, cleaner SQL, and safer data access using Views and Materialized Views.

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY
• Basic understanding of schemas/tables
• Ability to read execution plans is a bonus

Why this matters

Real tasks you’ll meet on the job:

• Standardize complex business logic (e.g., “paid orders” definition) once, reuse everywhere
• Speed up dashboards by pre-aggregating heavy queries
• Expose only safe columns to analysts/BI tools without copying data
• Reduce errors by centralizing query logic

Concept explained simply

View: a saved SQL SELECT. It does not store data. Every time you query it, the database runs the underlying SELECT on the base tables.

Materialized View (MV): a saved result set. It stores data physically like a table snapshot. You must refresh it to see new data.

Key differences at a glance

• Freshness: View is always current; MV can be stale until refreshed
• Performance: View may be slow if complex; MV can be fast
• Storage: View no extra storage; MV uses storage
• Maintenance: View minimal; MV needs REFRESH and sometimes indexes

Worked examples

-- PostgreSQL-style
CREATE OR REPLACE VIEW analytics.monthly_paid_revenue AS
SELECT
  date_trunc('month', o.order_date) AS month,
  SUM(o.total_amount)               AS revenue
FROM sales.orders o
WHERE o.status = 'paid'
GROUP BY 1;

-- Usage
SELECT * FROM analytics.monthly_paid_revenue
WHERE month >= date_trunc('month', CURRENT_DATE) - interval '11 months';

CREATE OR REPLACE VIEW analytics.customer_public AS
SELECT id, signup_date, country
FROM core.customers;  -- excludes email, phone, address

-- Create MV (PostgreSQL-style)
CREATE MATERIALIZED VIEW analytics.daily_paid_revenue_90d AS
SELECT
  ::date AS day,
  COUNT(*) AS orders_count,
  SUM(total_amount) AS revenue
FROM sales.orders
WHERE status = 'paid'
  AND order_date >= CURRENT_DATE - interval '90 days'
GROUP BY 1;

-- Optional: speed common filters
CREATE INDEX ON analytics.daily_paid_revenue_90d (day);

-- Refresh when needed
REFRESH MATERIALIZED VIEW analytics.daily_paid_revenue_90d;  -- or CONCURRENTLY if supported

-- Recreate with a rolling 30-day window
CREATE OR REPLACE MATERIALIZED VIEW analytics.daily_paid_revenue_30d AS
SELECT ::date AS day,
       COUNT(*) AS orders_count,
       SUM(total_amount) AS revenue
FROM sales.orders
WHERE status = 'paid'
  AND order_date >= CURRENT_DATE - interval '30 days'
GROUP BY 1;

-- Schedule a nightly refresh (outside SQL; via your scheduler)
-- Example manual trigger:
REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.daily_paid_revenue_30d;  -- if supported

How to choose

• Use a View when you need always-fresh results, simple logic reuse, or access control
• Use a Materialized View when the query is heavy and reused often, and slight staleness is acceptable
• Mix: View for logic definition + MV that selects from that view for speed

Exercises

Do these before the Quick Test. Your progress in the test is available to everyone; only logged-in users get saved progress.

• Goal: analytics.paid_orders_by_customer with columns (customer_id, orders_count, total_spent)
• Base: sales.orders(id, customer_id, order_date, status, total_amount)
• Filter: status = 'paid'

• Name: analytics.daily_paid_revenue_90d
• Columns: day, orders_count, revenue
• Index day, and show a refresh command

Common mistakes and self-check

• Expecting a view to be fast by itself: it’s still running the full query every time
• Forgetting to refresh a materialized view: leads to stale dashboards
• Over-broad MVs: snapshotting “all time” dramatically slows refresh and wastes storage
• Leaky security: granting base table access when you meant to grant only the view
• Assuming ordering in views: results are unordered unless your SELECT includes ORDER BY

Practical projects

• Build a “canonical metrics” layer: views for core definitions (paid orders, active users, churn)
• Create a dashboard powered by one MV (daily revenue 90d) and one view (monthly revenue), measure load time improvement
• Design a permissioned analytics schema exposing only views safe for BI

Learning path

• Foundations: SELECT, JOINs, GROUP BY, filtering by time windows
• This subskill: Views vs. Materialized Views, when to use each, refresh strategies
• Next: Indexing and partitioning basics to support fast MVs
• Later: Task/job scheduling to automate MV refreshes

Mini challenge

Create analytics.monthly_paid_revenue as a view and analytics.daily_paid_revenue_30d as a materialized view. Prove the MV speeds up a dashboard query by comparing execution time of:

• SELECT SUM(revenue) FROM analytics.daily_paid_revenue_30d WHERE day >= CURRENT_DATE - interval '14 days'
• vs the equivalent raw aggregation on sales.orders

When you’re ready, take the Quick Test below.