[ERROR UX] "No column found" errors should mention source schema cache and it be refreshed

[dataders]

everyday I open up internal analytics, I often see initial error like the below.

dbt0227: No column {NEW_COLUMN} found. Available are
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL1,
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL2,
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL3,
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL4, 
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL5, 
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL6, 
SOURCE_DB.SOURCE_DB.SOURCE_TABLE.OTHER_COL7 ..(and {N} more) 
  --> span/of/offending/model/that/refs/a/source.sql:1:1 

the not-immediately-obvious resolution, is to run clean. Then trigger a compile in the VSCE. The source is that new columns have arrived in the source schema overnight since I last ran compile.

Additionally, I don't even think it's valuable to tell the users the other columns that are in the table, unless they're fuzzy match results (i.e. same column but with other casing)

the error message should probably say something like

dbt0227: No column {NEW_COLUMN} found in the locally cached schema for the source in
  target/db/schemas/raw/fivetran_hubspot/contact/output.parquet
It is likely that this cache needs to be refreshed

via @pempey in #584 (comment)

On theme with the formatting of errors I want to mention the information provided in this error:

warning: dbt1014: Failed to download source schema for 'source.gitlab_snowflake.zuora_revenue_share.zuora_revenue_share_calendar'. Setting 'static_analysis' to off. Skipping analysis for 'model.gitlab_snowflake.zuora_revenue_calendar_source': [Snowflake] 002003 (02000): SQL compilation error:
Database 'ZUORA_SNOW_USW2_PROD_001_ZUORA_INHEVIE' does not exist or not authorized. (SQLSTATE: 02000, Vendor code: 2003)

For a while I thought that there was an error while while creating the error. After reading it a few times I surmised that the end of the error was trying to tell me why it failed to download the schema. I think that the repeated information at the end was really confusing me and leading me to not fully understand what was happening. To be fair I am currently getting hundreds of these errors/warnings so I never really stopped to read them before now. But, the formatting and they way the information is connected does not help with the communication.

more examples

dbt0227: No column PROPERTY_EVENT_SESSIONS found. Available are RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_EVENT_SESSION, RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_EVENT_SESSION_TIME, RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_EVENT_CONSENT, RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_EVENT_STATUS, RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_SESSION, RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_BOOTCAMP_SESSION, RAW.FIVETRAN_HUBSPOT.CONTACT.PROPERTY_HS_PERSONA ..(and 510 more)
  --> dbt_packages/hubspot/models/staging/tmp/stg_hubspot__contact_tmp.sql:3:8

dbt0227: No column FUSION_MIGRATION_PERMISSIONS found. Available are RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.PARTIAL_PARSING, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.CATALOG_INGESTION, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION._FIVETRAN_DELETED, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION._FIVETRAN_SYNCED, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.AI_FEATURES, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.REPO_CACHING, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.ACCOUNT_ID ..(and 7 more)
  --> models/staging/cloud_postgres/stg_cloud__account_entitlement_configurations.sql:318:26

dbt0227: No column CHANGED_SOURCE_COUNT found. Available are SOURCE.RESOURCE_TYPE, SOURCE.ACCOUNT_ID, SOURCE.COMPILE_TIME_SECONDS, SOURCE.MATERIALIZATION, SOURCE.PREVIOUS_RUN_ID, SOURCE.UNNECESSARY, SOURCE.DATABASE ..(and 12 more)
  --> models/staging/dbt_cost_analyzer/stg_dbt_cost_analyzer__model_builds.sql:49:9

dbt0227: No column FUSION_MIGRATION_PERMISSIONS found. Available are RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.PARTIAL_PARSING, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.CATALOG_INGESTION, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION._FIVETRAN_DELETED, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION._FIVETRAN_SYNCED, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.AI_FEATURES, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.REPO_CACHING, RAW.FIVETRAN_DBT_CLOUD_POSTGRES_PUBLIC.ACCOUNT_ENTITLEMENT_CONFIGURATION.ACCOUNT_ID ..(and 7 more)
  --> snapshots/cloud_account_entitlement_configurations_snapshot.sql:323:26

[NicolaiLolansen]

the not-immediately-obvious resolution, is to run clean. Then trigger a compile in the VSCE. The source is that new columns have arrived in the source schema overnight since I last ran compile.

Oh my god ... THANK YOU. I spent quite some time debugging compiled SQL statements that worked fine on my warehouse, but fusion just wouldn't accept the fields existing. This 100% needs to be added as a warning ( and even then, if fusion can detect there is a change, surely it would be a good idea to strongly hint that the user runs a clean? )

[schulte-lukas]

Should we consider giving the cache a TTL of 24hrs? This would require downloading schemas once a day but that it guarantees that the cache is not super out of date with warehouse state.

[dataders]

Should we consider giving the cache a TTL of 24hrs? This would require downloading schemas once a day but that it guarantees that the cache is not super out of date with warehouse state.

@schulte-lukas check out @venkaa28's #1042