Overview

This lab walks you through the process to create and execute a SQL workflow in Dataform to load data in BigQuery.

Learning objectives

1. Create a Dataform repository.
2. Create and initialize a Dataform development workspace.
3. Create and execute a SQL workflow.
4. View execution logs in Dataform.

Setup

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Task 1. Create a Dataform repository

1. In the Console, expand the Navigation menu, then select BigQuery > Dataform.

2. Click CREATE 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 .

   3. Click CREATE.

   4. Once the repository is created, you will see the Dataform service account. Please copy this down, so you can use it later to assign necessary permissions.

   5. Click Go to Repositories.

Note: If you get permission denied error related to API request wait for few minutes and create the repository again.

Test completed task

Click Check my progress to verify the objective.

Create a Dataform repository.

Task 2. Create and initialize a Dataform development workspace

1. On the Dataform page, click on the quickstart-repository repository you just created.

2. Click CREATE DEVELOPMENT WORKSPACE.

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

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

   2. Click CREATE.

4. Once created, click on the quickstart-workspace development workspace.

5. Click INITIALIZE WORKSPACE.

Test completed task

Click Check my progress to verify the objective.

Create a Dataform workspace.

Task 3. Create a SQLX file for defining a view

In this section, you define a view that you will later use as a data source for a table.

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 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

Task 4. Create a SQLX file for table definition

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.

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

Note: You might notice an error note in the compiled queries section. Ignore the message and proceed to further steps to execute the workflow.

Task 5. Grant Dataform access to BigQuery

1. In the Google Cloud console, on the Navigation menu (), select IAM & Admin > IAM.

2. Click VIEW BY PRINCIPALS. Next, click GRANT ACCESS

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.

Test completed task

Click Check my progress to verify the objective.

Grant Dataform access to BigQuery.

Task 6. Execute the workflow

1. In the Console, go to Navigation Menu > BigQuery > Dataform.

2. Click on quickstart-repository to open the repository.

3. Click on quickstart-workspace to open the development workspace.

4. On the quickstart-workspace page, click START EXECUTION.

5. Click Execute actions.

6. Click All actions tab.

7. 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.

Test completed task

Click Check my progress to verify the objective.

Create and execute the SQL workflow.

View execution logs

1. On the quickstart-workspace page, click EXECUTIONS to open the Executions pane.

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

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.