Using get_column_values with CTEs

Help
,
1

Using: dbt 0.17.0 with BigQuery

I’m new to dbt and looking to how to better incorporate macros from dbt_utils into my workflow. In particular, I’m interested in using pivot and get_column_values but I have been unsuccessful so far in incorporating it in my model code with CTEs defined in the same model. Here’s what I have done so far.

I have taken the example from github and created a new model like so:

– example_model.sql

with data as (
    SELECT 'S' as size, 'red' as color
    UNION ALL SELECT 'S' ,'blue'
    UNION ALL SELECT 'S', 'red'
    UNION ALL SELECT 'M', 'red'
),
SELECT * FROM data

Then in another file I have the following:

SELECT
    size,
    {{ dbt_utils.get.pivot('color', dbt_utils.get_column_values(ref('example_model'), 'color')) }}
FROM ref('example_model')
GROUP BY size

So far so good and this works as expected.

However, my problem is that I don’t actually want to create a new model for the first table – it’s much too small and not used for anything else (as are a lot of other temporary/CTE tables that I create). Thus, what I really want to be able to do is something like this (all in the same model file):

with data as (
    SELECT 'S' as size, 'red' as color
    UNION ALL SELECT 'S' ,'blue'
    UNION ALL SELECT 'S', 'red'
    UNION ALL SELECT 'M', 'red'
),
SELECT
    size,
    {{ dbt_utils.get.pivot('color', dbt_utils.get_column_values(data, 'color')) }}
FROM data
GROUP BY size

However, I can’t seem to get this work. If I use data (no quotes), I get a 'data' is undefined error; if I use 'data' I get an 'str object' has no attribute 'database' error.

Thus my question is: what is the right way to refer to data such that I can use it in dbt_utils.get_column_values and without creating a new model in the process?

Thanks for your help

1 Like