Work Smarter, Not Harder: Rerunnable Schema Changes with CREATE OR ALTER

Snowflake for Developers/Developer Blog/Work Smarter, Not Harder: Rerunnable Schema Changes with CREATE OR ALTER

On this page

Work Smarter, Not Harder: Rerunnable Schema Changes with CREATE OR ALTER

Sabrina Liu

Deploying a schema change usually means solving two problems yourself before you've touched the actual change.

First, you have to know whether the object already exists before you can pick the right verb. CREATE TABLE fails if the table is already there. ALTER TABLE fails if it isn't. So a deployment script ends up full of existence checks, or IF NOT EXISTS guards, just to decide which statement to run.

Second, once the object exists, you have to hand-write the diff. Add a column this release, drop one next release, rename one after that. Every environment — dev, staging, prod — might be sitting at a different point in that history, so the same script doesn't necessarily apply cleanly everywhere.

The common workaround is CREATE OR REPLACE. It's simple, but it's destructive: it drops the object and rebuilds it from scratch, taking the data, grants, tags, and masking policies with it. Anything that depended on the old object has to be manually reapplied. Multiply that across a schema with dozens of tables, views, and policies, and a simple change turns into an all-day deployment exercise: data disappears, lineage breaks, grants and ownership need to be restored, and fragile deployment scripts pile up trying to prevent it from happening again.

Snowflake's CREATE OR ALTER statement collapses both problems into one statement. You write the table, view, or role the way you want it to look today, and you can run that same statement against a brand-new environment or one three releases behind. Snowflake figures out whether to create it or diff it, and only touches what actually changed, so data, grants, and tags on the untouched parts stay put. You stop maintaining separate create-path and alter-path logic, and you stop reaching for CREATE OR REPLACE and then rebuilding everything it just wiped out.

CREATE OR ALTER is generally available for 24 Snowflake object types as of July 2026. The examples below use TABLE, VIEW, TAG, MASKING POLICY, and ROLE — confirm each object type you plan to use is on the current GA list before relying on this in production.

Testing CREATE OR ALTER Across Common Schema Changes

Let's walk through several common schema-change patterns, from creating new objects to evolving existing definitions while preserving important metadata and relationships.

Round 1: Deploy the Same Statement to a Fresh Environment and an Existing One

Say this is the definition your deployment pipeline runs for every environment, every release. On a brand-new environment, the table doesn't exist yet, so CREATE OR ALTER creates it from the declared definition:

CREATE OR ALTER TABLE orders (
  order_id NUMBER,
  cust_id NUMBER,
  amount NUMBER(10,2),
  order_date DATE
);

Insert a few rows so there's real data sitting in the table, the way there would be in staging or prod:

INSERT INTO orders (order_id, cust_id, amount, order_date)
VALUES
  (1001, 501, 125.50, '2026-08-01'),
  (1002, 502, 89.99, '2026-08-02'),
  (1003, 503, 240.00, '2026-08-03');

Now rerun the exact same CREATE OR ALTER TABLE statement, unchanged, against this environment. This is the part a plain CREATE TABLE or CREATE OR REPLACE can't do safely: on CREATE TABLE this fails outright because the table already exists, and on CREATE OR REPLACE it silently drops your three rows and rebuilds an empty table.

-- same statement, same environment, rerun on purpose
CREATE OR ALTER TABLE orders (
  order_id NUMBER,
  cust_id NUMBER,
  amount NUMBER(10,2),
  order_date DATE
);

SELECT COUNT(*) FROM orders;  -- still 3

CREATE OR ALTER compares the declared definition to what's already there, finds no difference, and does nothing. Your pipeline can run this statement on every deploy, to every environment, regardless of whether that environment is new or five releases old, without branching logic to detect which case it's in.

Round 2: Ship the Next Release Without a Separate Migration Script

The next release adds three columns. This is the same kind of change you'd normally hand-write as an ALTER TABLE ... ADD COLUMN migration step, tracked separately from the original CREATE TABLE. With CREATE OR ALTER, it's the same statement, just with the new columns declared:

CREATE OR ALTER TABLE orders (
  order_id NUMBER,
  cust_id NUMBER,
  amount NUMBER(10,2),
  order_date DATE,
  cust_name VARCHAR,                 -- new column
  items ARRAY,                       -- new column
  is_canceled BOOLEAN DEFAULT FALSE  -- new column
);

New columns are added to the end of the table. Columns without a default get NULL on existing rows; is_canceled gets its declared default of FALSE. The three rows from Round 1 are still there.

Rerun that same statement again, and again nothing happens; it's already the desired state. That's the property that matters for a real pipeline: the release-2 script and the rerun-of-release-2 script are identical, so there's no special "already deployed" branch to write or to get wrong.

Let's do a little pretend-backfill on the new columns:

