A SQL view is a stored query that presents data as if it were a table. Views simplify complex query logic, provide a stable API for downstream consumers, and enforce a layer of abstraction between data consumers and underlying table structure. This guide explains how views work, their advantages and limitations, and when to use them versus materialized views.
A SQL view is a stored query that the database presents as if it were a table. When you query a view, the database executes the view's underlying SQL and returns the result. From the querying user's perspective, a view looks exactly like a table — it has column names, data types, and returns rows. The difference is that its contents are computed from a query rather than stored independently.
Views serve several purposes: they simplify complex queries by giving a name to a common join pattern, they provide a stable interface that hides underlying table complexity, they enforce a layer of security by exposing only certain columns or rows, and they reduce the need to repeat complex SQL across multiple queries.
How Views Work
Creating a view stores the SQL query definition in the database catalog. When you query the view (SELECT * FROM customer_summary), the database substitutes the view's definition — executing the underlying query against the base tables — and returns the result. The view's SQL runs at query time; the data is not stored separately.
This "run at query time" characteristic is both the view's main limitation and its main advantage:
**Advantage:** The view always returns current data because it queries the live base tables on every execution. There is no stale data problem — the view is always fresh.
**Limitation:** Every query against a complex view executes all of the view's underlying SQL. A view that joins five tables, filters with complex conditions, and aggregates results must execute all of that logic every time it is queried. For views over large tables used by many BI dashboards, this repeated execution is expensive.
Views as Abstraction Layers
A key use of views in data warehouse design is providing a stable query interface that decouples consumers from the underlying table structure.
Consider a large fact table that is restructured — columns renamed, a normalization change, a new join introduced. Without views, every downstream query and BI dashboard that references the old column names breaks. With a view that presents the old interface (mapping new column names to old names), downstream consumers continue working without changes while the underlying structure evolves.
This abstraction pattern is particularly valuable for:
- **Published data sources in Tableau:** A Tableau published data source connected to a view is insulated from changes to the underlying table structure. The view maintains the stable interface.
- **External API consumers:** Applications querying the database through a view layer are not affected by schema evolution in the underlying tables.
- **Analyst queries:** Analysts can query the view without understanding the underlying join complexity.
Views vs Materialized Views
A **regular view** is a stored query that executes at query time. No data is stored; query time is the sum of the view's complexity plus the caller's query complexity.
A **materialized view** (or materialized table, depending on the database) stores the query results physically. When the materialized view is queried, the database reads stored results rather than re-executing the underlying query. The stored results are refreshed either on demand, on a schedule, or incrementally as base table data changes.
Materialized views are appropriate when:
- The view query is expensive (many joins, large aggregations)
- The view is queried frequently with similar patterns
- The base data does not change so rapidly that staleness is problematic
In cloud data warehouses:
- **Snowflake** supports materialized views that maintain automatically updated stored results; the base query is pre-computed
- **BigQuery** supports materialized views with configurable refresh intervals; queries can use the materialized view automatically when the query optimizer determines it is beneficial
- **dbt** implements materializations (table, incremental, view) as a configuration choice per model — a dbt table materialization is functionally a materialized view maintained by dbt's incremental strategy
Views in dbt
dbt's view materialization creates database views from dbt model SQL. Each time a dbt view model is queried, the model's SQL is re-executed. This is appropriate for:
- Staging models that are lightweight queries over raw tables and are queried infrequently (dbt tests run them, but they are not the final consumption layer)
- Intermediate models that are referenced by other dbt models but not directly by BI tools
For final mart models (the dimensional tables Tableau connects to), dbt typically uses table or incremental materialization — storing the results as physical tables so BI queries are fast. The view materialization is left for upstream intermediate computation.
Security Views
Views can enforce row-level or column-level security by presenting a restricted subset of the underlying data:
A view that includes a WHERE clause filtering to specific rows effectively implements row-level security for users who can only access the view, not the base table. A view that selects only non-sensitive columns excludes PII from users with view access.
Database-level permission grants on views can allow users to query the view while denying direct access to the base tables. This is simpler than managing column and row permissions directly on tables, and provides a documented, reviewable security boundary.
Our data architecture services and data engineering practice designs SQL view architectures — including view-based abstraction layers, materialization strategies, and security view implementations. Contact us to discuss your data architecture requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →