05.4 SQL Views & CTE Design

Phase 5 — SQL in Databricks

This chapter moves from individual queries to designing reusable SQL assets. The key question is not "Should I create a view?" but "What contract, lifecycle, performance and governance behavior do I need?"

1. CTE vs View vs Materialized View vs Table

Object	Stores result?	Scope	Main purpose
CTE	No	One query	Compose complex SQL
Temporary view	No	Session/workload	Short-lived reusable logic
View	No	Persistent	Reusable logical interface
Materialized view	Yes	Persistent	Precomputed analytical result
Table	Yes	Persistent	Durable data product

Databricks describes a standard view as a virtual table defined by a query, while materialized views precompute and store query results. Temporary views have limited scope. See the Databricks view documentation.

Do not materialize SQL simply because a query is long. Materialize when persistence, latency, reuse, workload isolation or economics justify it.

2. Enterprise SQL layering

Bronze / source tables
        ↓
Silver business tables
        ↓
+---------------------------+
| curated SQL assets        |
| CTE → Views → MVs → Gold  |
+---------------------------+
        ↓
BI / analytics / APIs

A view is an interface over data. A materialized view is an interface plus stored computation.

3. CTEs

A common table expression gives a query a named intermediate relation:

WITH completed_orders AS (
    SELECT order_id, customer_id, order_date, total_amount
    FROM sales.orders
    WHERE order_status = 'COMPLETED'
)
SELECT * FROM completed_orders;

Use CTEs to express meaningful transformation stages. See the Databricks CTE reference.

Good names describe semantic state:

WITH valid_purchase_orders AS (...),
supplier_enriched_orders AS (...),
monthly_supplier_spend AS (...)
SELECT ...;

Bad names hide intent:

WITH t1 AS (...), t2 AS (...), x AS (...)
SELECT ...;

CTEs do not automatically materialize

CTE ≠ physical temporary table

The optimizer can inline or transform the query. Use a table when you actually need a durable persisted result.

CTE anti-pattern

WITH a AS (SELECT * FROM orders),
b AS (SELECT * FROM a),
c AS (SELECT * FROM b)
SELECT * FROM c;

This adds naming noise without adding semantic value.

4. CTEs and grain

Every meaningful CTE should have a known grain.

WITH valid_orders AS (
    -- Grain: one row per valid order
    SELECT *
    FROM sales.orders
    WHERE order_id IS NOT NULL
),
customer_month AS (
    -- Grain: one row per customer per month
    SELECT
        customer_id,
        DATE_TRUNC('MONTH', order_date) AS month_start,
        SUM(total_amount) AS revenue
    FROM valid_orders
    GROUP BY customer_id, DATE_TRUNC('MONTH', order_date)
)
SELECT * FROM customer_month;

5. Persistent views

A persistent view stores a query definition rather than its result:

CREATE VIEW sales_prod.analytics.completed_orders AS
SELECT order_id, customer_id, order_date, total_amount
FROM sales_prod.commerce.orders
WHERE order_status = 'COMPLETED';

Consumers can query it like a table:

SELECT * FROM sales_prod.analytics.completed_orders;

Views are useful for stable interfaces, central business logic, approved columns, BI simplification and governed access. Databricks supports Unity Catalog views and schema behavior such as binding and evolution. See CREATE VIEW.

6. Treat a view as a data API

Physical implementation
orders + customers + products
             ↓
analytics.customer_order_summary
             ↓
BI / analysts / downstream SQL

A production view should have:

• stable column names
• documented grain
• documented measures
• clear ownership
• predictable data types
• controlled permissions
• a change-management strategy

Consumers should depend on the contract, not the internal joins.

7. Temporary views

CREATE OR REPLACE TEMP VIEW recent_orders AS
SELECT *
FROM sales.orders
WHERE order_date >= CURRENT_DATE() - INTERVAL 30 DAYS;

Temporary views are useful for exploration, notebooks, testing and short-lived pipelines. They are not durable catalog contracts. See the view documentation.

Requirement	CTE	Temporary view
One query	Excellent	Possible
Multiple statements	No	Yes
Persistent	No	No
Business contract	No	No

