This document shows you how to do the following:

• Define a SQL statement to be run before table creation.
• Define a SQL statement to be run after table creation.
• Disable table creation.
• Add execution tags.

Before you begin

1. In the Google Cloud console, go to theDataform page.

   Go to Dataform

2. Select orcreate a repository.

3. Select orcreate a development workspace.

4. Create a table.

5. Define a SQLX file of any of the following types:

   • type: table
   • type: view
   • type: incremental
   • type: assertion
   • type: operations

Required roles

To get the permissions that you need to complete the tasks in this document, ask your administrator to grant you theDataform Editor (roles/dataform.editor) IAM role on workspaces. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Define a SQL statement to be run before table creation

You can configure Dataform to run one or more SQL statementsbefore creating a selected table in BigQuery. To run a SQLstatement before Dataform creates a selected table, add yourstatement to thepre_operations block in the table definition SQLX file.

To create a custom SQL statement that's run before Dataform createsa specific table, follow these steps:

1. Go to your development workspace.
2. In theFiles pane, expanddefinitions/.
3. Open a SQLX table definition file.
4. Outside theconfig block, enterpre_operations { ... }.
5. Insidepre_operations { ... }, add your SQL statement.
6. Optional: To add multiple statements, separate them with---.
7. Optional: ClickFormat.

The following code sample shows apre_operations statement that creates atemporary function that can be used in the select statement:

pre_operations {  CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)    RETURNS FLOAT64    AS ((x + 4) / y);}

Define a SQL statement to be run after table creation

You can configure Dataform to run one or more SQL statementsafter creating a selected table in BigQuery. To run a SQL statementafter Dataform creates a selected table, add your statement tothepost_operations block in the table definition SQLX file. You can addmultiple SQL statements to thepost_operations block.

To create a custom SQL statement that's run after Dataform createsa specific table, follow these steps:

1. Go to your development workspace.
2. In theFiles pane, expanddefinitions/.
3. Open a SQLX table definition file.
4. Outside theconfig block, enterpost_operations { ... }.
5. Insidepost_operations { ... }, add your SQL statement.
6. Optional: ClickFormat.

The following code sample showspost_operations statements that grant groupsaccess to the created table:

  post_operations {    GRANT `roles/bigquery.dataViewer`    ON    TABLE ${self()}    TO "group:allusers@example.com", "user:otheruser@example.com"  }

Disable table creation

To stop Dataform from creating a selected table inBigQuery, you can disable the table in its SQLX table definition file.Dataform keeps a disabled table in the dependency graph, but it doesnot compile and create it. This can be useful, for example, if a table fails andyou don't want your whole workflow to fail while you fix the issue.

To disable a table, follow these steps:

1. Go to your development workspace.
2. In theFiles pane, expanddefinitions/.
3. Select a SQLX table definition file.
4. In theconfig block of the file, enterdisabled: true.
5. Optional: ClickFormat.

The following code sample shows a disabled table:

config {  type: "table",  disabled: true}select * from ${ref("source_data")}

Add execution tags

This section shows you how to add tags toDataform core SQLX files tocategorize your workflow.

To organize the components of your workflow into collections, you can addcustom tags to SQLX files of the following types:

• table
• view
• incremental
• assertion
• operations

During a workflow run, you canrun only files with a selected tag.

WithCloud Composer orWorkflows together with Cloud Scheduler,you can create a schedule that runs a Dataform workflow witha selected tag at a specific interval.

Add a tag

You can add multiple tags to a SQLX file.

To add a tag to a SQLX file, follow these steps:

1. Go to your development workspace.
2. In theFiles pane, expanddefinitions/.
3. Select a SQLX file.

4. In theconfig block, add a tag in the following format:

   tags: ["CUSTOM_TAG"]

   ReplaceCUSTOM_TAG with your tag.

5. Optional: To add multiple tags, separate them with a comma (,).

6. Optional: ClickFormat.

The following code sample shows theuser_counts view with thedaily andhourly tags:

config {  type: "view",  name: "user_counts",  tags: ["daily", "hourly"]}

What's next

• To learn how to configure Dataform settings inworkflow_settings.yaml, seeConfigure Dataform workflow settings.
• To learn how to test table data with assertions, seeTest data quality.
• To learn how to reuse code with includes, seeReuse code across a single repository with includes.
• To learn to manually trigger executions, seeManually trigger runs.
• To learn how to add BigQuery policy tags in Dataform, seeControl column access.