Ashby to Snowflake Data Share

24 min

This document aims to provide a brief outline of Ashby’s native Snowflake data sharing feature. A review of the available data, reference documentation, and analysis guidelines is provided.

The Ashby to Snowflake Data Share is available on the Enterprise plan only:

Foundations	Legacy Plus	Plus	Enterprise
❌	❌	❌	✅

Ashby supports data share mechanism which exposes your Ashby data in a native Snowflake database. After initial onboarding, your data team will be granted access to Ashby data tables within Snowflake whereupon standard analysis may proceed using the analysis tools of your team’s choice. At present, your data is scheduled to refresh with updates approximately once every 6 hours.

Please reach out to your Customer Success Manager to inquire about access and the setup steps for the Snowflake data share feature.

Getting Started with Analyzing Your Ashby Data

To aid your analysis efforts, we have provided a few topical comments across a variety of “core” tables and recruiting metrics. This introduction is not meant to be comprehensive, but rather orient you with your raw Ashby data to catalyze getting oriented such that you can pursue analysis efforts more effectively. Please see the table and ERD referencetable and ERD reference section below to review table and field name details.

Identifying your Employees

All employees are stored in the APP_USER table, and any reference to a ..._user_id will trace back to a user record. For example, if you wanted to analyze scorecard submission values by employee for a given job, or on average per department, you would count the scorecard values from the SCORECARD_SUBMISSION table and join to the APP_USER.app_user_id on SCORECARD_SUBMISSION.submitted_by_user_id to access the user’s first name, last name, and any other useful metadata.

Candidates and their Applications

Note, the Ashby in-app experience refers to Job Considerations (i.e. a candidate is considered for a job), but the underlying data table is called APPLICATION.

Almost all analyses will involve looking at candidates and their associated job considerations. A candidate can have multiple applications, but the basic association is done by way of joining on CANDIDATE_DATA.candidate_data_id and APPLICATION.candidate_id.

Analysis by Departments and Teams

It is common to analyze candidate data by their associated departments or teams, e.g. looking at time to hire for the engineering department or sub-teams. The Job definition contains department and team details, and every application has a job ID reference. Thus, to analyze application details by department, you join the JOB table on JOB.job_id and APPLICATION.job_id. The exact team details associated with the job are stored in the TEAM table, which can be joined on JOB.team_id and TEAM.team_id, whereupon team hierarchy and names can be used.

Example: Getting the count of applications per job

SELECT j.job_id , t.team_name , j.job_title , COUNT(DISTINCT a.application_id) total_applications , COUNT(DISTINCT a.candidate_id) total_candidates FROM job j LEFT JOIN application a USING (job_id) LEFT JOIN team t USING (team_id) WHERE -- Fitering by date, team or any other relevant fields DATE(a.application_created_at) >= '2025-01-01' GROUP BY ALL;

Analyzing Scorecard Submissions

Scorecard analysis is can be done directly by looking at the scorecard_submission table. However, if you want to analyze scorecards by their related interview metadata, the task gets a bit more involved. The example query below shows how to tie a scorecard submission to the associated interviewer event, interview event and interview objects — each of these objects hold different metadata about the overall scheduling process.

Note in this query the scorecard submission source is set to “InterviewerEvent” which indicates selecting scorecards submitted by employees within interviews. The other available sources are “ApplicationReview” and “OneOff” for scores submitted during application review or via ad hoc addition, respectively.

SELECT ss.submitted_by_user_id, ss.submitted_at, ss.overall_recommendation, -- the 1 through 4 score ss.application_id, ss.source, ss.source_id, ire.*, -- list the interviewer event details ie.*, -- list the interview event details i.* -- job, interview stage, title FROM scorecard_submission AS ss LEFT JOIN interviewer_event AS ire ON ss.source_id = ire.interviewer_event_id LEFT JOIN interview_event AS ie USING (interview_event_id) -- start time, end time LEFT JOIN interview AS i USING (interview_id) WHERE ss.source = 'InterviewerEvent' LIMIT 100;

Analyzing Application Stage History

If you wanted to analyze Time to Hire, you’d look at the first application history row entry time stamp and the time stamp of entering the hired stage, which would give the total duration the application was in process from start to being hired.

