DATA ENGINEERING LAB · SQL VIEW

Give a complex query a stable name—and make data easier to trust.

A VIEW is a stored query presented like a table. In data engineering, its deeper value is not shorter SQL. It creates a governed interface between changing source tables and the people, dashboards and pipelines that depend on them.

Start the lab →Why views matter ↓

Create

Define a reusable relational interface with CREATE VIEW.

Govern

Expose useful columns while hiding internal or sensitive details.

Maintain

Change transformation logic once instead of editing every consumer.

Validate

Test grain, uniqueness, nulls and totals before publishing a view.

MENTAL MODEL

A view stores logic, not another copy of the data.

When a normal view is queried, the database expands its definition and reads the underlying relations. This is different from a materialized view, which stores query results and must be refreshed.

RAWorders + customers

→

VIEWanalytics.v_order_value

→

CONSUMERSBI · notebook · API

VIEW

Always reflects underlying data; query cost is paid when read. Best for abstraction, governance and reusable logic.

MATERIALIZED VIEW

Stores computed results; reads can be faster but freshness depends on refresh policy. Best for expensive, repeated aggregation.

TABLE

Owns persisted rows and a write lifecycle. Use when the dataset needs independent storage, history or incremental loading.

01 · SET UP THE SOURCE

Begin with operational tables, not a polished report.

Run the statements in PostgreSQL, DuckDB or another SQL database after adapting types and syntax where necessary.

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS analytics;

CREATE TABLE raw.customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  email VARCHAR(150),
  segment VARCHAR(30) NOT NULL
);

CREATE TABLE raw.orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES raw.customers(customer_id),
  ordered_at TIMESTAMP NOT NULL,
  status VARCHAR(20) NOT NULL,
  amount DECIMAL(12,2) NOT NULL
);

INSERT INTO raw.customers VALUES
 (1,'Aster Studio','[email protected]','SME'),
 (2,'Blue River','[email protected]','Enterprise'),
 (3,'Cedar Lab','[email protected]','Education');

INSERT INTO raw.orders VALUES
 (101,1,'2026-08-01 09:15','completed',12500.00),
 (102,1,'2026-08-03 13:40','cancelled',3200.00),
 (103,2,'2026-08-04 10:10','completed',54000.00),
 (104,3,'2026-08-05 16:25','pending',7800.00),
 (105,2,'2026-08-07 11:30','completed',18500.00);

02 · CREATE THE FIRST VIEW

Publish one row per completed order.

The grain—what one row represents—is part of the contract. State it before writing SQL.

CREATE VIEW analytics.v_completed_orders AS
SELECT
  o.order_id,
  o.ordered_at,
  CAST(o.ordered_at AS DATE) AS order_date,
  c.customer_id,
  c.customer_name,
  c.segment,
  o.amount
FROM raw.orders AS o
JOIN raw.customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

SELECT *
FROM analytics.v_completed_orders
ORDER BY ordered_at;

Expected contractGrain: one completed order · Key: order_id · Sensitive email: not exposed

03 · BUILD A REUSABLE METRIC

Let every consumer calculate revenue the same way.

The second view depends on the first. This layering is useful when each layer has a clear responsibility; uncontrolled chains of views become difficult to debug.

CREATE VIEW analytics.v_daily_revenue AS
SELECT
  order_date,
  COUNT(*) AS completed_orders,
  COUNT(DISTINCT customer_id) AS active_customers,
  SUM(amount) AS revenue,
  AVG(amount) AS average_order_value
FROM analytics.v_completed_orders
GROUP BY order_date;

SELECT *
FROM analytics.v_daily_revenue
ORDER BY order_date;

Think before continuing

Should pending orders count as revenue? Should refunds reduce the original day or the refund day? SQL cannot decide accounting policy. The view must encode a definition agreed by the data owner.

04 · CHANGE WITHOUT BREAKING CONSUMERS

Treat the view name and columns as an interface.

CREATE OR REPLACE VIEW can update compatible logic while preserving the object name. Engines differ on whether columns may be removed, reordered or have types changed—verify your database.

CREATE OR REPLACE VIEW analytics.v_daily_revenue AS
SELECT
  order_date,
  COUNT(*) AS completed_orders,
  COUNT(DISTINCT customer_id) AS active_customers,
  SUM(amount) AS revenue,
  AVG(amount) AS average_order_value,
  MAX(amount) AS largest_order
FROM analytics.v_completed_orders
GROUP BY order_date;

Do not use SELECT * in a published contract

A new source column could silently enter downstream datasets. Explicit columns make schema review and lineage easier.

05 · VALIDATE BEFORE PUBLISHING

A view is useful only if its contract can be tested.

Run these checks after source or transformation changes. In production, automate them with dbt, orchestration or a data-quality framework.

Uniqueness

SELECT order_id, COUNT(*)
FROM analytics.v_completed_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Expected: zero rows

Required values

SELECT COUNT(*) AS invalid_rows
FROM analytics.v_completed_orders
WHERE customer_id IS NULL
   OR amount IS NULL
   OR amount < 0;

Expected: 0

Reconciliation

SELECT
 (SELECT SUM(amount) FROM raw.orders
  WHERE status='completed') AS source_total,
 (SELECT SUM(revenue)
  FROM analytics.v_daily_revenue) AS view_total;

Expected: equal totals

06 · DATA ENGINEERING VALUE

The real benefit appears at the boundary between teams.

