Account Usage and Information Schema views: Changes to DATA_TYPE output for string columns (Preview)¶

When this behavior change bundle is enabled, the output for the DATA_TYPE column in Account Usage and Snowflake Information Schema views changes for string columns:

Before the change:

In the output of a query on an Account Usage or Information Schema view, the DATA_TYPE column showed TEXT for a string column.

After the change:

In the output of a query on an Account Usage or Information Schema view, the DATA_TYPE column shows VARCHAR for a string column.

The following Account Usage views include a DATA_TYPE column:

• COLUMNS view

• ELEMENT_TYPES view

• FIELDS view

The following Information Schema views include a DATA_TYPE column:

• COLUMNS view

• ELEMENT_TYPES view

• FIELDS view

When you query these views, the DATA_TYPE column shows the data type of a column in a table. When this behavior change bundle is enabled, the output for a column of any text string type changes. For example, create a table with columns of various text string types:

CREATE TABLE text_string_columns_test(
  col1 VARCHAR,
  col2 CHAR,
  col3 TEXT,
  col4 STRING);

Execute a query on the INFORMATION_SCHEMA.COLUMNS view:

SELECT column_name, data_type
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name ILIKE 'text_string_columns_test'
  ORDER BY column_name;

Before the change, the query shows TEXT for these columns:

+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
|-------------+-----------|
| COL1        | TEXT      |
| COL2        | TEXT      |
| COL3        | TEXT      |
| COL4        | TEXT      |
+-------------+-----------+

After the change, the query shows VARCHAR for these columns:

+-------------+-----------+
| COLUMN_NAME | DATA_TYPE |
|-------------+-----------|
| COL1        | VARCHAR   |
| COL2        | VARCHAR   |
| COL3        | VARCHAR   |
| COL4        | VARCHAR   |
+-------------+-----------+

Ref: 1960