Applications progress through interview plans by way of stage transitions. All applications have a current stage, and when they move from one stage to the next they generate a row in the APPLICATION_HISTORY table, which tracks the timing along with “from stage” and “to stage” details. If a candidate’s application is transferred between jobs that will be noted and captured in this history table too. See the schema information for full details.

Note that you can join every transition to the INTERVIEW_STAGE table to get additional details about each stage, such as the stage type and name. This can be a rich resource for analyses related to applications moving into or out of application review, offer stages, or terminal stages (archived, hired).

Analyzing Versioned Objects: Offers & Openings

If you wanted to analyze compensation variation across an offer process, you’d use offer versions to find the first, and final offer version created and the delta in the compensation fields of interest. By a similar measure, you could also identify timing between offer iterations to evaluate your offer process across teams.

Some objects in Ashby follow a versioning design where there is a root object and then related versions. A good example of this is offers, where the root offer will contain the associated application ID, status and other details, while the offer version records will contain updates and edits to any of the potential iterations of an offer created in the offer process (i.e. variations due to candidate negotiation). To analyze offers and their versions, you can join these tables on OFFER.offer_id and OFFER_VERSION.offer_id. This same pattern applies to openings and opening versions.

To calculate Time to Fill you have two potential routes: you can analyze the open and close dates on the job records via the JOB table, or you can analyze the open and close dates on the opening record via the OPENING table. Given the opening model has more robust fields, such as target hire dates, this could permit more sophisticated analysis of close dates relative to targets. The opening record also has an opening history table, which permits more fine-tuned analysis of total time spent in the case of possibly closing and re-opening a role.

Example: Working with Opening Version custom fields with pre-defined selectableValues options

See the section below for more details on Working with Custom FieldsWorking with Custom Fields.

-- CTE to flatten the "custom fields" on the Opening Version table WITH opening_version_custom_fields AS ( SELECT opening_version_id, f.key::STRING as custom_field_id, f.value::STRING as custom_field_value FROM opening_version, LATERAL FLATTEN (input => custom_fields) f ), -- CTE to flatten the "selectable values" for the Opening Version custom fields opening_version_custom_field_values AS ( SELECT custom_field_id, v.value:value::STRING AS custom_field_value, v.value:label::STRING AS custom_field_label FROM custom_field, LATERAL FLATTEN (input => configuration:selectableValues) v WHERE object_type = 'opening_version' ) SELECT ovf.opening_version_id, cf.custom_field_title, -- Custom field value, which can on its own be an id ovf.custom_field_value, -- The title for the selected custom field, if the value above is an id ovfv.custom_field_label, -- Coalescing the two values above COALESCE(custom_field_label, custom_field_value) AS final_custom_field_value FROM opening_version_custom_fields AS ovf LEFT JOIN custom_field cf USING (custom_field_id) LEFT JOIN opening_version_custom_field_values AS ovfv USING (custom_field_id, custom_field_value);

Analyzing Hiring Team Configuration

Hiring team members can be assigned at both the Job and Job Consideration. When configuring a Job, you may want to assign a known Hiring Manager, Recruiter, Recruiting Coordinator and Sourcer, and these roles will apply to the downstream candidates as Job Considerations are created. Similarly, you may want to customize or edit the hiring team membership for a specific Job Consideration. To manage this Ashby has three main tables to work with:

PROCESS_ROLE_TYPE: this holds the unique role ID and role title (e.g. "Hiring Manager" or "Recruiter")

JOB_USER_ROLES: this holds the hiring team roles per individual Job. This table references the job via the job_id while the app_user_id refers to the Ashby user, i.e. coworker at your organization, assigned to the role.

PROCESS_USER_ROLES: this holds the hiring team roles per individual Job Consideration. The "process" here is the candidate's Job Consideration, so the underlying process_id references the Job Consideration table's application_id. The app_user_id refers to the Ashby user, i.e. coworker at your organization, assigned to the role.

The example queries below shows how to join human-readable hiring team role titles as well as the Ashby application user information to the hiring team roles associated with a Job or Job Consideration.

Example: getting a Job’s hiring team member information

