custom schema feature unintentionally creates schema stub

[hui-zheng]

Issue

Issue description

The custom schema feature unintentionally creates schema stub.
For example, when we specify "email_platform_out" and "email_platform_in" with custom schema feature, dbt will create "email_platform" as a dummy schema, which is not expected.

Results

For example, I want to use custom schema to specify two schemas, "email_platform_out" and "email_platform_in".

In profiles.yml, I set the scheme=email_platform
In dbt_project.yml, I set all models in these two schemas

models:
    email_dw_data_project:
        email_platform_out:
            schema: out

        email_platform_in:
            schema: in 

when I ran dbt run, receive the following error msg because dbt tried to create schema “email_platform” but failed, which is not expected, because I don't have nor want to create "email_platorm" schema.

System information

The output of dbt --version:

2018-08-03 18:45:19,017 (MainThread): Parsing materializations/incremental/incremental.sql
2018-08-03 18:45:19,037 (MainThread): Parsing materializations/seed/bigquery.sql
2018-08-03 18:45:19,042 (MainThread): Parsing materializations/seed/seed.sql
2018-08-03 18:45:19,072 (MainThread): Parsing materializations/table/bigquery_table.sql
2018-08-03 18:45:19,093 (MainThread): Parsing materializations/table/table.sql
2018-08-03 18:45:19,111 (MainThread): Parsing materializations/view/bigquery_view.sql
2018-08-03 18:45:19,119 (MainThread): Parsing materializations/view/view.sql
2018-08-03 18:45:19,136 (MainThread): Parsing operations/catalog/get_catalog.sql
2018-08-03 18:45:19,138 (MainThread): Parsing schema_tests/accepted_values.sql
2018-08-03 18:45:19,140 (MainThread): Parsing schema_tests/not_null.sql
2018-08-03 18:45:19,143 (MainThread): Parsing schema_tests/relationships.sql
2018-08-03 18:45:19,145 (MainThread): Parsing schema_tests/unique.sql
2018-08-03 18:45:19,149 (MainThread): Parsing model.o2e_email_dw_data_project.account_core_base
2018-08-03 18:45:19,152 (MainThread): Parsing model.o2e_email_dw_data_project.account_emails_base
2018-08-03 18:45:19,156 (MainThread): Parsing model.o2e_email_dw_data_project.account_opps_base
2018-08-03 18:45:19,162 (MainThread): Parsing model.o2e_email_dw_data_project.mcr_frame_base
2018-08-03 18:45:19,165 (MainThread): Parsing model.o2e_email_dw_data_project.franchise_partner_daily_out
2018-08-03 18:45:19,168 (MainThread): Parsing model.o2e_email_dw_data_project.master_account_with_emails_daily_out
2018-08-03 18:45:19,172 (MainThread): Parsing model.o2e_email_dw_data_project.sfdc_opt_out_email_daily_out
2018-08-03 18:45:19,179 (MainThread): * WARNING: No constraints found for model 'daily_out' in file models/email_platform_out/daily_out/schema.yml

2018-08-03 18:45:19,186 (MainThread): Found 7 models, 0 tests, 0 archives, 0 analyses, 76 macros, 1 operations, 0 seed files
2018-08-03 18:45:19,188 (MainThread):
2018-08-03 18:45:19,192 (MainThread): Acquiring new redshift connection "master".
2018-08-03 18:45:19,192 (MainThread): Opening a new connection (0 currently allocated)
2018-08-03 18:45:19,192 (MainThread): Connecting to Redshift using 'database' credentials
2018-08-03 18:45:19,693 (MainThread): Using redshift connection "master".
2018-08-03 18:45:19,693 (MainThread): On master: select distinct nspname from pg_namespace
2018-08-03 18:45:19,834 (MainThread): SQL status: SELECT in 0.14 seconds
2018-08-03 18:45:19,835 (MainThread): Creating schema "email_platform".
2018-08-03 18:45:19,836 (MainThread): Using redshift connection "master".
2018-08-03 18:45:19,836 (MainThread): On master: BEGIN
2018-08-03 18:45:19,911 (MainThread): SQL status: BEGIN in 0.07 seconds
2018-08-03 18:45:19,911 (MainThread): Using redshift connection "master".
2018-08-03 18:45:19,912 (MainThread): On master: create schema if not exists "email_platform"
2018-08-03 18:45:19,984 (MainThread): Postgres error: permission denied for database o2enexus

2018-08-03 18:45:19,984 (MainThread): On master: ROLLBACK
2018-08-03 18:45:20,059 (MainThread): Flushing usage events
2018-08-03 18:45:20,059 (MainThread): Encountered an error:
2018-08-03 18:45:20,060 (MainThread): Database Error
  permission denied for database o2enexus
2018-08-03 18:45:20,068 (MainThread): Traceback (most recent call last):
  File "/usr/local/Cellar/dbt/0.10.2/libexec/lib/python3.7/site-packages/dbt/adapters/postgres/impl.py", line 25, in exception_handler
    yield
  File "/usr/local/Cellar/dbt/0.10.2/libexec/lib/python3.7/site-packages/dbt/adapters/default/impl.py", line 648, in add_query
    cursor.execute(sql, bindings)

Steps to reproduce

[drewbanin]

Thanks for the report @hui-zheng. As a workaround, I'd recommend either giving create permissions to the dbt user, or manually creating the schema specified in the profile.

[jakebiesinger]

It'd be nice to fix this behavior rather than asking us to create unwanted schemas. There are no tables in there; why should we create such a schema?

Edit: I posted this same issue in Slack. Here's my original message:

I have a custom generate_schema_name which pulls the schema name from the folder that the model is defined in. This is working great so far, except that the schema defined on the profile (required field, but completely unused in my project) is being re-created every time I call dbt run.

Workaround is to set it to the name of one of my subfolders, but it's odd to me that some internal DBT machinery thinks it should go create schemas for things that will never have tables in them. Any reason for this behavior?

[drewbanin]

I think we discussed this on Slack, but: the reason for this stub schema is because Snowflake requires a "default" schema when performing certain operations (like creating temporary tables).

I think to fix this, we'll need to pass a schema name into the snowflake version of the create_table_as macro here. Alternatively, we could write some code to only create the stub schema for Snowflake connections, but I think that implementation would be undesirable.