Fivetran has a myriad of robust, custom connectors designed explicitly for commonly used sources; however, as a developer, you may find yourself in need of data from outside of this list of Fivetran-supported sources.Â
To solve this challenge, Fivetran offers cloud function connectors. These connectors allow developers to construct functions that extract data from their desired sources (typically through API endpoints).Â
Fivetran then consumes data from these functions and writes to the desired source, along with all the perks that Fivetran offers. At the time of writing this article, Fivetran supports cloud function connectors for Amazon Web Services (AWS), Microsoft Azure, and Google Cloud.
Conveniently, API credentials can be passed from Fivetran to these functions through a Secrets functionality within Fivetran.Â
In this blog, we’ll cover a few use cases for Fivetran Secrets and explore methods of how to implement them for yourself.
What are Secrets in Fivetran?
Secrets in Fivetran are a user-defined set of key-value pairs maintained in Fivetran and passed to the function on each request message. Although intended for API credentials, these key-value pairs can be leveraged to parameterize your function, thus adding customized behaviors specific to a connector’s Secrets values.
Use Cases for Parameters as Fivetran Secrets
Data Engineers commonly encounter scenarios where a single source system must write to a collection of destination database objects. If said source system does not have a native Fivetran connector type, cloud functions are commonly the connector type of choice. However, this selection lends itself to multiple design options.Â
The diagrams below demonstrate multiple cloud function/connector designs along with the pros and cons of each. Configuration C offers an organized and decoupled system design through the use of Fivetran Secrets as cloud function parameters.
Pro: Fully decoupled (failures are isolated)
Con: Redundant code and function/connector sprawl
Pro: Simple system design
Con: Heavy coupling (one failure means everything fails)
Pro: Reduced function sprawl, decoupled system designÂ
Con: Potential connector sprawl
To minimize cloud function sprawl while retaining the advantages of a decoupled system, one could design a system wherein a collection of Fivetran connectors call a single cloud function. Using parameters stored as Fivetran Secrets, each connector can then specify the desired operations carried out by the cloud function.Â
When combined with the native concurrent execution capabilities of cloud functions, teams can use this approach to reduce the sprawl of cloud functions in their cloud environment without compromising the advantages of a decoupled system. Furthermore, parameterizing your cloud function can increase its flexibility.
How to Create Secrets for an AWS Lambda Python Function Using the Fivetran User Interface
The following walkthrough demonstrates how developers can configure Secrets as function parameters with a simple AWS Lambda Python function example. Lastly, translating these Secrets to Infrastructure as Code (IAC) is made possible using Terraform, also detailed below.
Once a connector destination is selected from the Add Connector interface, select the AWS Lambda option for the connector type.
In the subsequent setup page, select + Add Secrets and configure your desired Secret name and value. Since we are using these Secrets exclusively as parameters in this example, we will name the pair
parameter_1with valueparameter_1_value_1. Complete the other setup fields to align with your AWS Lambda function configuration.
When the Fivetran connector attempts to call your Fivetran code, the request will resemble the following configuration (first code snippet below); therefore, one can structure the cloud function Python code to leverage the Secret value as a parameter (second code snippet below). The response to Fivetran is then configured specific to the parameter value.
{
"agent": "FivetranAWS Lambda Connector/<external_id>/<schemas>",
"state": {},
"secrets": {
"parameter_1": "parameter_1_value_1"
},
"sync_id": "467b681-c457-4118-bbc1-25d8e02aq453"
}
def get_data(request):
if request['secrets']['parameter_1'] == 'parameter_1_value_1':
results = 'parameter_1_value_1_result' # replace this with desired api call/behavior of your function
elif request['secrets']['parameter_1'] == 'parameter_1_value_2':
results = 'parameter_1_value_2_result' # replace this with desired api call/behavior of your function
return results
def lambda_handler(request, context):
data = get_data(request)
# Setup response to Fivetran, in this scenario, the table Fivetran writes to is dependent on the parameter_1 value
insert = {}
insert[str(request['secrets']['parameter_1'])] = data
state = {}
schema = {}
schema[str(request['secrets']['parameter_1'])]['primary_key'] = 'id' # Enter the desired primary_key
response = {}
response['state'] = state
response['schema'] = schema
response['hasMore'] = False
response['insert'] = insert
# Return response to Fivetran
return(response)
Configuring Fivetran Connector Secrets Using Terraform
When using the Fivetran user interface, Secret values are encrypted to the end user view. However, when translated to the Terraform Infrastructure as Code (IAC) solution, developers can retain visibility of the Secret values.Â
The image below is a Terraform Yaml file snippet for the above scenario.Â
In this scenario, two connectors call a single, shared cloud function (named shared_cloud_function). The Secrets portion of the connector configuration houses the parameters leveraged in the example.
...
- schema: "fivetran_connector_1"
function: "shared_cloud_function"
secrets_list: [
{
key: "parameter_1"
val: "parameter_1_value_1"
}
]
- schema: "fivetran_connector_2"
function: "shared_cloud_function"
secrets_list: [
{
key: "parameter_1"
val: "parameter_1_value_2"
}
]
...
Conclusion
Fivetran Secrets provides an excellent mechanism for storing and conveying cloud function configuration parameters. Although natively intended to act as credentials for integration access, a little out-of-the-box thinking and they can be repurposed for added cloud function capabilities.Â
Using the above overview and example, we hope that you are empowered to leverage this feature in your future Fivetran solutions!
Looking for more help with Fivetran?
As Fivetran’s Partner of the Year, phData can help your organization uncover sustained success with the Fivetran platform. Contact us today for guidance, automation, and support.Â
