clickzetta ClickZetta SQL Migration Guide
Use this skill when migrating SQL workloads from Snowflake, Databricks (Delta Lake), or Spark SQL to ClickZetta Lakehouse, or when answering "how does ClickZetta differ from " questions.
For native ClickZetta SQL syntax that does not differ from standard SQL, refer to the ClickZetta Lakehouse documentation.
Reference Documents
| Document | When to read |
|---|---|
| Snowflake migration guide | Migrating from Snowflake — object mapping, type mapping, syntax + function differences |
| Databricks migration guide | Migrating from Databricks/Delta Lake — APPLY CHANGES, ZORDER, WHEN NOT MATCHED BY SOURCE alternatives |
| dbt migration guide | Migrating an existing dbt project (Databricks / Snowflake / Redshift → ClickZetta) — adapter switch, project config, materializations, incremental strategies, macros, snapshots |
| vs Snowflake summary | Cross-platform comparison summary |
| vs Spark SQL summary | Cross-platform comparison summary |
| DML differences | INSERT/UPDATE/DELETE/MERGE/COPY syntax that differs from other systems (concise migration view) |
| Implicit type conversion | The #1 migration error — strict CAST rules for INSERT/UPDATE |
| Function mapping | Function-by-function mapping tables (Snowflake/Spark/Databricks → ClickZetta) and unsupported functions |
| DDL reference | Detailed DDL syntax — kept for migration completeness; for native ClickZetta DDL prefer the official documentation |
| DML reference | Detailed DML syntax — kept for migration completeness; for native ClickZetta DML prefer the official documentation |
| DQL reference | Detailed DQL syntax — kept for migration completeness; for native ClickZetta DQL prefer the official documentation |
| Functions reference | Detailed function list — kept for migration completeness; for native ClickZetta functions prefer the official documentation |
⚠️ Most Common Migration Pitfalls (Quick Reference)
| Scenario | Snowflake / Spark / Databricks | ClickZetta |
|---|---|---|
| Implicit string→DATE/TIMESTAMP/BOOLEAN/JSON in INSERT | ✅ allowed | ❌ Error — must use CAST or typed literals (DATE '...', TIMESTAMP '...', TRUE/FALSE, PARSE_JSON(...)) |
IFF(cond, a, b) (SF) |
— | IF(cond, a, b) |
ARRAY_SIZE(arr) (SF) |
size(arr) (Spark) |
SIZE(arr) ✅ or ARRAY_SIZE(arr) ✅ — both supported |
LISTAGG(col, ',') WITHIN GROUP (...) (SF) |
— | GROUP_CONCAT(col ORDER BY col SEPARATOR ',') |
LATERAL FLATTEN(input => arr) (SF) |
— | LATERAL VIEW EXPLODE(arr) |
data:key JSON access (SF) |
— | data['key'] |
OBJECT_CONSTRUCT('k', v) (SF) |
STRUCT(v AS k) (Spark) |
named_struct('k', v) |
VARIANT type (SF) |
— | JSON type |
NUMBER(p, s) (SF) |
— | DECIMAL(p, s) |
CHARINDEX(sub, s) (SF) |
— | INSTR(s, sub) ⚠️ parameter order reversed |
DATEDIFF(day, start, end) (SF) |
DATEDIFF(end, start) (Spark) |
both supported, ⚠️ Snowflake order has unit as first arg |
WHEN NOT MATCHED BY SOURCE THEN DELETE (Databricks) |
— | ❌ Not supported — use MERGE INTO + separate DELETE |
APPLY CHANGES INTO (DLT) |
— | TABLE STREAM + MERGE INTO |
WITH RECURSIVE (SF/Databricks) |
✅ supported | ❌ Not supported — iterate via Python/ZettaPark or pre-build helper tables |
BEGIN; COMMIT; ROLLBACK; (transactions) |
✅ | ❌ Not supported — use MERGE INTO for atomic operations |
TARGET_LAG = '1 minute' for dynamic tables (SF) |
— | REFRESH INTERVAL 1 MINUTE VCLUSTER xx |
METADATA$ACTION for streams (SF) |
— | __change_type (values: INSERT / UPDATE_BEFORE / UPDATE_AFTER / DELETE) |
OPTIMIZE t ZORDER BY (col) (Databricks) |
— | OPTIMIZE t (small file compaction only, no ZORDER) |
STRUCT(1 AS id, 'a' AS name) (Spark) |
— | named_struct('id', 1, 'name', 'a') |
TABLESAMPLE (50 PERCENT) |
— | ❌ PERCENT not supported — use ORDER BY RAND() LIMIT n |
CREATE SEQUENCE (SF) |
— | ❌ Not supported — use IDENTITY(seed) column (BIGINT only) |
CREATE TEMPORARY TABLE (SF) |
— | ❌ Not supported — use CTE |
CHARINDEX / EDITDISTANCE / SOUNDEX (SF) |
— | INSTR (reversed args) / Python UDF / no equivalent |
Object Concept Mapping
| Snowflake | Databricks | ClickZetta |
|---|---|---|
| DATABASE | Catalog (internal) | WORKSPACE |
| SCHEMA / DATABASE.SCHEMA | Database / Schema | SCHEMA |
| WAREHOUSE | Cluster / SQL Warehouse | VCLUSTER |
| STAGE | External Location | VOLUME (+ STORAGE CONNECTION) |
| STORAGE INTEGRATION | — | STORAGE CONNECTION |
| SNOWPIPE | Auto Loader | PIPE |
| STREAM | (Delta CDF / DLT CDC) | TABLE STREAM |
| DYNAMIC TABLE | DLT (Live Tables) | DYNAMIC TABLE (different syntax) |
| TASK | Job | Studio Task |
| SEQUENCE | — | IDENTITY column |
| SHARE | Delta Sharing | SHARE |
| — | Unity Catalog (federation) | EXTERNAL CATALOG |
Data Type Mapping Quick Reference
| Snowflake | Spark / Databricks | ClickZetta |
|---|---|---|
NUMBER(p, s) / NUMERIC |
DECIMAL(p, s) |
DECIMAL(p, s) |
INTEGER / NUMBER(10,0) |
INT / BIGINT |
INT / BIGINT |
VARCHAR(n) / TEXT |
STRING |
STRING (recommended) or VARCHAR(n) |
TIMESTAMP_LTZ |
TIMESTAMP |
TIMESTAMP |
TIMESTAMP_NTZ |
TIMESTAMP_NTZ |
TIMESTAMP_NTZ |
VARIANT |
— | JSON |
ARRAY (untyped) |
ARRAY<T> |
ARRAY<T> (must specify element type) |
OBJECT |
MAP<K,V> / STRUCT<...> |
MAP<K,V> or STRUCT<...> |
GEOGRAPHY |
— | not supported |
| — | — | VECTOR(FLOAT, N) (ClickZetta-specific) |
Migration Workflow Pointers
This skill focuses on SQL syntax compatibility. A complete migration involves more than SQL rewrites:
- Object mapping — see table above
- Schema/DDL conversion — see migration-snowflake.md and migration-databricks.md
- Data movement — typically via object storage (S3/OSS) staging + COPY INTO; not covered in detail here
- SQL rewrites — see this skill's reference documents
- Application/driver layer — JDBC, Python connector, BI tool reconnection; refer to
clickzetta-lakehouse-connectskill - Permission migration — RBAC concept comparison; refer to
lakehouse-doc-enofficial permission, GRANT, masking policy, and network policy docs - Performance tuning re-mapping — Snowflake CLUSTER BY / Databricks ZORDER → ClickZetta partitioning, indexes, and official optimization docs in
lakehouse-doc-en
For end-to-end migration planning, combine this skill with the skills listed above.