external materialization - temp table

[sergun]

Hi folks,

I see that external materialization creates "internal" table with __dbt_tmp suffix before copying tables to files and creation of views.

Why not to do just:
COPY (model_select_query) TO 'path_to_file'

and further CREATE VIEW ...?

[jwills]

I think there are 3-ish reasons, but the main one is historical-- the COPY (query) TO 'path_to_file' syntax didn't exist when we were first writing the project.

The second is for consistency with how other materializations work + for Python support (since in that case we execute a function and have a DataFrame to materialize instead of a query)

And the third is for the special case where the query returns no rows but we still want the column info to be materialized into the external file we create.

[sergun]

Thanks @jwills !

Maybe COPY (query) TO 'path_to_file' implementation can simplify something in in #586

For pure "external" tables use-case probably it is more native to do not use "internal" tables at all. So duckdb is just in-memory calculator (providing projection, filtering, join) on external data.