Question

Why is there no fivetran_deleted column for the leads table, this prevents me from viewing deleted leads?

Environment

Connector: Marketo

Answer

To track deleted records, we have added the ACTIVITY_DELETED_LEAD table, as mentioned in our docs. Use the below query to see the correct deleted status of your leads:

SELECT lead.*,
    CASE
        WHEN activity_deleted_lead.lead_id IS NULL THEN false
        ELSE true
    END as deleted
    FROM lead
    LEFT JOIN activity_deleted_lead on lead.id=activity_deleted_lead.lead_id;

Cause

Our Marketo connector uses priority-first sync to fetch your activities data. Sometimes, we pull recent lead data that is overwritten by outdated records when we sync your older data.

This may cause a lead to be created but the deletion to be missed. This is because we have read the deletion first when pulling the most recent activities.