Views and modelled tables

A view is a saved query with a name. Once it exists in the derived schema, you can read it from other views, put it on the semantic fluid as a table, and rebuild it on a schedule. This page covers views and materialized views. For work that takes several steps, use a procedure.

Only owners and admins can create, change or drop them, because what they make costs compute.

Choose a kind

Start with a view. Move to a materialized view when the query is slow or heavy and the answer only needs to be as fresh as a schedule. In the builder the two are labelled View · always current and Materialized view · scheduled refresh.

Names and rules

• A name is lowercase letters, digits and underscores, starts with a letter, is at most 63 characters, and does not begin with pg_. The object becomes derived.<name>.
• A name must be free. It cannot match a landing table or a published view.
• A view is one select, or a with query that ends in a select. No semicolons, no comments, no other kind of statement, at most 20,000 characters. The SQL reference lists exactly what is refused.
• It reads what the warehouse holds, by schema-qualified name: landing tables (raw.<connection>__<stream>), other derived views, and the tables in mapping. It cannot read a mapping view that the fluid publishes: read the table behind it.

Write a view in SQL

select
  date_trunc('month', s.sale_date)::date as month,
  s.region,
  sum(s.revenue) as revenue
from raw.csv__sales s
group by 1, 2

The table and column names depend on your data. Find them in the Explorer. Landing tables from a CSV are named raw.csv__<name>, and from a connection raw.<connection>__<stream>.

Draw a view

Visual builder builds the same view without typing SQL. Its title is Draw a view.

The drawing is saved beside the SQL, so Redefine on a drawn view reopens the drawing. A view written as SQL reopens in the SQL box.

Ask cherry

Press Describe the view to cherry, or + for the builder with Ask cherry beside it. cherry drafts a name, a description and a query. Edit any of it, watch Live preview, and press Save model. See Modelling for how drafting works.

Materialized views

A materialized view stores its rows, so it has a life a plain view does not.

The first build happens when you create it. It runs on your compute and is billed as the seconds it takes. If it outlasts 55 seconds, sanda stops it and still creates the view, empty, with a message saying so. Schedule it and sanda builds it in the background, with up to 14 minutes.

Refresh now rebuilds it. When it finishes, sanda says how long it took, how many rows and how much storage it holds, and how much compute it was billed as. The object shows built with the time and duration.

A unique key is the columns that identify one row, for example month, region. With one, a refresh runs without blocking anyone who is reading the view. Without one, readers wait for the build. The first build is always the blocking kind, because PostgreSQL cannot refresh an empty view any other way. The object's built line says which one it uses: refreshes concurrently on the key columns, or refreshes block readers (no unique key).

Keep it fresh with Schedule task on the object, or Save & schedule in the builder. See Tasks.

If a build fails, the object shows last build failed with the error, and it stays that way until a build succeeds. A build that only ran out of time is not marked as broken.

Put it on the semantic fluid

With the fluid box ticked, sanda publishes the view as a table on the fluid the moment it is saved, so cherry and reports can use it. If you left it off, or you want to add one later, press Put on the fluid on the object.

From then on the object shows on the fluid as its table name, with a link. Add relationships and metrics to it on the semantic fluid page. See Tables on the fluid.

The fluid reads one warehouse: the newest healthy one you own. An object in another warehouse is real and readable, but it cannot be put on the fluid.

Redefine a view

Select the object and press Redefine. The name and the kind stay fixed; to rename, drop and recreate. A new version is recorded and the old definition stays in the history.

A redefinition that only adds columns always works. One that removes or reorders columns needs the view dropped and recreated. sanda does that unless another object reads the view, in which case it stops and names the dependents. Redefine or drop those first. sanda never cascades.

Drop a view

Dropping refuses while another derived object reads it, and names it. A refused drop changes nothing: the object stays, and so does its place on the fluid. If the drop goes ahead and the object is on the fluid, sanda takes it off first and retires the definitions built on that table. A task that had a step for it skips that step with a message. Steps set to run only if the step before succeeded are skipped too, and the rest carry on.

Every change is on the record

Every definition is a version. Every build is a run with its seconds and its cost. Open the object's History tab, here or in the Explorer, to see them, with the statement sanda ran and who asked. An agent's change is recorded the same way, with the credential's name.

Errors you may meet