Bring Snowflake data to Adaptive

There are 3 ways to bring data from Snowflake into Adaptive:

  1. Use Adaptive's native integration module  >>> that's what I will cover in this post <<<
  2. Use third-party integration platforms such as Tray.ai, Workato, Boomi, etc.
  3. Host and Write your own script (in Python, Powershell, C#, you name it!)


This article focuses on point 1: Adaptive's integration module.

Within Adaptive's native integration module there are also 3 possibles methods! I will explain the pros and cons of each and will give more details on the easiest one! From within Adaptive, you can connect to Snowflake, by setting up one of these data sources: 

  1. JDBC 
  2. ETL (Scripted Data Source)
  3. CCDS (Custom Cloud Data Source)    >>> preferred method, explained below <<<


As of December 2024!

My preferred methodology is the CCDS using AWS S3, since Snowflake and S3 have super easy native integration within Snowflake. And Adaptive has a dedicated library to connect to S3.


Preferred Adaptive to Snowflake integration: CCDS + AWS S3

Adaptive will fetch your Snowflake data from a file on AWS S3 (or an SFTP); S3 being preferred due ot the ease of integration.

I will provide below, links to the documentation on the Snowflake <> AWS S3 integration. So keep reading.

❗As of today: you can't make HTTPS web requests from Adaptive's CCDS to Snowflake! Adaptive’s library is limited for HTTPS requests and it can’t work with Snowflake and you can't add external JavaScripts libraries! But maybe it will come down the road?


Workflow

Your Snowflake/Data team writes a small script that will trigger the queries, generate a CSV output file and save it in AWS S3 (on a scheduled basis for example!). Then on Adaptive's end, a simple JavaScript will fetch the file and push it to your sheets!


Snowflake's documentation

You will find here the key links on how to setup things on Snowflake's side:


Other integrations within Adaptive, pros and cons

As mentioned, there are for now 3 ways to integrate with Snowflake. Here are the pros and cons of each (including the CCDS example from above).


Datasource Requires Agent? Requires Pentaho Kettle? Requires Javascripting? Comments
JDBC Yes πŸ”΄ No 🟒 No 🟒 Requires install of data agent (more info below) + its drivers to manage the connection. It's a bit of an old way to do things. Sometimes the Agent service may stop running! Def. not my favorite!
ETL Yes πŸ”΄ Yes πŸ”΄ No 🟒 Requires install of data agent + Pentaho (more info below). Snowflake will deposit the data into the server where this agent runs. Pentaho transforms data for Adaptive. Definitely not recommended... Plus I heard rumors that this may retire!
CCDS No 🟒 No 🟒 Yes πŸ”΄ Setup explained above. Minimal Javascripting in Adaptive to fetch the CSV file. Recommended and preferred method: simple and quick to setup.

Data Agent and Pentaho

The Data Agent is a component of Integration that runs on a server, extracting data from JDBC-compliant databases or custom data sources, and can also export data from Adaptive Planning. It requires a Windows server behind the customer's firewall, operating as a hosted service to manage access to on-premises apps and connections to Adaptive Planning in the cloud.

The Agent Service, a Windows Service launching a Java application, manages the data agents by polling the Adaptive Planning Gateway, which communicates with the Agent Service through the firewall. Multiple data agents can be hosted by a single Agent Service instance.

The installation includes a Data Agent Service Manager for setup and configuration, and optionally, Pentaho Kettle for ETL processes. If used, the Agent Service manages Kettle ETL job runners in Java virtual machines. Adaptive Planning provides plugins for integrating Kettle with the cloud. Pentaho components are only needed for Kettle scripts; otherwise, JDBC-compliant databases are used.

Data agents are managed through the agent UI in the cloud, accessible via a web browser. The UI allows customers to install, provision, suspend, resume, and upgrade data agents, as well as monitor their status and version.


More info from Workday's Adaptive documentation: 



Comments

Post a Comment

Popular posts from this blog

Adaptive REST API Calls for Platform Customers with User Sync Enabled

How to Make Adaptive REST API Calls for Platform Customers with User Sync Enabled If you’re a platform customer (HCM + FINS + Adaptive) or using Workday HCM or Financials alongside Adaptive and want to create custom scripts leveraging Adaptive’s REST API (XML), you’ve likely encountered a key requirement: providing Adaptive user credentials (username and password). For standalone customers or those not using user sync, this typically involves creating an integration user in Adaptive. Easy peasy.  But what if you’re using user sync? How do you supply credentials for API calls in the XML input? In this article, I’ll guide you through the steps to generate the necessary credentials when user sync is enabled. I’ll also share helpful links to official documentation to ensure a smooth integration process. 1. Overview The process allows Workday-synchronized users to make API calls to Adaptive Planning using Workday credentials . The key steps involve: Generating an OAuth token using Work...
Read more

Introduction to Adaptive's API

To integrate data in Workday Adaptive Planning, customers typically use the integration module to connect with ERPs, SFTPs, AWS S3, Snowflake, SalesForce, Excel, and Google Sheets, etc.  You can also use third party platforms such as Tray.ai , Boomi , Matilion etc. which leverage Adaptive's APIs in a user friendly setup.  And lastly, you can write your very own scripts (C#, Python, Powershell...) to make these API calls.  Most  common Adaptive's API use cases Import Data from ERPs Actual summaries Actual transactions Assets, amortization, depreciation Transactional details by Supplier Payroll details and taxes Import Sales data from CRMs or datalakes Pipeline, churn, opportunities and customer list Billings and bookings ARR and MRR Import Headcount data from an HRIS Current headcount Terminated Hired, not started Wages and compensation plans Benefits Other Demographics Import Other data: Exchange Rates Weather Import Meta data: currencies, attributes, dimensions such...
Read more

What is OAuth 2.0 and how to set it up in Adaptive?

Image
Uh Oh... OAuth? Was ist das? Adaptive can connect with other applications's via REST APIs using the OAuth 2.0 authentication.  I will explain what OAUTH is so you will no longer ask: "What is this?" (Was ist das?) and feel like someone is talking a foreign language to you!   What is OAUTH 2 really? How does it work and what do you need to do in Adaptive? Let me give you 2 sets of explanations: a real life illustration to demistify OAuth 2.0 and a more technical summary. And lastly high level steps on the setup in Adaptive. If you have no idea what OAUTH is, continue reading, otherwise jump to the 2nd section.  In this example: imagine you need to connect to Microsoft Sharepoint and you want to bring CSV files  into Adaptive. 1) Illustrational explanation for total newbs: OAuth Access vs Hotel Access! OAuth 2.0 can sound overwhelming at first, but it's essentially a way for applications to securely talk to each other and access data. Let me break it down into sim...
Read more