UPDATE orders
SET
  cust_name = CASE cust_id
    WHEN 501 THEN 'Ava'
    WHEN 502 THEN 'Ben'
    WHEN 503 THEN 'Cara'
  END,
  items = CASE cust_id
    WHEN 501 THEN ARRAY_CONSTRUCT('book', 'pen')
    WHEN 502 THEN ARRAY_CONSTRUCT('bag')
    WHEN 503 THEN ARRAY_CONSTRUCT('monitor', 'cable')
  END;

Preserving Dependencies: Views, Tags, and Grants

CREATE OR ALTER also holds up when objects are connected. In this example, we create a view, attach a masking policy to a tag, apply that tag to cust_name, and grant a role access to the view:

CREATE OR ALTER VIEW order_summary AS
SELECT order_id, cust_id, amount, order_date
FROM orders;

CREATE OR ALTER TAG customer_id_tag;

CREATE OR ALTER MASKING POLICY mask_customer_id
  AS (val NUMBER)
  RETURNS NUMBER
  -> IFF(IS_ROLE_IN_SESSION('DUMMY_ROLE'), val, -1);

ALTER TAG customer_id_tag
  SET MASKING POLICY mask_customer_id;

ALTER TABLE orders
  MODIFY COLUMN cust_id
  SET TAG customer_id_tag = 'sensitive';

CREATE OR ALTER ROLE DUMMY_ROLE;
GRANT ROLE DUMMY_ROLE TO USER <yourself>;
GRANT SELECT ON VIEW order_summary TO ROLE DUMMY_ROLE;
GRANT USAGE ON ... TO ROLE DUMMY_ROLE;  -- database, schema, warehouse

The table, view, tag, policy, and role can all be rerun as desired-state definitions. The tag-to-policy and role-to-view grants show how object associations survive the redeployment too.

The results below confirm the view definition, the column-tag association on cust_id, the active masking policy, and the SELECT grant on order_summary:

SELECT * FROM order_summary;   -- cannot see cust_id

USE ROLE DUMMY_ROLE;
SELECT * FROM order_summary;   -- can see cust_id

What Happens When You Add or Remove Columns

Adding another column doesn't touch the current data:

CREATE OR ALTER TABLE orders (
  order_id NUMBER,
  cust_id NUMBER,
  amount NUMBER(10,2),
  order_date DATE,
  cust_name VARCHAR,
  items ARRAY,
  is_canceled BOOLEAN DEFAULT FALSE,
  dummy_column VARCHAR
);

Removing an existing column from the table DDL drops the column, and all the data in that column goes with it.

aside negative If you still need the data in a dropped column, it's recoverable from time travel, but not the way you'd first guess.

CREATE OR ALTER TABLE orders (
  order_id NUMBER,
  cust_id NUMBER,
  amount NUMBER(10,2),
  order_date DATE,
  items ARRAY,
  is_canceled BOOLEAN DEFAULT FALSE,
  dummy_column VARCHAR
);

-- WRONG!!
SELECT cust_name FROM orders AT (OFFSET => -60*5);

-- CORRECT :)
CREATE OR REPLACE TRANSIENT TABLE orders_before_drop
  CLONE orders AT (OFFSET => -60*5);
SELECT * FROM orders_before_drop;

You can't select a dropped column directly from time travel on the current table. You have to clone the table as it existed before the drop, then query the clone.

Two Limitations to Plan Around

CREATE OR ALTER handles a lot, but table DDL has two limits worth knowing before you lean on it for a real deployment pipeline.

Rename columns with an explicit ALTER TABLE ... RENAME COLUMN. Changing a column's name in the desired-state definition doesn't rename it. It drops the old column and creates a new one, which means you lose the data.

CREATE OR ALTER also can't reorder columns. If display order matters for your use case, put that ordering in a view instead of fighting the table DDL for it.

Where This Fits in a Deployment Workflow

These patterns are why CREATE OR ALTER earns a place in a CI/CD-driven deployment pipeline: the same desired-state DDL runs against dev, staging, and prod as part of an automated release, creating objects where they don't exist yet and diffing them where they do, without a human deciding which case applies.

The biggest win is keeping the complete desired-state DDL in a version-controlled deployment workflow. You review exactly what the objects should become, instead of stacking up small, isolated edits that obscure the overall design over time.

CREATE OR ALTER doesn't replace every migration tool. If you're doing complex, multi-step transformations, or you need fine-grained control over ordering across many objects, a dedicated migration framework with explicit up/down scripts is still the more careful choice. For the common case in a schema-deployment pipeline, "this table needs one more column" or "this view definition changed," it removes a surprising amount of the scripting that tends to accumulate around tracking environment state.

That's the actual shift: the same DDL statement is safe to run on day one and safe to rerun on day two hundred. You stop asking "does this object exist yet, and what state is it in" before every deploy, because the statement already answers that question for you, one desired-state definition at a time, in every environment.

Updated Oct 14, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances