Sign Up
Back to changelog
New
•2 minute read

Snowflake: Column Recreation with allow_recreate

The Snowflake diff policy accepts allow_recreate = true, which plans the column changes that ALTER cannot apply as DROP COLUMN plus ADD COLUMN without a prompt, including in CI.

Snowflake cannot apply some column changes with ALTER, such as a change of type family or the addition of a generated expression. Atlas can plan these changes as DROP COLUMN followed by ADD COLUMN. With allow_recreate = true in the diff policy, it does so without prompting, including in CI.

Enabling allow_recreate

Set allow_recreate = true in the modify_column block of diff "snowflake":

atlas.hcl
env "local" {  url = "snowflake://user:password@account_identifier/YOUR_DB?warehouse=YOUR_WH"  dev = "snowflake://user:password@account_identifier/YOUR_DEV_DB?warehouse=YOUR_WH"  schema {    src = "file://schema/main.hcl"  }  diff "snowflake" {    modify_column {      allow_recreate = true    }  }}
Note: Recreating a column deletes the data it holds. Setting drop_column = true in the skip block of the diff policy does not prevent a column from being recreated.

Behavior

With allow_recreate = true, Atlas recreates the column without prompting. Without it:

  • In CI, where the CI environment variable is set or stdout is not a TTY, the diff fails. The error names the column and points to allow_recreate in the modify_column block.
  • In a terminal, Atlas prompts you to choose between Abort and Drop and recreate column "X" (data loss).

Changes that need recreation

Atlas recreates a column for:

  • A type change Snowflake cannot alter: a different type family (such as NUMBER to VARCHAR), a different NUMBER scale, a shorter VARCHAR, a different TIMESTAMP variant or precision, a different TIME precision, an INTERVAL change, and FLOAT to or from DECFLOAT.
  • Adding, removing, or changing a generated expression (as).
  • Setting or changing a default, except replacing one sequence default with another, which is applied in place with SET DEFAULT.

Changes Snowflake can alter, such as a longer VARCHAR, nullability, or dropping a default, are still planned as ALTER, with or without allow_recreate.

Example

The migration directory starts with this migration file:

migrations/20261009084422_init.sql
-- Create sequence "ORDER_SEQ"CREATE SEQUENCE "PUBLIC"."ORDER_SEQ" START = 1 INCREMENT = 1 NOORDER;-- Create sequence "ORDER_SEQ_V2"CREATE SEQUENCE "PUBLIC"."ORDER_SEQ_V2" START = 1 INCREMENT = 1 NOORDER;-- Create "ORDERS" tableCREATE TABLE "PUBLIC"."ORDERS" (  "ID" NUMBER NULL,  "QUANTITY" NUMBER NULL,  "UNIT_PRICE" NUMBER(10, 2) NULL,  "ORDER_CODE" NUMBER(10, 0) NULL,  "DISCOUNT" NUMBER(5, 2) NULL,  "NOTE" VARCHAR(500) NULL,  "CREATED_AT" TIMESTAMP_NTZ(6) NULL,  "UPDATED_AT" TIMESTAMP_NTZ(3) NULL,  "PICKUP_TIME" TIME(3) NULL,  "LEAD_TIME" INTERVAL DAY TO SECOND(3) NULL,  "EXCHANGE_RATE" FLOAT NULL,  "TOTAL" NUMBER(38, 2) NULL,  "TAX" NUMBER(12, 3) AS (UNIT_PRICE * 0.1),  "NET_PRICE" NUMBER(12, 3) AS (UNIT_PRICE * 0.9),  "STATUS" VARCHAR(20) NULL,  "ORDER_NO" NUMBER NULL DEFAULT PUBLIC.ORDER_SEQ.NEXTVAL,  "CUSTOMER_NAME" VARCHAR(50) NOT NULL,  "CURRENCY" VARCHAR(3) NULL DEFAULT 'USD') DATA_RETENTION_TIME_IN_DAYS = 1;

The desired schema in schema/main.hcl changes every column except ID, QUANTITY, and UNIT_PRICE:

