Publish / database · All operations

Publish as a view

A variant of Write to a database

Save the query as a view or materialised view in the database instead of writing its rows.

Status

In arcform

Not built yet

arcform does not do this yet. Its place on the roadmap

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (2)
  • CREATE VIEWread 2026-09-27

    Create a virtual table from a query

  • into_viewread 2026-09-27

    DataFrame method: Turn the DataFrame into a table provider that can be registered as a view

Bruin (1)
  • viewread 2026-09-27

    Materialize the asset query as a view

dbt-utils (2)
  • materialized_viewread 2026-09-27

    Create and maintain a materialized view that the platform refreshes

  • viewread 2026-09-27

    Rebuild the model as a view on each run via create view as

SQLMesh (2)
  • VIEWread 2026-09-27

    Create or replace a non-materialized view; the default kind

  • VIEW (materialized)read 2026-09-27

    VIEW with materialized true, on engines that support materialized views

Background

In a database, a view is the result set of a stored query: a virtual table computed from the data in the database when the view is accessed.[1] Changes to the data in a view's underlying tables are reflected in later queries of the view.[1] A materialised view, by contrast, is a database object that contains the results of a query.[2]

When to use it

In dbt, a view stores no additional data and always holds the latest records, but a view that performs significant transformation, or is stacked on other views, is slow to query.[3] Start with a view, and store the result only once reading it becomes slow. (Meridian's view)

A view can limit exposure of the underlying tables: a user may be allowed to query the view while denied access to the rest of the base table.[1] A view takes very little space to store, since the database holds only its definition, not a copy of the data it presents.[1]

A materialised view gives much more efficient access, at the cost of extra storage and of some data being potentially out of date.[2] In PostgreSQL, a materialised view is given fresh data with REFRESH MATERIALIZED VIEW, which a job can be scheduled to run, for example each night.[4] Choose a materialised view only when readers can live with data as old as the last refresh, and say how old that is. (Meridian's view)

The Structured Query Language (SQL) standard, as of SQL:2003, does not allow an ORDER BY clause in the subquery of a CREATE VIEW statement, which defines a view, though some systems, such as Oracle Database, do not abide by the restriction.[1] In PostgreSQL, CREATE OR REPLACE VIEW replaces an existing view only if the new query generates the same columns, with the same names, order and types, though it may add columns at the end.[5]

The method

In DuckDB and PostgreSQL, CREATE VIEW defines a view of a query that is not physically materialised; instead the query runs every time the view is referenced.[6][5]

In PostgreSQL, a materialised view stores its query the same way a view does, and keeps the query's results in a table-like form.[4] Materialised views were first implemented by Oracle Database, and PostgreSQL has supported them natively since version 9.3.[2] Microsoft SQL Server implements materialised views as indexed views, which need no refresh because they are always synchronised with the tables they are built on.[2]

Compared with the alternatives

In PostgreSQL, a materialised view differs from a table made with CREATE TABLE AS in that it cannot be updated directly and keeps the query it was made from, so it can be refreshed.[4] In dbt, a table materialisation is fast to query but can take a long time to rebuild, and new records in the source data are not added to it automatically.[3] In dbt, materialised views combine the query performance of a table with the data freshness of a view, but may not be supported by every database platform.[3]

Sources

  1. [1]View (SQL) - Wikipediasecondary source
  2. [2]Materialized view - Wikipediasecondary source
  3. [3]Materializations | dbt Developer Hubdocumentation
  4. [4]PostgreSQL: Documentation: 18: 39.3. Materialized Viewsdocumentation
  5. [5]PostgreSQL: Documentation: 18: CREATE VIEWdocumentation
  6. [6]CREATE VIEW Statement – DuckDBdocumentation