Known differences

A known difference is Snowflake behaviour that DuckDB can’t match exactly. Each entry says what differs, when relying on it gives Snowflake’s result and when it doesn’t, and whether the defaults refuse or allow it. To change that, or to replace a function with your own DuckDB SQL, see Refuse, allow or replace. sqlquack explain <name> prints one entry with its full details.

Can return a different value

Where one of these doesn’t hold, the converted SQL returns a value that differs from Snowflake’s, with no error. Read its entry before you allow it.

Known difference	Name	Default
A FLOAT in a VARIANT column can lose its type between models	variant_chain_type	refused
VARIANT columns of source tables are trusted to hold plain JSON	source_variant_json	refused
Your Python functions are trusted to run locally as in Snowflake	project_udf	refused
Your replacement for a Snowflake function is trusted to match it	user_override	allowed
TIMESTAMP_TZ source columns are trusted to hold their declared offset	declared_offset	allowed
Results that depend on the session time zone need UTC	session_timezone_utc	allowed
HASH gives a different value in DuckDB	hash	refused
RANDOM gives different values in DuckDB	random	allowed
UUID_STRING gives different identifiers in DuckDB	uuid	allowed
SEQ1, SEQ2, SEQ4 and SEQ8 give different values in DuckDB	sequences	refused
CURRENT_DATE and CURRENT_TIMESTAMP read DuckDB’s clock	current_time	allowed

variant_chain_type

A FLOAT in a VARIANT column can lose its type between models. Refused by default.

• What differs: A VARIANT column built from a FLOAT is written as JSON text, so a model reading it back loses the FLOAT’s type. A check meant to catch this has no test showing it works.
• Fine when: The column is stored as a DuckDB VARIANT, which keeps the type.
• Wrong when: A model reads a FLOAT back from such a column stored as JSON text: it can get a different value, with no error.

source_variant_json

VARIANT columns of source tables are trusted to hold plain JSON. Refused by default.

• What differs: Snowflake keeps a date, time, timestamp or binary value inside a VARIANT as its own type, but DuckDB’s JSON holds only its text. Comparisons of VARIANT values from a source table rely on them holding plain JSON.
• Fine when: The source column holds values loaded from JSON.
• Wrong when: Something outside the project wrote typed values into the column: they compare equal to their text, with no error.

project_udf

Your Python functions are trusted to run locally as in Snowflake. Refused by default.

• What differs: A project Python function runs its handler in a local Python process. SQLQuack cannot tell whether it returns what it returns in Snowflake.
• Fine when: The handler gives the same result for the same input, whatever the Python version, packages or environment.
• Wrong when: The handler’s result depends on its Python version, packages or environment: it returns a different value, with no error.

user_override

Your replacement for a Snowflake function is trusted to match it. Allowed by default.

• What differs: A replacement swaps a Snowflake function for your own DuckDB SQL. SQLQuack cannot tell whether it gives what the function it replaces gives.
• Fine when: Your SQL gives the same result as the Snowflake function for every input.
• Wrong when: Your SQL differs for some input: those calls return a different value, with no error.

declared_offset

TIMESTAMP_TZ source columns are trusted to hold their declared offset. Allowed by default.

• What differs: DuckDB does not keep a TIMESTAMP_TZ value’s own offset. Where the schema declares a column’s offset as +00:00, its wall time, date and parts are worked out for that offset.
• Fine when: Every value in the column has the +00:00 offset.
• Wrong when: A value has another offset: its wall time, date or parts come out different, with no error.

session_timezone_utc

Results that depend on the session time zone need UTC. Allowed by default.

• What differs: Converted SQL matches Snowflake only when both sessions use UTC. Some results depend on the session time zone.
• Fine when: Both the Snowflake and the DuckDB session use UTC.
• Wrong when: Either session uses another time zone: such results come out different, with no error.

hash

HASH gives a different value in DuckDB. Refused by default.

• What differs: DuckDB computes its own hash, never Snowflake’s. Equal inputs still get equal hashes within one run.
• Fine when: Joins, grouping, removing duplicates and spotting changed rows by hash.
• Wrong when: Storing, comparing or sorting by the hash value itself: it differs, with no error.

random

RANDOM gives different values in DuckDB. Allowed by default.

• What differs: Random values differ from Snowflake’s by nature.
• Fine when: Anything that needs only random values.
• Wrong when: Anything expecting Snowflake’s random values: they differ, with no error.

uuid

UUID_STRING gives different identifiers in DuckDB. Allowed by default.

• What differs: Generated identifiers differ from Snowflake’s, but each stays unique within one run.
• Fine when: Anything that needs only unique identifiers.
• Wrong when: Anything expecting the identifiers Snowflake generated: they differ, with no error.

sequences

SEQ1, SEQ2, SEQ4 and SEQ8 give different values in DuckDB. Refused by default.

• What differs: Sequence values and gaps differ from Snowflake’s, but values stay unique within one run.
• Fine when: Anything that needs only unique values.
• Wrong when: Anything expecting Snowflake’s sequence values or gaps: they differ, with no error.

current_time

