[Bug] int_marketo__lead model introducing duplicates

[m-feeser]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Our source data contains multiple entries in the activity_delete_lead for the same lead_id. This results in failed tests downstream.

Relevant error log or model output

No response

Expected behavior

I would expect if a lead_id was deleted twice, that only 1 record would be returned indicating that the record had been deleted.

dbt Project configurations

  marketo__enable_campaigns: true
  marketo__enable_programs: true

Package versions

0.8.0

What database are you using dbt with?

redshift

dbt Version

1.0.4

Additional Context

I think this could easily be fixed in the deleted_leads CTE in the model by select distinct lead_id as opposed to select *. None of the other columns from the CTE are referenced later in the SQL, so this seems like a reasonable, non-breaking change.

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[fivetran-joemarkiewicz]

Hi @m-feeser thank you for opening this issue!

I agree with you that this seems like a reasonable adjustment to apply within the package in the location you specified. However, before moving forward with a PR I would like to understand the use case of deleting a lead twice. What is a scenario where a lead would need to be deleted twice? Can a lead be reactivated and then deleted again?

[m-feeser]

Thanks @fivetran-joemarkiewicz. I will reach out to our users and see if I can get some information about the use case of the lead being deleted twice.

[m-feeser]

Hi @fivetran-joemarkiewicz. Marketo support seems to this is a "system glitch" and is not part of normal business activity. We are trying to follow up to get more detail.

However, I don't see a downside to going forward with the recommended change. It would make the transformations resilient to this type of "glitch" in the data. In addition, since the model does not attempt to publish any metadata about the delete (i.e. who did it, when it occurred), rather just an indicator that the lead_id has been deleted, no functionality would be lost with this change.

[fivetran-joemarkiewicz]

Thanks for the additional context @m-feeser! That is interesting that this is a system glitch on the marketo side.

I would agree with you that this could still be a relevant addition to the package to account for these glitches. I noticed you marked you would be interested in contributing a PR to apply this update. Is that something you are interested in doing? If so, feel free to go forward with your approach and open a PR. My team and I will then be able to reviewed and work to integrate into the following release.

If not, our team can pick this up in a future sprint in the new year. Thanks!

[m-feeser]

Hi @fivetran-joemarkiewicz. I will take a stab at making this change and see how far I get.

[fivetran-joemarkiewicz]

Sounds great!! Feel free to message here (or in a PR) if you need any support. 🎉

[m-feeser]

I opened PR#26 for this issue. Hopefully I did it correctly. Please, let me know if there's anything else you need from me.

[fivetran-joemarkiewicz]

Thanks so much @m-feeser! I am going to be on PTO until the new year, so I will plan to review and let you know if I have any thoughts the first week of January 😄

[fivetran-joemarkiewicz]

Hi @m-feeser this issue should be addressed (by your PR 🎉) in the latest v0.9.1 release! You should expect to see this release live on the dbt hub at the top of the hour.

As such, I will close out this issue. Let me know if you have any other issues or any questions. Thanks again!