POST_START

Modifying Table Structure and Properties with ALTER TABLE

I recently needed to update the structure of a table in Unity Catalog to better align with our data modeling standards. The table in question was training.sales.customers, and the first change I wanted to make was to add a new column called phone of type STRING. This column was necessary to capture additional customer contact information that wasn’t previously tracked.

ALTER TABLE training.sales.customers ADD COLUMNS (phone STRING);

I saw a representative result like this:

I verified that the phone column had been added by running a DESCRIBE TABLE command to check the updated schema. The output confirmed the new column was present, and I noted that the existing columns remained unchanged.

DESCRIBE TABLE EXTENDED training.sales.customers;

I saw a representative result like this:

col_name	data_type	comment
customer_id	bigint	customer identifier
customer_name	string	customer display name
region	string	sales region
phone	string

Next, I realized that the column name customer_name wasn’t descriptive enough for our team’s data governance standards. I decided to rename it to full_name to better reflect the content of the data.

ALTER TABLE training.sales.customers RENAME COLUMN customer_name TO full_name;

I saw a representative result like this:

I ran the DESCRIBE TABLE command again to confirm the column had been renamed. The output now showed full_name instead of customer_name, and I felt confident that the table structure now better aligned with our internal naming conventions.

DESCRIBE TABLE EXTENDED training.sales.customers;

I saw a representative result like this:

col_name	data_type	comment
customer_id	bigint	customer identifier
full_name	string	customer display name
region	string	sales region
phone	string

Finally, I wanted to add a table property to explicitly associate this table with the sales department, as it was no longer clear from the table name alone. I used the SET TBLPROPERTIES command to accomplish this.

ALTER TABLE training.sales.customers SET TBLPROPERTIES ('department' = 'sales');

I saw a representative result like this:

To ensure the property had been applied, I ran the DESCRIBE TABLE EXTENDED command once more and confirmed that the department property was now listed alongside the other metadata. This helped improve the discoverability and governance of the table within Unity Catalog.

Throughout this process, I learned how to use the ALTER TABLE command to modify table structures and properties in Unity Catalog. These operations are essential for maintaining clean, well-documented, and governable data assets. I also reinforced the importance of checking the schema and metadata after each change to ensure that the modifications were applied correctly and as intended.