CURRENT_DATE and CURRENT_TIMESTAMP read DuckDB’s clock. Allowed by default.

• What differs: CURRENT_DATE and CURRENT_TIMESTAMP read the clock, so they differ between Snowflake and DuckDB and between runs.
• Fine when: The result does not depend on the moment, such as a comparison with a fixed past date.
• Wrong when: The result depends on the moment it runs: it differs, with no error.

Raise an error instead

Where one of these doesn’t hold, DuckDB raises an error instead of returning a value, never a different value.

Numbers

numeric_range

Numbers beyond 38 digits raise an error in DuckDB. Allowed by default.

• What differs: Inside a calculation Snowflake can hold numbers wider than 38 digits, but DuckDB cannot.
• Fine when: Totals and working values stay within 38 digits.
• Wrong when: A value goes beyond 38 digits.

text_number_scale

Text compared with a NUMBER must fit the NUMBER’s scale. Allowed by default.

• What differs: Snowflake reads text compared with a NUMBER as a number. How it rounds text with more fraction digits or an exponent has not been checked against Snowflake.
• Fine when: The text holds a number exact at the NUMBER’s scale.
• Wrong when: Other numeric text.

float_decimal_digits

Rounding a FLOAT to a NUMBER works only for some scales and sizes. Allowed by default.

• What differs: Snowflake rounds a FLOAT’s exact double value half away from zero when it becomes a NUMBER. This has been checked only for scales 2, 6 and 8, with up to ten digits once scaled.
• Fine when: A NUMBER of scale 2, 6 or 8, with up to ten digits once scaled.
• Wrong when: A value with more significant digits.

Text

text_similarity

JAROWINKLER_SIMILARITY raises an error for some text. Allowed by default.

• What differs: Snowflake’s JAROWINKLER_SIMILARITY ignores case and counts characters. How it folds letters outside ASCII has not been checked against Snowflake.
• Fine when: Text whose cased letters are all ASCII.
• Wrong when: Text holding a cased letter outside ASCII, such as É.

case_folding

ILIKE raises an error for some letters outside ASCII. Allowed by default.

• What differs: Snowflake’s ILIKE matches letters by their lower case, with a few exceptions such as the Kelvin sign. Letters whose case folding differs from their lower case have not been checked against Snowflake.
• Fine when: Text without such letters.
• Wrong when: Text holding such a letter.

regexp_pattern_syntax

Regular expressions from a column can raise an error. Allowed by default.

• What differs: A pattern from a column cannot be checked before the query runs. Only common regular expression syntax has been checked to behave the same in Snowflake and DuckDB.
• Fine when: Column patterns use only the checked syntax.
• Wrong when: A column pattern uses other syntax, such as escapes or counted repetition.

initcap_text

INITCAP raises an error for text with unusual characters. Allowed by default.

• What differs: Snowflake’s INITCAP capitalises after whitespace and many punctuation characters. Only letters, digits, spaces, underscores, hyphens and apostrophes have been checked against Snowflake.
• Fine when: Text holds only letters, digits, spaces, underscores, hyphens and apostrophes.
• Wrong when: Text with any other character.

Dates and times

time_zone_spelling

Time zone names given per row must be spelled as Snowflake accepts them. Allowed by default.

• What differs: Snowflake reads a time zone name exactly, case included, and rejects spellings such as ‘utc’ that DuckDB accepts.
• Fine when: Each row’s zone name is one Snowflake has been checked to accept.
• Wrong when: A row’s zone name is spelled another way: DuckDB raises an error where Snowflake may return a value, never a different value.

timestamp_tz_offset

Zoned timestamp text with a non-UTC offset raises an error. Allowed by default.

• What differs: Text read as a TIMESTAMP_TZ carries its offset, which DuckDB does not keep. Its wall time, date and parts would come out as UTC’s.
• Fine when: The text’s offset is +00:00, Z or absent.
• Wrong when: Any other offset.

date_text_spelling

Dates and times in unusual spellings raise an error. Allowed by default.

• What differs: Snowflake reads more date and time spellings than have been checked against Snowflake. Text in an unchecked spelling raises an error rather than guess.
• Fine when: Text in a spelling checked against Snowflake.
• Wrong when: Any other spelling: DuckDB raises an error where Snowflake may return a value, never a different value.

timestamp_range

Timestamps outside 1677 to 2262 raise an error in DuckDB. Allowed by default.

• What differs: DuckDB’s nanosecond timestamps span 1677-09-21 to 2262-04-11, but Snowflake’s go beyond.
• Fine when: Timestamps stay within 1677-09-21 to 2262-04-11.
• Wrong when: A value outside that range.

timestamp_tz_submicro

Zoned timestamps finer than a microsecond raise an error in DuckDB. Allowed by default.

• What differs: DuckDB’s zoned timestamps hold microseconds, where Snowflake’s hold nanoseconds.
• Fine when: Values have no part below a microsecond.
• Wrong when: A value has a part below a microsecond.

time_submicro

TIME values finer than a microsecond raise an error. Allowed by default.

