Generate Source, but for external files

[dataders]

Describe the feature

From dbt-labs/dbt-external-tables#249 (comment)

If I have to add an external table as a source in dbt, I use the dbt-external-tables package.

Most platforms offer an infer_schema choice where can query a file in a given location without specifying any column names or types (see below example). This is convenient, but not necessarily dbt-onic, where we normally document all of our source columns.

With dbt-labs/dbt-external-tables#211, dbt-external-tables now offers users an infer_schema config, that will, for a given external table defined in sources.yml, first fetch the column names and types, then use the metadata to populate the CREATE EXTERNAL TABLE statement. The problem with this approach is that while the user can then refer within a model to external table with {{ source()}}, the sources.yml is still missing important column information.

I propose instead of the introspective schema-inferring query that dbt-external-tables does to template the DDL, a more robust approach (imho) would be to use codegen.

1. Invoke codegen to generate YAML for the external table columns (names and types)
2. Add information to sources.yml
3. invoke dbt-external-tables to create the defined external table (without need to specify infer_schema: True

select * from table(
    infer_schema(
        location=>'{{external.location}}',
        file_format=>'{{external.file_format}}'
    )
)

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.