# Replicate Workday WQL Data to Snowflake using Workday Prompts (without errors) **URL:** \ **Category:** CData Sync\ **Tags:** discussion, workday, snowflake, best-practices, prompts, workday-prompts\ **Created:** [May 9, 2024, 11:30am UTC](https://community.cdata.com/t/replicate-workday-wql-data-to-snowflake-using-workday-prompts-without-errors/711 "2024-05-09T11:30:41Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![Ankit\_Singh](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ankit_singh/32/1614_2.png) [@Ankit\_Singh](https://community.cdata.com/u/Ankit_Singh)\ **Post date:** [May 9, 2024, 11:31am UTC](https://community.cdata.com/t/replicate-workday-wql-data-to-snowflake-using-workday-prompts-without-errors/711/1 "2024-05-09T11:31:41Z")
## **Replicating Workday Data: The Challenges** Replicating Workday HCM and Financials data can be a challenging task in itself, especially when the Workday APIs are so vast, support a variety of data models, and offer different types of authentication mechanisms. With [CData Workday connectivity solutions](https://www.cdata.com/solutions/workday/), we simplify the complexities of the backend APIs and offer you a relational view of all your Workday that can be queried using SQL. CData Workday connectors support a variety of Workday connections through: - Workday SOAP APIs - Workday REST APIs - Workday RaaS (Reports-as-a-Service) - Workday WQL To learn more about the seamless Workday connectivity, CData offers, please refer here: [CData Sync - Workday | 24.3.9120](https://cdn.cdata.com/help/ASK/sync/Workday-Source.html#connect-to-workday). ## **Powerful Querying through Workday WQL** The Workday WQL service enables high-performance querying of Workday data. Each CData Workday connectivity solution has a built-in SQL Engine that is optimized to work with Workday WQL. As such, CData solutions can push the complexities of queries for data down to Workday for processing. This results in faster responses to queries and less reliance on client-side resources for data processing. To learn more about connectivity through Workday WQL, please refer here: [https://www.cdata.com/kb/articles/workday-wql-raas.rst](https://www.cdata.com/kb/articles/workday-wql-raas.rst). ## **Workday Prompts** The entities available from WQL are often large models and require prompts to retrieve data. Prompts are fields that allow you to refine the results of a data request. If you’ve ever exported data from Workday, you’ve seen these prompts in the form(s) you use to download the data. **Challenges:** However, Workday Prompts can be tricky, and can often land you with a series of hits and trials before you replicate your data successfully. In the article, we will learn how to replicate data from Workday WQL using Workday Prompts to Snowflake. ## **Replicate Data from Workday to Snowflake (using CData Sync)** To get started, download and install the CData Sync from [https://www.cdata.com/sync/trial/](https://www.cdata.com/sync/trial/). Once done, please follow the steps here: **1.) Create a Destination connection - Snowflake:** To configure a Destination connection to Snowflake, please refer to the Help documentation here: [CData Sync - Snowflake | 24.3.9120](https://cdn.cdata.com/help/ASK/sync/Snowflake-Destination.html). ![8a085f2f-9bf4-4206-8c63-eb1b2fce04da.png](https://us1.discourse-cdn.com/cdata/original/2X/3/30a22cdd75d7757230dfb502e081e672caf0b880.png) **2.) Create a Source connection - Workday (using WQL):** To configure a Source connection to Workday, please refer to the Help documentation here: [CData Sync - Workday | 24.3.9120](https://cdn.cdata.com/help/ASK/sync/Workday-Source.html). Please note that I am using _ **Connection Type=WQL** _ and _ **Auth Scheme=OAuthISU.** _ ![cdf7ec2b-7ce3-4b2d-a7bb-b5132ed27746.png](https://us1.discourse-cdn.com/cdata/original/2X/f/fb538150c480407894e801664845f72b7e6028ac.png) **3.) Create a Job:** Once you have the source and destination connection set, let’s create a job: ![068f0cda-599d-49e0-b53a-bbe0f4d7957a.png](https://us1.discourse-cdn.com/cdata/original/1X/c53aece4db9a190b3ae0e716b863c201ba720deb.png) **4.) Add a Task:** Once the job is created, you can select a Task - the Workday Object you wish to replicate. We are using **‘Journals’** data here: ![4d7d1484-fe2d-4e97-917e-75d8acf4137c.png](https://us1.discourse-cdn.com/cdata/original/2X/2/2804df9c2cd22d4faa1643f7c007bcf91fae213a.png) Once done, follow the step-by-step guide below to work with Workday Prompts. ###   ### **Working with Workday Prompts: Step-by-step Guide:** **1.) Identify Workday Prompts for Table:** - First of all, we will try to identify the Workday Prompt columns for the table/view we have selected. In this case, **‘Journals’.** To do this, navigate to the task level and then to the Query tab to modify your query: ```sql REPLICATE [journals] SELECT * FROM sys_tablecolumns WHERE TableName = 'Journals' AND ColumnName LIKE '%prompt%' ``` ![d97168c4-6769-4fa7-880b-87ae011af471.png](https://us1.discourse-cdn.com/cdata/original/2X/5/53aff06d9a7fc00adddcd23d6f90ef6f7d6de9a7.png) - We are querying a system table **sys\_tablecolumn** to fetch the columns with ‘prompt’ in its name. This is the best way to identify a prompt column in your Workday objects. - Now go ahead and preview the data: ![c7b66163-aaec-4a27-b579-4e15d07304dc.png](https://us1.discourse-cdn.com/cdata/original/1X/cae4afbacb9b0cb4d658eb077d721113a1a6cfc2.png) This will list all the prompt columns for the  **‘Journals’** table. As we can see, here are the prompt columns: 1. accountingDateOnOrAfter\_Prompt 2. accountingDateOnOrBefore\_Prompt 3. company\_Prompt Once you know the available prompts, you can query the related tables to retrieve the appropriate values (typically an “id” field) for subsequent prompts. The first two are DateTime Columns that define a range for which you wish to fetch the journal’s data. The third one is a company ID that is fetched from the **‘Companies’** view. **2.) Get Company ID:** - In your job, add a Task for  **Companies:** ![1080147b-7388-43ba-acbf-b45759d9d3be.png](https://us1.discourse-cdn.com/cdata/original/2X/3/39205a1386258b85af5ab2aace7b73b2a191d7f1.png) - Click on the Task Name -  **‘Companies’**  and navigate to  **Preview,**  and Execute to fetch data: ![d1f325f6-d373-4a72-8071-92ed0415b1e1.png](https://us1.discourse-cdn.com/cdata/original/2X/e/e83680080e926da01e5de78953dd1cd0ccc30efd.png) - This should give you the Company Name and respective Company ID. Pick the Company ID you wish to fetch the data from. **3.) Filter Calculated Fields:** - Navigate to  **Workday**  connection, go to the  **Advanced**  section, under  **Miscellaneous, and**  find  **‘Metadata Filters’**  and set it to  **‘Calculated’.** - This will filter all the Calculated Fields when fetching metadata: [Workday Connector for CData Sync - Workday Connector for CData Sync](https://cdn.cdata.com/help/JWJ/synch/default.htm#RSBWorkday_p_MetadataFilters) ![2abcbfc2-0e40-4311-8f82-2e1a45ef9be3.png](https://us1.discourse-cdn.com/cdata/original/1X/1db4f48e78cb74a8b3af39600b63ed909f2f7e67.png) - This will avoid the error you were getting similar to this: **4.) Replicate Journals Data:** - Modify the task for ‘ **Journals’.** - Modify the Query to the following: ```sql REPLICATE [journals] SELECT * FROM journals WHERE company_Prompt = 'cb550da820584750aae8f807882fa79a' ANDaccountingDateOnOrAfter_Prompt = '2023-01-01' AND accountingDateOnOrBefore_Prompt = CURRENT_TIMESTAMP() ``` ![caecbdc8-8d21-4cab-8586-63c458d39f3b.png](https://us1.discourse-cdn.com/cdata/original/2X/4/42b710de46f4971381fd80b68b141397946cd6da.png) - This query uses three prompts: - **Company\_Prompt**  = Company ID we fetched from Step 2 - **accountingDateOnOrAfter\_Prompt**  = Start Date when you wish to fetch Journals data from. - **accountingDateOnOrBefore\_Prompt**  = End Date when you wish to fetch Journals data. I have set it to _**CURRENT\_TIMESTAMP()**_ which is a dynamic value for today’s date. - **Preview:** ![ea749d80-a347-4e49-8643-88bdf15f6e9b.png](https://us1.discourse-cdn.com/cdata/original/2X/3/37015aeec55fc5e5e8103c291f18e8754d917b17.png) **5.) Execute the Job:** - Once the queries have been modified correctly, execute the job: ![065c82a4-f9f1-488a-95c8-fad71fdbb65a.png](https://us1.discourse-cdn.com/cdata/original/2X/a/a153984bb2a4fbba250449b38dc3c2ea08fb16b2.png) - **Preview in Snowflake:** ![97614d20-6caa-444c-927e-de52ecbf16c4.png](https://us1.discourse-cdn.com/cdata/original/1X/ac85b043c512bc85bd4392292cb111bd7855dd12.png) ## **More Information & Free Trial**   The tips above will help you get the most out of our CData connections to Workday, improving performance and following the integration recommendations of Workday experts. To get more information about working with your Workday data through CData’s connectivity solutions, check out our [Workday integration page](https://www.cdata.com/solutions/workday). Sign up for a [free trial of Sync](https://www.cdata.com/sync/trial/) or download a trial of any of our [drivers or connectors](https://www.cdata.com/drivers/workday/download/) to get started with easier access to your Workday data today! ---
**Author:** ![Ankit\_Singh](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ankit_singh/32/1614_2.png) [@Ankit\_Singh](https://community.cdata.com/u/Ankit_Singh)\ **Post date:** [May 16, 2024, 1:36pm UTC](https://community.cdata.com/t/replicate-workday-wql-data-to-snowflake-using-workday-prompts-without-errors/711/2 "2024-05-16T13:36:27Z")
**UPDATE:** If you wish to fetch data from multiple Company IDs (Step 2), you can enable **SplitSingleValuePrompts** in Workday connection: [Workday Connector for CData Sync - Workday Connector for CData Sync](https://cdn.cdata.com/help/JWJ/synch/default.htm#RSBWorkday_p_SplitSingleValuePrompts). This will allow you to query data for multiple queries using a Nested SELECT Statement like this: ![d3888198-1c51-495b-9218-f707b5bc2901.png](https://us1.discourse-cdn.com/cdata/original/1X/0e0bc88580181f5e8b7d1921e1e0f935af71b8b1.png)