Function regexp_like(text, unknown) does not exist error when running snowplow_web

For data modelers & consumers
1

I’m quite new to using dbt and still figuring some things out. When I run dbt run --selector snowplow_web I get the following error:

00:24:52  9 of 18 ERROR creating sql table model dbt_aukevos_scratch.snowplow_web_sessions_this_run  [ERROR in 5.09s]

00:25:02  Completed with 1 error and 0 warnings:
00:25:02
00:25:02  Database Error in model snowplow_web_sessions_this_run (models/sessions/scratch/default/snowplow_web_sessions_this_run.sql)
00:25:02    function regexp_like(text, unknown) does not exist
00:25:02    LINE 73:    when regexp_like(lower(trim(mkt_medium)), '^(.*cp.*|ppc|r...
00:25:02                     ^
00:25:02    HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
00:25:02    compiled Code at target/run/snowplow_web/models/sessions/scratch/default/snowplow_web_sessions_this_run.sql

Looking at the hint I figure that it’s an issue with the db type not being set properly. However this is set in my profile as follows:

snowplow_pilot:
  target: dev
  outputs:
    dev:
      type: postgres
      method: HTTP
      threads: 4

This profile is properly referenced in the root dbt_project.yml and in the package dbt_project.yml the profiel is set as default.

2

Hi @aukevos, since version 0.15.0 of snowolow_web we require that your warehouse is version 15 of Postgres, as regexp_like was introduced in this release.

Alternatively if you are not in a position to upgrade your postgres instance you could override the channel group query macro that uses this function with your own version that only uses older SQL functions.

3

@Ryan thanks, for now I downgraded the snowplow_web version and that did the trick!