Declarative configuration manages database objects by declaring the desired state rather than scripting every operation needed to reach it. Snowflake supports this approach for database change management with CREATE OR ALTER. section 15.2.1
Use declarative object definitions to simplify pipeline change management:
- Define each object at its intended version without knowing the previous version. section 15.2.1
- Apply the current definition regardless of the sequence of earlier changes. section 15.2.1
- Recreate an object after a partial rollback by running its definition for the required point in time. section 15.2.1
- Organize scripts by schema, object type, and database object within the repository. section 15.2.2
Adding and dropping a column
An imperative workflow appends ALTER TABLE statements. A declarative workflow replaces the table definition with its desired state: CREATE OR ALTER TABLE EMPLOYEE(id integer, name varchar, SSN varchar, birth_dt date); Later, rerun the definition without SSN to declare that the column should be absent. 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 table creation, column addition, and column removal through imperative ALTER statements and declarative CREATE OR ALTER definitions. section 15.2.1
For tables, use CREATE OR ALTER. For other object types, use CREATE OR REPLACE so development scripts can be rerun without failing when objects already exist. section 15.2.2
