|Available for||User roles||Super Admin|
|Packages||Advanced Analytics add-on|
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
- Use cases for Data Warehouse Sync
- Enabling and configuring Data Warehouse Sync
- Copying data through Data Warehouse Sync
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
- Select a time for the sync, click the Set New Time button, and then click Yes, Sync on the confirmation pop-up
- 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.
- 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.
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:
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.
The machine you are using to connect to AWS Athena may have an unstable internet connection. Check your internet connection and try again.
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.