Migration
Greenplum to Snowflake migration: a practical guide
How to migrate a Greenplum warehouse to Snowflake: PostgreSQL-dialect SQL conversion, DISTINCT ON and arrays, PL/pgSQL to Snowflake Scripting, replacing DISTRIBUTED BY and gpfdist, MADlib to Snowpark, and reconciliation.
Hemant Walchale
· 2 min read
Greenplum users are re-evaluating after changes to the product’s licensing and open-source status, and many choose a managed warehouse so they no longer run an MPP cluster themselves. Snowflake is a natural fit for SQL-heavy Greenplum estates, and its support for several PostgreSQL conventions makes conversion smoother.
This is the approach we recommend.
1. Inventory
List schemas, tables (heap, append-optimized, column-oriented), views, PL/pgSQL functions, external tables, gpload and gpfdist jobs, MADlib usage, schedules and consumers. Query logs and pg_stat views show what is used; archive the rest.
2. Map Greenplum to Snowflake
| Greenplum | On Snowflake |
|---|---|
| Tables (heap, AO, AOCO) | Snowflake tables |
DISTRIBUTED BY, DISTRIBUTED RANDOMLY |
Removed |
| Partitioned tables | Automatic micro-partition pruning; optional clustering keys |
VACUUM, ANALYZE |
Not needed |
:: casts |
Supported |
DISTINCT ON |
QUALIFY ROW_NUMBER() OVER (...) = 1 |
Arrays, json and jsonb operators |
ARRAY, OBJECT, VARIANT and their functions |
| Serial columns, sequences | Sequences or IDENTITY |
| PL/pgSQL functions | Snowflake Scripting procedures, SQL UDFs |
| gpfdist, gpload, external tables | Stages with COPY INTO, Snowpipe, external tables |
| MADlib | Snowpark Python with scikit-learn and similar libraries |
| Roles and grants | Role-based access control, masking and row access policies |
3. Convert and test
Most queries convert with small changes. Test regular expression functions, date and interval arithmetic, string concatenation with NULL and implicit casts. Conversion tools can handle much of the DDL and SQL.
4. Move data and reconcile
Export tables in parallel to cloud storage (for example via writable external tables), load them with COPY INTO, and keep them in sync during the parallel run. Compare every table by domain before cutting over.
5. Retire the cluster
Stop each domain’s Greenplum jobs on cutover day and decommission hosts as the cluster empties.
The short version
Archive what’s unused, drop distribution and storage options, convert PostgreSQL-dialect SQL (DISTINCT ON, arrays, JSON) and PL/pgSQL, move MADlib to Snowpark, replace gpfdist with stages and COPY INTO, reconcile and retire the cluster by domain.
Compare with Greenplum to Databricks, or browse all migration guides.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Snowflake. Unfamiliar terms are explained in the data engineering glossary.