Inserting data into table created using Bigquery CREATE statement , through Dataform pipeline

[amitdhote]

Hi Team, 

We are trying to insert data into bigquery table explicitly created through bigquery console/cli command using create statement. But dataform pipeline is not populating that table and neither throwing any error.

Can someone please confirm if it is supported and if supported what are the steps to populate such table through dataform pipeline.

Thanks in advance.

Regards,

Amit Dhote

[ms4446]

Yes, Dataform supports the ability to insert data into an explicitly created table in BigQuery. You can define the SQL query in your Dataform script to load data into this table. Here are the steps you need to follow:

• Create a new Dataform SQLX file: In your Dataform project, create a new SQLX file (e.g., populate_table.sqlx). SQLX is a file format used by Dataform for writing SQL workflows.

• Define a SQL operation: Inside the SQLX file, define a SQL operation to populate the BigQuery table. The operation could be an INSERT statement or a SELECT statement that generates a result set to be inserted into the table. Here's an example: 

  config {
  type: "incremental",
  bigquery: {
  partitionBy: "date_column",
  clusterBy: ["cluster_column1", "cluster_column2"]
  }
  }

  SELECT
  column1,
  column2,
  ...
  FROM
  source_table

Publish your changes: After defining your SQL operation, you need to publish your changes so that they become part of the Dataform project.

Run your Dataform pipeline: Now, you can run your Dataform pipeline. The new SQLX file you created will be executed as part of the pipeline, and it will populate your BigQuery table with data.

However, there are a few things to note:

• Make sure that the service account used by Dataform has the appropriate permissions to insert data into the BigQuery table. This usually means that it needs the roles/bigquery.dataEditor or roles/bigquery.dataOwner IAM role.

• Ensure the table exists in BigQuery before running the Dataform pipeline. If the table does not exist, the pipeline will fail.

• If you're still not seeing the data being populated in your table, check the logs for your Dataform run to see if there are any warnings or errors. The problem could be with the SQL query you're using to populate the table, or there could be an issue with how your Dataform project is set up.