Migrating from Snowflake to ClickHouse - ClickHouse Documentation

Data types

Numerics

Users moving data between ClickHouse and Snowflake will immediately notice that ClickHouse offers more granular precision concerning declaring numerics. For example, Snowflake offers the type Number for numerics. This requires the user to specify a precision (total number of digits) and scale (digits to the right of the decimal place) up to a total of 38. Integer declarations are synonymous with Number, and simply define a fixed precision and scale where the range is the same. This convenience is possible as modifying the precision (scale is 0 for integers) doesn’t impact the size of data on disk in Snowflake - the minimal required bytes are used for a numeric range at write time at a micro partition level. The scale does, however, impact storage space and is offset with compression. A Float64 type offers a wider range of values with a loss of precision. Contrast this with ClickHouse, which offers multiple signed and unsigned precision for floats and integers. With these, you can be explicit about the precision required for integers to optimize storage and memory overhead. A Decimal type, equivalent to Snowflake’s Number type, also offers twice the precision and scale at 76 digits. In addition to a similar Float64 value, ClickHouse also provides a Float32 for when precision is less critical and compression paramount.

Strings

ClickHouse and Snowflake take contrasting approaches to the storage of string data. The VARCHAR in Snowflake holds Unicode characters in UTF-8, allowing the user to specify a maximum length. This length has no impact on storage or performance, with the minimum number of bytes always used to store a string, and rather provides only constraints useful for downstream tooling. Other types, such as Text and NChar, are simply aliases for this type. ClickHouse conversely stores all string data as raw bytes with a String type (no length specification required), deferring encoding to the user, with query time functions available for different encodings. We refer the reader to “Opaque data argument” for the motivation as to why. The ClickHouse String is thus more comparable to the Snowflake Binary type in its implementation. Both Snowflake and ClickHouse support “collation”, allowing users to override how strings are sorted and compared.

Semi-structured types

Snowflake supports the VARIANT, OBJECT and ARRAY types for semi-structured data. ClickHouse offers the equivalent Variant, Object (now deprecated in favor of the native JSON type) and Array types. Additionally, ClickHouse has the JSON type which replaces the now deprecated Object('json') type and is particularly performant and storage efficient in comparison to other native JSON types. ClickHouse also supports named Tuples and arrays of Tuples via the Nested type, allowing users to explicitly map nested structures. This allows codecs and type optimizations to be applied throughout the hierarchy, unlike Snowflake, which requires the user to use the OBJECT, VARIANT, and ARRAY types for the outer object and doesn’t allow explicit internal typing. This internal typing also simplifies queries on nested numerics in ClickHouse, which don’t need to be cast and can be used in index definitions. In ClickHouse, codecs and optimized types can also be applied to substructures. This provides an added benefit that compression with nested structures remains excellent, and comparable, to flattened data. In contrast, as a result of the inability to apply specific types to substructures, Snowflake recommends flattening data to achieve optimal compression. Snowflake also imposes size restrictions for these data types.

Type reference

Snowflake	ClickHouse	Note
`NUMBER`	`Decimal`	ClickHouse supports twice the precision and scale than Snowflake - 76 digits vs. 38.
`FLOAT`, `FLOAT4`, `FLOAT8`	`Float32`, `Float64`	All floats in Snowflake are 64 bit.
`VARCHAR`	`String`
`BINARY`	`String`
`BOOLEAN`	`Bool`
`DATE`	`Date`, `Date32`	`DATE` in Snowflake offers a wider date range than ClickHouse e.g. min for `Date32` is `1900-01-01` and `Date` `1970-01-01`. `Date` in ClickHouse provides more cost efficient (two byte) storage.
`TIME(N)`	No direct equivalent but can be represented by `DateTime` and `DateTime64(N)`.	`DateTime64` uses the same concepts of precision.
`TIMESTAMP` - `TIMESTAMP_LTZ`, `TIMESTAMP_NTZ`, `TIMESTAMP_TZ`	`DateTime` and `DateTime64`	`DateTime` and `DateTime64` can optionally have a TZ parameter defined for the column. If not present, the server’s timezone is used. Additionally a `--use_client_time_zone` parameter is available for the client.
`VARIANT`	`JSON`, `Tuple`, `Nested`	`JSON` type is experimental in ClickHouse. This type infers the column types at insert time. `Tuple`, `Nested` and `Array` can also be used to build explicitly type structures as an alternative.
`OBJECT`	`Tuple`, `Map`, `JSON`	Both `OBJECT` and `Map` are analogous to `JSON` type in ClickHouse where the keys are a `String`. ClickHouse requires the value to be consistent and strongly typed whereas Snowflake uses `VARIANT`. This means the values of different keys can be a different type. If this is required in ClickHouse, explicitly define the hierarchy using `Tuple` or rely on `JSON` type.
`ARRAY`	`Array`, `Nested`	`ARRAY` in Snowflake uses `VARIANT` for the elements - a super type. Conversely these are strongly typed in ClickHouse.
`GEOGRAPHY`	`Point`, `Ring`, `Polygon`, `MultiPolygon`	Snowflake imposes a coordinate system (WGS 84) while ClickHouse applies at query time.
`GEOMETRY`	`Point`, `Ring`, `Polygon`, `MultiPolygon`

ClickHouse Type	Description
`IPv4` and `IPv6`	IP-specific types, potentially allowing more efficient storage than Snowflake.
`FixedString`	Allows a fixed length of bytes to be used, which is useful for hashes.
`LowCardinality`	Allows any type to be dictionary encoded. Useful for when the cardinality is expected to be < 100k.
`Enum`	Allows efficient encoding of named values in either 8 or 16-bit ranges.
`UUID`	For efficient storage of UUIDs.
`Array(Float32)`	Vectors can be represented as an Array of Float32 with supported distance functions.

Finally, ClickHouse offers the unique ability to store the intermediate state of aggregate functions. This state is implementation-specific, but allows the result of an aggregation to be stored and later queried (with corresponding merge functions). Typically, this feature is used via a materialized view and, as demonstrated below, offers the ability to improve performance of specific queries with minimal storage cost by storing the incremental result of queries over inserted data (more details here).

​Data types

​Numerics

​Strings

​Semi-structured types

​Type reference

Data types

Numerics

Strings

Semi-structured types

Type reference