-- CTE to select the hiring team role IDs and titles WITH process_role_types AS ( SELECT process_role_type_id , process_role_type_title -- e.g. "Recruiter" or "Hiring Manager" FROM PROCESS_ROLE_TYPE ) SELECT jur.job_id , jur.app_user_id as job_user_id , prt.process_role_type_title as job_role_title , au.first_name ||' '|| au.last_name as job_hiring_team_user_name , au.email job_hiring_team_user_email FROM JOB_USER_ROLES jur INNER JOIN process_role_types prt ON jur.process_role_type_id = prt.process_role_type_id LEFT JOIN APP_USER au ON jur.app_user_id = au.app_user_id --WHERE jur.job_id = '<job_id_of_interest>'

Example: getting a Job Consideration’s hiring team member information

-- CTE to select the hiring team role IDs and titles WITH process_role_types AS ( SELECT process_role_type_id , process_role_type_title -- e.g. "Recruiter" or "Hiring Manager" FROM PROCESS_ROLE_TYPE ) SELECT pur.process_id as application_id , pur.app_user_id , prt.process_role_type_title as app_role_title , au.first_name ||' '|| au.last_name as job_hiring_team_user_name FROM PROCESS_USER_ROLES pur LEFT JOIN process_role_types prt ON pur.process_role_type_id = prt.process_role_type_id LEFT JOIN APP_USER au ON pur.app_user_id = au.app_user_id -- WHERE application_id = '<application_id>' -- NOTE: the application_id would come from the APPLICATION table

Working with Custom Fields

If you are using the Snowflake data share while implementing a migration to Ashby from another ATS, please note that Workday-based custom field ingestion may impact pre-existing custom field names (i.e. fields from the former ATS) upon activation of the integration.

All custom fields are stored in the CUSTOM_FIELD table and follow the format of having a unique ID, an object type, field type, and field title. The object type can be any Ashby object that supports custom fields (e.g. Offers, Jobs, Candidates, etc). The field type gives the type of data to expect (e.g. boolean, number, string, etc) and the title is the human-readable field name you will find in your Ashby UI.

When it comes to the object tables that have custom fields, e.g. OFFER_VERSION, there will always be a custom_fields column that stores a json blob of key:value pairs where the key is the custom field ID and the value is the custom field value. To work directly with custom fields for an object you must first unpack the json join to the primary CUSTOM_FIELD table to pull in the custom field title. An example of this pattern is given below.

Example: working with Offer Version custom fields

WITH base AS ( -- CTE to flatten the offer version custom field data SELECT * , f.key::text as custom_field_id , f.value::text as custom_field_value FROM offer_version, lateral flatten(input=>custom_fields) f ) , joined AS ( -- CTE to incorporate custom field title and description SELECT base.* , cf.custom_field_title , cf.custom_field_description FROM base LEFT JOIN custom_field cf ON base.custom_field_id = cf.custom_field_id ) SELECT -- custom query against offer version custom fields FROM joined GROUP BY ALL ORDER BY count_offer_versions DESC

Reference Information: Available Data Tables & ERD

Below you can find a list of core tables, providing a name and brief description of currently shared content.

All shared tables and fields can be found in this spreadsheet for reference.