schema/main.hcl
schema "PUBLIC" {}
sequence "ORDER_SEQ" {  schema = schema.PUBLIC}sequence "ORDER_SEQ_V2" {  schema = schema.PUBLIC}
table "ORDERS" {  schema = schema.PUBLIC  column "ID" {    null = true    type = NUMBER(38)  }  column "QUANTITY" {    null = true    type = NUMBER(38)  }  column "UNIT_PRICE" {    null = true    type = NUMBER(10,2)  }  column "ORDER_CODE" {    null = true    type = VARCHAR(20)  }  column "DISCOUNT" {    null = true    type = NUMBER(5,4)  }  column "NOTE" {    null = true    type = VARCHAR(100)  }  column "CREATED_AT" {    null = true    type = TIMESTAMP_LTZ(6)  }  column "UPDATED_AT" {    null = true    type = TIMESTAMP_NTZ(6)  }  column "PICKUP_TIME" {    null = true    type = TIME(6)  }  column "LEAD_TIME" {    null = true    type = sql("INTERVAL DAY TO SECOND(6)")  }  column "EXCHANGE_RATE" {    null = true    type = DECFLOAT  }  column "TOTAL" {    null = true    type = NUMBER(38,2)    as   = "QUANTITY * UNIT_PRICE"  }  column "TAX" {    null = true    type = NUMBER(12,3)  }  column "NET_PRICE" {    null = true    type = NUMBER(12,3)    as   = "UNIT_PRICE * 0.8"  }  column "STATUS" {    null    = true    type    = VARCHAR(20)    default = "NEW"  }  column "ORDER_NO" {    null    = true    type    = NUMBER(38)    default = sql("PUBLIC.ORDER_SEQ_V2.NEXTVAL")  }  column "CUSTOMER_NAME" {    null = true    type = VARCHAR(100)  }  column "CURRENCY" {    null = true    type = VARCHAR(3)  }  depends_on = [sequence.ORDER_SEQ_V2]}

Run the diff:

atlas migrate diff v2 --env local

Atlas writes the next migration file without prompting:

migrations/20261009085710_v2.sql
-- Modify "ORDERS" table to drop "ORDER_CODE" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "ORDER_CODE";-- Modify "ORDERS" table to add "ORDER_CODE" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "ORDER_CODE" VARCHAR(20) NULL;-- Modify "ORDERS" table to drop "DISCOUNT" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "DISCOUNT";-- Modify "ORDERS" table to add "DISCOUNT" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "DISCOUNT" NUMBER(5, 4) NULL;-- Modify "ORDERS" table to drop "NOTE" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "NOTE";-- Modify "ORDERS" table to add "NOTE" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "NOTE" VARCHAR(100) NULL;-- Modify "ORDERS" table to drop "CREATED_AT" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "CREATED_AT";-- Modify "ORDERS" table to add "CREATED_AT" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "CREATED_AT" TIMESTAMP_LTZ(6) NULL;-- Modify "ORDERS" table to drop "UPDATED_AT" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "UPDATED_AT";-- Modify "ORDERS" table to add "UPDATED_AT" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "UPDATED_AT" TIMESTAMP_NTZ(6) NULL;-- Modify "ORDERS" table to drop "PICKUP_TIME" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "PICKUP_TIME";-- Modify "ORDERS" table to add "PICKUP_TIME" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "PICKUP_TIME" TIME(6) NULL;-- Modify "ORDERS" table to drop "LEAD_TIME" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "LEAD_TIME";-- Modify "ORDERS" table to add "LEAD_TIME" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "LEAD_TIME" INTERVAL DAY TO SECOND(6) NULL;-- Modify "ORDERS" table to drop "EXCHANGE_RATE" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "EXCHANGE_RATE";-- Modify "ORDERS" table to add "EXCHANGE_RATE" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "EXCHANGE_RATE" DECFLOAT NULL;-- Modify "ORDERS" table to drop "TOTAL" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "TOTAL";-- Modify "ORDERS" table to add "TOTAL" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "TOTAL" NUMBER(38, 2) AS (QUANTITY * (CAST(UNIT_PRICE AS NUMBER(10,2))));-- Modify "ORDERS" table to drop "TAX" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "TAX";-- Modify "ORDERS" table to add "TAX" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "TAX" NUMBER(12, 3) NULL;-- Modify "ORDERS" table to drop "NET_PRICE" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "NET_PRICE";-- Modify "ORDERS" table to add "NET_PRICE" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "NET_PRICE" NUMBER(12, 3) AS (UNIT_PRICE * 0.8);-- Modify "ORDERS" table to drop "STATUS" columnALTER TABLE "PUBLIC"."ORDERS" DROP COLUMN "STATUS";-- Modify "ORDERS" table to add "STATUS" columnALTER TABLE "PUBLIC"."ORDERS" ADD COLUMN "STATUS" VARCHAR(20) NULL DEFAULT 'NEW';-- Modify "ORDERS" table to change column "ORDER_NO"ALTER TABLE "PUBLIC"."ORDERS" MODIFY COLUMN "ORDER_NO" SET DEFAULT PUBLIC.ORDER_SEQ_V2.NEXTVAL;-- Modify "ORDERS" table to change column "CUSTOMER_NAME"ALTER TABLE "PUBLIC"."ORDERS" MODIFY COLUMN "CUSTOMER_NAME" SET DATA TYPE VARCHAR(100), COLUMN "CUSTOMER_NAME" DROP NOT NULL;-- Modify "ORDERS" table to change column "CURRENCY"ALTER TABLE "PUBLIC"."ORDERS" MODIFY COLUMN "CURRENCY" DROP DEFAULT;

Getting Started

Snowflake support is part of Atlas Pro. Run atlas login to get started.

See the diff policy docs for the other options of the diff block.

featuresnowflakediff policyatlas pro
Back to changelog