Post-hook create errors on correctly compiled custom test

[wissam-code]

Describe the bug

We currently have only "custom" generic and singular tests in our dbt repos.
The post-hook of elementary create errors on correctly compiled custom generic test

To Reproduce

Here an exemple of our custom generic test unique :

I have an errors:

The compilated test work well in the database

      WITH duplicates AS (
              SELECT reserving_coverage_sk, COUNT(*) as total_count
              FROM pr13088_DPF_DB.marts.claims_reserving_by_coverage
              GROUP BY reserving_coverage_sk
              HAVING COUNT(*) > 1
          )
      
          SELECT a.* FROM pr13088_DPF_DB.marts.claims_reserving_by_coverage a
          INNER JOIN duplicates d
              ON a.reserving_coverage_sk = d.reserving_coverage_sk

When I set require_explicit_package_overrides_for_builtin_materializations to true instead of false, the error no longer occurs on tests. However, this prevents me from benefiting from Elementary's features.

Expected behavior
When the compiled test runs correctly in the database, I expect no errors, even for custom generic tests that override the dbt library test unique, duplicates ect.

Environment:

• Elementary dbt package version: 0.16.4
• dbt-core: 1.9.1
• Data warehouse: snowflake

Would you be willing to contribute a fix for this issue?
yes