8. Materialized views

A materialized view stores precomputed query results:

CREATE OR REPLACE MATERIALIZED VIEW prod_sales.analytics.daily_sales AS
SELECT
    order_date,
    SUM(total_amount) AS revenue,
    COUNT(*) AS order_count
FROM prod_sales.commerce.orders
WHERE order_status = 'COMPLETED'
GROUP BY order_date;

Databricks materialized views are backed by pipelines and can be refreshed manually or on a schedule. Databricks can use incremental refresh when the query and source design support it. See CREATE MATERIALIZED VIEW and materialized views.

9. When materialization makes sense

Good candidates include:

• expensive aggregations
• frequently queried dashboards
• complex joins
• repeated analytical transformations
• predictable refresh requirements
• workloads where lower query latency justifies refresh compute.

Conceptually:

Repeated query-time computation
            ↓
Precomputation during refresh
            ↓
Faster consumer queries

The architecture question is: is refresh cost lower than repeatedly computing the same result for every consumer?

10. Incremental vs full refresh

Changed source data
       ↓
Can result be updated incrementally?
       /              \
     YES              NO
      ↓                ↓
Incremental        Full recomputation
refresh

Databricks uses a cost-based approach for materialized-view refreshes and can choose incremental or full refresh depending on query and source characteristics. See standalone materialized views.

Materialized views are analytical precomputation, not a millisecond streaming system. Databricks distinguishes materialized-view batch semantics from streaming-table semantics. See tables and views.

11. View security and governance

A governed reporting view can expose only approved columns:

CREATE VIEW prod_hr.analytics.employee_reporting AS
SELECT employee_id, department, job_title
FROM prod_hr.core.employee;

Do not expose sensitive columns merely because the underlying table contains them.

Dynamic views can implement row and column security and data masking. Unity Catalog permission behavior depends on the view, underlying objects and compute/access model. See Databricks views.

12. Schema contracts

Suppose a base table changes:

Before: customer_id, customer_name, revenue
After:  customer_id, customer_name, revenue, currency

Your view must have an intentional answer to the change.

Databricks supports schema binding, schema compensation, schema type evolution and schema evolution for supported view definitions. See CREATE VIEW.

For critical enterprise interfaces, strict contracts are often safer than accidental evolution.

13. Views in a Medallion architecture

Bronze → durable ingestion data
   ↓
Silver → durable cleaned/enriched data
   ↓
Gold → tables + views + materialized views
   ↓
BI / analytics / applications

Do not create hundreds of views as a replacement for durable ingestion storage.

14. Production patterns

Pattern A — Semantic view

Physical tables → business transformation → governed view → many consumers

Use when reuse and consistency matter but precomputed storage is unnecessary.

Pattern B — Expensive dashboard

Large Delta tables → expensive aggregation → materialized view → BI

Use when repeated computation and latency justify precomputation.

Pattern C — Exploration

Source → CTE / temporary view → analysis

Do not promote every experiment into a permanent object.

Pattern D — Durable Gold product

Silver → business transformation → Gold table → views / MVs → consumers

Use a table when the result itself is a durable governed data product.

15. Example enterprise procurement view

Requirement: one row per supplier + company code + month.

CREATE OR REPLACE VIEW prod_procurement.analytics.supplier_monthly_spend AS
WITH valid_po AS (
    SELECT po_id, supplier_id, company_code, order_date, net_value
    FROM prod_procurement.silver.purchase_orders
    WHERE deletion_flag = false
      AND cancellation_flag = false
),
monthly AS (
    SELECT
        supplier_id,
        company_code,
        DATE_TRUNC('MONTH', order_date) AS month_start,
        COUNT(DISTINCT po_id) AS po_count,
        SUM(net_value) AS spend
    FROM valid_po
    GROUP BY supplier_id, company_code, DATE_TRUNC('MONTH', order_date)
)
SELECT * FROM monthly;

Before writing this SQL, the architect should state:

One row per supplier + company code + month.

That single sentence is an important correctness contract.

16. Dependency management

base table
   ↓
