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

About query preview in Dataform

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

When you run preview of a query, Dataform runs a job inBigQuery to run your compiled query in a temporary destinationtable and display query results in your workspace. You can inspect the output ofthe query before you trigger execution to publish your table toBigQuery. The temporary table with the previewed query is automaticallydeleted in up to 24 hours. For more information about BigQuery jobs,seeManage jobs.

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

During query preview, Dataform uses your user credentials to run jobsin BigQuery. However, during workflow execution Dataform usesyour service agent or service account credentials to create or update assets inBigQuery.

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

• Job information
  • BigQuery job ID
  • User running the preview
  • Location where the job is run, taken from yourworkflow 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 oflegacy SQLin the query
  • Temporary destination table of the query
• Results of the query
• JSON representation of the query output

Moreover, in theQuery results panel, you can navigate to the source file ofeach previewed query, and navigate to BigQuery Studio to view the previewjob.

Before you begin

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

   Go to the Dataform page

2. Select orcreate a repository.

3. Select orcreate a development workspace.

4. Create a SQL query, for example,create 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, seeManage access to projects, folders, and organizations.

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

Preview a query

To inspect query results in Dataform before execution, run preview of aquery in your workspace. If the file being edited 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 moreinformation, seePreview a table with assertions.

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

1. In theFiles pane, expanddefinitions/.
2. Select a SQLX or JavaScript file.
3. ClickRun.
   1. If the file contains multiple queries, in the drop-down, select thequery that you want to preview.
4. Inspect results of the preview in theQuery results panel.
   1. To inspect job details, select theJob information tab.
   2. To inspect query results, select theResults tab.
   3. To view the JSON output of the query, select theJSON tab.
   4. To view the preview job in SQL workspace, click themore_vertMore menu >View job in SQL workspace.

Preview a table with assertions

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

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

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

1. In theFiles pane, expanddefinitions/.
2. Select a definition file of a table with assertions.
3. Run preview:
   • To preview the table creation query, clickRun.
   • To preview an assertion query, click the drop-down icon next toRun, and then select the query that you want to preview.
4. Inspect results of the preview in theQuery results panel.
   1. To inspect job details, select theJob information tab.
   2. To inspect query results, select theResults tab.
   3. To view the JSON output of the query, select theJSON tab.
   4. To view the preview job in SQL workspace, click themore_vertMore menu >View job in SQL workspace.

What's next

• To learn more about BigQuery pricing, seeBigQuery Pricing.
• To learn more about Dataform pricing, seePricing.
• To learn more about workspaces in Dataform, seeCreate a development workspace.
• To learn how to test table queries with assertions, seeTest tables with assertions.