Views, Materialized Views & Stable Interfaces — SQL Course

What you'll learn

Encapsulate query contracts with views, precompute expensive reads with materialized views, and manage refresh and security tradeoffs. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply CREATE VIEW to a realistic data question
Apply Updatable views to a realistic data question
Apply Security boundaries to a realistic data question
Apply Materialized views to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
View	A named stored query evaluated at read time	Use as a stable interface, not a promise of cached performance
Materialized view	Persisted query results refreshed separately	Use when stale reads are acceptable and refresh is operationally owned
Security barrier	A view option restricting unsafe predicate reordering	Use only as part of a reviewed privilege design

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the database query interface question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Publish a stable reporting view

Explicit columns and a fixed grain create a reviewable interface for downstream users.

customer_summary_view.sql

CREATE VIEW reporting.customer_summary AS
SELECT
  c.id AS customer_id,
  c.email,
  count(o.id) AS order_count,
  COALESCE(sum(o.total) FILTER (WHERE o.status = 'paid'), 0) AS paid_revenue
FROM sales.customers AS c
LEFT JOIN sales.orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.email;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

SELECT * in a view makes its contract depend on table shape, while stacked views can hide expensive plans and ownership boundaries.

Independent workshop

Build a review-ready database query interface lab against the course commerce dataset.

Your finished workshop must include:

CREATE VIEW
Updatable views
Security boundaries
Materialized views
Refresh strategies
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

View: Use as a stable interface, not a promise of cached performance
Materialized view: Use when stale reads are acceptable and refresh is operationally owned
Security barrier: Use only as part of a reviewed privilege design

Quick check

1. Which rule best applies to View?

2. Which rule best applies to Materialized view?

3. Which rule best applies to Security barrier?

Next: JSON, Arrays & LATERAL Queries