Snowflake and ClickHouse: equivalent concepts
The tables below map each Snowflake concept to its ClickHouse equivalent. For function-by-function SQL syntax mapping, see the SQL translation reference. For the end-to-end migration walkthrough, see Migrating from Snowflake to ClickHouse.
Resource hierarchy
How the platform organizes accounts, logical containers for data, and where compute is provisioned.
Warehouse terminology
A ClickHouse warehouse is a grouping of services that share storage and scale compute independently — not a compute cluster as in Snowflake.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Organization | Organization | Root node of the hierarchy in both. |
| Account | Warehouse | Each service scales compute independently; storage is shared at the warehouse level. Tier and billing are set at the organization level, not per warehouse. |
| Database | Database | Logical container for tables. Snowflake uses a Database → Schema → Table hierarchy; ClickHouse flattens this to Database → Table — see Schemas below. |
Schemas
A Snowflake schema serves multiple roles and has no single equivalent in ClickHouse. The table below maps each role to its ClickHouse counterpart.
| Snowflake | ClickHouse | Notes |
|---|---|---|
Namespace partitioning — letting objects with the same name coexist (analytics.users vs marketing.users) | One database per Snowflake schema, or fold the schema name into the database (analytics.public.events → analytics_public.events) | Object references move from three-level (DB.SCHEMA.TABLE) to two-level (DB.TABLE). |
Logical grouping by domain or processing stage (analytics.raw, analytics.staging, analytics.marts) | Separate databases or a consistent naming convention | — |
| Permission boundary | SQL grants at the database, table, or column level | Database-wide grants cover the schema-level grant footprint; per-table grants are also available for finer-grained control. |
| Future grants | Database wildcards (GRANT … ON db.* TO role) apply to current and future tables | Can't scope future grants to a subset of tables within a database. |
Schema OWNERSHIP and MANAGED ACCESS | — | ClickHouse has no object-ownership model, so grants are always explicit. |
Cloning unit (CREATE SCHEMA … CLONE) | No copy-on-write at any granularity — see the Storage and tables section for the zero-copy clone row | Every copy reads source data fully in ClickHouse. |
| Time Travel and replication boundary | Handled at the table level (TTL) or service level (backups, database replication) | No intermediate per-schema boundary. |
| Tagging and classification scope | Apply at the table or column level | No intermediate namespace inherits down. |
Roles and access control
ClickHouse Cloud's access layer splits into console roles at console.clickhouse.cloud (organization-, service-, and SQL-console-scoped) for org admin, billing, and service management; and SQL roles and grants inside each service for database, table, and column access.
| Snowflake | ClickHouse | Notes |
|---|---|---|
Account-level system roles (ACCOUNTADMIN, SYSADMIN, SECURITYADMIN, USERADMIN, PUBLIC) | Organization roles (Admin, Billing, Org API reader, Member) and service roles (Service admin, Service reader, Service API admin/reader) in the console; SQL roles inside each service | Org-scoped console roles cover billing, org admin, and user management; service-scoped roles cover service config, scaling, and backups. |
| Custom account roles | CREATE ROLE in SQL | Same pattern: create a role, grant privileges to it, grant the role to users. |
| Database roles | — | ClickHouse has only one tier of SQL roles, all service-scoped. No equivalent to Snowflake's two-tier account/database role split. |
Role hierarchy (GRANT ROLE … TO ROLE …) | GRANT role1 TO role2 | — |
Privilege grants on objects (GRANT … ON … TO ROLE …) | GRANT … ON db.table TO role | — |
| Object ownership and ownership transfer | — | Access in ClickHouse is controlled entirely through explicit grants. Snowflake patterns that rely on owners delegating access need to be rebuilt as explicit role-based grants. |
USE ROLE | SET ROLE | — |
Compute and capacity
How processing is allocated to a query and sized.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Virtual warehouse | Service (one or more replicas) | A ClickHouse service runs across one or more replicas (typically 3 by default on Scale/Enterprise); queries parallelize across them. |
| Warehouse size (XS through 6X-Large) | Vertical autoscaling bounds | Sizing is configured as min/max memory and CPU bounds rather than discrete t-shirt sizes; setting min = max effectively fixes the size. |
| Multi-cluster warehouse | Manual horizontal scaling | ClickHouse scales replica count rather than cluster count. There's no direct equivalent to Snowflake's auto-scaling policies (Standard/Economy); horizontal replica count is set manually. |
| Auto-suspend / auto-resume | Service idling | Compute stops when there's no work, restarts on the next query. |
| Resource monitors (credit-quota spend caps) | Workloads for runtime scheduling; per-query limits (memory, threads, execution time) | ClickHouse workloads cover runtime resource scheduling but not spend caps — there's no primitive that suspends a service on hitting a credit threshold. |
| Query Acceleration Service | No direct equivalent | ClickHouse has no per-query compute booster; scale the service via vertical autoscaling if queries are consistently large. |
Billing and pricing model
ClickHouse Cloud bills compute as RAM-minutes rather than credits scaled by warehouse size, storage as compressed bytes without Time Travel or Fail-safe overhead, and backups as their own line item rather than bundled into retention windows. Most Snowflake "serverless compute" features (Snowpipe, Search Optimization, Auto-clustering, materialized view refresh, Cortex) are bundled into service compute on ClickHouse — ClickPipes is the explicit exception and is metered separately. Both platforms charge for public internet egress and cross-region data transfer, and both offer committed-spend discounts. See ClickHouse Cloud pricing for current rates, tiers, and commitment options.
Storage and tables
In ClickHouse, a table's behavior is set at creation time: the engine (MergeTree family) determines merge and storage semantics, and ORDER BY / PARTITION BY / TTL clauses configure physical layout and retention. Many Snowflake per-feature settings map to a clause in the ClickHouse CREATE TABLE statement. Physical schema design also differs between platforms — see the migration guide for design tradeoffs.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Permanent table | MergeTree-family table | Engine choice determines storage and merge behavior — pick by access pattern (MergeTree for append-mostly facts, ReplacingMergeTree for upserts, AggregatingMergeTree for pre-aggregations). |
| Transient table (no Fail-safe) | MergeTree table | ClickHouse has no Fail-safe tier, so the permanent/transient distinction doesn't apply. |
| Temporary table (session-scoped) | CREATE TEMPORARY TABLE | Session-scoped temporary tables exist in both; semantics are similar. |
| External table | s3 / gcs / azureBlobStorage table functions for direct file access; Iceberg engine for open catalogs | Object storage and open-table formats are read directly through these functions and engines. |
| Stage (internal / external / user / table) | Object storage referenced directly via s3 / gcs / azureBlobStorage table functions; ClickPipes for managed staging on load | ClickHouse has no stage object: there's no managed internal storage layer for files awaiting load, and no PUT / GET equivalents for moving files in and out. Read from the bucket directly, or use ClickPipes to coordinate ingest. |
| Iceberg table (managed or unmanaged) | Iceberg engine | Reads Iceberg tables stored in S3, Azure, HDFS, or local storage. See the engine page for the current list of supported features. |
| Snowflake Open Catalog (Polaris) | Iceberg engine with REST catalog support | ClickHouse reads from a REST catalog but isn't itself a catalog server. |
| Hybrid table (Unistore) | — | ClickHouse is OLAP-only; OLTP-style point reads and writes aren't a supported workload pattern. |
| Dynamic table | Refreshable MV (scheduled) or incremental MV (per-insert) | Dynamic tables target a lag SLA; ClickHouse MVs cover both the periodic-refresh and per-insert models. See the Query model section for the MV mapping. |
Column data type modes (NOT NULL / nullable) | Nullable(T) for optional; omit for required | In ClickHouse, columns are non-nullable unless wrapped with Nullable(T). Nullability has a small storage and query cost, so use it only when the column needs nulls. |
VARIANT, OBJECT, ARRAY (semi-structured) | JSON, Tuple, Nested, Map, Array | ClickHouse exposes typed alternatives instead of a single variant column — pick the type that matches the data's shape. The JSON type covers schemaless cases; see the SQL translation reference for the full mapping. |
| Schema evolution (add / drop / modify columns) | ALTER TABLE ... ADD / DROP / MODIFY COLUMN | Same DDL surface as Snowflake. Many column changes are metadata-only. |
| Micro-partitions (auto-managed only) | Data parts (auto-managed) plus user-controlled PARTITION BY | Snowflake's micro-partitions are an internal storage detail with no user-facing knob. ClickHouse exposes PARTITION BY as an explicit clause, useful for retention (drop a partition) and pruning. |
| Clustering key | ORDER BY columns in the table definition | Where Snowflake's clustering key is advisory and reorganized in the background, ClickHouse's ORDER BY is enforced at insert time and drives the sparse primary index. |
| Data retention (table / database default) | TTL clause on the table, column, or partition | TTL automatically deletes data older than a configured window. Set at table creation or via ALTER TABLE ... MODIFY TTL. |
| Time Travel | Point-in-time backup restore into a new service | See callout below — granularity differs significantly. |
| Fail-safe | — | Recovery beyond the backup window goes through ClickHouse Cloud support, not a self-service tier. |
| Zero-copy clone | CREATE TABLE ... AS SELECT copy, or backup restore into a new service | ClickHouse has no copy-on-write primitive — every copy reads the source data fully. |
| Secure view | View with SQL SECURITY DEFINER (runs with the view-owner's privileges) | See CREATE VIEW for the syntax and the INVOKER / DEFINER / NONE modes. |
| Row access policy | Row policy — a WHERE-style expression evaluated per user | Row policies apply transparently to every query against the table. |
| Sequence | No direct equivalent — use generateSnowflakeID, generateUUIDv7, or an external generator | ClickHouse has no auto-incrementing sequence object; generated IDs are produced per row at insert time. |
Time Travel and backups
ClickHouse Cloud backups are per-service rather than per-table. Restoring creates a new service, historical state isn't queryable inline, and a single table can't be restored back into the original service.
Updates and deletes
ClickHouse is append-optimized. There's no SQL MERGE statement
(unrelated to the Merge and MergeTree engines), and
ALTER TABLE … UPDATE /
DELETE run as background mutations
rather than transactional row writes. Update patterns from Snowflake (MERGE,
dbt incremental updates) typically port to engine choice in ClickHouse:
ReplacingMergeTree
keeps the latest row by sort key, CollapsingMergeTree
marks deletes inline, and AggregatingMergeTree
maintains aggregated state. Engine choice is set at table creation and is
non-trivial to change later.
Query model and performance
How queries run and are accelerated — indexes, materialized views, caches, and streaming inputs.
Query acceleration in ClickHouse comes from three layers: primary-key ordering (a sparse index over the on-disk sort order), secondary indexes on non-key columns, and materialized views — incremental or refreshable.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Primary key (advisory) | Primary key — drives the on-disk sort order and the sparse primary index | Where Snowflake's PK is advisory only, ClickHouse's PK is load-bearing — it determines physical layout and is used to prune granules, avoid re-sorts, and short-circuit LIMIT. Neither system enforces uniqueness. |
| Foreign key (advisory) | Wide tables or dictionaries for lookups | ClickHouse doesn't accept foreign-key declarations even as advisory hints. |
| Search Optimization Service | Secondary indexes — bloom-filter, token-bloom, minmax | ClickHouse asks you to pick the index type per column and tune its parameters; there's no automatic equivalent. |
| Cortex Search / Snowflake Cortex Search | Full-text index | Token index over string columns for in-database search. |
VECTOR data type and vector search | Array(Float32) plus a vector ANN index | ClickHouse has no dedicated VECTOR type — embeddings are stored as Array(Float32) and accelerated with an ANN index for approximate nearest-neighbor lookups. |
| Materialized view | Incremental MV — updates on each insert into a base table | Source-shape rules differ; review both before porting an existing MV. Cost is paid at insert time in ClickHouse. |
| Dynamic table | Refreshable MV | Refreshable MVs run on a cron-style schedule; same end-state as a target-lag dynamic table. |
| Result cache | Query cache | ClickHouse's query cache is per-replica and not transactionally consistent. |
| Task (scheduled SQL) | Refreshable MV for query-driven scheduled work; external orchestrator (dbt, Airflow) for procedural pipelines | Task DAGs have no direct equivalent — model dependencies in the orchestrator. |
| Stream (CDC over a table) | Materialized view over base-table inserts, or ClickPipes for source-side CDC | ClickHouse MVs react on each insert; there's no offset/consume model. |
EXPLAIN / EXPLAIN_JSON | EXPLAIN variants (PLAN, PIPELINE, SYNTAX, ESTIMATE) | EXPLAIN ESTIMATE reports rows, parts, and marks the query would read; other variants cover deeper plan inspection. |
| External functions | No direct equivalent — closest options are executable UDFs (local script invocation) or a database engine attaching a live source | ClickHouse has no managed outbound HTTP call from SQL; the surrogates run locally or attach a database, not call an arbitrary service. |
| Sessions / session variables | Per-statement execution; multi-step state managed in the client or an orchestrator | ClickHouse has no per-session variables or shared state. |
Transformation and modeling
How transformation pipelines port over: dbt adapters and the modeling shifts they expose.
| Snowflake | ClickHouse | Notes |
|---|---|---|
dbt on Snowflake (dbt-snowflake adapter) | dbt on ClickHouse via the dbt-clickhouse adapter | The adapter covers the standard dbt materializations — view, table, incremental, materialized_view, ephemeral — plus snapshots, seeds, sources, and tests. |
dbt incremental (MERGE-based update strategy) | dbt incremental — supports append, delete+insert, insert_overwrite, and microbatch strategies (plus a legacy default) | ClickHouse incremental models don't issue SQL MERGE; the adapter rewrites the update pattern around append-optimized engines. See the dbt materialization reference for strategy details. |
dbt materialized_view (refresh-based) | dbt materialized_view — backed by ClickHouse incremental MVs; experimental in the adapter | ClickHouse MVs update on insert into the base table, not by re-running the model. Source-shape rules differ between platforms — see the materialized_view materialization page. |
| dbt Cloud | dbt-clickhouse isn't available in dbt Cloud today; dbt Core is the supported path | See the dbt-clickhouse adapter page for current status. |
| Other transformation frameworks (Coalesce, SQLMesh, etc.) | Use the tool's ClickHouse adapter | Adapter coverage and maturity vary; verify supported features against the tool's own documentation. |
Security and governance
Access control, encryption, masking, and network boundaries.
Secure views and row access policies are listed under Storage and tables. Roles and grants are covered in Roles and access control.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Column masking policies (including tag-based) | Column-level grants on specific columns of a table, or data masking patterns | Grants apply at the column level. Snowflake's centralized tag/policy governance has no direct equivalent. |
| Dynamic data masking (function-based) | Views, row policies, or function-based transforms — see data masking patterns | No column-mask primitive yet; patterns are SQL-level. |
| Network policies (IP allowlist) | IP allowlists and private connectivity — PrivateLink (AWS, Azure) and Private Service Connect (GCP) for ingress restriction | Private connectivity is available across the three major clouds. |
| Tri-Secret Secure (customer-managed keys) | CMEK on the service | Available on AWS (KMS) and GCP, with rotation and revocation. |
| Object tagging (governance metadata) | — | ClickHouse exposes metadata via system.* tables rather than user-defined tags. |
| Data classification (sensitive-data detection) | No direct equivalent — external tools (e.g. DataHub) | Not a managed feature. |
Encryption functions (ENCRYPT / DECRYPT) | Encryption functions (encrypt / decrypt) | Covers AES-128/256-CBC/GCM and AEAD modes. |
| OAuth / SAML SSO | SSO (SAML, OIDC) | Same role; configured in the cloud console. |
Audit logs (ACCOUNT_USAGE.LOGIN_HISTORY, QUERY_HISTORY) | Cloud audit log and system.query_log | Admin events go to the audit log; query activity to system.query_log. |
Data sharing
Cross-organization data exchange and clean-room patterns.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Secure Data Sharing | Read access to a shared database, or a dedicated service with consumer-specific row policies | ClickHouse has no zero-copy cross-account share; sharing uses standard access primitives. |
| Snowflake Marketplace / Listings | — | ClickHouse has no in-product data marketplace. |
| Reader accounts (provider-managed consumer) | Dedicated service per consumer, or shared service with row policies | Consumers must have their own ClickHouse Cloud account; no equivalent for serving non-customers under the provider's billing. |
| Data Clean Rooms | Row policies and secure views — assembled per use case | No managed clean-room product. |
Operations and ecosystem
Day-2 concerns: ingestion, ML/BI integration, observability, metadata, and disaster recovery.
ClickHouse surfaces operational state through system.* tables (queries, sessions, replication, parts, metrics) and the cloud console; managed ingestion is handled by ClickPipes; ML, BI, and notebook workflows are typically handled in external systems that read from ClickHouse.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Snowpipe (continuous ingest from object storage) | ClickPipes for S3, GCS, and Azure Blob Storage | Managed ingest from object storage. |
| Snowpipe Streaming | ClickPipes for Kafka, Kinesis, Pub/Sub | Managed low-latency streaming ingest. |
| Openflow connectors | ClickPipes and the broader integrations library | ClickPipes is ClickHouse Cloud's managed connector platform; coverage spans streaming systems, OLTP sources, and object storage. See the integrations library for the current source list. |
| Kafka connector | ClickPipes for Kafka, or the Kafka table engine for self-managed pipelines | Same role; ClickPipes is the managed option. |
| Snowflake Connector for Postgres / MySQL | ClickPipes for Postgres, MySQL | Managed CDC from OLTP sources. |
| Snowpark (Python / Java / Scala DataFrames) | External Python with clickhouse-connect or another client library | No in-database DataFrame runtime; notebook-side libraries cover the same workflow. |
| Snowflake ML (in-database training; formerly Snowpark ML) | External training and serving (notebooks, Spark, Vertex AI, feature stores) reading from ClickHouse; see AI/ML in Cloud for managed-side features | ClickHouse has no in-database ML — use it as the analytical store and run training elsewhere. |
Cortex LLM functions (CORTEX.COMPLETE, CORTEX.SUMMARIZE, etc.) | No in-SQL equivalent — call LLM providers from the application layer or an orchestrator and write results back to ClickHouse | Ask-AI in the docs and console is a docs/console helper, not a SQL surface. |
| Cortex Analyst | — | ClickHouse has no in-product NL-to-SQL service. |
| Snowsight (web UI: editor, dashboards, monitoring, admin) | ClickHouse Cloud console, which includes SQL Console, service management, monitoring, and dashboards | The ClickHouse Cloud console is the equivalent web surface; SQL Console is one component of it, not the whole UI. |
| Streamlit in Snowflake / Native Apps / Snowpark Container Services | No direct equivalent — host Streamlit, container workloads, and packaged apps externally, then query ClickHouse over its native protocol or HTTP | ClickHouse has no in-product app-hosting, container, or app-distribution layer. |
| Notebooks in Snowflake | Jupyter with clickhouse-connect or another client library | No in-product notebook environment; notebook-side libraries cover the same workflow. |
INFORMATION_SCHEMA | Native system.* tables for ClickHouse-specific detail, or the ANSI information_schema views for tool compatibility | Both surfaces available. |
ACCOUNT_USAGE / READER_ACCOUNT_USAGE views | Native system.* tables — system.query_log, system.metric_log, system.processes, and others | Same kind of operational telemetry, exposed through system tables. |
| Query History (UI and view) | system.query_log and system.processes for inspection; KILL QUERY to cancel | Same information, exposed through system tables instead of a job view. |
| Data lineage / Snowflake Horizon Catalog | system.* tables for metadata; external tools (dbt, DataHub) for lineage and quality | ClickHouse exposes metadata via system tables rather than a managed catalog product. |
| Database replication / Account replication / Failover Groups (Snowgrid) | In-region high availability via multiple replicas (managed by Cloud); cross-region resiliency via backups — see Disaster recovery | Cross-region DR is handled at the backup and service level rather than via a single replication primitive. |
Next steps
- For function-by-function SQL syntax mapping, see the SQL translation reference.
- For the end-to-end migration walkthrough, see Migrating from Snowflake to ClickHouse.
