# Views and modelled tables

> Create a view or materialized view, preview it, put it on the semantic fluid, and refresh, redefine or drop it.

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](https://docs.sanda-os.com.au/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](https://docs.sanda-os.com.au/modelling/procedures).

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

## Choose a kind

| | View | Materialized view |
|---|---|---|
| Stores its rows | No | Yes |
| Runs its query | Every time it is read | When it is built or refreshed |
| Freshness | Always current | As of its last build |
| Cost | Nothing until it is read | Each build is billed as the seconds it runs |
| Can be scheduled | No, it has nothing to refresh | Yes, in a [task](https://docs.sanda-os.com.au/modelling/tasks) |

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](https://docs.sanda-os.com.au/reference/sql) 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

:::steps
1. **Open Write SQL.** In **Data · Modelling**, on the **Views** tab, press **Write SQL** under **Or start your way**. The **+** button beside **Your views** opens a different builder, with the definition beside a live preview and an **Ask cherry** button.
2. **Name it.** Type a **Name** and, if you like, a **Description** (shown in the Explorer and to agents).
3. **Choose the kind.** **View** or **Materialized view**. For a materialized view, add a **Unique key** if you can (see below).
4. **Write the query.** Type a `select` in the **SELECT** box.
5. **Preview it.** Press **Preview 50 rows**. The rows appear under the box. If the query is wrong, PostgreSQL's own message is shown as it came, because you wrote the SQL.
6. **Decide about the fluid.** **Put it on the semantic fluid, so sanda may query it** is ticked by default. In the **+** builder the same box reads **Add to semantic fluid**.
7. **Create it.** The button reads **Create view**, or **Create and build** for a materialized view.
:::

```sql title="Revenue by month and region"
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](https://docs.sanda-os.com.au/warehouse/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**.

:::steps
1. **Bring in a table.** The list on the left is every table the warehouse holds that you may build on, in three groups: landing (`raw`), yours (`derived`) and modelled (`mapping`). Press **use** beside one to start from it. Press **use** on a second to join it. If a table is already on the semantic fluid, its relationships come along as suggested joins.

   A drawn view can read a table from any of the three groups. The fluid's published `mapping` views are never listed, because nothing may be built on them.
2. **Choose columns.** Click a column, or drag it, into the **columns** area. Each output column has a name you can edit.
3. **Wrap a column in a function.** Press **fx** beside a column to apply `concat`, `coalesce`, `nullif`, `lower`, `upper`, `trim`, `length`, `abs`, `round`, `date_trunc`, or an aggregate: `sum`, `count`, `avg`, `min`, `max`. Arithmetic, joining text with `||` and casts are also available, and each can be unwrapped in one press. An aggregate turns the view into a group-by over everything that is not aggregated.
4. **Filter rows.** Under **rows to keep**, type an optional SQL condition using the table aliases, for example `t0.status = 'AUTHORISED'`.
5. **Name it and choose the kind.** The same choices as above, except that a **unique key** here is one output column, chosen from a list.
6. **Preview and create.** Press **preview 50 rows**, then **create view** (or **create and build**).
:::

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](https://docs.sanda-os.com.au/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](https://docs.sanda-os.com.au/modelling/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](https://docs.sanda-os.com.au/semantic-fluid/tables).

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

:::steps
1. **Select the object** in the list and press **Drop**.
2. **Read the confirmation.** It says what goes, that its definitions stay in the history, whether it comes off the fluid first, and which tasks will skip it.
3. **Confirm.**
:::

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](https://docs.sanda-os.com.au/warehouse/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

| You see | What it means |
|---|---|
| A landing table is already called `name`. Pick another name. | The name matches a table in `raw`. |
| A published view is already called `name`. Pick another name. | The name matches a view the fluid publishes. |
| `derived.name` is already a view (or materialized view or procedure). | A name has one kind. Drop it first, or pick another name. |
| A view is one SELECT (or WITH … SELECT)... | The text broke the rules above. |
| `mapping.name` is a view the semantic fluid publishes... | A view cannot be built on a published view. Read the table behind it. |
| `derived.a` reads `derived.b`. Drop or redefine it first. | Something depends on what you are changing. |
| This materialized view has not been built yet. Refresh it first. | Read it after its first build succeeds. |
| Permission denied | You read something the builder role cannot. It reads `raw`, `derived` and the tables in `mapping`, and writes only `derived`. |

:::links
- [Procedures](https://docs.sanda-os.com.au/modelling/procedures): Multi-step work that a view cannot do.
- [Tasks](https://docs.sanda-os.com.au/modelling/tasks): Rebuild materialized views on a schedule or after a sync.
- [SQL reference](https://docs.sanda-os.com.au/reference/sql): Exactly what a view definition may contain.
:::