"Unrecognized name: root_id" and "No matching signature for function TIMESTAMP"

[dbt-labs]

"Unrecognized name: root_id" and "No matching signature for function TIMESTAMP"

[wub]

Hey @drewbanin, thanks for taking the time to look into this.

Every single line is in one table - it seems to make use of BigQuery's nested & repeating column types.

No info yet available on the timeline for BigQuery support for the original web data model (hence me coming to this repo) - so I'll override the relevant models and macros until it works (as you suggest in README).

First step will be changing root_id to contexts_com_snowplowanalytics_snowplow_web_page_1_0_0!

Feel free to close this one; we can probably revisit once Snowplow themselves release BQ for the original model.

[drewbanin]

Hey @wub - we find that the names of these columns are (confusingly!) different depending on how Snowplow is hosted. I checked out the schema you sent over above - is contexts_com_snowplowanalytics_snowplow_web_page_1_0_0 a table itself or is it a column in the event table?

At a high level, I think there are two distinct things happening here:

Unrecognized name: root_id

• dbt is looking for a column called root_id, but it might be called something else in your setup. This columns should be the foreign key to the event_id in the event table. I'm no so familiar with the expected Snowplow schema on BigQuery. In something like Redshift, the web_page context will render out as a separate table with two columns: root_id and id. It looks to me like this is not the case in your setup!

No matching signature for function TIMESTAMP for argument types: TIMESTAMP

• I think this error is coming from these lines of code - BQ doesn't let you call timestamp() on a timestamp field. I think the Snowplow datasets we've worked with have been provided collector_tstamp as a datetime, and this code is intended to turn it into a timestamp.

In general, this package is based on Snowplow's original web data model. It sounds like some models for BQ are coming soon, and it would be super interesting to see how they end up recommending that these models work!

[drewbanin]

Cool, thanks for the update @wub!