dbt-labs/snowplow

Out of range exception

sphinks opened this issue · 4 comments

sphinks commented

Due to code regarding timing: https://github.com/fishtown-analytics/snowplow/blob/692938e572e3eca5cb019d2adf877f0d1b7f0147/macros/adapters/default/page_views/snowplow_page_views.sql#L272
there is a possible issue. It is casted to integer, however in source table
https://github.com/fishtown-analytics/snowplow/blob/692938e572e3eca5cb019d2adf877f0d1b7f0147/models/page_views/optional/snowplow_web_timing_context.sql#L110
There is a calculation with bigint values, so resulting value is also bigint. I get such error:

Value out of range for 4 bytes.
DETAIL:
-----------------------------------------------
error: Value out of range for 4 bytes.
code: 8001
context: Input:2311553464.
query: 1121021
location: funcs_int.hpp:97
process: query0_106_1121021 [pid=12067]
-----------------------------------------------
compiled SQL at target/compiled/snowplow/page_views/snowplow_page_views.sql`

I figured out that it is about field dom_loading_to_interactive_time_in_ms, but it can happens with any field.

I'm going to prepare PR regarding fixing casting to int. The problem is that table is incremental, so where should I place some migration script from int based table to bigint table?

drewbanin commented

Hey @sphinks! How are you thinking about doing this casting? I don't know that any migration is strictly necessary, as users can just run a --full-refresh to get the new code