herzock avatar image
herzock asked

Snowflake connect fetching data to table is pretty slow. Am i doing this right?


i connected my playfab account to Snowflake to do data analysis.

I am running this query:

create or replace table LevelProgressTable as 
    to_timestamp(p:Timestamp) as exact_ts,
    to_varchar(p:appversion) as appversion,
    to_varchar(p:levelName) as levelName,
    TO_NUMBER(p:levelId) as levelId,
    to_varchar(p:spirit) as spirit,
    TO_NUMBER(p:spiritLevel) as spiritLevel,
    TO_NUMBER(p:damLevel) as damLevel,
    TO_BOOLEAN(p:won) as won,
    TO_NUMBER(p:score) as score,
    TO_NUMBER(p:movesLeft) as movesLeft,
    TO_NUMBER(p:triesNeeded) as triesNeeded,
    TO_BOOLEAN(p:extraMovesBought) as extraMovesBought,
    TO_NUMBER(p:playThroughs) as playThroughs
    where event_namespace = 'title.XXXX' and event_name = 'player_completed_level'
    and ts >= dateadd(day, -1, current_date)
    and title_id = 'XXXX'

The query runs over 8 minutes although i only have 23 events of that type. I am using an S Warehouse.

Is it normal that it takes that long? Will it be even longer if i have more events? Or am i using the wrong keys maybe?


Player Datadata
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

brendan avatar image
brendan answered

One thing to know is that creation of tables will always take more time than a simple Select query. Can you try it as a Select and see how long it takes? If that's more than a few seconds, can you provide the Title ID, so that we can have a look?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

herzock avatar image herzock commented ·

Hello, thank you for your answer. The titleid is: 7051.

The select took 9:23 minutes.

It seems it parses way more than data necessary for some reason.

0 Likes 0 ·
brendan avatar image brendan herzock commented ·

Hm. So I just tried the query as simply:

select * from PLAYFAB_ARCHIVE_SHARED where TITLE_ID = '7051' and EVENT_NAMESPACE = 'title.7051' and EVENT_NAME = 'player_completed_level' AND ts >= dateadd(day, -1, current_date)

And that only took just over a minute to run, which is about what I'd expect, given the size of the overall database. What size warehouse are you using, though? I'm using Large. A smaller warehouse will take longer.

0 Likes 0 ·
herzock avatar image herzock commented ·

Hmm okay, today the exact same query took only 2.49 minutes.

Which is ok for an S warehouse i guess. Maybe last week Snowflake was slow or something. The saving of worksheets was also broken last week.

I hope the query times will be more consistent in the future in snowflake otherwise it will be hard to optimize performance. :D

Thank you very much for your answer and the test query!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.