search

tnightengale / dbt-activity-schema

A dbt-Core package for generating models from an activity stream.
GNU General Public License v3.0
38 stars 5 forks source link

SAFE_CAST compilation error for BigQuery #33

Open kennyning-current opened 1 year ago

kennyning-current commented 1 year ago

I've set up my customer_stream according Activity Schema spec V2. I tried a basic funnel to start:

with

dataset_cte as (
    {{ dbt_activity_schema.dataset(
        activity_stream = ref("customer_stream"),

        primary_activity = dbt_activity_schema.activity(
            dbt_activity_schema.all_ever(), "opened_email"),

        appended_activities = [
          dbt_activity_schema.activity(
              dbt_activity_schema.first_after(), "deposited_payroll"),
           dbt_activity_schema.activity(
              dbt_activity_schema.first_after(), "visited_points_tab"),
        ]
    ) }}
)

select * from dataset_cte

And getting this error at this line in the compiled sql: image

It looks like BigQuery's safe_cast function does not like casting json to string at all, so we might need a different approach: image

tnightengale commented 1 year ago

@kennyning-current Would you mind taking a look at https://github.com/tnightengale/dbt-activity-schema/issues/26 for context, and perhaps open a PR to solve this for the BQ case?