Preview SQL queries

This document shows you how to preview output of a SQL query in a Dataform workspace before executing the query to BigQuery.

About query preview in Dataform

When you develop a query in a SQLX or JavaScript file with Dataform core, Dataform compiles the query to SQL in real-time. To test the output of the compiled SQL query before you execute it to BigQuery, you can run preview of the query in your Dataform workspace.

When you run preview of a query, Dataform runs a job in BigQuery to execute your compiled query in a temporary destination table and display query results in your workspace. You can inspect the output of the query before you trigger execution to publish your table to BigQuery. The temporary table with the previewed query is automatically deleted in up to 24 hours. For more information about BigQuery jobs, see Introduction to BigQuery jobs.

Running query preview in Dataform incurs billing in BigQuery. For more information about BigQuery pricing, see BigQuery Pricing.

During query preview, Dataform uses your user credentials to run jobs in BigQuery. However, during workflow execution Dataform uses your service account credentials to create or update assets in BigQuery.

Dataform displays results of all queries previewed during the current browser session in the Query results panel in your workspace. In the Query results panel, you can view the following information about each query previewed during the current browser session:

  • Job information
    • BigQuery job ID
    • User running the preview
    • Location where the job is run, taken from your workflow settings file
    • Creation, start, and end times of the BigQuery job
    • Duration of the BigQuery job
    • Bytes processed
    • Bytes billed in BigQuery
    • Job priority
    • Use of legacy SQL in the query
    • Temporary destination table of the query
  • Results of the query
  • JSON representation of the query output

Moreover, in the Query results panel, you can navigate to the source file of each previewed query, and navigate to BigQuery Studio to view the preview job.

Before you begin

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

    Go to the Dataform page

  2. Select or create a repository.

  3. Select or create a development workspace.

  4. Create a SQL query, for example, define a table.

Required roles

To get the permissions that you need to preview queries, ask your administrator to grant you the following IAM roles on your project:

  • Dataform Editor (roles/dataform.editor) - workspaces
  • BigQuery Job User (roles/bigquery.jobUser) - the project

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.

Preview a query

To inspect query results in Dataform before execution, run preview of a query in your workspace. If the currently edited file contains multiple queries, select the query that you want to preview. You can preview one query at a time.

Running preview is a little different for tables with assertions. For more information, see Preview a table with assertions.

To preview a SQL query and view query results in a Dataform workspace, follow these steps:

  1. In the Files pane, expand definitions/.
  2. Select a SQLX or JavaScript file.
  3. Click Run.
    1. If the file contains multiple queries, in the drop-down, select the query that you want to preview.
  4. Inspect results of the preview in the Query results panel.
    1. To inspect job details, select the Job information tab.
    2. To inspect query results, select the Results tab.
    3. To view the JSON output of the query, select the JSON tab.
    4. To view the preview job in SQL workspace, click the More menu > View job in SQL workspace.

Preview a table with assertions

In a SQLX table definition file with assertions, you can preview the table creation query and the defined assertion queries.

Dataform treats the table definition query as the default query. To preview the table creation query, click Run in your workspace. To preview an assertion query, you need to select it in a drop-down next to the Run button. You can preview one query at a time.

To preview a query in a table definition file with assertions in a Dataform workspace, follow these steps:

  1. In the Files pane, expand definitions/.
  2. Select a definition file of a table with assertions.
  3. Run preview:
    • To preview the table creation query, click Run.
    • To preview an assertion query, click the drop-down icon next to Run, and then select the query that you want to preview.
  4. Inspect results of the preview in the Query results panel.
    1. To inspect job details, select the Job information tab.
    2. To inspect query results, select the Results tab.
    3. To view the JSON output of the query, select the JSON tab.
    4. To view the preview job in SQL workspace, click the More menu > View job in SQL workspace.

What's next