schema creation within schema_test sequence?

[dataders]

In dbt-msft/dbt-sqlserver#173, I override the test materialization to always store failures, but drop that table after running the test query unless otherwise specified. I don't think this test suite tests store_failures, so normally the schema_test sequuence shouldn't need to make a new schema. but my adapter does!

so currently, schema_test is failing with the message: dbt_test_azure_sql_211106005925396596672779_dbt_test__audit does not exist.

azuresql.dbtspec specifies:

schema: "dbt_test_azure_sql_{{ var('_dbt_random_suffix') }}"

Schema tests work perfectly in jaffle shop and an internal dbt project. The problem is I can't find:

1. where _dbt_test__audit is concatenated with the target schema, and
2. where the execute_schema is invoked prior to table creation... is executed by the test suite.

cc: @jtcohen6

[jtcohen6]

The default value of schema config for tests (dbt_test__audit) is set here, inside dbt-core.

I don't think this is an issue with the adapter suite, actually. dbt uses the is_relational check to determine which schemas need to be created at the start of runnable tasks (source). When a test has store_failures enabled (by config or by flag), it is included as "relational"; when it doesn't, it isn't. So when --store-failures is not enabled, dbt doesn't know to create the schema named "dbt_test_azure_sql_{{ var('_dbt_random_suffix') }}_ _dbt_test__audit.

As a workaround, just to get the test suite passing, you could disable the configured schema for tests:

dbt_project_yml:
  tests:
    +schema: null

That's going to be a required step for everyone who wants to use tests with dbt-sqlserver, though, to avoid the missing schema error. Instead, I'd recommend that the dbt-sqlserver test materialization makes the location of the test target table conditional based on whether should_store_failures() is enabled. If it's not enabled, I think a temp relation would make a lot of sense—then you don't have to worry about dropping it later, right?

[dataders]

why anders is avoiding temp tables in the test materialization

so up front. the reason I'm not taking the the temp table route is:

1. temp tables are weird in TSQL (e.g. there's no schema for temp tables only tempdb), and
2. sqlserver__create_table_as is doing some sketchy things right now that I need to think through more carefully (e.g. stripping out # out of identifiers?!) Relatedly, can you point me to somewhere in the global project or adapter, where create_table_as is invoked where temporary=True?

I think my workaround for the above issue w.r.t to this test suite to is to add a create schema if not exists to the custom test materialization.

why I think there's still logic missing from the schema_test sequence

1. does this adapter test suite test the functionality of storing test failures today? If not... shouldn't it?
2. If it does, where in the default test materialization would the concatenated schema of: 1) whatever's given as the schema in the .dbtspec and 2) _ _dbt_test__audit` be created?