Snowflake SQL translation guide

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/onboard/02_migrate/01_migration_guides/04_snowflake/03_sql_translation_reference.md)

## Data types[​](#data-types "Direct link to Data types")

### Numerics[​](#numerics "Direct link to 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[​](#strings "Direct link to 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](/docs/sql-reference/data-types/string) with a `String`
type (no length specification required), deferring encoding to the user, with
[query time functions](/docs/sql-reference/functions/string-functions#lengthUTF8)
available for different encodings. We refer the reader to ["Opaque data argument"](https://utf8everywhere.org/#cookie)
for the motivation as to why. The ClickHouse `String` is thus more comparable
to the Snowflake Binary type in its implementation. Both [Snowflake](https://docs.snowflake.com/en/sql-reference/collation)
and [ClickHouse](/docs/sql-reference/statements/select/order-by#collation-support)
support “collation”, allowing users to override how strings are sorted and compared.

### Semi-structured types[​](#semi-structured-data "Direct link to Semi-structured types")

Snowflake supports the `VARIANT`, `OBJECT` and `ARRAY` types for semi-structured
data.

ClickHouse offers the equivalent [`Variant`](/docs/sql-reference/data-types/variant),
`Object` (now deprecated in favor of the native `JSON` type) and [`Array`](/docs/sql-reference/data-types/array)
types. Additionally, ClickHouse has the [`JSON`](/docs/sql-reference/data-types/newjson)
type which replaces the now deprecated `Object('json')` type and is particularly
performant and storage efficient in [comparison to other native JSON types](https://jsonbench.com/).

ClickHouse also supports named [`Tuple`s](/docs/sql-reference/data-types/tuple) and arrays of Tuples
via the [`Nested`](/docs/sql-reference/data-types/nested-data-structures/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](https://docs.snowflake.com/en/sql-reference/data-types-semistructured#characteristics-of-an-object).
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](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")