Migration
Vertica to Databricks migration: a practical guide
How to migrate a Vertica analytics warehouse to Databricks: exporting to Parquet, replacing projections and segmentation, flex tables, Vertica-specific SQL such as TIMESERIES, UDx and in-database ML, and reconciliation.
Hemant Walchale
· 2 min read
Vertica is a fast columnar analytics database used heavily for event, telemetry and marketing analytics. Teams move to Databricks to combine those analytics with data engineering and machine learning, to stop managing projections and node counts, and to keep data in open formats. The SQL is mostly portable; projections and Vertica-specific functions are where the work is.
This is the approach we recommend.
1. Inventory
List schemas, tables, projections, flex tables, views, user-defined extensions (UDx), load jobs (COPY), ML models, schedules and consumers. Vertica’s system tables (query_requests, projection_usage) show what is queried. Archive the rest.
2. Map Vertica to Databricks
| Vertica | On Databricks |
|---|---|
| Tables | Delta tables in Unity Catalog |
Projections, ORDER BY and SEGMENTED BY |
Not needed; liquid clustering on the most-filtered columns |
| Flex tables | VARIANT columns, or structured tables from parsed JSON |
COPY from files |
Auto Loader or COPY INTO |
EXPORT TO PARQUET |
The migration path for historical data |
TIMESERIES, gap filling, interpolation |
Window functions with generated time ranges |
| Analytic functions | Spark SQL window functions |
| UDx (C++, Java, Python, R) | Python UDFs or SQL functions; rewrite and test |
| In-database ML | Spark MLlib, scikit-learn and MLflow |
| Resource pools | Separate SQL warehouses and job compute per workload |
| Users, roles and grants | Unity Catalog grants, row filters and column masks |
3. Convert SQL
Most Vertica SQL runs on Databricks SQL with small changes. Test date and time functions, INTERVAL arithmetic, string functions, LIMIT ... OVER patterns and implicit casts, and rewrite time-series clauses.
4. Move data and reconcile
Export each table to Parquet, load it as Delta, and keep it in sync with the upstream sources during the parallel run. Compare every table by domain, paying attention to timestamp precision and time zones in event data.
5. Shrink the cluster
Remove Vertica nodes as domains move, and stop renewing licence capacity once the last domain is cut over.
The short version
Export tables to Parquet and load them as Delta, replace projections with liquid clustering, convert flex tables to VARIANT, rewrite time-series SQL and UDx, rebuild in-database ML in Python with MLflow, reconcile and shrink the cluster by domain.
Browse all migration guides, or estimate effort with the migration estimator.
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Databricks. Unfamiliar terms are explained in the data engineering glossary.