Add partition to existing Delta Table

Hi mrojze ,
Thanks for using Microsoft Fabric platform.
In Delta Lake, partitions are typically defined when the table is initially created, and you cannot directly add or modify partitions to an existing Delta table using built-in SQL commands like "ALTER TABLE." However, you can achieve this by creating a new Delta table with the desired partitions and then inserting data from the existing table into the new one. Here's how you can do it:

1. Create a new Delta table with the desired partitions:

CREATE TABLE NewTable
USING delta
PARTITIONED BY (part_1 STRING, part_2 STRING)

Replace `(part_1 STRING, part_2 STRING)` with the actual partition columns you want to use.

2. Insert data from the existing table into the new table, specifying the partition columns:

INSERT INTO NewTable
SELECT *, 'partition_value' AS part_1, 'partition_value' AS part_2
FROM ExistingTable

Replace "partition_value" with the actual partition values you want to use.

This process effectively creates a new Delta table with the desired partitions and copies the data from the existing table into it. 

And the error which you are experiencing regarding empty partition can be corrected by using the below code:

ALTER TABLE Table1 ADD PARTITION (part_1 VALUES (2023-09-13));

You have to specify the value for the partition. But by using ALTER TABLE you cannot create a partition for an existing Delta table. Hence try to use the Insert and Select queries mentioned above for solving your issue.

Hope this helps. Let us know if you have any other questions.

Thanks. This makes sense now, but what I am trying to do it to save the effort of creating a table manually, especially if the table is wide.

Could I create a partitioned table from another existing table?