Business Intelligence Connector update – November 16 & 30, 2022

We are making updates to the BI Connector schema on November 16 and November 30, 2022.

Download the latest schema file by clicking the attachment on the right side of the page. 

Overview of changes

On November 16 and 30, the interviews and stages tables will receive a similar update to the attributes table from the November 2 schema update.

• Interviews table update: November 16, 2022
• Stages table update: November 30, 2022

Prior to this update, the interviews and stages tables were generated as a unique list of values with an id that changed each time the data was refreshed. Additionally, this list required an additional JOIN statement to correctly map the values to the actual IDs in Greenhouse Recruiting.

For more specific summaries of each changeset, see the sections below: interviews, stages.

Example query

Before update

When the interviews table contained a random id, an additional JOIN statement was needed to query the tables.

SELECT job_id 

  FROM jobs_stages

  JOIN stages on stages.id = jobs_stages.stage_id

 WHERE stages.stage_name = 'Phone Screen';

After update

After the above updates, the ID in the Business Intelligence Connector will match the ID Greenhouse Recruiting, providing a stable id for the table and allowing you to query each table directly without the additional JOIN: 

SELECT job_id

  FROM jobs_stages

 WHERE stage_name = 'Phone Screen';

Interviews table updates (November 16)

• The id column will become a real, primary key ID as it exists in Greenhouse Recruiting and won't update with each data refresh
• The created_at and updated_at columns provide the actual created_at and updated_at times instead of a null value
• Added interview name as a string column to jobs_interviews

After this change, the jobs_interviews table contains the same data as the interviews table. The interviews table will be deprecated in a future release. Organizations should plan to update their queries with the following mapping.

Stages tables updates (November 30) 

• The id column will become a real, primary key ID as it exists in Greenhouse Recruiting and won't update with each data refresh.
• The list of job stages now exactly matches the list as it exists in Greenhouse Recruiting.
• Added the following columns:
  • stage_name added to jobs_interviews for the name of the stage referenced by the stage_id
  • stage_name to stage_snapshots for the name of the stage referenced by the stage_id
  • created_at and updated_at columns added stages
• Updated the jobs_stages table to contain a full list of currently active stages. A full list of active and inactive stages will be stored in the stages table.
• Updated behavior of the "Hired" stages when marking a candidate as hired. See the section below for more information.

Updates to the Hired stage

Since stages weren't consistently mapped to the same ID, a placeholder "Hired" stage would be created in the jobs_stages table for the Business Intelligence Connector when a candidate was marked as hired.

This placeholder stage behavior caused conflicts since it would take precedence over any "Hired" stages created by your organization, leading to inconsistency between the Harvest API and the Business Intelligence Connector.

Since we are now mapping stages to unique IDs, this issue will no longer occur. As a result of this change, the following things will occur:

• Placeholder "Hired" stages will no longer be created.
• Existing placeholder "Hired" stages will be removed from the jobs_stages table, since they have no use outside of the previous BI Connector behavior.
• Any remaining stage named "Hired" (created by your organization) will be treated as a real stage
• The stage_id column on the jobs_stages table should be considered the primary key

In previous schema files, a list of hired candidates was collected by querying the applications or application_stages table. As a result of this change, this method is no longer the most reliable way to find a list of hired candidates.

Now, hired candidates on the applications or applications_stages table will be differentiated by their stage_id:

• • Candidates who have not been marked as hired, but are currently in a stage named "Hired" will have a stage_id
  • Candidates who have been marked as hired will have a NULL stage_id

You can run the following query to find a list of candidates who were hired:

SELECT * 

  FROM applications 

 WHERE stage_name = 'Hired' 

   AND stage_id IS NULL;