Dbt macro: create external table in bigquery

In-Depth Discussions
, ,
1

Hi, I am trying to execute the macro below to create external table in bigquery.

{%- macro create_external_table()  -%}
    create or replace external table `dataset.table`(
        Id STRING NOT NULL,
        FirstName STRING NOT NULL,
        LastName STRING NOT NULL
    )
    OPTIONS(
        format = "CSV",
        uris = ['gs://some_folder/my_file.csv'] 
    );
{%- endmacro -%}

When I run dbt run-operation create_external_table, it does not create the table in bigquery. But then I have modified my macro like below:

{%- macro create_external_table()  -%}
 {% set create_table %}
  create or replace external table `dataset.table`(
      Id STRING NOT NULL,
      FirstName STRING NOT NULL,
      LastName STRING NOT NULL
  )
  OPTIONS(
      format = "CSV",
      uris = ['gs://some_folder/my_file.csv'] 
  );
  {% endset %}

  {% do run_query(create_table) %}
{%- endmacro -%}

Now it works and it is creating the table in bigquery. I would like to know why the first code did not work and why the second one worked. Thanks!

2

because you created the macro but you did not execute it , you did not run it via run_query

1 Like