CODEWITHSUNDEEP
snowflake-cloud-data-platformdbt
Roger Leung
Roger Leung

Reputation: 11

DBT - How to use snowflake COPY INTO

I am wondering how do i use Copy Into command in a dbt snowflake settings? And in general i am not sure how to perform functions like creating an empty table like below

CREATE OR replace TABLE anaplan_raw.quarterly_cashflow_master_project
(
                                                xx      varchar(100),
                                                yy        varchar(100),
                                                zz         varchar(100),
                                                aa         number(12,2),
                                                bb         number(12,2),
                                                cc       number(12,2),
                                                dd         date
                        );

I tried a few way utilizing macro but to no avail. Thank you very much.

here is my macro and the results


{% macro unload_data_to_snowflake() %}

    {{ log("Unloading data", True) }}
    BEGIN;
    COPY INTO anaplan_raw.quarterly_cashflow_master_project
    FROM @my_azure_stage/QuaterlyMasterProjectDDQ.csv
    FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1 field_optionally_enclosed_by='"')
    COMMIT;
    {{ log("Unloaded data", True) }}

{% endmacro %}

Results

03:49:19  Unloading data
03:49:19  Unloaded data

Seems that nothing is being run at all

Upvotes: 0

Views: 1789

Answers (1)

Rockie Yang
Rockie Yang

Reputation: 4925

DBT is specialized on T of the ELT (Extract Load Transform). It assume the data has been loaded to the data warehouse.

Here is a quote from dbt document.

Can dbt be used to load data?

No, dbt does not extract or load data. It focuses on the transformation step only.

It is recommended use other tools to load data into data warehouse first. Though there are couple workaround might be used for limited amount of usecases.

Workaround A, dbt seed

dbt seed might be used if just small amount of seed data. It is not recommended for load large data, see dbt document load-raw-data-with-seed

Workaround B, snowflake external table

snowflake external data could be potentially used. see snowflake document Introduction to External Tables

Recommendation

As dbt recommended, it is best use other tools load data into data warehouse.

Further more snowflake COPY INTO has a nice feature that will only load newly added files but not load again to create duplicates.

Upvotes: 0

Related Questions