I'm new to Dataform technology and I need your support to understand if I'm doing something wrong. I've read various topics and documentation, and I've come to the conclusion that for the work I need to do, I should create my SQLX file of type "operations". The purpose of my Dataform is to create table inserts by reading from views and then execute them via Composer with a parameter. To achieve this, I need to create a releaseId and a workflow in Dataform that includes the SQLX files to be executed, which will then be passed to Composer.

Here is my SQLX configuration:

definitions/first_view.sqlx

config {
  type: "operations",
  description: "Create view to other table.",
  hasOutput: true
}

CREATE OR REPLACE VIEW `prova-001-prj.L0_VIEW.First_View` AS
SELECT
  *
FROM
  `prova-001-prj.L0_TABLE.First_Table_OLD`
UNION ALL
SELECT
  *
FROM
  `prova-001-prj.L0_TABLE.First_Table`

When I click "run" on the SQLX file, everything works fine and the view is created. However, when I try to "start execution", it generates additional code that I don't need:

BEGIN
  CREATE SCHEMA IF NOT EXISTS prova-001-prj.L0_VIEW OPTIONS(location="europe-west8");
EXCEPTION WHEN ERROR THEN
  IF NOT CONTAINS_SUBSTR(@@error.message, "already exists: dataset") AND
    NOT CONTAINS_SUBSTR(@@error.message, "too many dataset metadata update operations") AND
    NOT CONTAINS_SUBSTR(@@error.message, "User does not have bigquery.datasets.create permission")
  THEN
    RAISE USING MESSAGE = @@error.message;
  END IF;
END;
BEGIN
  CREATE OR REPLACE VIEW `prova-001-prj.L0_VIEW.First_View` .........

I thought adding

hasOutput: true

would solve the issue, but it didn't. Could you please help me understand why this is happening and how I can prevent the generation of unnecessary code during execution?

Thank you!

I'm new to Dataform technology and I need your support to understand if I'm doing something wrong. I've read various topics and documentation, and I've come to the conclusion that for the work I need to do, I should create my SQLX file of type "operations". The purpose of my Dataform is to create table inserts by reading from views and then execute them via Composer with a parameter. To achieve this, I need to create a releaseId and a workflow in Dataform that includes the SQLX files to be executed, which will then be passed to Composer.

Here is my SQLX configuration:

definitions/first_view.sqlx

config {
  type: "operations",
  description: "Create view to other table.",
  hasOutput: true
}

CREATE OR REPLACE VIEW `prova-001-prj.L0_VIEW.First_View` AS
SELECT
  *
FROM
  `prova-001-prj.L0_TABLE.First_Table_OLD`
UNION ALL
SELECT
  *
FROM
  `prova-001-prj.L0_TABLE.First_Table`

When I click "run" on the SQLX file, everything works fine and the view is created. However, when I try to "start execution", it generates additional code that I don't need:

BEGIN
  CREATE SCHEMA IF NOT EXISTS prova-001-prj.L0_VIEW OPTIONS(location="europe-west8");
EXCEPTION WHEN ERROR THEN
  IF NOT CONTAINS_SUBSTR(@@error.message, "already exists: dataset") AND
    NOT CONTAINS_SUBSTR(@@error.message, "too many dataset metadata update operations") AND
    NOT CONTAINS_SUBSTR(@@error.message, "User does not have bigquery.datasets.create permission")
  THEN
    RAISE USING MESSAGE = @@error.message;
  END IF;
END;
BEGIN
  CREATE OR REPLACE VIEW `prova-001-prj.L0_VIEW.First_View` .........

I thought adding

hasOutput: true

would solve the issue, but it didn't. Could you please help me understand why this is happening and how I can prevent the generation of unnecessary code during execution?

Thank you!

• google-bigquery
• dataform

• Worth mentioning that the sqlx you've created could easily be of type: "view" – Jeffrey Van Laethem Commented Mar 26 at 17:05

1 Answer 1

This extra code you're seeing during "start execution" is Dataform's way of ensuring the environment is properly set up before running your SQL. To put it simply, it's taking care of schema creation if it doesn't already exist. This is a built-in feature to prevent errors if the target schema is missing. This is a best practice for production deployments as it prevents failures if the schema doesn't already exist.

You don't actually need to remove this generated code. It's crucial for robust deployments.