Sample dbt macro for loading data with COPY INTO statement

external_source_load.sql

{% macro external_source_load() %}

{% set files_to_load = [
    ["jaffle_shop_customers.csv", "jaffle_shop_customers_load", [
        "ID NUMBER",
        "FIRST_NAME VARCHAR",
        "LAST_NAME VARCHAR",
    ]],
]
%}

{% for csv_file_name, table_name, columns in files_to_load %}
    {% set result = columns|join(", ") %}
    {% do run_query("CREATE TABLE IF NOT EXISTS analytics.dbt_smcintyre." + table_name + " (" + result + ");")%}
    {% do run_query("COPY INTO analytics.dbt_smcintyre." + table_name + " FROM @s3_dbt_tutorial_public/" + csv_file_name + " file_format = (type=csv field_delimiter=',' skip_header=1);") %}
{% endfor %}

{% endmacro %}