CREATE OR ALTER is a Snowflake SQL command for declarative database change management. It creates an object when absent or alters an existing object to match the supplied definition, allowing each script to describe the desired state without manually sequencing prior ALTER statements. section 15.2.1
Use CREATE OR ALTER to make object-definition scripts suitable for repeated deployment:
- Define the complete object state required at a specific version. section 15.2.1
- Apply the definition without knowing which earlier changes have already run. section 15.2.1
- Recreate an object after a partial rollback by running its definition script for the required point in time. section 15.2.1
- Store scripts by database, schema, and object so repository-based continuous integration can execute them repeatedly. section 15.2.2
Evolving an EMPLOYEE table
Start with: CREATE TABLE EMPLOYEE(id integer, name varchar, SSN varchar); To add birth_dt, replace a separate ALTER TABLE step with: CREATE OR ALTER TABLE EMPLOYEE(id integer, name varchar, SSN varchar, birth_dt date); To remove SSN, execute the complete desired definition: CREATE OR ALTER TABLE EMPLOYEE(id integer, name varchar, birth_dt date); section 15.2.1
Table 15.2 An example illustrating the comparison between the imperative and declarative approaches to database change management
|
Step
|
Imperative approach
|
Declarative approach
|
|---|---|---|
|
Creating a table
|
create table EMPLOYEE(
id integer,
name varchar,
SSN varchar);
|
create table EMPLOYEE(
id integer,
name varchar,
SSN varchar);
|
|
Adding a column
|
alter table EMPLOYEE
add column birth_dt date;
|
create or alter
table EMPLOYEE(
id integer,
name varchar,
SSN varchar,
birth_dt date);
|
|
Dropping a column
|
alter table EMPLOYEE
drop column SSN;
|
create or alter
table EMPLOYEE(
id integer,
name varchar,
birth_dt date);
|
Compare imperative CREATE-plus-ALTER scripts with declarative CREATE OR ALTER definitions for creating, extending, and reducing a table. section 15.2.1
