Ashby to Snowflake Data Share

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 ❌ ❌ ❌ ✅ data share summary 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 onboarding and snowflake data sharing setup 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 docid\ pg1llokjqbrln5goehn2c 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 docid\ pg1llokjqbrln5goehn2c \ 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 ( &#x9;select &#x9; process role type id &#x9; , process role type title e g "recruiter" or "hiring manager" &#x9;from process role type ) select &#x9;jur job id &#x9;, jur app user id as job user id &#x9;, prt process role type title as job role title &#x9;, au first name ||' '|| au last name as job hiring team user name &#x9;, 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 ( &#x9;select &#x9; process role type id &#x9; , process role type title e g "recruiter" or "hiring manager" &#x9;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 ( &#x9;\ cte to flatten the offer version custom field data &#x9;select &#x9; &#x9; , f key text as custom field id &#x9; , f value text as custom field value &#x9;from offer version, lateral flatten(input=>custom fields) f ) , joined as ( &#x9;\ cte to incorporate custom field title and description &#x9;select &#x9; base &#x9; , cf custom field title &#x9; , cf custom field description &#x9;from base &#x9;left join custom field cf &#x9; on base custom field id = cf custom field id ) select &#x9;\ custom query against offer version custom fields from joined group by all order by count offer versions desc reference information available data tables & erd all shared tables and fields can be found in https //docs google com/spreadsheets/d/1jtezzsosdjag9rvxdvvumlriusjeollo4bwwe3 ffhi/edit?gid=1316200980#gid=1316200980 for reference below you can find a convenient table of tables, providing a name and brief description of currently shared content for a full list of every field, please see https //docs google com/spreadsheets/d/1jtezzsosdjag9rvxdvvumlriusjeollo4bwwe3 ffhi/edit?gid=1316200980#gid=1316200980 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 below you can find an erd for the current set of shared tables given the size and relative complexity, a zipped version is also made available for download 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) syncing the deletions application history table once a week on saturdays at 11 30pm pst application table once a week on saturdays at 11 30pm pst process user roles table once a day at 11pm pst 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 ho w often does the data refresh? our current data share mechanism aims to refresh data approximately every 6 hours d o you support data sharing to warehouses or destinations other than snowflake? 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 support\@ashbyhq com for escalation