Table Name	Description
APPLICATION	A candidate's Job Consideration in Ashby is stored in this table. One row represents one candidate's application for a specific job.
APPLICATION_HISTORY	Every time a candidate's Job Consideration changes a stage in Ashby, a row is logged in this table. One row represents a stage transition with the relevant metadata on from which stage, to which stage, and whether the transition corresponds to a job transfer.
APP_USER	Each row in this table represents an Ashby user. This can include bots, but is representative of what you would see in your Admin > Employees section of Ashby.
ARCHIVE_REASON	Each row in this table represents one of the configured archive reasons. An application will reference these items when archived.
CANDIDATE_DATA	Each row in this table represents a candidate’s set of stored information, such as name, email, and other details.
CANDIDATE_LOCATION	Each row in this table represents candidate location information, including whether it was extracted from a resume or provided on an application form.
CUSTOM_FIELD	Each row in this table represents a custom field created in Ashby on any of the objects that support custom fields (e.g. Offers or Openings)
GEO_LOCATION	Each row in this table represents a hierarchically stored geo location, such as a city, state, or country, along with other geo metadata.
INTERVIEW_STAGE	Each row in this table represents a stage within an interview plan. All stages are tied to an interview plan and have an order in that interview plan, representing the sequence of steps an application will move through for a given job.
INTERVIEW_STAGE_GROUP_TYPE	When stage grouping is enabled in your Ashby instance, every interview stage is mapped to a stage group. Stage groups have a type and order and are useful for aggregate analysis across many distinct interview plans.
JOB	Each row in this table represents a stored Job record in Ashby.
JOB_POSTING	Each row in this table represents a Job Posting. Every Job Posting is associated with a Job in Ashby, but Jobs can have multiple Job Postings.
JOB_POSTING_SECONDARY_LOCATIONS	For Job Postings that have secondary locations, the reference to the location geo data record is stored in this table.
OFFER	Each row in this table represents the root object of an offer. Additional offer details are stored in versions of offers created.
OFFER_VERSION	Each row in this table represents a version of an offer created and decided upon by the candidate. An Offer can have multiple versions, depending on the number of edits or negotiations required for any given candidate's case.
OPENING	Each row in this table represents the root object of an Opening. Additional opening details are stored as opening versions.
OPENING_APPLICATIONS	When an application is at offer stage or hired, the association to a specific Opening is made. This is a join table that store specific openings to applications.
OPENING_HISTORY	Each row in this table represents a state change of an opening (e.g. from Open to Closed).
OPENING_VERSION	Each row in this table represents a version of an opening created and contains additional details about the opening (such as target hire dates, custom fields, etc).
ORG_LOCATION	A table with a single row containing the geo location information about your organization.
REFERRAL	Each row in this table represents a referral made, including details such as the referrer, the referred application created, and any referral form details captured.
REFERRAL_FORM_DEFINITION	Each row in this table represents the structure of your referral form definition.
SCORECARD_FORM_DEFINITION	Each row in this table represents the structure of your scorecard form definition.
SCORECARD_SUBMISSION	Each row in this table represents a scorecard submission, with fields and values as derived from the scorecard form used.
SOURCE	Each row in this table represents a candidate or application source (e.g. "LinkedIn"). Sources are further grouped into source types for broader categorization.
SOURCE_TYPE	Each row in this table represents a source type as configured in your Ashby instance.
TEAM	Each row in this table represents a team. Teams are hierarchical, and as a root node they are referred to as "Department", and as sub-nodes they are called "Teams". For example, teams appear on Jobs and Openings (e.g. the "Engineering" department or "Engineering

You can find an ERD for the current set of shared tables in the Ashby Data Share documentation.

How Often Does the Data Refresh?

The synced data refreshes once every 6 hours. The data sync from our production databases to Snowflake is variable and takes between 1-2 hours, after which dynamic tables are generated and Customer-specific shares are updated. The sync schedule start times are given below, but in reality the updated data will manifest itself in your share ~2 hours from the listed start times.

Production Databases to Snowflake Sync:

Syncing the Updates: scheduled to run 4 times a day at 3AM, 9AM, 3PM and 9PM PST (every 6 hrs)

Snowflake Shared Views: dynamic tables based on the synced data run after production database sync completion.

On each shared view, you will find a column named ___extracted_at. This field represents when the given row in that table was last pulled from the production database (for large tables, pulling all new or updated rows from the database may take minutes or in some cases hours, so you may expect to see different timestamps on different rows of each table). In order to get a sense of last sync time for each table, you can run a query similar to the following for the application table:

SELECT MAX(___extracted_at) AS last_synced_at FROM application

FAQ

How often does the data refresh?

Our current data share mechanism aims to refresh data approximately every 6 hours.

Not at present, but we plan to do so in the future.

Is this different than the Ashby API?

Yes, the Ashby developer API is intended for application and integration development and is entirely distinct from the Snowflake data share mechanism described here.

I don’t see the data I need or expect, what do I do?

If this is an urgent matter, please reach out to [email protected] for escalation.

Is there an ERD available?

Yes, the ERD can be found in our Data Share documentation.