Relationships schema test does not support expressions anymore

[dmateusp]

Describe the bug

Up until 0.20.x, the relationships test was commonly used with a column/field that is an expression concat(col_a, col_b). This was a way to support a relationship test on multiple keys.

The macro was refactored in a way that breaks this use of it.
Previously, the column was aliased to id, so expressions were supported. But now that the column/field is not aliased anymore, the macro throws an "ambiguous column" error and produces invalid SQL.

For example the JOIN condition looks like this now:

on child.concat(col_a, col_b) = parent.concat(col_a, col_b)

Steps To Reproduce

Use dbt > 0.19, use the relationships test with concat(...)

Expected behavior

Supports relationship tests that include a JOIN on multiple keys

Screenshots and log output

  002028 (42601): SQL compilation error:
  ambiguous column name 'COL_A'
  compiled SQL at target/run/<redacted>/data/<redacted>.sql

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.20.1
   latest version: 0.20.1

Up to date!

Plugins:
  - bigquery: 0.20.1
  - snowflake: 0.20.1
  - redshift: 0.20.1
  - postgres: 0.20.1

The operating system you're using: Mac OSX

The output of python --version: Python 3.9.0

Additional context

Slack thread: https://getdbt.slack.com/archives/CBSQTAPLG/p1629717601126100

[dmateusp]

I've added the following macro to our project as a drop-in replacement to the existing relationships which fixes the issue:

{% macro test_relationships_expr(model, column_name, to, field) %}

with child as (
    select *, {{ column_name }} as dbt_test_expr from {{ model }}
    where {{ column_name }} is not null
),

parent as (
    select *, {{ field }} as dbt_test_expr from {{ to }}
)

select
    child.dbt_test_expr

from child
left join parent
    on child.dbt_test_expr = parent.dbt_test_expr

where parent.dbt_test_expr is null

{% endmacro %}

[jtcohen6]

@dmateusp You're right. The ability to do this is slightly counterintuitive, since it involves passing an expression into an argument called column_name:

version: 2

models:
  - name: my_model
    columns:
      - name: id
        # standard
        tests:
          - relationships:
              to: ref('another_model')
              field: other_id
    # expression instead
    tests:
      - relationships:
          column_name: concat(col_a, col_b)
          to: ref('another_model')
          field: concat(col_a, col_b)

At the same time, I hear you, it's a common enough use case, and we should keep supporting it while it's easy to do.

Any chance I could ask you to contribute your drop-in replacement as a fix? It would be great to include this in the next patch release (v0.20.2), if possible.

I'm not totally sold on dbt_test_expr, but I also don't have a much better idea. Perhaps from_col_expr and to_col_expr?

I think the only "breaking" change here would be the name of the column returned by the test query, which is relevant to folks using the --store-failures feature: the column would no longer be named something_id, it would be named dbt_test_expr/from_col_expr.