Analysis 1900 results insert fails on Snowflake (w. excess length source values)

[katy-sadowski]

In analysis 1900, the raw source_value is stored in stratum_3:

Achilles/inst/sql/sql_server/analyses/1900.sql

Line 7 in ea478a3

source_value as stratum_3,

. Later on, when analysis results are merged into the results tables, the column is cast to VARCHAR(255).

Now, if we complied with the CDM spec and kept our source_value columns as VARCHAR(50) this would be no problem. But we've decided to relax this requirement in order to preserve the utility of some larger source values, as I believe many other organizations choose to do as well. As such we've got values exceeding 255 characters in length.

In Snowflake, CASTing a string to a smaller length throws an error. Instead, we must truncate the string first. So our solution was to swap out the above linked line to LEFT(source_value::varchar,255) as stratum_3,

If you agree with supporting some deviations from varchar length specifications in Achilles, please let me know what you think about this solution :)