--- title: Ashby to Snowflake Data Share slug: ashby-to-snowflake-data-share docTags: createdAt: 2025-11-28T16:07:54.229Z --- 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 [table and ERD reference](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 :::hint{type="info"} 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 :::BlockQuote 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. :::BlockQuote 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 :::hint{type="info"} 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 :::hint{type="info"} 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. :::hint{type="info"} 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 Fields](docId\:pG1llokJQbRln5GoEhn2C). :::BlockQuote \-- 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 :::BlockQuote \-- 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 = '\' ::: ### Example: getting a Job Consideration’s hiring team member information :::BlockQuote \-- 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 = '\' \-- NOTE: the application\_id would come from the APPLICATION table ::: ### Working with Custom Fields :::hint{type="info"} 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 :::BlockQuote 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. :::hint{type="info"} All shared tables and fields can be found in [this spreadsheet](https://docs.google.com/spreadsheets/d/1JTezzsoSDJag9RvXdvvUMlrIUSjeOllO4bwwE3-ffhI/edit?gid=1316200980#gid=1316200980) 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 | :::hint{type="info"} You can find an ERD for the current set of shared tables in the [Ashby Data Share documentation](https://dbdocs.io/data-share/Ashby-Data-Share?view=relationships). ::: ## 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: :::BlockQuote 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. ### Do 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. ### Is there an ERD available? Yes, the ERD can be found in our [Data Share documentation](https://dbdocs.io/data-share/Ashby-Data-Share?view=relationships).