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
orMERGE 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.