Stay organized with collections Save and categorize content based on your preferences.

Create and execute a SQL workflow in Dataform

This quickstart walks you through the following process in Dataform to create a SQL workflow and execute it in BigQuery:

Before you begin

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  4. Enable the BigQuery and Dataform APIs.

    Enable the APIs

  5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  6. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

  7. Enable the BigQuery and Dataform APIs.

    Enable the APIs

Required roles

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

  • Dataform Admin (roles/dataform.admin) on repositories
  • Dataform Editor (roles/dataform.editor) on workspaces and workflowInvocations

For more information about granting roles, see Manage access.

Create a Dataform repository

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

    Go to Dataform

  2. Click Add repository.

  3. On the Create repository page, do the following:

    1. In the Repository ID field, enter quickstart-repository.

    2. In the Region list, select europe-west4.

    3. Click Create repository.

Create and initialize a Dataform development workspace

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

    Go to Dataform

  2. Click quickstart-repository.

  3. Click Create development workspace.

  4. In the Create development workspace window, do the following:

    1. In the Workspace ID field, enter quickstart-workspace.

    2. Click Create development workspace.

    The development workspace page appears.

  5. Click Initialize workspace.

Create a view

In the following sections, you define a view that you will later use as a data source for a table.

Create a SQLX file for defining a view

  1. In the Files pane, next to definitions/, click the More menu.

  2. Click Create file.

  3. In the Create new file pane, do the following:

    1. In the Add a file path field, enter definitions/quickstart-source.sqlx.

    2. Click Create file.

Define a view

  1. In the Files pane, expand the definitions folder.

  2. Click definitions/quickstart-source.sqlx.

  3. In the file, enter the following code snippet:

    config {
      type: "view"
    }
    
    SELECT
      "apples" AS fruit,
      2 AS count
    UNION ALL
    SELECT
      "oranges" AS fruit,
      5 AS count
    UNION ALL
    SELECT
      "pears" AS fruit,
      1 AS count
    UNION ALL
    SELECT
      "bananas" AS fruit,
      0 AS count
    

Create a table

In the following sections, you define the table type in a SQLX file, and then write a SELECT statement to define the table structure within the same file.

Create a SQLX file for table definition

  1. In the Files pane, next to definitions/, click the More menu, and then select Create file.

  2. In the Add a file path field, enter definitions/quickstart-table.sqlx.

  3. Click Create file.

Define the table type, structure and dependencies

  1. In the Files pane, expand the definitions/ directory.

  2. Select quickstart-table.sqlx, and then enter the following table type and SELECT statement:

    config {
      type: "table"
    }
    
    SELECT
      fruit,
      SUM(count) as count
    FROM ${ref("quickstart-source")}
    GROUP BY 1
    

Grant Dataform access to BigQuery

To execute workflows in BigQuery, the Dataform service account must have the following required roles:

  • BigQuery Data Editor on projects to which Dataform needs both read and write access. They usually include the project hosting your Dataform repository.
  • BigQuery Data Viewer on projects to which Dataform needs read only access.
  • BigQuery Job User on the project hosting your Dataform repository.

To grant these roles, follow these steps:

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

    Go to the IAM page

  2. Click Add.

  3. In the New principals field, enter your Dataform service account ID.

  4. In the Select a role drop-down list, select the BigQuery Job User role.

  5. Click Add another role, and then in the Select a role drop-down list, select the BigQuery Data Editor role.

  6. Click Add another role, and then in the Select a role drop-down list, select the BigQuery Data Viewer role.

  7. Click Save.

Execute the workflow

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

    Go to Dataform

  2. On the quickstart-workspace page, click Start execution.

  3. Click All actions.

  4. In the Execute pane, click Start execution.

    Dataform uses the default repository settings to create the contents of your workflow in a BigQuery dataset called dataform.

View execution logs in Dataform

  1. On the quickstart-repository page, click Workflow Execution Logs.

  2. To view details of your execution, click the latest execution.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

Delete the dataset created in BigQuery

To avoid incurring charges for BigQuery assets, delete the dataset called dataform.

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

    Go to BigQuery

  2. In the Explorer panel, expand your project and select dataform.

  3. Click the Actions menu, and then select Delete.

  4. In the Delete dataset dialog, enter delete into the text box, and then click Delete.

Delete the Dataform development workspace

There are no costs associated with creating Dataform development workspaces. However, if you wish to delete the development workspace, follow these steps:

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

    Go to Dataform

  2. Click quickstart-repository.

  3. In the Development workspaces tab, click the More menu by quickstart-workspace, and then select Delete.

  4. To confirm, click Delete.

Delete the Dataform repository

There are no costs associated with creating Dataform repositories. However, if you wish to delete a repository and all its contents, follow these steps:

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

    Go to Dataform

  2. By quickstart-repository, click the More menu, and then select Delete.

    1. In the Delete repository window, enter the name of the repository to confirm deletion.
  3. To confirm, click Delete.

What's next