Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Greenhouse connector missing job_post_id in Application table

Completed
Daniyar Belkhojayev User

 

Hi there!

We are missing a vital column in our Greenhouse integration. 

In greenhouse documentation table "Applications" has a field "job_post_id",
but we don't see this field in synced data. Indeed, Fivetran's ERD schema for Greenhouse integration doesn't contain the field.
Same problem exists for "Application_QNA" table.

I know "job_post_id" exists in other tables but they don't allow for 1:1 match with "application_id". 

Generally, there are many other mismatches between actual Greenhouse schema and what Fivetran syncs.

Please sign in to leave a comment.

Comments

3 comments

Date Votes
  • Official comment
    Erin Fivetranner

    Hi Daniyar -

    Thanks for submitting this feature request. In the greenhouse schema we have the JOB_APPLICATION table which acts as a bridge table between APPLICATION and JOB. Typically an APPLICATION can only be associated with 1 JOB, however when the APPLICATION is for a prospect it can have 0 or many JOBS which is why we created the bridge table as part of the schema.  You can do a query across the bridge table as shown below to get all of the the APPLICATION_ID's with the JOB_ID.

    Thanks, 

    Erin 

    SELECT
    A.ID as APPLICATON_ID
    ,J.ID AS JOP_POST_ID
    FROM APPLICATION A
    LEFT JOIN JOB_APPLICATION JA
    ON A.ID = JA.APPLICATION_ID
    LEFT JOIN JOB J
    ON JA.JOB_ID = J.ID
  • Daniyar Belkhojayev User

    Hi Erin,
    thank you for your reply. 

    Do I understand it correctly that the suggested script will help to establish application_id to job_id relationship?
    It's not exactly what we are looking for. 
    We need to establish a connection between application_id and job_post_id.
    In you script you use 

    J.ID AS JOP_POST_ID

    while J is a JOB table, so J.ID is JOB_ID, not JOB_POST_ID. 
    So, wrong table.


    P.S.
    I know there is a relationship between JOB and JOB_POST tables, but it is a 1:N relationship and therefore doesn't help getting 1:1 relationship between application_id and job_post_id.
    Here is the prove:

    SELECT J.id, count(*)
    FROM JOB J
    left join JOB_POST JP
    ON J.ID = JP.JOB_ID
    group by 1
    order by 2 desc;



  • Erin Fivetranner

    Hi Daniyar - 

    My apologies, i was indeed looking at the wrong field. Yes, that is a missing foreign key relationship that we should have in our ERD. 

    I'll add this as a Feature Improvement task in our backlog and work to prioritize it in an upcoming sprint. 

    Best, 
    Erin