This page will help you  troubleshoot possible differences whilst upgrading from VERTICA to SNOWFLAKE

Please be aware Snowflake columns are all CAPITALIZED

These query suggestions are for querying in the Data Mining Tool or Direct Access. Premium Data Access querying is slightly different and a specific section for this can be found at the bottom of this page.

invalid identifier ‘SYSDATE’

Snowflake requires function brackets when using sysdate:

Vertica
select sysdate from events
Snowflake
select sysdate() from events

Unknown function ISNULL

Snowflake uses ifnull instead of isnull:

Vertica
select isnull(eventName,'') from events_live
Snowflake
select ifnull(eventName,'') from events_live

Unknown function NOW

Now function does not exist in Snowflake use CURRENT_TIMESTAMP()

Vertica
select now() from events_live
Snowflake
select CURRENT_TIMESTAMP() from events_live

Invalid result

Values of param = null will not give the same results as param is null

Vertica
select 
	CASE WHEN platform = NULL then
		'no platform'
	end as "missingPlatform",
		CASE WHEN platform != null then
			platform
	end as "Platform Name" 
 from events_live
Snowflake
select 
	CASE WHEN platform IS NULL then
		'no platform'
	end as "missingPlatform",
		CASE WHEN platform is not  null then
			platform
	end as "Platform Name" 
 from events_live

Invalid identifier ‘COUNT’

When using count you need to specify a column name if you plan to use the count further in your query

Vertica
Select Count(UserID) from events_dev
Snowflake
Select	Count(UserID) as count from events_dev

Invalid argument types for function ‘-‘: (TIMESTAMP_NTZ(9), TIMESTAMP_NTZ(9))

You cannot minus two date timestamps in Snowflake instead use DATEDIFF

Vertica
select COLLECTINSERTEDTIMESTAMP-EVENTTIMESTAMP from events
Snowflake
select DATEDIFF('DAY',Current_Date(),eventdate) as date from events_dev

TRIM different syntax  – syntax error line 3 at position 34 unexpected ‘)’.

Trim syntax differs from Vertica to snowflake

Vertica
SELECT TRIM('game' from EventName) FROM events_dev
Snowflake
SELECT TRIM(EventName,'game') FROM EVENTS

Unsupported data type ‘MONEY’.

MONEY not a supported Snowflake function

Vertica
SELECT '2.99'::money
Snowflake
SELECT '2.99'::float

Invalid argument types for function ‘+’: (TIMESTAMP_LTZ(9), NUMBER(1,0))

Use TIMESTAMPADD to add days months years to a timestamp

Vertica
select current_timestamp() + 1 from events_dev
Snowflake
select  TIMESTAMPADD('d',1,current_timestamp()) from events_dev

ERROR: Unsupported feature ‘^’.

^2 is not supported in snowflake instead use SQUARE(2)

Vertica
select 5^2
Snowflake
select square(5)

Syntax error at or near “INTERVAL”

Using interval in snowflake requires the following text to be in single quotations

Vertica
select interval + 1 year from events_dev
Snowflake
select interval + '1 year' from events_dev

ERROR: Timestamp ‘today’ is not recognized

Today is not a valid snowflake parameter instead use current_date() or sysdate()

Vertica
select  TIMESTAMP 'today'
Snowflake
select  current_date() //no timestamp
//OR
select  sysdate() //timestamp

ERROR: Division by zero

Vertica allowed some division that would not pass on other databases. Use DIV0 to safely divide by a potential 0 value

Vertica
SELECT 10/0
Snowflake
select div0(10, 0)

Unknown function BOOL_OR

the syntax between both functions are different. Remove the underscore for snowflake

Vertica
SELECT BOOL_OR(1,1)
Snowflake
SELECT BOOLOR(1,1)

Unsupported feature ^

The ^ shortcut for the Power function is not supported in Snowflake

Vertica
SELECT 3^2
Snowflake
SELECT POWER(3,2)

Timeseries Clause

The Timeseries Clause is not supported in Snowflake

Vertica
SELECT
slice_time
FROM
(SELECT NOW()-INTERVAL '6 hours' AS date_range
UNION
SELECT NOW()
) AS ts timeseries slice_time AS '1 minutes' OVER(ORDER BY date_range)
Snowflake
SELECT DISTINCT TIME_SLICE(EVENTTIMESTAMP, 1, 'MINUTE') AS slice_time
FROM events
WHERE EVENTTIMESTAMP BETWEEN CURRENT_TIMESTAMP - INTERVAL '6 hour' AND CURRENT_TIMESTAMP
)

Snowflake (Data Mining) / Snowflake (Premium Data Access) query differences

Event JSON

Many events (userID, sessionID, eventDate, etc.) are nested within a single event named EVENT_JSON. To query this, you’ll have to use a colon between EVENT_JSON and the parameter you’d wish to query.

Snowflake (Data Mining)
SELECT
  EVENTTIMESTAMP,
  EVENTDATE,
  SESSIONID,
  USERID,
  PLATFORM
FROM
  EVENTS
WHERE
  EVENTTIMESTAMP > CURRENT_DATE - 1
Snowflake (Premium Data Access)
SELECT
EVENT_TIMESTAMP,
EVENT_JSON,
EVENT_JSON:eventDate,
EVENT_JSON:sessionID,
EVENT_JSON:userID,
EVENT_JSON:platform
FROM
"DELTADNA"."SHARES".ACCOUNT_EVENTS
WHERE 
EVENT_TIMESTAMP > CURRENT_DATE - 1

The parameters within the EVENT_JSON are case sensitive, so if the parameter  is called “eventDate”, the SQL must read:

EVENT_JSON:eventDate

AND NOT:

EVENT_JSON:EVENTDATE”