Define foreign key in model joined to itself

Help
,
1

The problem I’m having

I need to have a table with a self-joined foreign key, and I need it to be defined and explicitly reflected in the dbt ERD docs site for my project. I always generate a corresponding .yml file for all of my models that define the definitions, constraints, foreign keys, etc.

I have raw data that has a certain self joined column almost identical to the example defined here:
https://docs.getdbt.com/sql-reference/self-join

It’s works fine for me to write a simple transform to move the data from the src to a model. For example:

SELECT
    id,
    name,
    parent_id
FROM {{ source("raw_schema", "raw_table") }}

However, that doesn’t actually create a real foreign key relationship. If I add the corresponding .yml file and define the foreign key like:

version: 2

models:
  - name: transformed_table
    description: foobar.
    config:
      materialized: table
      contract:
        enforced: true
    columns:
      - name: id
        description: aaaaaaaa.
        data_type: integer
        constraints:
          - type: primary_key
          - type: not_null
          - type: unique
      - name: name
        description: aaaaaaaaa.
        data_type: text
        constraints:
          - type: not_null
          - type: unique
      - name: parent_id
        description: aaaaaaaa.
        data_type: integer
        constraints:
          - type: foreign_key
            expression: fake_transformed_schema_name.transformed_table (id)

When I add the .yml, I get an error:

 Database Error in model provider (models/src/stuff/transformed_table.sql)
  relation "fake_transformed_schema_name.transformed_table" does not exist

The context of why I’m trying to do this

I need the table to have the self join.

What I’ve already tried

I have tried following this, but that doesn’t really say if this would work when you need a real FK defined in the .yml file.

I also went down the road of using ephemeral tables, but I keep running into the issue that it seems like it can not create the FK on the transformed table for a self join.