ClickUp - Snowflake QuickStart Template Implementation

  • Updated

Summary

This integration template establishes two flows to export data from ClickUp to Snowflake. Both flows utilize ClickUp as the source and Snowflake as the destination. At Nova Module, we used these flows ourselves. We used DF1 to backup our product management data in Snowflake that was migrated to ClickUp from Asana when we moved to ClickUp. DF2 was used to push our internal knowledge base from ClickUp to Snowflake. You can use this template as a starting point. Modify these flows to fit your business needs. 

Flows

  1. DF1: Export Folders, Lists, Tasks & Comments from Space: Exports all folders, lists, tasks, and task comments within a specified ClickUp space.
  2. DF2: Export Lists, Tasks & Comments from Folder: Exports all lists, tasks, and task comments within a designated ClickUp folder.

Prerequisites

  • ClickUp Account: A ClickUp account with API access enabled.
  • Snowflake Account: A Snowflake account with appropriate permissions for data loading.
  • Snowflake Configuration: Create tables in Snowflake. This template utilizes the following tables to store the exported data. The table structures can be customized based on your requirements. However, ensure any changes are reflected in the Snowflake imports and lookups.

create or replace TABLE CLICK_UP_INTEGRATION.CLICK_UP_INTEGRATION.CLICKUP_SPACE (

ID NUMBER(38,0) autoincrement start 1 increment 1 noorder,

RECORD_ID NUMBER(38,0),

RECORD_NAME VARCHAR(16777216)

);

 

create or replace TABLE CLICK_UP_INTEGRATION.CLICK_UP_INTEGRATION.CLICKUP_FOLDERS (

ID NUMBER(38,0) autoincrement start 1 increment 1 noorder,

RECORD_ID NUMBER(38,0),

RECORD_NAME VARCHAR(16777216),

SPACE_ID VARCHAR(16777216),

SNOWFLAKE_SPACE_ID NUMBER(38,0)

);



create or replace TABLE CLICK_UP_INTEGRATION.CLICK_UP_INTEGRATION.CLICKUP_LISTS (

ID NUMBER(38,0) autoincrement start 1 increment 1 noorder,

RECORD_ID NUMBER(38,0),

RECORD_NAME VARCHAR(16777216),

SPACE_ID VARCHAR(16777216),

FOLDER_ID VARCHAR(16777216),

SNOWFLAKE_SPACE_ID NUMBER(38,0),

SNOWFLAKE_FOLDER_ID NUMBER(38,0)

);

 

create or replace TABLE CLICK_UP_INTEGRATION.CLICK_UP_INTEGRATION.CLICKUP_TASKS (

ID NUMBER(38,0) autoincrement start 1 increment 1 noorder,

RECORD_NAME VARCHAR(16777216),

RECORD_DESCRIPTION VARCHAR(16777216),

RECORD_CONTENT VARCHAR(16777216),

RECORD_STATUS VARCHAR(16777216),

RECORD_PARENT VARCHAR(16777216),

SPACE_ID VARCHAR(16777216),

FOLDER_ID VARCHAR(16777216),

RECORD_ID VARCHAR(16777216)

);

**in ClickUp, the Task ID is alphanumeric

create or replace TABLE CLICK_UP_INTEGRATION.CLICK_UP_INTEGRATION.CLICKUP_COMMENTS (
        ID NUMBER(38,0) autoincrement start 1 increment 1 noorder,
        RECORD_ID NUMBER(38,0),
        COMMENT VARCHAR(16777216),
        COMMENT_TEXT VARCHAR(16777216),
        RECORD_PARENT VARCHAR(16777216)
);

 

Setting Up the Integration

  1. Download the Integration Template: In integrator.io, navigate to the Marketplace, find the “ClickUp - Snowflake” template and click “Install”
  2. Configure Connections by following the installation steps:
    • ClickUp: Provide your ClickUp API key and access token. Refer to ClickUp's documentation for API access instructions: https://clickup.com/api/
    • Snowflake: Enter your Snowflake account details including username, password, account name, warehouse, and database schema. For additional help, please refer to Celigo’s help article Set up a connection to Snowflake 
  3. Update Settings: Navigate to the settings of the “ClickUp - Snowflake” integration tile and update the following
    • spaceID (for DF1): Specify the ClickUp Space ID for exporting all its data.
    • folderID (for DF2): Indicate the ClickUp Folder ID for exporting its contents.

  1. Data Mapping: If your Snowflake schemas differ from our samples above, be sure to update the SQL of each Snowflow Import step as well as Snowflake Lookup steps. SQL statements were used in order to get the IDs of the rows inserted into Snowflake so they could be used in other tables downstream. On our imports, we did not use the bulk insert option as our data volume was low. Feel free to switch to “Use bulk insert SQL query” for large data volumes. If you make this change, make sure you use the mapper to add the data mappings.

  2. Test: After configuration, test each data flow to ensure data is imported into Snowflake.
  3. Run: Once you are satisfied with the results of the test runs, you can trigger the flows manually. Note: These are use cases from our own internal integrations where we only imported spaces and folders that are no longer used. If you need to run the flows on a scheduled basis, you can include filter parameters for the Task steps like the “Get Tasks for each List” using the date created or date updated. Unfortunately, at the time of this integration, ClickUp does not support date based filtering for Spaces, Folders, Lists and Comments. Be sure to include additional filtering to prevent duplicate rows of spaces, folders, lists and comments, if the flows will be run multiple times.

Reminders

  • This template provides basic flows for exporting ClickUp data. You can modify the flows to suit your specific needs.
  • Ensure you have the necessary permissions within ClickUp and Snowflake to access and modify data.
  • If you need further assistance, please contact support@novamodule.com