Alternative solution to ALTER table ADD column

Help Center
1

The Challenge:

You need to add columns to an existing Firebolt table, but the direct ALTER TABLE ADD COLUMN operation isn't supported. Deleting and recreating the table with new columns is a cumbersome process.

Solution:

Leverage the power of views in Firebolt to simplify this process. Views allow you to add "virtual" columns to your table without altering the underlying data structure, thus avoiding the need to delete or recreate tables.

Example:

Suppose you have an existing table sales_data with columns sale_id and amount . You want to add a new column discount_rate without directly modifying sales_data .

Create a New Table (optional): If you have new data that includes the discount_rate , create a new table, sales_data_v2 , with the additional column:

CREATE FACT TABLE IF NOT EXISTS sales_data_v2 (
  sale_id INT NOT NULL,    
  amount DECIMAL(10,2) NOT NULL,    
  discount_rate DECIMAL(5,2) NULL
);

Create a View: Combine the original sales_data with the new sales_data_v2 (if applicable) or simply add the discount_rate as a null or default value for all existing records:

CREATE VIEW IF NOT EXISTS sales_data_with_discount AS 
SELECT 
  sale_id, 
  amount, 
  NULL AS discount_rate 
FROM sales_data
UNION ALL -- Omit this  if you don't have a new table
SELECT 
  sale_id, 
  amount, 
  discount_rate 
FROM sales_data_v2;

This view, sales_data_with_discount , will now act as if discount_rate was a part of the original table, allowing for queries against it without the overhead of physically altering the table structure. From this point forward, reference the view in any queries. Future additions can repeat this process, and there will no need to update the queries to use a different FROM object.

Conclusion:

Using views to "add" columns is a flexible and efficient method, particularly beneficial for large datasets or when maintaining historical table names is crucial. This approach significantly reduces maintenance efforts and enhances query performance by avoiding the direct modification of large tables.