Database change management (DCM) defines database objects in code and deploys both their initial definitions and subsequent changes. It keeps object versions aligned with data-pipeline code as part of automated continuous integration. section 15.2 section 15.7
The imperative approach records an initial CREATE statement followed by ordered ALTER statements. The declarative approach records the desired object state, allowing the deployment tool to determine required changes from the existing definition. section 15.2.1
A declarative DCM workflow typically:
- Defines tables with CREATE OR ALTER so a script creates a missing object or aligns an existing object with its declared structure. section 15.2.1
- Defines other object types with CREATE OR REPLACE where appropriate, preventing failures when objects already exist. section 15.2.2
- Stores scripts in a repository hierarchy that follows Snowflake databases, schemas, object types, and individual objects. section 15.2.2
- Executes individual scripts or a coordinating script through a Git repository stage, EXECUTE IMMEDIATE FROM, or Snowflake CLI. section 15.7
Adding and removing a table column
An imperative change uses ALTER TABLE EMPLOYEE ADD COLUMN birth_dt DATE, followed by another ALTER statement to drop SSN. A declarative version reruns CREATE OR ALTER TABLE EMPLOYEE with the complete desired column list, including or excluding each column as required. 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 the SQL required to create a table, add a column, and drop a column under imperative and declarative approaches. section 15.2.1
