Changing a Delta Live Table's schema

rcostanza — Tue, 24 Sep 2024 17:43:29 GMT

I have a Delta Live Table whose source is a Kafka stream. One of the columns is a Decimal and I need to change its precision.

What's the correct approach to changing the DLT's schema?

Just changing the column's precision in the DLT definition will result in an error running the pipeline - the old and new columns are incompatible, even if it's the same type but a different precision. And the overwriteSchema/mergeSchema options are not valid for a Kafka stream.

My current options are:

- Deleting the DLT and letting the pipeline rebuild it

- Changing the DLT's column through ALTER TABLE

Re: Changing a Delta Live Table's schema

Sidhant07 — Fri, 06 Dec 2024 06:38:17 GMT

To change the precision of a Decimal column in a Delta Live Table (DLT) with a Kafka stream source, you can follow these steps:

1. Create a new column in the DLT with the desired precision.
2. Copy the data from the old column to the new column.
3. Drop the old column.
4. Rename the new column to the original column name.

Here's an example of how to do this using SQL:

```sql
-- Create a new column with the desired precision
ALTER TABLE your_table_name ADD COLUMNS (new_column Decimal(precision, scale));

-- Copy the data from the old column to the new column
UPDATE your_table_name SET new_column = old_column;

-- Drop the old column
ALTER TABLE your_table_name DROP COLUMN old_column;

-- Rename the new column to the original column name
ALTER TABLE your_table_name CHANGE COLUMN new_column old_column Decimal(precision, scale);
```

Replace `your_table_name` with the name of your DLT, `old_column` with the name of the column you want to change, `new_column` with a temporary name for the new column, and `precision` and `scale` with the desired precision and scale for the Decimal type.

This approach allows you to change the precision of the column without deleting the DLT or using the `overwriteSchema` or `mergeSchema` options.

topic Changing a Delta Live Table's schema in Data Engineering

Changing a Delta Live Table's schema

Re: Changing a Delta Live Table's schema