SQL Views 101: Definition, Use-Cases & Best Practices

Learning Objectives

• Define what a SQL view is and how it differs from a table.
• Create, query, update, and drop views in popular databases (PostgreSQL, MySQL, Snowflake).
• Understand core use-cases: security, abstraction, reuse, performance.
• Avoid common pitfalls and follow best practices for naming, versioning, and performance.
• Practice every concept in Galaxy’s next-generation SQL editor.

Prerequisites

You should be able to write basic SELECT statements and understand primary key/foreign key relationships. No prior experience with views is required.

1. What Is a SQL View?

A view is a saved query definition that behaves like a virtual table. When you query a view, the database runs the underlying SELECT statement and returns the result set on demand.

1.1 Logical vs. Physical Storage

Unlike a table, a standard (non-materialized) view stores no data. It stores only the SQL text. Each time you access the view, the database recomputes the results.

-- Logical definition only
CREATE VIEW active_users AS
SELECT id, email, last_login
FROM users
WHERE last_login >= NOW() - INTERVAL '30 days';

Because it’s virtual, the data is always as fresh as the underlying tables. But it also means performance is tied to the complexity of the SELECT.

1.2 Materialized Views (Bonus)

Some databases support materialized views, which cache results physically and refresh on a schedule. They improve read performance at the cost of storage and freshness. This guide focuses on regular views, but nearly all concepts carry over.

2. Why Use Views?

2.1 Abstraction & Reuse

Wrap complex joins and calculations once and expose a clean interface:

CREATE VIEW customer_revenue AS
SELECT c.id,
c.name,
SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Analysts can now SELECT * FROM customer_revenue without re-writing the aggregation.

2.2 Security & Row-Level Access

Grant SELECT on a view instead of the base table to hide sensitive columns or rows.

CREATE VIEW public_users AS
SELECT id, username
FROM users
WHERE is_staff = FALSE;

GRANT SELECT ON public_users TO analytics_team;

2.3 Consistency & Single Source of Truth

By centralizing a formula (e.g., “active user”), you reduce metric drift across teams.

2.4 Performance Tuning

With materialized views or indexed views (SQL Server), you can precompute heavy aggregations.

3. Creating, Querying, and Managing Views

3.1 CREATE VIEW

General syntax:

CREATE [OR REPLACE] VIEW view_name AS
SELECT ...;

OR REPLACE allows you to overwrite an existing view atomically (supported in PostgreSQL, Snowflake, BigQuery).

Example: PostgreSQL

-- 1. Create
CREATE VIEW recent_orders AS
SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days';

-- 2. Query like a table
SELECT customer_id, COUNT(*)
FROM recent_orders
GROUP BY customer_id;

-- 3. Update definition
CREATE OR REPLACE VIEW recent_orders AS
SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL '14 days';

3.2 Updating Data Through a View

Can you INSERT, UPDATE, or DELETE rows via a view? It depends:

• PostgreSQL: Updatable if it references a single table and no aggregations.
• MySQL: Similar, but requires WITH CHECK OPTION for constraint enforcement.
• Snowflake: Views are read-only.

3.3 Dropping Views

DROP VIEW IF EXISTS recent_orders;

Careful! Dependent views or stored procedures may break.

4. Best Practices

• Name clearly: Use a prefix/suffix (e.g., v_ or _view) or descriptive nouns (daily_active_users).
• Version control: Store CREATE VIEW statements in Git. Galaxy autoversion and GitHub sync make this seamless.
• Watch performance: Avoid SELECT *; choose needed columns and index base tables.
• Document: In Galaxy, add a markdown cell or Collection description explaining business logic.
• Use OR REPLACE carefully: Accidental changes propagate instantly. Review diffs in Galaxy before committing.

5. Hands-On: Building Views in Galaxy

Follow this mini-project using Galaxy’s free workspace. We’ll use a sample e-commerce schema (customers, orders, order_items).

Step 1 — Load the Sample Data

If you’re on PostgreSQL, run:

\i https://raw.githubusercontent.com/galaxy-examples/ecommerce.sql

Galaxy’s AI copilot can auto-suggest this command if you start typing “load ecommerce”.

Step 2 — Define a View for Monthly Revenue

CREATE VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', created_at) AS revenue_month,
SUM(total) AS revenue
FROM orders
GROUP BY 1;

SELECT * FROM monthly_revenue ORDER BY revenue_month;

Use Galaxy’s inline chart (shortcut Shift + V) to visualize the revenue trend without leaving the editor.

Step 3 — Secure Customer PII

CREATE VIEW anon_customers AS
SELECT id,
LEFT(email, 3) || '***@' || SPLIT_PART(email, '@', 2) AS masked_email,
country
FROM customers;

GRANT SELECT ON anon_customers TO support_team;

Galaxy’s permission UI lets you map database roles to workspace roles so that only the support team can run this query.

Step 4 — Endorse and Share

Add both views to a “Finance Metrics” Collection and click “Endorse.” Teammates can now reference them with confidence.

6. Common Errors & Troubleshooting

• ERROR: column does not exist – A view broke after a schema change. Use Galaxy’s dependency graph to see which downstream views are affected before renaming a column.
• View is not materialized – Heavy queries slow down dashboards. Consider converting to a materialized view or adding indexes.
• Cannot insert into view – Ensure it’s updatable: no aggregates, one base table, primary key preserved.
• OR REPLACE drops privileges – In some DBs (PostgreSQL pre-13), privileges reset. Re-grant or use CREATE OR REPLACE VIEW ... WITH (security_barrier).

Key Takeaways & Next Steps

• A SQL view is a stored SELECT that acts like a virtual table.
• Views simplify complex logic, enforce security, and standardize metrics.
• Create views with CREATE VIEW, update with OR REPLACE, and manage them like code.
• Galaxy supercharges view workflows with AI suggestions, version control, and sharing.
• Next: Explore materialized views, indexed views, and view dependency management.

Happy querying — and may your views always stay in sync!