COLUMNS view (multiple schemas): New column (Preview)¶

For the current status of the bundle, refer to Bundle History.

When this behavior change bundle is enabled, the COLUMNS view in Account Usage, Organization Usage, and Information Schema includes the following new column:

The data_type column shows the standard Snowflake data type of the column. The data_type_alias column displays the original data type name that was specified for a column when a table was created, or when the column was altered with an ALTER TABLE table ALTER COLUMN column SET DATA TYPE data_type statement.

For example, NUMBER is a standard Snowflake data type. BIGINT is synonymous with NUMBER. If you specify BIGINT as the data type for a column, the data_type column in the COLUMNS view shows NUMBER for this column, but the data_type_alias column shows BIGINT:

CREATE TABLE test_data_type_alias (b BIGINT);

SELECT data_type, data_type_alias
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'TEST_DATA_TYPE_ALIAS';

+-----------+-----------------+
| DATA_TYPE | DATA_TYPE_ALIAS |
|-----------+-----------------|
| NUMBER    | BIGINT          |
+-----------+-----------------+

When a standard, unqualified Snowflake data type is specified for a column, the values in the data_type and data_type_alias columns are the same. For example, if NUMBER is specified as the data type of a column, then data_type and data_type_alias in the COLUMNS view both show NUMBER as the data type:

CREATE TABLE test_data_type_alias_2 (n NUMBER);

SELECT data_type, data_type_alias
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'TEST_DATA_TYPE_ALIAS_2';

+-----------+-----------------+
| DATA_TYPE | DATA_TYPE_ALIAS |
|-----------+-----------------|
| NUMBER    | NUMBER          |
+-----------+-----------------+

The data_type_alias column shows the exact name that was specified for a data type. For example, the following statement creates a table with a fully-qualified NUMBER column:

CREATE TABLE test_data_type_alias_3 (n NUMBER(16, 2));

SELECT data_type, data_type_alias
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'TEST_DATA_TYPE_ALIAS_3';

+-----------+-----------------+
| DATA_TYPE | DATA_TYPE_ALIAS |
|-----------+-----------------|
| NUMBER    | NUMBER(16, 2)   |
+-----------+-----------------+

The only exceptions are data types for text strings. The standard Snowflake data type for text strings is VARCHAR, but the data_type column displays TEXT for these columns:

CREATE TABLE test_data_type_alias_4 (
  c CHAR,
  s STRING,
  t TEXT,
  v VARCHAR,
  vq VARCHAR(25));

SELECT column_name, data_type, data_type_alias
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'TEST_DATA_TYPE_ALIAS_4'
  ORDER BY column_name;

+-------------+-----------+-----------------+
| COLUMN_NAME | DATA_TYPE | DATA_TYPE_ALIAS |
|-------------+-----------+-----------------|
| C           | TEXT      | CHAR            |
| S           | TEXT      | STRING          |
| T           | TEXT      | TEXT            |
| V           | TEXT      | VARCHAR         |
| VQ          | TEXT      | VARCHAR(25)     |
+-------------+-----------+-----------------+

This behavior change affects any scripts or data loading processes that use SELECT * to query these views and depend on a fixed number of columns. To avoid any disruptions, review your scripts and applications. Update any queries on ACCOUNT_USAGE.COLUMNS, ORGANIZATION_USAGE.COLUMNS, and INFORMATION_SCHEMA.COLUMNS to specify the exact columns needed, instead of using SELECT *.

Ref: 2061