Procedures

A view is one query. A procedure is a named block of SQL that can do several things in order: create a table, clear a slice of it, refill it from your landing tables, commit between steps. You run it with arguments, by hand or from a task.

Use a procedure when a view cannot express the work, for example to keep an incrementally updated reporting table, or to prepare data in steps. Only owners and admins can create, run or drop one.

What a procedure may do

A procedure runs as the warehouse's builder role. That role can:

• read every table in raw and derived, and the tables in mapping;
• create, change and drop objects in derived, and read and write the tables it makes there;
• commit between steps, when the body is plpgsql.

It cannot write anywhere else, and it cannot touch a published mapping view, sanda's bookkeeping, or another database, whatever the body says. sanda does not scan a procedure body for what it might do, because no scanner could vouch for arbitrary code. The role's permissions are the boundary.

What a procedure can do is spend compute. That is why creating and running one needs an owner or admin, and why every run is recorded with its duration and cost.

Create a procedure

begin
  create table if not exists derived.daily_sales (
    sale_date date primary key,
    revenue numeric not null
  );

  delete from derived.daily_sales where sale_date >= since;

  insert into derived.daily_sales (sale_date, revenue)
  select s.sale_date, sum(s.revenue)
  from raw.csv__sales s
  where s.sale_date is not null
    and s.sale_date >= since
  group by s.sale_date;

  commit;
end

This one clears everything from a date onward, then rebuilds it, so it is safe to run twice. Table and column names depend on your data. Find them in the Explorer.

In the sql language the body is just the statements, one after another. PostgreSQL checks a sql body when you create the procedure, so this version needs derived.daily_sales to exist already:

delete from derived.daily_sales where sale_date >= since;
insert into derived.daily_sales (sale_date, revenue)
select s.sale_date, sum(s.revenue)
from raw.csv__sales s
where s.sale_date is not null and s.sale_date >= since
group by s.sale_date;

Preview

A procedure has nothing to preview, so the builder shows Source preview: a read-only look at the source data, when a query for it is provided, and a Steps tab listing the steps cherry planned when it drafted the procedure. The procedure has not run, and those rows are not a simulation of its output.

Run a procedure

Select the procedure and, if it takes arguments, type them in the box beside Run, separated by commas and in the order they were declared. An empty argument is passed as null. Press Run.

When it finishes, sanda says how long it ran and what it was billed as, for example Ran in 4.2 s, billed as 4.2 s of compute. A run started from the page has 55 seconds. If it takes longer, it is stopped, and sanda suggests adding it to a task, which has up to 14 minutes.

If the arguments do not fit, sanda says how many it takes and what they are called.

Keep it running

Press Schedule task on the object, or use Save & schedule when you save it. A task calls the procedure with the arguments you give, on a schedule or after a sync. See Tasks.

Redefine and drop

Redefine replaces the procedure. A name is one object, so changing the arguments replaces the procedure rather than adding a second version with a different signature. Each definition is a version, and the old ones stay in the history.

Drop asks you to confirm. The procedure goes and its definitions stay in the history. A task that calls it skips that step. Tables the procedure built are not dropped.

History

The History tab shows every version with the statement sanda ran, every run with its duration, cost and who started it, and the audit records. It is the same tab as in the Explorer.