view A
   ↓
view B
   ↓
view C
   ↓
dashboard

Track owner, upstream dependencies, downstream consumers, schema, sensitivity, SLA and change history.

Avoid a view maze:

view_1 → view_2 → view_3 → view_4 → view_5 → dashboard

Too many nested semantic layers create debugging, ownership and performance problems.

17. Performance reality

Standard views do not inherently improve runtime. They improve abstraction, reuse, consistency and governance.

Materialized views change the performance model:

Query-time computation
        ↓
moves toward
        ↓
Refresh-time computation

Always verify with EXPLAIN, Query Profile and workload measurements rather than assuming a view is faster.

18. Production contract checklist

Every production view should document:

• grain
• keys
• measures
• filters
• NULL behavior
• data types
• owner
• upstream dependencies
• downstream consumers
• sensitivity
• refresh behavior if materialized
• SLA and expected usage.

19. Troubleshooting matrix

Symptom	First question
View is slow	Is the underlying query expensive?
Duplicate rows	What join cardinality created them?
View breaks after schema change	What schema contract was chosen?
Dashboard is slow	Is the same expensive logic recomputed repeatedly?
MV refresh is expensive	Is refresh full or incremental?
Consumer cannot query view	Are Unity Catalog privileges correct?
Temporary view missing	Did its session end?
Too many nested views	Is the semantic layer over-composed?

20. Hands-on lab

Lab 1 — CTE design

Build valid_orders → completed_orders → customer_month and document the grain at every stage.

Lab 2 — Persistent view

Create analytics.customer_monthly_revenue with documented grain, keys, measures, filters and owner.

Lab 3 — Temporary view

Create a last-30-days temporary view and reuse it from multiple statements in one session.

Lab 4 — Materialized view

Create daily revenue as a materialized view and compare it with the equivalent standard view.

Lab 5 — Schema change

Add a source column and test the selected view schema behavior.

Lab 6 — Governance

Create a reporting view that exposes only approved attributes.

21. Architect decision matrix

Requirement	Recommended object
Compose one query	CTE
Reuse within a session	Temporary view
Reusable governed logical interface	View
Precompute expensive analytical result	Materialized view
Durable business data product	Table
Streaming ingestion/transformation	Streaming table / streaming pipeline
Sensitive reporting interface	Governed or dynamic view
Frequently queried expensive BI result	Materialized view candidate

22. Architect review questions

1. What is the grain?
2. Who owns it?
3. Who consumes it?
4. Is the result virtual or persisted?
5. What happens when the source schema changes?
6. What are the upstream and downstream dependencies?
7. Is the query expensive?
8. Is precomputation justified?
9. What is the refresh SLA?
10. What permissions are required?
11. Does it expose sensitive data?
12. Is this object actually necessary?

23. Interview questions

CTE vs view? A CTE is query-scoped composition; a view is a persistent catalog object.

Does a view store data? A standard view stores its definition, not query results. A materialized view stores precomputed results.

Does a CTE materialize? Not by definition.

When use a materialized view? When precomputation of an expensive or frequently consumed result provides worthwhile latency and compute benefits.

View vs table? A view is primarily a logical interface; a table stores durable data.

Why not create a view for every transformation? Excessive layers create dependency complexity and make performance and ownership harder to reason about.

24. Architect mental model

BUSINESS REQUIREMENT
        ↓
WHAT IS THE GRAIN?
        ↓
IS PERSISTENCE REQUIRED?
     /           \
   NO             YES
   ↓               ↓
CTE / VIEW    DATA PRODUCT NEEDED?
                   /       \
                 NO         YES
                 ↓           ↓
                MV       TABLE / MV
                 ↓
          REFRESH ECONOMICS
                 ↓
          GOVERNANCE + LINEAGE
                 ↓
             CONSUMER SLA

A SQL asset is an architectural boundary, not just a SQL statement.

Official references

• Databricks — What is a view?
• CREATE VIEW
• CREATE MATERIALIZED VIEW
• Materialized views
• Use standalone materialized views
• Tables and views

Next

05.5 SQL Functions & Advanced Expressions