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
| 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. |
Warehouse terminology
A ClickHouse warehouse is a grouping of services that share storage and scale compute independently, not a compute cluster as in Snowflake.
Schemas
A Snowflake schema serves multiple roles and has no single equivalent in ClickHouse.
| 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) | Per-table CREATE TABLE ... CLONE AS, not per-schema; see the Storage and tables section | No single-statement clone of a whole schema or database. Clone each table individually. |
| Time Travel and replication boundary | Service level — point-in-time recovery via backups; replication via managed replicas, see Operations and ecosystem | No per-schema boundary: recovery and replication are scoped per service. TTL controls data expiry, not point-in-time recovery. See the Data retention row. |
| 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. For per-user scoped SQL console access, see the callout below. |
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 | — |
Per-user SQL console access
ClickHouse has no separate database-role tier, but ClickHouse Cloud can still grant per-user, scoped access to the SQL console. Create a role matching the sql-console-role:<email> naming convention and grant it the privileges that user should have; the console assigns it in place of the default sql_console_admin / sql_console_read_only roles. These are ordinary service-scoped SQL roles — the namespace is just a mapping convention. See granular access control.
Compute and capacity
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Virtual warehouse | Service (one or more replicas) | Basic tier services are single-replica; Scale and Enterprise support multi-replica (2+) deployments for higher SLAs. Queries parallelize across replicas. |
| 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 bills based on dedicated machines, metered in per-minute compute units (8 GiB RAM, 2 vCPU). Storage pricing is based on compressed bytes, and backups are billed separately. ClickPipes is metered separately.
Snowflake bills on credits scaled by warehouse size, charges for storage as compressed bytes without Time Travel or Fail-safe overhead, and bills backups as a separate line item rather than bundling them into retention windows. Most Snowflake "serverless compute" features (Snowpipe, Search Optimization, Auto-clustering, materialized view refresh, Cortex) are bundled into service compute on ClickHouse.
Both charges for public internet egress and cross-region data transfer and offers 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 does not support an intermediary staging step. Read from the bucket directly, or use ClickPipes to coordinate ingest. |
| Iceberg table (managed or unmanaged) | Iceberg engine | See the data lake support matrix for read, write, and storage-backend support. |
| 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 | Maps to a scheduled Refreshable MV; 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. 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 | Granularity differs significantly; see the callout below. |
| Fail-safe | — | Recovery beyond the backup window goes through ClickHouse Cloud support, not a self-service tier. |
| Zero-copy clone | CREATE TABLE ... CLONE AS within a service, or backup restore into a new service | CLONE AS hardlinks the source table's parts (part-level copy-on-write), so no data is physically copied. Copying across services still reads the source fully. |
| Secure view | View with SQL SECURITY DEFINER | SQL SECURITY DEFINER delegates privileges (the view runs as its owner) but isn't a full Secure View: the definition stays readable via SHOW CREATE by anyone who can query it. See CREATE VIEW for the DEFINER / INVOKER / 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 | generateSerialID for a Keeper-backed sequential counter; generateSnowflakeID or generateUUIDv7 for distributed unique IDs | generateSerialID is the closest match to an auto-incrementing sequence: a named, monotonic counter coordinated through ClickHouse Keeper. The UUID functions suit high-throughput unique IDs that don't need a shared counter. |
Time Travel and backups
ClickHouse has no inline query of historical state like Snowflake Time Travel; point-in-time recovery goes through backups. Console-managed backups are per-service and restore into a new service. SQL BACKUP / RESTORE commands work at table or database granularity and can restore into an existing 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
cancels rows by inserting a matching row with Sign = -1, and AggregatingMergeTree
maintains aggregated state. Engine choice is set at table creation and is
non-trivial to change later.
Query model and performance
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.
| 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) or Array(BFloat16) with a vector ANN index; or QBit for tunable-precision search | ClickHouse has no dedicated VECTOR type. Embeddings store as Array(Float32), or Array(BFloat16) to halve storage, with an ANN index accelerating approximate nearest-neighbor lookups. QBit keeps full precision while letting you trade bits for speed at query time. |
| 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. |
| Result cache | Query cache | ClickHouse's query cache lives in each replica's memory and is per-user by default; identical queries to different replicas don't share results. 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 | URL table engine or url for remote HTTP/HTTPS I/O, remote for another ClickHouse server, executable UDFs for local scripts, or a database engine to attach a live source | ClickHouse can read from and write to HTTP endpoints from SQL via the URL engine, but has no per-row remote function call with managed batching and auth like a Snowflake External Function. |
| Sessions / session variables | SET for session-scoped settings and query parameters | SET name = value applies a setting for the session's lifetime, and SET param_name = value defines query parameters referenced as {name:Type}. Free-form Snowflake-style variables ($var) and multi-step procedural state have no equivalent; keep those in the client or an orchestrator. |
Transformation and modeling
| 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
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) | CREATE MASKING POLICY (ClickHouse Cloud), or column-level grants. See data masking patterns | Masking policies cover the column-masking part. ClickHouse targets roles, not tags, so Snowflake's centralized tag-based governance has no direct equivalent. |
| Dynamic data masking (function-based) | CREATE MASKING POLICY (ClickHouse Cloud); or views and row policies. See data masking patterns | CREATE MASKING POLICY is a direct equivalent: function-based column masking applied at query time per role, without changing stored data. |
| 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 | Supports key rotation and revocation. See the CMEK page for the current list of supported cloud providers. |
| Object tagging (governance metadata) | — | ClickHouse exposes metadata via system.* tables rather than user-defined tags. |
| Data classification (sensitive-data detection) | — | Not a managed feature; external tools (e.g. DataHub) cover this layer. |
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
| 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, views, and masking policies (ClickHouse Cloud) | No managed clean-room product; build access controls from row policies, views, and query-time column masking. |
Operations and ecosystem
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 handled in external systems that read from ClickHouse.
| Snowflake | ClickHouse | Notes |
|---|---|---|
| Snowpipe (continuous ingest from object storage) | ClickPipes for object storage (S3, GCS, Azure Blob Storage) | Managed ingest from object storage. See supported data sources for the full list. |
| Snowpipe Streaming | ClickPipes streaming sources (Kafka, Kinesis, Pub/Sub) | Managed low-latency streaming ingest. See supported data sources for the full list. |
| 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. To host the source database in ClickHouse Cloud, Managed Postgres is an NVMe-backed Postgres service that replicates into ClickHouse via the same Postgres CDC connector. |
| 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 LLM surface; call hosted models from the application layer or an orchestrator and write results back to ClickHouse. |
| Cortex Analyst | ClickHouse Agents in the cloud console | Agents are conversational: natural-language queries against your data with tool calls and chat workflows. Check the Agents page for the current capability surface. |
| 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 | — | ClickHouse has no in-product app-hosting, container, or app-distribution layer. Host Streamlit, container workloads, and packaged apps externally, then query ClickHouse over its native protocol or HTTP. |
| Notebooks in Snowflake | Hex, or Jupyter with clickhouse-connect | No in-product notebook in ClickHouse Cloud. Hex is a first-class partner with a native ClickHouse connector; Jupyter covers the self-managed path through the Python client. |
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 and failover work differently in ClickHouse Cloud. See Disaster recovery for the current model. |
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.
