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 Cloud meters compute as per-minute compute units (8 GiB RAM, 2 vCPU) rather than as 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; ClickPipes is the explicit exception and is metered separately. As in Snowflake, ClickHouse Cloud 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 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 | 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.
