Create tables

In Dataform, a table is one of the types of objects that make up a workflow. You can create tables that reference data from the data sources declared for your workflow or from other tables in your workflow. Dataform compiles your table definitions into SQL in real time. When you trigger execution, Dataform runs the SQL code and creates your defined tables in BigQuery.

You can create the following table types in a type: "table" SQLX file:

You can also define table partitions and clusters.

To keep a record of the purpose of a table or its relation to other tables in your workflow, you can add documentation to the table or its selected columns.

To test the data in a table against specific conditions, you can create data quality test queries called assertions. Dataform runs assertions every time it updates your workflow and alerts you if any assertions fail.

To override the default schema, database, and name of a selected table, you can override table settings.

To disable table creation or run a SQL statement before or after table creation, you can configure additional actions.

To organize your tables in BigQuery after you run them, you can add BigQuery labels. To learn more, see Introduction to labels.

To restrict data access at the table column level, you can add BigQuery policy tags. To learn more, see Introduction to column-level access control.

In addition to defining tables in a type: "table" SQLX file, you can create empty tables by defining a custom SQL query in a type: "operations" SQLX file. You might want to create an empty table so that a different service can populate it with data.

Before you begin

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

    Go to Dataform

  2. Create and initialize a development workspace in your repository.

  3. Optional: Declare a data source.

Required roles

To get the permissions that you need to complete the tasks in this document, ask your administrator to grant you the following IAM roles:

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a table

This section shows you how to create tables with Dataform core in Dataform.

About table definitions

To define a table, you define the table type and write a SELECT statement in a type: "table" SQLX file. Dataform then compiles your Dataform core code into SQL, runs the SQL code, and creates your defined tables in BigQuery.

In a Dataform core SELECT statement, you define the table structure and reference other objects of your workflow.

In addition to defining tables in a type: "table" SQLX file, you can create empty tables by defining a custom SQL query in a type: "operations" SQLX file. For more information, see Create an empty table.

Reference dependencies with ref

To reference a workflow action in a SELECT statement and automatically add it as a dependency, use the ref function. Dataform runs dependencies before tables that depend on them to verify the correct pipeline ordering.

The ref function is a built-in Dataform core function that is critical to dependency management in Dataform. The ref function lets you reference and automatically depend on the following objects defined in your Dataform workflow, instead of hard coding the schema and table names:

Dataform uses the ref function to build a dependency tree of all the tables to be created or updated.

After compiling, Dataform adds boilerplate statements to the SQL statement, such as CREATE, REPLACE, INSERT, or MERGE.

The following code sample shows a table definition with the use of the ref function:

config { type: "table" }

SELECT
  order_date AS date,
  order_id AS order_id,
  order_status AS order_status,
  SUM(item_count) AS item_count,
  SUM(amount) AS revenue

FROM ${ref("store_clean")}

GROUP BY 1, 2

In the ref function, you provide the name of the table or data source declaration that you want to depend on. This is typically the filename of the SQLX file in which that table or data source declaration is defined.

If a table name is overridden, use the overridden name in the ref function. For example, reference a table with config { name: "overridden_name" } as ref("overridden_name"). For more information, see Override table settings and Reference a table with an overridden table name.

When you have multiple tables of the same name in different schemas, you can reference a specific table by providing two arguments to the ref function: schema name and table name.

The following code sample shows the ref function with two arguments to specify a table within a specific schema:

config { type: "table" }
SELECT * FROM ${ref("schema", "store_clean")}

You can also add table dependencies manually to the config block for tables, assertions, data source declarations, or custom SQL operations that are not referenced in a ref function in the SELECT statement. Dataform runs these dependencies before dependent tables.

The following code sample shows a table dependency in the config block:

config { dependencies: [ "unreferenced_table" ] }
SELECT * FROM ...

For more information on dependency management in your workflow, see Set dependencies.

Reference other tables with resolve

The resolve function lets you reference a table or data source declaration in a SELECT statement like the ref function, but it doesn't add the reference as a dependency. This means that the object referenced using the resolve function does not affect the execution of the table that uses the resolve function.

For more information on built-in Dataform core functions, see Dataform core reference.

Create a SQLX file for a table definition

Store table definition SQLX files in the