--- url: 'https://altinity.com/webinarspage/moving-big-data-from-snowflake-to-clickhouse-for-fun-and-profit' title: Moving Big Data from Snowflake to ClickHouse® for Fun and Profit author: name: Altinity url: 'https://altinity.com/author/davidmcknightfosforus/' date: '2022-06-24T10:56:26-07:00' modified: '2026-06-04T12:55:21-07:00' type: post summary: 'Move data from Snowflake to ClickHouse via S3 and Parquet: COPY INTO stage, s3() import, schema generation, and optimizing with codecs and partitioning.' categories: - Webinars tags: - ClickHouse - Parquet - Snowflake - webinar image: 'https://altinity.com/wp-content/uploads/2022/06/moving-big-data-snowflake.png' published: true --- # Moving Big Data from Snowflake to ClickHouse® for Fun and Profit **Recorded:** Thursday, June 23 | 10 am PT **Presenters:** Robert Hodges & Altinity Support Engineering Architected three-paragraph condensation of migration tutorial content In this practical tutorial, Altinity CEO Robert Hodges demonstrates how to migrate 150 million rows from Snowflake to ClickHouse using Amazon S3 and the Parquet format as an intermediary, walking through the complete process end-to-end with runnable SQL, a Python automation script, and a live demo of the optimized result. He frames the motivation by comparing the two systems: Snowflake is an excellent, fully managed proprietary warehouse with automatic tuning, but costs can reach tens of millions per year at scale, whereas ClickHouse is open source under Apache 2.0, far more cost-efficient (especially for multi-tenant SaaS analytics), and comparably or more performant for time-ordered analytical workloads—at the price of more explicit schema tuning. The migration uses S3 and Parquet rather than a direct ODBC connection, which is too slow for bulk transfers, and Parquet’s embedded metadata makes it easy to reconstruct table definitions on the destination side. The process is three steps: in Snowflake, create an S3 stage with Parquet format and COPY INTO it; in ClickHouse, create a working table and load the data via the s3() table function with wildcard support and max_insert_threads for parallelism; and finally optimize the schema by switching to smaller data types, applying ZSTD compression codecs, and adding PARTITION BY and ORDER BY clauses to enable primary key indexing. Robert also introduces a PyArrow-based Python helper that reads Parquet metadata from S3 and automatically generates both the CREATE TABLE and INSERT INTO … SELECT * FROM s3(…) statements, handling the tedious column-type mapping. A live demo on Altinity.Cloud shows before/after table sizes (6.6 GB → 4.37 GB for orders) and a column-level compression analysis using system.columns. He notes a temporary known issue with nullable columns in Parquet that requires explicit column-type hints in the s3() call, referencing the relevant GitHub issue. **Here are the slides:** [Moving-Big-Data-from-Snowflake-to-ClickHouse-for-Fun-and-Profit](https://altinity.com/wp-content/uploads/2024/03/Moving-Big-Data-from-Snowflake-to-ClickHouse-for-Fun-and-Profit.pdf)[Download](https://altinity.com/wp-content/uploads/2024/03/Moving-Big-Data-from-Snowflake-to-ClickHouse-for-Fun-and-Profit.pdf) ## **Key Moments (Timestamps)** Key moments generated with AI assistance. - 0:06 – Introduction: Robert Hodges, Altinity - 1:12 – Altinity overview: enterprise ClickHouse provider, Altinity.Cloud, Kubernetes Operator - 2:55 – Snowflake overview: SQL completeness, decoupled compute/storage, automatic tuning, proprietary - 6:06 – ClickHouse overview: runs anywhere, shared-nothing, cost efficiency, fast ingest, Apache 2.0 - 9:46 – Migration goal: move data via S3 and Parquet (vs slower ODBC approach) - 11:11 – Step 1 (Snowflake): export to Parquet on S3 - 12:20 – Snowflake: create a schema for organization - 12:35 – Snowflake: CREATE OR REPLACE STAGE with Parquet file format, S3 URL, credentials - 13:44 – Snowflake: COPY INTO stage with HEADER = TRUE - 15:19 – Verify: SELECT COUNT on original table and SELECT COUNT from S3 stage — both 150M rows - 16:20 – Step 2 (ClickHouse): load from S3 - 17:39 – ClickHouse: create a working (unoptimized) table from generated schema - 19:10 – ClickHouse: INSERT INTO … SELECT * FROM s3() with wildcard and max_insert_threads = 16 - 20:41 – Known issue: nullable columns require explicit column-type string in s3() call - 21:35 – Verify: SELECT COUNT confirms 150M rows loaded - 22:16 – Parquet format overview: row groups, column data files, metadata at end - 23:41 – Parquet dataset: multiple files in a prefix, read together with wildcard - 24:09 – Python helper script: generate ClickHouse DDL and s3() load command from Parquet metadata - 24:35 – Script setup: clone repo, create virtualenv, install PyArrow dependencies - 25:00 – Environment variables: S3 region, access key, secret key, S3 path - 25:37 – Running generate_ch_schema.py: prints CREATE TABLE + INSERT command - 26:22 – Step 3: Optimize the imported table - 27:00 – Optimization changes: smaller data types (Int128 → Int64), ZSTD codec, PARTITION BY month, ORDER BY date+customer - 29:00 – Load optimized table from unoptimized table using max_insert_threads - 29:12 – OPTIMIZE TABLE FINAL: force all parts to merge - 29:54 – Before/after size comparison: 6.6 GB → 4.37 GB; within ~2% of Snowflake’s storage - 30:54 – Live demo: connected to Altinity.Cloud - 34:25 – Demo: column-level compression analysis using system.columns - 36:30 – Demo: example query on optimized orders table - 37:00 – Q&A: why not import directly into the optimized table? - 38:03 – Summary: three-step process; documentation and GitHub resources --- ## **Webinar Transcript** ### **[0:06] — Introduction and Housekeeping** **Robert:** Hello everyone and welcome to our webinar on moving big data from Snowflake to ClickHouse for fun and profit. My name is Robert Hodges; I work for Altinity and I’ll be your presenter today. This is being recorded and everyone who signed up will get links to both the recording and the slides in about 24 hours. You can post questions in the Q&A box or the chat as we go along. ### **[1:12] — About Altinity** **Robert:** I’ve been working on databases for over 38 years, from pre-relational databases like M204 all the way to analytic databases like ClickHouse. My day job is CEO of Altinity, backed by a great engineering team with deep experience in analytic databases and the applications built on them. Altinity is the number one provider of support and services for ClickHouse in the American and European markets. This includes Altinity.Cloud, our cloud platform for managed ClickHouse running on Amazon and GCP — up and running for close to two years. We are committers on ClickHouse and also developed the Altinity Kubernetes Operator for ClickHouse, the first Kubernetes operator ever written for a data warehouse. ### **[2:55] — Snowflake Overview** **Robert:** One of the things that was fun about preparing this presentation was spending quality time with Snowflake. It has many great features. Snowflake is a SQL data warehouse with a very complete SQL implementation — most SQL standard analytics features are supported. It runs in the public cloud (Amazon, Google, Azure). A really important feature is that it **decouples compute and storage**: data lives in object storage, compute nodes cache it on SSD, and you can spin up multiple virtual data warehouses operating on the same data independently. This is a genuine architectural innovation that Snowflake pioneered. It also has very advanced cloud management — you don’t really need to understand how Snowflake works internally, you just need to understand SQL databases. It’s particularly well suited for **star schemas** with complex large-table joins, supported by sophisticated query planning and data shuffling across nodes. Snowflake has **automatic performance tuning** — when you create tables you don’t set compression codecs, sorting keys, or other internal properties; Snowflake figures it out for you. And it’s **proprietary**: you run it in their cloud or you don’t run it. You can’t install it yourself. All of these properties together have made Snowflake hugely popular, backed by a large ecosystem of tools. ### **[6:06] — ClickHouse Overview** **Robert:** ClickHouse is a SQL data warehouse as well. If you’re on Snowflake and want to try ClickHouse, here are the key properties. It understands SQL — the dialect is not as complete as Snowflake, but it’s very focused on operations required to select data off very large datasets. It **runs anywhere Linux does**: an Android phone, a laptop, Kubernetes clusters with hundreds of nodes, or cloud VMs. It uses a **shared-nothing architecture** — nodes with attached storage connected over a network. ClickHouse is evolving toward decoupled compute/storage (S3-backed tables are increasingly capable), but it’s still primarily shared-nothing. The most compelling difference from Snowflake: **cost efficiency**. There are Snowflake accounts spending tens of millions of dollars per year. ClickHouse, because it’s open source and extremely parsimonious with resources, is ideally suited as the back end for SaaS analytics where you have hundreds or thousands of tenants and need cost-efficient dashboards for each. **Extremely fast data ingest**: loading a trillion rows of data I was able to achieve around 73 million rows per second on a moderately powerful host. For the types of applications ClickHouse handles best — very large time-ordered data sets like observability data, market data, CDN telemetry — you can build systems with linear scaling and fixed query response as you add more resources. It’s **open source under Apache 2.0**, which permits use for any business purpose with few limitations. The result: ClickHouse has become a phenomenally popular engine for real-time analytics, growing in popularity every month. ### **[9:46] — Why S3 + Parquet for Migration** **Robert:** Our goal is to move data from Snowflake to ClickHouse via S3 and Parquet. There are other ways — for example, ClickHouse can actually read directly from Snowflake tables through the Snowflake ODBC driver. But that’s slow: you have a single network connection affected by bandwidth and latency between Snowflake and ClickHouse. It’s not a good way to move large amounts of data. For moving data in bulk, generating Parquet files and putting them in object storage is the right approach. In this example we’re moving 150 million rows — not a huge dataset but not trivial either. For details on the complete procedure, see the companion article [migrating data from Snowflake to ClickHouse using S3 and Parquet](https://altinity.com/blog/migrating-data-from-snowflake-to-clickhouse-using-s3-and-parquet) on the Altinity blog. ### **[11:11] — Step 1: Export from Snowflake to Parquet on S3** **Robert:** Step one is getting data out of Snowflake into Parquet files in S3. This is actually pretty easy. We’re using a table called ORDERS from the TPC-H sample dataset (SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS), which is included in any Snowflake account created in the last few years. It has about 150 million rows, a few numeric fields, a few string fields (VARCHAR), and a date. There are no nulls — all columns are NOT NULL — which matters when we get to the ClickHouse side. **Create a schema for organization:** CREATE SCHEMA S3; **Create a stage pointing to your S3 bucket:** CREATE OR REPLACE STAGE S3.BUCKET   URL=’s3://your-migration-bucket/snowflake’   CREDENTIALS=(     aws_key_id=’your_access_key’,     aws_secret_key=’your_secret_key’   )   FILE_FORMAT = (TYPE = PARQUET); The FILE_FORMAT = (TYPE = PARQUET) setting means anything copied into this stage will be written as Parquet files automatically. The stage can also be used to read data back in. **Copy the table into the stage:** COPY INTO @S3.BUCKET/tpch/orders FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS HEADER = TRUE; The HEADER = TRUE is important: it stores the column names in the Parquet metadata, which we’ll use later to generate the ClickHouse table definition. After this completes, S3 will contain a set of Parquet files of roughly similar size. Verify the counts match: — Snowflake original table SELECT COUNT(*) FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.ORDERS; — Count from the S3 stage (may take a while on a small warehouse) SELECT COUNT(*) FROM @S3.BUCKET/tpch/orders; Both should return 150 million rows. Data has left the building. ### **[16:20] — Step 2: Load from S3 into ClickHouse** **Robert:** Now we load the Parquet data into ClickHouse. ClickHouse doesn’t have COPY INTO — it uses INSERT INTO with a SELECT from the s3() table function. **First, create a working table:** CREATE TABLE orders (     O_ORDERKEY      Int128,     O_CUSTKEY       Int128,     O_ORDERSTATUS   FixedString(1),     O_TOTALPRICE    Float64,     O_ORDERDATE     Date,     O_ORDERPRIORITY String,     O_CLERK         String,     O_SHIPPRIORITY  Int128,     O_COMMENT       String ) ENGINE = MergeTree() ORDER BY tuple(); This is not an optimized table — it uses oversized data types (Int128), no compression codecs, no partitioning, no meaningful ORDER BY. That’s intentional for the first pass. We’ll optimize after we’ve inspected the data inside ClickHouse. **Load data from S3:** INSERT INTO orders SELECT * FROM s3(     ‘s3://your-migration-bucket/snowflake/tpch/orders/*.parquet’,     ‘your_access_key’,     ‘your_secret_key’,     ‘Parquet’ ) SETTINGS max_insert_threads = 16; The *.parquet wildcard reads all the files in parallel. max_insert_threads = 16 (on a 32-vCPU machine) means ClickHouse spawns 16 threads to read files from S3 simultaneously, dramatically speeding up the load. ### **[20:41] — Handling Nullable Columns** **Robert:** There is a known issue when the Parquet files contain nullable fields and you’re loading them into ClickHouse non-nullable columns: ClickHouse’s s3() function can’t automatically cast nullable Parquet data into non-nullable columns and will throw an error. The workaround is to provide an explicit column schema string as a fourth parameter to the s3() function: INSERT INTO orders SELECT * FROM s3(     ‘s3://your-migration-bucket/snowflake/tpch/orders/*.parquet’,     ‘your_access_key’,     ‘your_secret_key’,     ‘Parquet’,     ‘O_ORDERKEY Int128, O_CUSTKEY Int128, …’  — explicit column types ) SETTINGS max_insert_threads = 16; This is a temporary issue that will be resolved in a future ClickHouse version. The Python helper script we’ll describe generates this column string automatically. Verify the load: SELECT count() FROM orders; — Should return 150000000 ### **[22:16] — Parquet Format Overview** **Robert:** Before moving to optimization, let me briefly explain how Parquet is organized, because understanding it helps with the Python schema generation step. Parquet is an open-source columnar format that originated in the Hadoop ecosystem but is now widely used as a portable columnar storage format. A Parquet file contains: - **Row groups**: chunks of rows (e.g., 50,000 rows at a time) - Within each row group, **column data** for all columns, stored separately and compressed - **Metadata at the end of the file**: column names, data types, whether columns are nullable, statistics. The metadata is at the end so it can be written after all the data is known. For [what’s up with Parquet performance in ClickHouse](https://altinity.com/blog/whats-up-with-parquet-performance-in-clickhouse) — including benchmarks and optimizations — see the Altinity blog. The metadata is generic enough that you can read Parquet files with PyArrow and extract column names, types, and nullability — which is exactly what the helper script does. When you have many Parquet files in S3 under a common prefix (a “dataset”), Parquet libraries can read them all together as a single logical table. The s3() wildcard function uses this property. ### **[24:09] — Python Schema Generation Script** **Robert:** I wrote a Python script that reads Parquet metadata from S3 using PyArrow and generates both the ClickHouse CREATE TABLE statement and the INSERT INTO … SELECT * FROM s3(…) load command. You can find it in the [clickhouse-sql-examples](https://github.com/Altinity/clickhouse-sql-examples) GitHub repo. Setup: # Clone the repo git clone https://github.com/Altinity/clickhouse-sql-examples # Create and activate a Python virtual environment python3 -m venv vn source vn/bin/activate pip install pyarrow boto3 # Set environment variables export AWS_DEFAULT_REGION=us-east-1 export AWS_ACCESS_KEY_ID=your_access_key export AWS_SECRET_ACCESS_KEY=your_secret_key export S3_PATH=s3://your-migration-bucket/snowflake/tpch/orders Run the script: python3 generate_ch_schema.py The script reads the Parquet metadata, maps Parquet types to ClickHouse types, and prints: - A CREATE TABLE statement with appropriate column types - An INSERT INTO … SELECT * FROM s3(…) command with your credentials and the column-type string already filled in You then copy these commands, modify them as needed, and run them in your ClickHouse client. The script doesn’t execute anything — it just generates the SQL. This is particularly useful if you have many tables or if the table is complex. ### **[26:22] — Step 3: Optimize the Imported Table** **Robert:** If you’re coming from Snowflake you’ll notice it makes many choices for you automatically. That’s one of Snowflake’s strengths. In ClickHouse you have incredible power to optimize performance, but you need to make those choices explicitly. Let’s create an optimized version of the table: CREATE TABLE orders_optimized (     O_ORDERKEY      Int64                    CODEC(ZSTD),     O_CUSTKEY       Int64                    CODEC(ZSTD),     O_ORDERSTATUS   FixedString(1),     O_TOTALPRICE    Float64,     O_ORDERDATE     Date,     O_ORDERPRIORITY LowCardinality(String),     O_CLERK         LowCardinality(String),     O_SHIPPRIORITY  Int32,     O_COMMENT       String                   CODEC(ZSTD) ) ENGINE = MergeTree() PARTITION BY toYYYYMM(O_ORDERDATE) ORDER BY (O_ORDERDATE, O_CUSTKEY); Key changes: - **Int128 → Int64**: the original schema used 128-bit integers as a safe default. For a table with 150M orders, 64-bit is almost certainly sufficient. - **ZSTD codec**: more aggressive compression than the default LZ4 on key numeric and string columns. - **LowCardinality**: for low-cardinality string columns like order priority and clerk, this applies dictionary encoding, dramatically reducing storage and improving query speed. - **PARTITION BY toYYYYMM(O_ORDERDATE)**: breaks the table into monthly partitions, standard practice for time-series data spanning multiple years. - **ORDER BY (O_ORDERDATE, O_CUSTKEY)**: sorts the data by date and then customer, building a sparse primary key index that speeds up queries filtering on those columns. Load the optimized table from the working table: INSERT INTO orders_optimized SELECT * FROM orders SETTINGS max_insert_threads = 16; Then force a full merge: OPTIMIZE TABLE orders_optimized FINAL; ClickHouse merges parts in the background automatically over time, but OPTIMIZE TABLE FINAL forces it immediately — useful when you want to check performance without waiting. ### **[29:54] — Before/After Size Comparison** **Robert:** Check the results using system.tables: SELECT     name,     formatReadableSize(total_bytes) AS size FROM system.tables WHERE name LIKE ‘orders%’ ORDER BY name; Results: | **Table** | **Size** | | --- | --- | | orders (unoptimized) | 6.6 GB | | orders_optimized | **4.37 GB** | The optimized table is 34% smaller than the unoptimized one. Remarkably, it’s within about 2% of the size Snowflake reports for the same table — which means Snowflake’s automatic optimization and ClickHouse’s explicit optimization land at nearly the same place. ### **[30:54] — Live Demo: Column-Level Compression Analysis** **Robert:** Let me show you how to analyze compression at the column level using system.columns: SELECT     name,     formatReadableSize(data_uncompressed_bytes)  AS uncompressed,     formatReadableSize(data_compressed_bytes)    AS compressed,     round(data_compressed_bytes / data_uncompressed_bytes, 3) AS ratio FROM system.columns WHERE table IN (‘orders’, ‘orders_optimized’) ORDER BY table, data_uncompressed_bytes DESC; This shows the uncompressed size, compressed size, and compression ratio for every column in both tables. You’ll see things like: - O_ORDERKEY in the unoptimized table: ~2 GB uncompressed → ~1 GB compressed (ratio 0.5) - After switching to Int64 + ZSTD, the same column compresses significantly further - O_COMMENT (a String column) benefits greatly from ZSTD This kind of column-level analysis is how you iterate toward an optimal schema. You run it, look at which columns have poor compression ratios, try different codecs, and reload a subset of data to compare. ### **[37:00] — Q&A: Why Not Import Directly into the Optimized Table?** **Robert:** Question from the audience: why not just load directly into the optimized table instead of loading into an unoptimized table first? The reason is that optimizing a ClickHouse schema is usually an iterative process. You load the data into a working table, run the column-level compression analysis, try different data types and codecs, create new optimized tables, reload from the working table, compare sizes — and repeat. Because you’re doing this inside ClickHouse, you can reload very quickly using INSERT INTO … SELECT * FROM orders SETTINGS max_insert_threads = 16. That’s much faster than re-reading from S3 every time you iterate. If you already know what your optimal schema looks like (because you’ve done this before or you’re migrating from a system you understand well), then yes, you can absolutely generate the optimized CREATE TABLE and load directly from S3 in one step. ### **[38:03] — Summary and Resources** **Robert:** The process for moving data from Snowflake to ClickHouse in three steps: - **In Snowflake**: create an S3 bucket, create a PARQUET stage, COPY INTO your tables with HEADER = TRUE. - **In ClickHouse**: use the Python script in[ clickhouse-sql-examples](https://github.com/Altinity/clickhouse-sql-examples) to generate CREATE TABLE and INSERT INTO … SELECT * FROM s3(…) commands, then load with max_insert_threads. - **Optimize**: analyze column sizes with system.columns, choose appropriate data types, add ZSTD or LowCardinality codecs, define PARTITION BY and ORDER BY, reload from the working table, and OPTIMIZE TABLE FINAL to verify performance. For full documentation and runnable SQL, see the [migrating data from Snowflake to ClickHouse using S3 and Parquet](https://altinity.com/blog/migrating-data-from-snowflake-to-clickhouse-using-s3-and-parquet) blog article and the GitHub repo. For [what’s up with Parquet performance in ClickHouse](https://altinity.com/blog/whats-up-with-parquet-performance-in-clickhouse) including benchmarks on reading Parquet directly from S3, see the Altinity blog. If you’d like to try this out, [Altinity.Cloud](https://altinity.com/managed-clickhouse/) provides a managed ClickHouse platform with a free trial. We offer complete software and support for ClickHouse — everything needed to get analytic applications running on ClickHouse. Contact us at altinity.com. ## **FAQ** **Why use S3 and Parquet to migrate data from Snowflake to ClickHouse instead of direct database connection?** Direct Snowflake-to-ClickHouse migration via ODBC or native connector is slow because it relies on a single network connection and is subject to bandwidth and latency between the two systems. For bulk migration of millions or hundreds of millions of rows, writing to S3 as Parquet files and reading them in parallel is dramatically faster. ClickHouse’s s3() table function supports wildcards to read all files in a prefix at once, and the max_insert_threads setting parallelizes the reads across as many CPU threads as you allocate. **What is Parquet and why is it a good intermediary format for this migration?** Parquet is an open-source columnar file format that stores data in row groups with per-column compressed data and file-level metadata (column names, data types, nullability) appended at the end. It’s widely supported by ClickHouse, Snowflake, Python (via PyArrow), Apache Spark, and many other tools. The embedded metadata makes it possible to automatically generate a ClickHouse CREATE TABLE statement from the Parquet files without manually inspecting the source schema. It’s also compact and compressed, making it efficient for bulk transfer via S3. **What does the HEADER = TRUE setting do in the Snowflake COPY INTO command?** HEADER = TRUE tells Snowflake to include column names in the Parquet file metadata when writing to the stage. Without it, the Parquet files would contain data but not the column name information, making it impossible to automatically map data to ClickHouse columns. With it, the PyArrow helper script can read the Parquet metadata and generate a correctly named CREATE TABLE statement. **Why is there a known issue with nullable columns in Parquet when loading into ClickHouse?** Parquet’s type system marks every field as either required (not null) or optional (nullable). When ClickHouse’s s3() function reads a Parquet file, it needs to know whether to expect nullable data. If the source column is Parquet-optional (nullable) but the destination ClickHouse column is non-nullable, ClickHouse throws an error because it doesn’t know how to handle a NULL that arrives for a non-nullable column. The workaround is to provide an explicit column schema string as a parameter to the s3() function. The Python helper script generates this string automatically from the Parquet metadata. **What ClickHouse schema optimizations improve performance after loading from Parquet?** The most impactful optimizations are: using appropriately-sized integer types (Int64 rather than Int128 where the data range fits); applying the ZSTD compression codec to columns with poor default LZ4 compression ratios; applying LowCardinality encoding to low-cardinality string columns for dictionary compression; defining PARTITION BY on a time column (typically by month) to break the table into manageable parts; and defining ORDER BY on columns you filter on frequently, which creates a sparse primary key index. These choices are what Snowflake makes automatically, but ClickHouse gives you full control, allowing you to tune for your specific query patterns. --- © 2022 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.