Data Warehouse Sync

Follow
Available for User roles Super Admin
Packages Advanced Analytics add-on

 

The Snowflake Data Sharing feature referenced in this article is included in the Fall 2022 Product Release, scheduled for progressive rollout starting in late October 2022. For more information on the Fall 2022 release, keep an eye on our Product Release Notes.

Data Warehouse Sync (DWS) enables you to download a complete copy of your raw Lever data. You can then analyze and report on your Lever data using a business intelligence tool and/or your organization's own data warehouse. DWS is a powerful and comprehensive option for accessing all of your talent acquisition data and putting to use in a way that works best for you and your team. Readers of this article will learn about:

How Data Warehouse Sync works

DWS provides you with direct access to your Lever data. When Data Warehouse Sync is enabled and configured for your Lever environment, you are provided with credentials to access the database where reporting data is stored. You can then use these credentials to extract a full copy of your reporting data once per day. Once you have extracted the data, you can store, manipulate, and report on that data in whatever way works best for you. To get an idea of the information that can be extracted through Data Warehouse Sync, refer to our DWS schema.

Limitations to Data Warehouse Sync

While Data Warehouse Sync is a powerful option when it comes to data retrieval, it is important to keep in mind the following limitations:

  • Data made available through DWS is hosted on Amazon Web Services (AWS) Athena in the form of parquet files. Lever does not host the data that is made available through DWS. You must copy, host, and manage your own data.
  • Data made available through DWS does not support direct or live queries. In order to query your data, you must first copy it into your own business intelligence tool or data warehouse.
  • DWS cannot be used to build reports (as an alternative, refer to our help article on how you can use Lever's Data Explorer feature to build custom charts and dashboards in Visual Insights).
  • Data made available through DWS is refreshed once every 24 hours. As such, DWS cannot be used for activity tracking or real-time analytics.

 

If you use Snowflake to store and query your organization's data, considering using Lever's Snowflake Data Sharing feature as an alternative to DWS. To learn more, refer to our Snowflake Data Sharing help article.

Use cases for Data Warehouse Sync

Data Warehouse Sync is a data retrieval option best-suited for organization's that have:

  • A dedicated resource or team that can extract SQL-ready data from which to build reports
  • A pre-existing reporting infrastructure or business intelligence solution

The design philosophy behind Data Warehouse Sync makes it an appropriate solution for the following use cases:

  • Joining Lever data with other data sources such as a performance management tool or HRIS
  • Building highly specialized reports that apply to specific business cases
  • Building anonymized or de-identified reports for compliance, privacy, or security purposes

Enabling and configuring Data Warehouse Sync

 

Data Warehouse Sync can only be enabled and configured by users with Super Admin level access.

To enable and configure Data Warehouse Sync:

  • Navigate to Settings > Integrations and API > Reporting Warehouse
  • Click the Data Warehouse Sync button

Reporting Warehouse page in Integration and API settings; Data Warehouse Sync button is outlined.

  • Select a time for the sync, click the Set New Time button, and then click Yes, Sync on the confirmation pop-up

Close-up of sync time selection menu with Set New Time button outlined.

  • You will be provided with an AWS Access Key and a Secret Access Key. Copy these codes and store them in a secure location. Once you have copied the keys, click the Got It button on the access key modal.
    • For data security purposes, this is the one and only time these keys will be accessible. If you lose these keys at any point, you will need to generate new ones by returning to this page and clicking the Generate New Key button.

Access codes modal

  • Click the link under 'Workgroup details' to navigate to the AWS Athena database. From here, you can sign in and use the keys you copied from the previous step to access your data.

Workgroup details frame with drop-down button outlined.

Copying data through Data Warehouse Sync

Once you have access to your data in AWS Athena, you can connect the data store to your existing reporting infrastructure - either a business intelligence tool such as PowerBI, Tableau, or Looker, or your organization's own data warehouse.

AWS Athena can be connected to your existing reporting infrastructure using one of two applications - Open Database Connectivity (ODBC) or DBeaver. For instructions on how to set up connections using these applications, refer to the following help articles:

For an example of how to copy data, check out our help article on copying data to PowerBI via ODBC.

 

If you are copying data from a sandbox Lever environment via Data Warehouse Sync the data that gets copied to your reporting infrastructure will be a replica of the data in your production environment.

Troubleshooting issues related to Data Warehouse Sync

Below you will find a list of solutions to issues you may encounter when attempting to connect to AWS Athena or copy data into your existing reporting infrastructure:

Firewall
You may have a corporate firewall that is blocking the connection. In order to bypass this, you will need to open ports and/or protocols in order to fix the issue. Please reach out to your IT team for instructions on how to bypass your firewall.

Poor connectivity
The machine you are using to connect to AWS Athena may have an unstable internet connection. Check your internet connection and try again.

Incorrect credentials
Check that the credentials you entered match the credentials provided when you configured DWS and try again.

Incorrect or missing information
Ensure that all required parameters are used and entered correctly. This includes setting the "Workgroup" parameter to your account-specific value and setting the "Catalog" parameter to "AwsDataCatalog

Invalid username or password (Tableau via DBeaver)
If you receive the following error when attempting to connect AWS Athena to Tableau via DBeaver:

An error occurred while communicating with Amazon Athena
Invalid username or password.
Error Code: 37CE01A3
[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. User is not authorized to perform: athena:StartQueryExecution on resource: arn:aws:athena:us-west-2:46364624578:workgroup/primary [Execution ID not available]
Invalid username or password.

You will need need to customize your connection using a 'Properties' file. Refer to Tableau's guidelines on how to customize connections using the 'Properties' file for instructions.

If you experience an issue copying data through Data Warehouse Sync that is not listed above, we advised reaching out to the provider of your organization's business intelligence tool or data warehouse for assistance.

Was this article helpful?
0 out of 1 found this helpful