Type widening

Important

This feature is in Public Preview in Databricks Runtime 15.2 and above.

Tables with type widening enabled allow you to change column data types to a wider type without rewriting underlying data files. You can either change column types manually or use schema evolution to evolve column types.

Type widening requires Delta Lake. All Unity Catalog managed tables use Delta Lake by default.

Supported type changes

You can widen types according to the following rules:

Source type	Supported wider types
byte	short, int, long, decimal, double
short	int, long, decimal, double
int	long, decimal, double
long	decimal
float	double
decimal	decimal with greater precision and scale
date	timestampNTZ

To avoid accidental promotion of integer values to decimals, you must manually commit type changes from byte, short, int, or long to decimal or double.

Note

When changing any numeric type to decimal, the total precision must be equal to or greater than the starting precision. If you also increase the scale, the total precision must increase by a corresponding amount.

The minimum target for byte, short, and int types is decimal(10,0). The minimum target for long is decimal(20,0).

If you want to add two decimal places to a field with decimal(10,1), the minimum target is decimal(12,3).

Type changes are supported for top-level columns and fields nested inside structs, maps, and arrays.

Enable type widening

You can enable type widening on an existing table by setting the delta.enableTypeWidening table property to true:

  ALTER TABLE <table_name> SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true')

You can also enable type widening during table creation:

  CREATE TABLE T(c1 INT) USING DELTA TBLPROPERTIES('delta.enableTypeWidening' = 'true')

Important

When you enable type widening, it sets the table feature typeWidening-preview, which upgrades the reader and writer protocols. You must use Databricks Runtime 15.2 or above for to interact with tables with type widening enabled. If external clients also interact with the table, verify that they support this table feature. See How does Azure Databricks manage Delta Lake feature compatibility?.

Manually apply a type change

Use the ALTER COLUMN command to manually change types:

ALTER TABLE <table_name> ALTER COLUMN <col_name> TYPE <new_type>

This operation updates the table schema without rewriting the underlying data files.

Widen types with automatic schema evolution

Schema evolution works with type widening to update data types in target tables to match the type of incoming data.

Note

Without type widening enabled, schema evolution always attempts to safely downcast data to match column types in the target table. If you don’t want to automatically widen data types in your target tables, disable type widening before you run workloads with schema evolution enabled.

To use schema evolution to widen the data type of a column, you must meet the following conditions:

• The command uses INSERT or MERGE INTO.
• The command runs with automatic schema evolution enabled.
• The target table has type widening enabled.
• The source column type is wider than the target column type.
• Type widening supports the type change.

Type mismatches that don’t meet all of these conditions follow normal schema enforcement rules. See Schema enforcement.

Disable the type widening table feature

You can prevent accidental type widening on enabled tables by setting the property to false:

  ALTER TABLE <table_name> SET TBLPROPERTIES ('delta.enableTypeWidening' = 'false')

This setting prevents future type changes to the table, but doesn’t remove the type widening table feature or undo types that have changed.

If you need to completely remove the type widening table features, you can use the DROP FEATURE command as shown in the following example:

 ALTER TABLE <table-name> DROP FEATURE 'typeWidening-preview' [TRUNCATE HISTORY]

When dropping type widening, all data files that don’t conform to the current table schema are rewritten. See Drop Delta table features.