BUG - Tablename missing __alias when multiple left joins

[pekkajauhi]

Are you a current Fivetran customer?

Yes. Jukka-Pekka Jauhiainen, Data Engineer, Kalevamedia

Describe the bug

When a formula field is included that requires the use of multiple left joins, the tablename in the join condition is missing __alias from the end.

For example if I have a formula field in the account table that uses field from the user_role table, which is joined with two left joins and the user table, view_sql will look something like this:

select account.id, ( user_role__alias.name ) as dummy_field
from account
left join user as user__alias 
on account.owner_id = user__alias.id 
left join user_role as user_role__alias 
on user.user_role_id = user_role__alias.id

The last join condition should look like this: user__alias.user_role_id = user_role__alias.id but instead the join condition looks like this: user.user_role_id = user_role__alias.id

Resulting in the following error:

000904 (42000): SQL compilation error: error line 71 at position 418
  invalid identifier 'USER.USER_ROLE_ID'

I'm not sure if this is a bug in the macro or is there a problem in the way view_sql is generated. But I think the problem could be fixed with some changes to the macro.

Steps to reproduce

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior

Project variables configuration

copy dbt_project.yml here

Package Version

packages:
     - package: fishtown-analytics/dbt_utils
       version: 0.6.6
    - package: fivetran/salesforce_formula_utils
      version: [">=0.5.0", "<0.6.0"]

Warehouse

- [ ] BigQuery - [ ] Redshift - [X] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Additional context** **Screenshots**

Please indicate the level of urgency

We are working on a important dashboard and this package would make things a lot easier, but because of this (and this) bug, we can't really use this package.

Are you interested in contributing to this package?

• [ ] Yes, I can do this and open a PR for your review.
• [X] Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• [ ] No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

[fivetran-joemarkiewicz]

Hi @pekkajauhi thanks so much for opening this issue. This seems to be an issue at the core of how the view_sql is being generated. I will forward this bug report to our engineering team to address this during the sync to the fivetran_formula table.

Additionally, our team is currently working on the Bug #34 and should have a fix implemented following the new year!

[fivetran-joemarkiewicz]

Hi @pekkajauhi a fix has been implemented into the connector code and the dbt package code earlier this morning. Would you mind trying your run again and letting me know if you still see the issue persist?

[pekkajauhi]

Hi @fivetran-joemarkiewicz I tried running some models and everything seems to be working as expected. Thanks!

[fivetran-joemarkiewicz]

That's great news! Thanks so much for checking @pekkajauhi 😄

Closing this issue, but feel free to open a new issue if you have any other questions in the future.