Support of TABLE RENAME and COLUMN RENAME

[minghungcho]

Currently, I can handle table & column rename in a pre-deployment script via SnowSQL before the execution of SnowDDL.
However, I am wondering if there is a better way to support rename within SnowDDL YAML config.

What if there is a property such as former_name / aka that allows you to instruct rename behavior.

I understand this might also create some circular reference problem. But when use carefully, this would reduce the chance of data loss, and eliminate some pre- & post- deployment script needs.

Proposal
public/table/booking.yaml

columns:
  ticket_no:
    type: VARCHAR(13) NOT NULL
    former_name: ticket_number
    comment: "Ticket number"

former_name: bookings

Potentially generate the following scripts

ALTER TABLE IF EXISTS public.bookings RENAME TO public.booking;
ALTER TABLE IF EXISTS public.booking RENAME COLUMN ticket_number TO ticket_no;

OR, generate a better CREATE-SELECT statement during --apply-replace-table.

CREATE TABLE public.booking
(
ticket_no VARCHAR(13) NOT NULL COMMENT 'Ticket number'
)
AS
SELECT ticket_number::VARCHAR(13) AS ticket_no
FROM public.bookings

[littleK0i]

Thank you for the idea.

I've contemplated the renaming problem since very early stages. In theory, it can be implemented, but it has a few potential problems and edge cases:

1. Renaming might be in a form of name swapping (e.g. foo -> bar, bar -> foo). It requires a third temporary name in case of columns.
2. Dependency resolution is based on names. So all renaming has to be executed as a separate "pre-processing" stage, with additional execution of SHOW ... command afterward.
3. One of SnowDDL features is the ability to checkout previous version of config and apply it. However, renaming will not be reverted in this case.

---

So it is possible to implement a basic renaming for trivial use cases. But it is really hard to make this feature complete and reliable for complex cases. I am still not sure how to make it work.

On the flip side, we already know that some manual query execution by ACCOUNTADMIN is unavoidable due to Snowflake architecture. Renaming is currently a part of this manual execution.

Also, if --apply-replace-table is not enabled (should not be enabled for prod), it should prevent potential data loss due to accidentally missing RENAME. As long as all suggested queries are reviewed properly.