Issue with Profile Parsing and `main` Schema Path Resolution (v1.9.1)

[allenday]

Hi Jeff,

First off, thank you for maintaining dbt-sqlite!

I've encountered some persistent and confusing issues with profile parsing and schema path resolution when using dbt-sqlite v1.9.1 (with dbt-core v1.9.4) on macOS arm64.

Specifically, dbt debug and dbt run/test are inconsistently failing with errors about missing required profile keys (schema_directory, schema, schemas_and_paths), even when the profiles.yml file is correctly configured according to the documentation.

Additionally, when attempting to build models targeting the default main schema, the adapter seems to incorrectly generate paths like main_main.db instead of using the file specified in schemas_and_paths for the main schema.

We've done extensive troubleshooting (clean venvs, different dbt versions, Docker isolation, various profile configurations) and documented the full details, error messages, and reproduction steps in a corresponding dbt-core issue here:

dbt-core #11467

It seems there might be a bug in how the adapter interacts with dbt's profile parsing or how it resolves paths specifically for the main schema in recent versions.

Environment:

• dbt-sqlite: 1.9.1
• dbt-core: 1.9.4
• Python: 3.10.14
• OS: macOS Sonoma 14.x (arm64)

Thanks for looking into this when you have a chance!

[codeforkjeff]

Thanks for the detailed write-up in the linked issue! I hope to get the time to look at this in the next few days. Hoping it's a new issue that arose with dbt-core 1.9.4 and not the 1.9.x series in general.

[dbeatty10]

I closed dbt-labs/dbt-core#11467 in favor of this one. But if @codeforkjeff traces this back to an issue in dbt-core, we can re-open it as-needed.

[codeforkjeff]

I tried to follow the steps in the other issue, and couldn't reproduce this. But going through the steps, I suspect it's happening because there is an explicit "schema" configuration key specified somewhere, triggering the "main_main" filename to be used for the sqlite file. The dbt docs mention that it uses that underscore convention when "a custom schema is specified":

https://docs.getdbt.com/reference/resource-configs/schema#definition

This is almost certainly a problem in the adapter somewhere and not dbt-core.

Is there any chance you could create a git repository with an extremely simple dbt project that reproduces this problem?