value too long for type character varying(600) when building new columns models

[borismo]

Describe the bug

Two models introduced in v0.9.0 fail to build for us. More specifically they come from #386:

• base_node_columns
• base_source_columns

Steps to reproduce

1. update package to v0.9.0
2. run dbt deps
3. run dbt build --select package:dbt_project_evaluator

Expected results

I expect models to build successfully

Actual results

Getting

02:12:33  Completed with 2 errors and 1 warning:
02:12:33
02:12:33    Database Error in model base_node_columns (models/staging/graph/base/base_node_columns.sql)
  value too long for type character varying(600)
  compiled Code at target/run/dbt_project_evaluator/models/staging/graph/base/base_node_columns.sql
02:12:33
02:12:33    Database Error in model base_source_columns (models/staging/graph/base/base_source_columns.sql)
  value too long for type character varying(600)
  compiled Code at target/run/dbt_project_evaluator/models/staging/graph/base/base_source_columns.sql

System information

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: calogica/dbt_expectations
    version: 0.10.3
  - package: dbt-labs/dbt_external_tables
    version: 0.8.7
  - package: elementary-data/elementary
    version: 0.14.1
  - package: dbt-labs/codegen
    version: 0.12.1
  - package: dbt-labs/dbt_project_evaluator
    version: 0.8.1

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• trino/starburst
• other (specify: ____________)

The output of dbt --version:

Core:
  - installed: 1.7.3
  - latest:    1.7.9 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - postgres: 1.7.3 - Update available!
  - redshift: 1.7.3 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Additional context

Compiled code for base_node_columns:

with dummy_cte as (
    select 1 as foo
) 

select 
    cast(null as VARCHAR(600)) as node_unique_id,
    cast(null as VARCHAR(600)) as name,
    cast(null as VARCHAR(600)) as description,
    cast(null as VARCHAR(600)) as data_type,
    cast(null as VARCHAR(600)) as quote

from dummy_cte
where false

and obviously runs

Are you interested in contributing the fix?

Yes, I would like to start with finding out what are the values that don't fit in a VARCHAR(600) but I don't know how.

[sphinks]

@borismo
Have the same issues. The issues is happining because of some long names for dbt model or test (pretty sure it is test name that goes over 600 symbols). To find the test name causeing the issue:

1. Run dbt with debug mode and output log into file: dbt -d build --select package:dbt_project_evaluator > output.txt
2. Open log file with some advanced text editor (Sublime/Notepad++). I use Notepad++ and enable regular expression search. Look for pattern: ^.{600,}. It will find names over 600 chars that is causing the issue.
3. Find those tests and reduce the name of generated name. In my case it was because of long list of accepted values for field. I delete most of value from the test temporary.
4. Run the validation. Get output and return test back to original value.

Root cause of the issue: dbt macro: {{ dbt.type_string() }}. Looks like it has hardcoded value in 600 chars. Need to workaround that. I suppose need to perform string truncation up to max value. But not sure what is the best way to go.

[b-per]

@sphinks , for your particular issue with tests having long names for accepted values, you can actually provide your own custom name to the test (see here). I don't know why it is not in the docs, I am asking the team.

[b-per]

@borismo , could you also try to create a new macro in your project with the following:

{% macro redshift__type_string() %}
    varchar(5000)
{% endmacro %}

In your case, this is likely due to some long descriptions of columns in models and/or sources.

We could as well truncate the description field for Redshift if it is easier (I don't think that this issue was reported for other adapters yet)