Snowflake SQL translation guide

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](https://docs.snowflake.com/en/user-guide/semistructured-considerations#storing-semi-structured-data-in-a-variant-column-vs-flattening-the-nested-structure).
Snowflake also [imposes size restrictions](https://docs.snowflake.com/en/user-guide/semistructured-considerations#data-size-limitations)
for these data types.

### Type reference[​](#type-reference "Direct link to Type reference")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Snowflake ClickHouse Note|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`NUMBER`](https://docs.snowflake.com/en/sql-reference/data-types-numeric) [`Decimal`](/docs/sql-reference/data-types/decimal) ClickHouse supports twice the precision and scale than Snowflake - 76 digits vs. 38.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`FLOAT`, `FLOAT4`, `FLOAT8`](https://docs.snowflake.com/en/sql-reference/data-types-numeric#data-types-for-floating-point-numbers) [`Float32`, `Float64`](/docs/sql-reference/data-types/float) All floats in Snowflake are 64 bit.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`VARCHAR`](https://docs.snowflake.com/en/sql-reference/data-types-text#varchar) [`String`](/docs/sql-reference/data-types/string) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`BINARY`](https://docs.snowflake.com/en/sql-reference/data-types-text#binary) [`String`](/docs/sql-reference/data-types/string) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`BOOLEAN`](https://docs.snowflake.com/en/sql-reference/data-types-logical) [`Bool`](/docs/sql-reference/data-types/boolean) |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`DATE`](https://docs.snowflake.com/en/sql-reference/data-types-datetime#date) [`Date`](/docs/sql-reference/data-types/date), [`Date32`](/docs/sql-reference/data-types/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)`](https://docs.snowflake.com/en/sql-reference/data-types-datetime#time) No direct equivalent but can be represented by [`DateTime`](/docs/sql-reference/data-types/datetime) and [`DateTime64(N)`](/docs/sql-reference/data-types/datetime64). `DateTime64` uses the same concepts of precision.| [`TIMESTAMP`](https://docs.snowflake.com/en/sql-reference/data-types-datetime#timestamp) - [`TIMESTAMP_LTZ`](https://docs.snowflake.com/en/sql-reference/data-types-datetime#timestamp-ltz-timestamp-ntz-timestamp-tz), [`TIMESTAMP_NTZ`](https://docs.snowflake.com/en/sql-reference/data-types-datetime#timestamp-ltz-timestamp-ntz-timestamp-tz), [`TIMESTAMP_TZ`](https://docs.snowflake.com/en/sql-reference/data-types-datetime#timestamp-ltz-timestamp-ntz-timestamp-tz) [`DateTime`](/docs/sql-reference/data-types/datetime) and [`DateTime64`](/docs/sql-reference/data-types/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`](https://docs.snowflake.com/en/sql-reference/data-types-semistructured#variant) [`JSON`, `Tuple`, `Nested`](/docs/interfaces/formats) `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`](https://docs.snowflake.com/en/sql-reference/data-types-semistructured#object) [`Tuple`, `Map`, `JSON`](/docs/interfaces/formats) 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`](https://docs.snowflake.com/en/sql-reference/data-types-semistructured#array) [`Array`](/docs/sql-reference/data-types/array), [`Nested`](/docs/sql-reference/data-types/nested-data-structures/nested) `ARRAY` in Snowflake uses `VARIANT` for the elements - a super type. Conversely these are strongly typed in ClickHouse.| [`GEOGRAPHY`](https://docs.snowflake.com/en/sql-reference/data-types-geospatial#geography-data-type) [`Point`, `Ring`, `Polygon`, `MultiPolygon`](/docs/sql-reference/data-types/geo) Snowflake imposes a coordinate system (WGS 84) while ClickHouse applies at query time.|  |  |  | | --- | --- | --- | | [`GEOMETRY`](https://docs.snowflake.com/en/sql-reference/data-types-geospatial#geometry-data-type) [`Point`, `Ring`, `Polygon`, `MultiPolygon`](/docs/sql-reference/data-types/geo)  | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 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. | | | | | | | | | | | | | |