How can I reference a table in dbt using its alias and a var, not its resource name?

I have been able to create a reasonably complex dbt model which contains several models all of which rely on a single model that acts as a filter.

Broadly, the numerous models follow the pattern:

{{ config(materialized = 'view') }}
SELECT *
FROM TABLE
INNER JOIN {{ ref('filter_table') }} FILTER
ON TABLE.KEY = FILTER.KEY

The filter table, let's imagine it's called filter_table.sql is simply:

{{ config(materialized = 'view') }}
SELECT *
FROM FILTER_SOURCE
WHERE RELEVANT = True

This works fine when I reference it in the numerous models like this: {{ ref('filter_table') }}.

However, when I try to use an alias in the filter table it seems that the alias is not resolved in time for dbt to be able to recognise it.

I amend the config of filter_table.sql to this...

{{ config(materialized = 'view', alias = 'FILT') }}

...and the references in the dependant models like this...

{{ ref(var('filter_table_alias')) }}

...with a var in dbt_project.yml set like this:

vars: filter_table_alias: 'FILT'

I get a message though which states that the node named 'FILT' is not found.

So my working theory is that although dbt recognised the dependencies based on how the refs are set up it is not able to do this using an alias - presumably the alias is not processed by the time that it is setting up the graph.

Is there a quick way to set up the alias and force it to be loaded first?

Or am I barking up the wrong tree?

2 Answers

The alias only impacts the name of the relation where the model is materialized in your database. ref always takes a model name, not an alias.

So you can add an alias = 'FILT' config to your filter table if you want, but in the other models you must continue to ref('filter_table').

The reason for this distinction is that dbt model names must be unique (within a dbt package/project), but aliases need not be unique (if they are materialized to different schemas).

You might be able to take advantage of dbt Classing - check out api.Relation, in which the identifier could be set as the alias I believe...