• What differs: Snowflake keeps a TIME to nanoseconds, but only TIME values to microseconds have been checked against Snowflake.
• Fine when: TIME values have no part below a microsecond.
• Wrong when: Text read as a TIME has a part below a microsecond.

timestamp_text_rounding

Nanosecond timestamps written as text can raise an error. Allowed by default.

• What differs: A TIMESTAMP_NTZ(9) is written as text with three fraction digits, and whether Snowflake truncates or rounds the dropped digits has not been checked.
• Fine when: The dropped part is below half a millisecond, where truncating and rounding agree.
• Wrong when: The dropped part is half a millisecond or more.

VARIANT and JSON

array_sort_order

Sorting mixed VARIANT values can raise an error. Allowed by default.

• What differs: Snowflake sorts VARIANT values by type, then by value. How it orders two different objects, two different arrays, or numbers such as 1 and 1.0 has not been checked against Snowflake.
• Fine when: Arrays without such pairs, and MAX of VARIANT numbers or strings.
• Wrong when: An array holding such a pair, or MAX of another mix of VARIANT values.

json_fidelity

JSON that DuckDB might read differently raises an error. Allowed by default.

• What differs: DuckDB reads JSON numbers, spellings and repeated keys differently from Snowflake. The converted SQL checks every document and writes objects as Snowflake does.
• Fine when: Both engines read the JSON the same way.
• Wrong when: A document DuckDB might read differently.

variant_typed_values

Dates and times inside a VARIANT are stored as Snowflake’s text. Allowed by default.

• What differs: In Snowflake a date, timestamp or time inside a VARIANT keeps its type, which DuckDB’s JSON cannot. The converted SQL stores the text Snowflake writes for it instead.
• Fine when: The text keeps every digit and offset of the value.
• Wrong when: The text would drop digits or an offset.

float_text

A FLOAT at the top of a VARIANT written as text raises an error. Allowed by default.

• What differs: Snowflake writes such a FLOAT as Java does when it works out a constant ahead of time, and as C’s %g does otherwise. The conversion cannot tell which.
• Fine when: A FLOAT written as text directly, not from the top level of a VARIANT.
• Wrong when: A FLOAT at the top level of a VARIANT cast to text.

Aggregates and ordering

percentile_interpolation

PERCENTILE_CONT and MEDIAN can raise an error instead of interpolating. Allowed by default.

• What differs: Snowflake’s PERCENTILE_CONT rounds its last digits in a way no formula reproduces, and its MEDIAN of NUMBER loses digits for wide values.
• Fine when: The percentile lands on a row or between equal values.
• Wrong when: PERCENTILE_CONT would interpolate between different values, or MEDIAN’s middle values are too wide.

mode_of_no_rows

MODE over no rows raises an error. Allowed by default.

• What differs: MODE without GROUP BY over no rows returns zero in some Snowflake plans and NULL in others.
• Fine when: The input has at least one row, or the query uses GROUP BY.
• Wrong when: MODE without GROUP BY over no rows.

float_sum_order

Sums of FLOAT values raise an error unless every order gives the same total. Allowed by default.

• What differs: Snowflake adds FLOAT values in an order DuckDB does not share, so totals can differ in the last digits.
• Fine when: Every order of addition gives the exact total.
• Wrong when: Any other FLOAT values.

extreme_key_ties

MAX_BY and MIN_BY raise an error when tied rows differ. Allowed by default.

• What differs: MAX_BY and MIN_BY pick one row among rows tied on the key, and which one Snowflake picks has not been checked.
• Fine when: Every row tied on the key holds the same value.
• Wrong when: Tied rows hold different values.

mode_ties

MODE raises an error when several values tie. Allowed by default.

• What differs: MODE returns one of the values tied for most frequent, and DuckDB may choose another than Snowflake.
• Fine when: Exactly one value is the most frequent.
• Wrong when: Several values tie.

array_agg_order

ARRAY_AGG raises an error where an undefined order could show. Allowed by default.

• What differs: ARRAY_AGG without WITHIN GROUP, or with ties on its keys, returns values in an order neither engine defines.
• Fine when: The order of the values cannot show in the result.
• Wrong when: The order of different values could show.

listagg_order

LISTAGG raises an error when tied rows differ. Allowed by default.

• What differs: LISTAGG WITHIN GROUP joins rows tied on every ORDER BY key in an order neither engine defines.
• Fine when: Tied rows hold equal values, or the value itself is the only ORDER BY key.
• Wrong when: Tied rows hold different values.

any_value_choice

ANY_VALUE raises an error when a group’s values differ. Allowed by default.

• What differs: ANY_VALUE returns some value of the group, and Snowflake and DuckDB may choose different ones.
• Fine when: Every row of the group holds the same value.
• Wrong when: A group holds different values, or NULL beside a value.

Python and project

python_value_crossing

Some Python values raise an error when crossing to or from SQL. Allowed by default.

• What differs: Values cross between SQL and a Python handler only in the shapes checked against Snowflake. SQLQuack’s runtime raises an error for any other shape.
• Fine when: Handlers take and return values in their declared types and checked shapes.
• Wrong when: A value in another shape.