Refreshing external tables does not persist new files.

elliottohara commented 3 years ago

Describe the bug

External tables do not refresh on snowflake.

Steps to reproduce

When running dbt run-operation stage_external_sources I expect my external tables to be refreshed. While I do see the queries being executed and get results for all new files, subsequent queries to the external table do not show the new results.

Expected results

Results should be committed

Actual results

Results are not committed.

Screenshots and log output

System information

The contents of your packages.yml file:

packages:
  - package: fishtown-analytics/dbt_utils
    version: 0.6.4
  - git: "https://github.com/upside-services/dbt-external-tables.git"
    revision: 0.6.2
  - package: fishtown-analytics/codegen
    version: 0.3.1
  - package: fivetran/hubspot_source
    version: 0.3.1
  - package: fivetran/hubspot
    version: 0.3.2
  - package: fivetran/zendesk_source
    version: 0.3.0
  - package: fivetran/zendesk
    version: 0.4.0
  - package: calogica/dbt_expectations
    version: [">=0.3.0", "<0.4.0"]

Which database are you using dbt with?

[ ] redshift
[ *] snowflake
[ ] other (specify: ____)

The output of dbt --version:

<output goes here>

The operating system you're using: Docker image for 0.19.1

The output of python --version:

Additional context

I've modified the snowflake/refresh_external_tables.sql file starting at line 13 like so and it does the issue. I just don't know if this is the preferred way to handle it since the macro is expected to return an array of commands, not execute actual statements.

        {% set ddl %}
        begin;
        alter external table {{source(source_node.source_name, source_node.name)}} refresh;
        commit;
        {% endset %}

jasonzondor commented 3 years ago

I believe this is the same as bug https://github.com/fishtown-analytics/dbt-external-tables/issues/43 and there's a PR submitted for a fix https://github.com/fishtown-analytics/dbt-external-tables/pull/47, which worked for me... but I'm not sure why it hasn't been committed into master yet.

jtcohen6 commented 3 years ago

Thanks for the detailed report @elliottohara! As @jasonzondor notes, I'm hopeful that #47 (just merged) offers a resolution for this. If that's not the case, let's figure out what else is at play.

jasonzondor commented 3 years ago

@jtcohen6, I just went through and tested again, and it is not fixed in https://github.com/fishtown-analytics/dbt-external-tables/pull/47. The only way to get it to work was to wrap the alter refresh statement in BEGIN and COMMIT statements. I'm sure there's a more "correct" way to handle this but that's a bit beyond my knowledge of dbt development for the time being.

dbt-labs / dbt-external-tables