Views are lightweight architecture. Their value becomes visible when source owners, data engineers, analysts, application developers and governance teams need to share data without sharing every internal detail. The view becomes a small but explicit agreement about what others may rely on.

01SOURCE TEAM → DATA CONSUMER

Abstraction

Hide how data is stored; publish what the business concept means.

Picture the situation

The order system stores status as C, P and X across orders, status_history and customer tables. An analyst should not need to learn three schemas and decode internal codes before answering “How much did we sell yesterday?”

THE VIEW PUBLISHES

order_id, order_date, customer_segment, order_status, net_amount with readable values and an explicit grain.

Result: Source tables may remain optimized for transactions while consumers work with a stable analytical vocabulary. Abstraction does not remove the need for lineage—the view must still show where each field came from.

02DATA ENGINEER → MANY ANALYSTS

Reusable transformation

Write difficult logic once, review it once, then let many consumers reuse it.

Picture the situation

Finance excludes cancelled orders, Marketing includes paid orders awaiting fulfilment, and Operations uses shipment date instead of order date. Each dashboard contains a slightly different 40-line query, so three “revenue” numbers appear in the same meeting.

THE VIEW CENTRALIZES

Joins, status filters, casts, currency conversion and deduplication are reviewed as one transformation instead of copied into every notebook and dashboard.

Result: A correction is made once and reaches every consumer on the next query. Reuse is safe only when the shared definition really represents a shared concept; different business meanings may deserve different views.

03DATA PRODUCER → DOWNSTREAM PIPELINE

Data contract

Turn an informal query into an interface with a name, grain, key and expectations.

Picture the situation

A machine-learning pipeline assumes one row per customer, while a new join silently creates one row per customer per address. The pipeline still runs, but customers with several addresses receive more weight in training.

THE CONTRACT STATES

One row per customer; customer_id is unique and non-null; amount is THB; data is ready daily by 07:00; owner and breaking-change process are documented.

Result: Downstream teams know what may be assumed and can automate tests around it. A view alone is not a complete contract—the promise also needs documentation, ownership, freshness monitoring and change communication.

04DATA OWNER → AUTHORIZED USER

Security boundary

Give people the data needed for the task without handing them the entire source.

Picture the situation

A university analyst needs enrolment counts by faculty and year. The student table also contains names, personal email, telephone numbers and national identifiers. Granting SELECT on the base table exposes far more than the analysis requires.

THE VIEW EXPOSES

Faculty, academic year and aggregated counts—or approved pseudonymous identifiers—while direct access to the base table remains revoked.

Result: The access surface becomes smaller and easier to audit. But database ownership rules, row-level policies, small-group inference and permissions must still be tested with the consumer role.

05BUSINESS OWNER → BI / ML / API

Semantic consistency

Make the same term mean the same thing across tools.

Picture the situation

“Active customer” means logged in within 30 days to Product, purchased within 90 days to Marketing, and has an open contract to Finance. None is inherently wrong, but an unlabeled metric lets users compare values that answer different questions.

THE SEMANTIC VIEW NAMES

v_product_active_customer_30d, v_purchasing_customer_90d and definitions approved by the relevant owners. Consumers select a concept instead of inventing another one.

Result: Meetings move from arguing about whose SQL is correct to choosing which business question is being answered. Consistency does not mean forcing every context into one universal metric.

06PLATFORM TEAM → EXISTING CONSUMERS

Migration buffer

Change the machinery behind a stable interface, then migrate consumers deliberately.

Picture the situation

Orders move from a legacy database to a new platform. Table names, timestamp types and customer keys change, but twelve dashboards, two scheduled exports and one model still query the old analytical shape. A big-bang switch risks breaking all of them together.

THE VIEW PRESERVES

Existing column names, types and grain while reading from the new source. A v2 view exposes the improved schema so consumers can migrate and validate one by one.

Result: Migration risk is spread across observable steps and rollback remains possible. The compatibility view needs an expiry date, or a temporary bridge becomes permanent technical debt.

THE HANDOFF PRINCIPLE

A good view does not merely make SQL shorter. It lets the producer change internal implementation while giving the consumer a clear, testable and appropriately governed promise.

07 · GOVERN ACCESS

Expose the product, not the private source.

The exact security semantics depend on the database. Ownership, invoker/definer rights, row-level security and inference risks must be reviewed—not assumed.

-- Example role names; adapt to your environment.
REVOKE ALL ON raw.customers, raw.orders FROM analyst_role;
GRANT USAGE ON SCHEMA analytics TO analyst_role;
GRANT SELECT ON analytics.v_completed_orders,
                analytics.v_daily_revenue
TO analyst_role;

A view is not automatically a security wall

Test permissions using the consumer role. Review whether predicates, functions, error messages or aggregates could reveal protected information.

CHALLENGES

Move from syntax to engineering judgement.

Create v_customer_lifetime_value with one row per customer; include customers with no completed orders.

Add a refund table and define net revenue. Write the business assumption before writing SQL.

Create an analyst-safe view that contains customer_id and segment but excludes name and email.

Use EXPLAIN to compare a direct query and a query through the view. Explain why a view is not a performance guarantee.

Design v_daily_revenue_v2 without breaking v_daily_revenue. State a deprecation plan.

Decide whether one expensive monthly aggregate should be a view, materialized view or table—and justify freshness, cost and recovery.

FINAL CHECK

Do not publish a view until you can describe its promise in one paragraph.

What does one row mean?
Which columns form the key?
How fresh is the result?
Who owns the metric definition?
Which changes are considered breaking?
How will quality and access be tested?