Operations

Introduction

An operation defines a set of SQL commands that will be executed in order against your warehouse, and can be used for running SQL or warehouse specific that don't necessarily fit into the model of publishing a table or writing a test.

Simple example

To define a new operation action, create a file such as definitions/example.ops.sql:

create or replace view someschema.someview as select 1 as test

Multiple statements can be seperated with a single line containing only 3 dashes (---):

create or replace view someschema.someview as select 1 as test
---
drop view if exists someschema.someview

These statements will be run as is against the warehouse. You can use warehouse specific commands in these files, such as BigQuery's DML or DDL statements, or Redshift/Postgres specific commands.

Operations SQL files behave very similarly to the statements provided to postOps() and preOps() configuration statements that can be used when publishing tables.

Custom table builds

In some cases, you may want to create a table manually, rather than relying on Dataform's built in logic. This can be achieved by writing an operations file and specifying the full create table/view commands manually:

create or replace view sometable.someschema as (select 1 as test)

Declaring and referencing outputs

If an operations file creates a table or view that can be referenced using ref(), then you can use the configuration method hasOutput() to tell Dataform that this operations file can be referenced.

For example create the following file definitions/customview.ops.sql:

--js hasOutput(true);
create or replace view defaultschema.customview as (select 1 as test)
The output created must match the name of the file excluding the prefix in order for references to work properly. In this case customview. If you are creating a table in a custom schema, you can change the file name to reflect this to: definitions/customschema.customview.ops.sql.

The self() method can also be used to automatically reference the table or view denoted by the current file name. This saves you having to make sure they both match:

--js hasOutput(true);
create or replace view ${self()} as (select 1 as test)

References to "customview" will now resolve to "defaultschema.customview" and can be used in other SQL files, for example:

select * from ${ref("customview")}

Running vacuum commands (Redshift)

Redshift has a command vacuum that can be used to improve the performance of some tables. This is a common use case for operations:

vacuum delete only ${ref("sometable")} to 75 percent
---
vacuum reindex ${ref("sometable")}

In the above example, the ref() function can be used within the operations file like normal.

Reference

Check the operations reference for a list of all methods you can use in .ops.sql files.