# ETL Discovery with Apache Airflow *Arthur Kotkowiak and Tom Torres' project* The goal of this project is to create and to orchestrate ETL (Extract, Load, Transform) pipelines between a **data warehouse tool** and a **CRM** (Customer Relationship Management) **tool** for the University of Rhode Island Foundation. The tools and services involved in this project are the following: - [**Salesforce**](https://www.salesforce.com/), the CRM tool - [**Apache Airflow**](https://airflow.apache.org/), more specifically a cloud-based service offered by AWS (Amazon Web Services) called [**Amazon MWAA**](https://aws.amazon.com/managed-workflows-for-apache-airflow/) (Amazon Managed Workflow for Apache Airflow), to create and orchestrate the pipelines - The data warehouse tool has not been decided yet, but it will probably be [**Snowflake**](https://www.snowflake.com/en/) This documentation details the different steps of the project. # Proof of Concept The first step of this project was to establish a **PoC** (Proof of Concept) to show that it is possible to create a basic ETL pipeline between a data warehouse tool and Salesforce using Apache Airflow. As the data warehouse tool had not been chosen at the time, it was decided to use a [**PostgreSQL database**](https://www.postgresql.org/) instead. For **Salesforce**, a **sandbox** populated with mock data is used rather than the instance running in production, which is more appropriate for experimentation and testing. This documentation details the different steps for creating this proof of concept. It can be used as a reference for the remaining of the project, especially to get instructions on how to set up an Airflow environment and how to connect Airflow to different external services. As mentioned in the first paragraph, the goal was to create a pipeline between a data warehouse tool and Salesforce. Before being able to write the code for this pipeline, the tools and services involved had to be set up and connected together. The different steps that were required are listed below, in order: - [Set up and configure Apache Airflow](https://bookstack.foundation.uri.edu/books/summer-internship-program-2026/page/setting-up-apache-airflow) - Connect Airflow to the PostgreSQL database - [Connect Airflow to Salesforce](https://bookstack.foundation.uri.edu/books/summer-internship-program-2026/page/connecting-apache-airflow-to-salesforce) The explanations and technical details for each of these steps can be found in their respective page, listed above. # Setting up Apache Airflow --- *Last updated: 05/22/2026* --- As mentioned in the beginning of this chapter, Amazon Managed Workflows for Apache Airflow, or **[Amazon MWAA](https://aws.amazon.com/managed-workflows-for-apache-airflow/)**, is used for this project. This cloud-based service has been chosen because, even though Airflow is supported by all major operating systems, installing it locally on a server can prove to be very difficult. Indeed, setting up Airflow is much more complicated that simply downloading and installing the software—in reality, it relies on several other services to function properly, most notably a database and a web server for the user interface. The advantage of using Amazon MWAA is that everything is setup, managed and secured by AWS. An overview of the architecture of each Airflow environment is presented [here](https://docs.aws.amazon.com/mwaa/latest/userguide/what-is-mwaa.html#architecture-mwaa). AWS is not the only cloud provider to offer **[Airflow as a Service](https://airflow.apache.org/ecosystem/#airflow-as-a-service)**—another popular provider that makes it available for its users is [Microsoft Azure](https://azure.microsoft.com/en-us/get-started/azure-portal/). ITS chose MWAA because it is already using AWS for most of its operations and systems. This page explains how to configure and launch an Airflow environment using MWAA. It also gives details on how the environment for the proof of concept was set up and configured. ## MWAA As explained above, Amazon Managed Workflows for Apache Airflow is one of the services provided by AWS and is accessible via the dashboard. For now, the URI Foundation does not have its own AWS accounts and shares the accounts used by ITS. To get access to these accounts, permission must be requested to Chi Shen () or Patrick Canole (). Once the right permissions have been granted, the AWS dashboard can be accessed by: - Logging into Microsoft using the URI SSO - Going to the Microsoft "My Apps" page - Clicking on "AWS Single Sign-On" - Choosing the appropriate account and permission ---
**For the proof of concept:**
The account we are using is **"ITS shared non-prod"**, with the permission **"URIPermSet\_FullAdmin"**. This account is only used for testing.
--- Once on the AWS dashboard, Amazon Managed Workflows for Apache Airflow can be accessed by simply typing "MWAA" in the search bar on top of the page. It can also appear in the "Recently visited" section if it has already been accessed. When clicking on "Amazon MWAA", a list of **environments** is displayed. An environment is simply an instance of Airflow isolated from the others that is running in one of the data centers of AWS. The configuration of an environment can be seen by clicking on its name, and the dashboard of Airflow can be accessed by clicking on "Open Airflow UI". ---
**For the proof of concept:**
The environment that was created for the proof of concept is called **"foundation-etl-poc-airflow"**.
--- ### Creating a new Airflow environment If a new environment needs to be created, AWS provides a detailed guide on how to proceed [here](https://docs.aws.amazon.com/mwaa/latest/userguide/get-started.html). The AWS CLI (command line interface) can be used, but it is much simpler and more intuitive to do it using the console. #### Creating a new S3 bucket Before creating a new Airflow environment, an **S3 bucket** needs to be created. [S3](https://aws.amazon.com/s3/) (Simple Storage Service) is another service provided by AWS that is used to store objects in the cloud. As explained in [this part](https://docs.aws.amazon.com/mwaa/latest/userguide/mwaa-s3-bucket.html) of the guide, the S3 bucket will be used to store the requirements file and the DAGs of the Airflow environment. The structure of this bucket will be extremely simple. ```text S3 bucket |___ requirements.txt |___ dags/ |___ first_dag.py |___ second_dag.py |___ ... ``` ---
**For the proof of concept:**
The S3 bucket that was created for the "foundation-etl-poc-airflow" environment is called **"foundation-etl-poc-bucket".**
--- #### Creating a new VPC network Technically, there should be no need to create a new VPC (Virtual Private Cloud) network. When configuring the new Airflow environment, the VPC called "MWAAEnvironment" can be selected. #### Configuring the new Airflow environment To configure the new Airflow environment, only its name, the version of Airflow (it is recommended to select the latest available version) and the paths to the requirements file as well as the DAGs folder need to be specified for the details. For the advanced setting, once the VPC called "MWAAEnvironment" has been selected, everything else can be left as is, unless some other settings need to be modified in order to meet the requirements for the environment. Once the configuration has been reviewed, the environment can be created—this process will take between 20 and 30 minutes to complete. ---
**For the proof of concept:**
In the advanced settings, under **"Web server access"**, **"Public Network (Internet Accessible)"** was selected. This setting makes the Airflow UI of the environment easily accessible on web browsers via Internet.
As the environment created for the proof of concept only has to run a few simple DAGs occasionally, the smallest "**Environment class"** was selected (**mw1.micro**).
--- ### Updating an Airflow environment The configuration of an Airflow environment can be edited and updated at any time by simply clicking on "Edit". Like the creation process, the environment will take between 20 and 30 minutes to update. If the requirements file of an Airflow environment is modified (i.e. some Python libraries are added or removed), this environment **needs** to be updated. This can be done by clicking on "Edit", selecting the appropriate version of the requirements file and updating the environment. Once again, this process will take between 20 and 30 minutes. If the environment is not updated, the Python libraries that were added to the requirements file will not be installed automatically and they will not be available—it they are imported in a DAG, it will result in an error. ### MWAA Local Runner If the requirements file is regularly modified during the development phase of a DAG, having to wait up to 30 minutes every time the environment updates can massively slow down work. Luckily, AWS has a GitHub repository that provides "a command line interface (CLI) utility that replicates an Amazon Managed Workflows for Apache Airflow (MWAA) environment locally." > The CLI builds a Docker container image locally that’s similar to a MWAA production image. This allows you to run a local Apache Airflow environment to develop and test DAGs, custom plugins, and dependencies before deploying to MWAA. > > > [GitHub - aws-mwaa-local-runner](https://github.com/aws/aws-mwaa-local-runner#readme:~:text=CLI-,The,MWAA,-%2E) As rebuilding the Docker image only takes a couple of minutes, this local Airflow environment updates way faster than Airflow environments running in the cloud.

The latest available Airflow version on the local runner is 2.10. AWS also provides Docker images for later versions but not through the local runner, and the installation process is slightly more complicated. If using the latest Airflow version on MWAA (currently 3.2.1), the user interface will be different on the local runner. The Docker images for Airflow versions above 2.10 can be found [here](https://github.com/aws/amazon-mwaa-docker-images).

This local runner can be installed on MacOS, Linux and Windows with WSL. The instructions can be found on the README of [this GitHub repository](https://github.com/aws/aws-mwaa-local-runner). When the requirements file has been modified, the environment can be updated by stopping the Docker container, building a new image and starting the container again. # Connecting Apache Airflow to Salesforce --- *Last updated: 07/15/2026* --- When a company or a business uses Salesforce for CRM (Customer Relationship Management), it has one or several **orgs**. > \[...\] A Salesforce org is your business’s dedicated workspace within Salesforce. It is a customizable digital environment where your business teams manage customer relationships and automated workflows while securely storing business data and service records. > > > [Smart IT Staff - Salesforce Org Types: Guide to Understanding Which One to Choose and When](https://smartitstaff.com/blog/salesforce-org-types/#:~:text=put%2C-,Salesforce,records.) There are different types of Salesforce orgs, which are listed and explained [here](https://developer.salesforce.com/blogs/2024/05/choose-the-right-salesforce-org-for-the-right-job). In the context of the **proof of concept**, the type of org used is a **sandbox**, which was populated with mock data for testing purposes. Before an Apache Airflow environment can interact with the data stored in a Salesforce org, a connection must be established. This process involves two separate steps : - Configuring the Salesforce org to receive and accept connections coming from external applications - Connecting the Apache Airflow environment to the Salesforce org On Apache Airflow, the connection mechanism is simple and only slightly changes from one connection type to another. On Salesforce, the configuration process involves multiple steps and can be complex at times. This page details the two steps listed above and shows how the Apache Airflow environment and the Salesforce sandbox used in the proof of concept were configured. ## Configuring a Salesforce org to receive external connections On Salesforce, the mechanism to connect third-party applications to orgs is called [**'External Client Apps'**](https://help.salesforce.com/s/articleView?id=xcloud.external_client_apps.htm&type=5). There is a second mechanism called 'Connected Apps', but it is restricted as of Spring '26—existing Connected Apps still work but it is not possible to create new ones. The goal of an External Client App is, as its name implies, to authorize external applications to access specific data and perform predefined actions on a Salesforce org using API calls. The first step in configuring a Salesforce org to receive external connections is to create a new External Client App within that org. ### Creating a new External Client App The Salesforce documentation to create a new External Client App can be found [here](https://help.salesforce.com/s/articleView?id=xcloud.create_a_local_external_client_app.htm&type=5). External Client Apps are managed from the 'Setup' section of Salesforce. When connected to a Salesforce org, this section can be accessed by clicking on the bolt icon located in the top-right corner of the dashboard and then clicking on 'Setup'. Once in the 'Setup' section, the External Client App Manager is located on the left panel, under 'Platform Tools' > 'Apps' > 'External Client Apps' > 'External Client App Manager'. [![Screenshot 2026-05-26 at 12.00.55 PM.png](https://notes.its.uri.edu/uploads/images/gallery/2026-05/scaled-1680-/V1BziiTdVUTWrdYX-screenshot-2026-05-26-at-12-00-55-pm.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-05/V1BziiTdVUTWrdYX-screenshot-2026-05-26-at-12-00-55-pm.png) After clicking on 'External Client App Manager', the list of existing External Client Apps will appear. Each app has a dedicated page, which notably contains the details of its configuration, that can be accessed by simply clicking on its name. The page to create a new External Client App can be accessed by clicking on the 'New External Client App' button located on the top-right corner of the External Client App Manager page. [![Screenshot 2026-05-26 at 2.32.02 PM.png](https://notes.its.uri.edu/uploads/images/gallery/2026-05/scaled-1680-/0sQRy44MY3ODsLE1-screenshot-2026-05-26-at-2-32-02-pm.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-05/0sQRy44MY3ODsLE1-screenshot-2026-05-26-at-2-32-02-pm.png)

In Salesforce, a user that wants to create and manage External Client Apps needs to have a profile with the appropriate permissions: '**Create, edit and delete External Client Apps**', '**View all External Client Apps**' and '**View all External Client Apps, view their settings, and edit their policies**'. These permissions can be granted to a profile by an administrator in the 'System Permissions' of the profile, which can be found in the 'Setup' section (in the 'System' section, at the top of the page), under 'Administration' > 'Users' > 'Profiles'.

[![Second.png](https://notes.its.uri.edu/uploads/images/gallery/2026-06/scaled-1680-/e4BES0fuHgNyeAgy-second.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-06/e4BES0fuHgNyeAgy-second.png) First, basic information for the new External Client App needs to be filled in. It is very straightforward—the new app simply needs a name, an API name (usually the name of the app with underscores instead of white spaces, which should be automatically filled in by Salesforce when the name of the app is typed) and a contact email (can be anything, usually the work email of the developer creating the app). Additional information can be given as well (a contact phone, an icon for the app, etc.). Before moving to the next step, the distribution state of the new app needs to be selected. Once an External Client App has been created and configured in a Salesforce org, it can be packaged and sent to other Salesforce orgs that might want to use it. For this project, and therefore for the **proof of concept** as well, the goal is to create an integration only between an Apache Airflow environment and the Salesforce orgs of the URI Foundation, so the distribution state that was selected is **local**. Indications to package and send an External Client App can be found [here](https://help.salesforce.com/s/articleView?id=xcloud.configure_packageable_external_client_apps.htm&type=5), but it is not covered in this documentation. Once basic information has been filled in, OAuth needs to be enabled. This can be done by clicking on the 'API (Enable OAuth Settings)' tab and checking the box labelled 'Enable OAuth'. After this box is checked, the section to configure OAuth appears. There are other tabs below the 'API (Enable OAuth Settings)' tab, which can be used to configure specific settings for the External Client App. These settings were not needed for the **proof of concept** and will likely not be needed in the future for this project, so they are not covered in this documentation. [![Screenshot 2026-05-28 at 10.42.15 AM.png](https://notes.its.uri.edu/uploads/images/gallery/2026-05/scaled-1680-/cmhtQTP3BcqmoyKT-screenshot-2026-05-28-at-10-42-15-am.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-05/cmhtQTP3BcqmoyKT-screenshot-2026-05-28-at-10-42-15-am.png) ### OAuth Before going over the different steps to configure OAuth, it is necessary to explain what it is. #### What is OAuth? An External Client App can be seen as an entry point through which third-party applications can connect to a Salesforce org in order to access its data and perform actions. Metaphorically, it acts as a door that any entity outside of Salesforce must pass through to reach the org. Without an External Client App, there are no doors, and the org remains inaccessible to external applications. Continuing the metaphor, an External Client App only defines the door itself, not the process for opening it. When an third-party application wants to access a Salesforce org through an External Client App, it first needs to be **authenticated** and **authorized**. To do so, External Client Apps rely on several protocols and standards. **OAuth** is one of them, used for **authorization**. The current version of OAuth is **OAuth 2.0**. > [OAuth 2.0](https://auth0.com/intro-to-iam/what-is-oauth-2), which stands for “Open Authorization”, is a standard designed to allow a website or application to access resources hosted by other web apps on behalf of a user. > > > [auth0 - What is OAuth 2.0?](https://auth0.com/intro-to-iam/what-is-oauth-2#:~:text=OAuth%202.0%2C%20which%20stands%20for%20%E2%80%9COpen%20Authorization%E2%80%9D%2C%20is%20a%20standard%20designed%20to%20allow%20a%20website%20or%20application%20to%20access%20resources%20hosted%20by%20other%20web%20apps%20on%20behalf%20of%20a%20user.&text=OAuth%202.0%20is%20an%20authorization%20protocol%20and%20NOT%20an%20authentication%20protocol.%20As%20such%2C%20it%20is%20designed%20primarily%20as%20a%20means%20of%20granting%20access%20to%20a%20set%20of%20resources%2C%20for%20example%2C%20remote%20APIs%20or%20user%20data) #### Roles OAuth 2.0 provides a mechanism that lets an application access protected resources stored in another application on behalf of a user without having to expose the user's credentials. This mechanism involves four actors with different [**roles**](https://auth0.com/intro-to-iam/what-is-oauth-2#oauth-2-roles:~:text=OAuth2.0%20Roles): - The **Resource Owner**: the user who owns the data and grants access to it - The **Client**: the application that wants to access the data - The **Authorization Server**: the system that authenticates the Resource Owner and issues access tokens to the Client - The **Resource Server**: the system that hosts the data and accepts access tokens to grant access to it #### Access Tokens OAuth 2.0 relies on **Access Tokens**. These tokens are issued by the Authorization Server and let the Client access the protected resources of the Resource Owner stored on the Resource Server for a short amount of time. > An **Access Token** is a piece of data that represents the authorization to access resources on behalf of the end-user. > > > [auth0 - What is OAuth 2.0?](https://auth0.com/intro-to-iam/what-is-oauth-2#:~:text=An%20Access%20Token%20is%20a%20piece%20of%20data%20that%20represents%20the%20authorization%20to%20access%20resources%20on%20behalf%20of%20the%20end-user.) All Access Tokens have **scopes** tied to them. A scope specifies which actions and/or data a token gives access to. Each application that uses OAuth 2.0 defines its own specific scopes. #### How does OAuth work? Before a Client can request any Access Token, it needs to be registered on the Authorization Server. Once registered, it receives a **Client ID** and a **Client Secret**. This way, only Clients that have been registered can request Access Tokens. On Salesforce, creating an External Client App and enabling OAuth is the equivalent of registering a Client on the Authorization Server. The only difference is that it doesn't register one specific Client but rather an entry point, as mentioned earlier, that multiple Clients can connect to if they have the credentials, called **Consumer Key and Secret** instead of Client ID and Secret. Once a Client has been registered on an Authorization Server and received its credentials, it can request Access Tokens. OAuth 2.0 defines different ways for Clients to request and receive Access Tokens. #### Flows or Grant Types A [**flow**](https://auth0.com/docs/get-started/applications/application-grant-types), also called a **grant**, is a set of steps a Client has to go through in order to retrieve an Access Token. These flows exist to address different scenarios. Choosing the appropriate flow for a Client that needs to access resources on a Resource Server depends on the capabilities of the Client, its security requirements, the type of integration that is being built, etc. > Application grant types (or flows) are methods through which applications can gain Access Tokens and by which you grant limited access to your resources to another entity without exposing credentials. The OAuth 2.0 protocol supports several types of grants, which allow different types of access. > > > [auth0 - Application Grant Types](https://auth0.com/docs/get-started/applications/application-grant-types) OAuth 2.0 defines multiple flows, but this page only covers three of them: **Authorization Code**, **Client Credentials**, and **JWT Bearer**. ##### Authorization Code The most common flow is [**Authorization Code**](https://auth0.com/docs/get-started/authentication-and-authorization-flow/authorization-code-flow). It is designed for scenarios where a user (Resource Owner) interacts with a Client through a web browser to grant access to their data. In this flow, the Client must provide its Client ID and its Client Secret to the Authorization Server in order to request Access Tokens, so the Client Secret needs to be stored securely on the server side and never be publicly displayed on the client side. The steps required for this flow are the following: 1. The Client redirects the user to the Authorization Server's login page 2. The user authenticates (e.g., enters their username and password) and approves the requested permissions (equivalent to the scopes mentioned earlier) 3. The Authorization Server redirects the user back to the Client with a short-lived **Authorization Code** 4. The Client sends this Authorization Code to the Authorization Server, along with its own credentials (Client ID and Client Secret), to prove its identity 5. The Authorization Server verifies everything and returns an **Access Token** to the Client 6. The Client uses the Access Token to make requests to the Resource Server on behalf of the user The use of an intermediate Authorization Code, rather than returning the Access Token directly, adds a layer of security—the Access Token is always exchanged on the server side between the Client and the Authorization Server and is never exposed in the user's browser. This is the flow used by web applications that let users log in using Google or Facebook, for example. Once a user logs into their account and authenticate themselves, the Client can access their data on the Resource Server (Google, Facebook or other), such as their email, their username, their profile picture, etc.

OAuth 2.0 is an **authorization protocol** and not an authentication protocol. In the Authorization Code flow, users (Resources Owners) have to authenticate themselves on the Authorization Server in order to grant access to their personal data to the Client. For the authentication process, Authorization Servers can use different protocols. Most modern web apps use [**OpenID**](https://openid.net/developers/how-connect-works/), which relies on OAuth 2.0 to provide authentication and authorization at the same time.

When registering a new Client that will use the Authorization Code flow on an Authorization Server, a **callback URL** needs to be provided. This URL points to a page of the web application (Client) the Authorization Server will send the Authorization Code and redirect the user to once it has been authenticated. For certain web applications that cannot store securely their Client Secret, an even more secure version of this flow exists, called [**Authorization Code Flow with Proof Key for Code Exchange** **(PKCE)**](https://auth0.com/docs/get-started/authentication-and-authorization-flow/authorization-code-flow-with-pkce). It is not covered on this page. ##### Client Credentials The [**Client Credentials**](https://auth0.com/docs/get-started/authentication-and-authorization-flow/client-credentials-flow) flow, also very common, is much simpler than the Authorization Code flow. It is designed for server-to-server integrations—scenarios where no user is involved and the Client needs to access resources stored in a Resource Server on its own behalf, most commonly for automation purposes. The steps required for this flow are the following: - The Client sends its credentials (Client ID and Client Secret) directly to the Authorization Server - The Authorization Server verifies the credentials and, if valid, returns an **Access Token** to the Client - The Client uses this access token to make requests to the Resource Server In this flow, there is no additional authentication process needed. The Client will always access the resources of the same user account (Resource Owner) on the Resource Server—usually, this account is defined during the registration process on the Authorization Server. However, as in the Authorization Code flow, the Client must provide its Client ID and its Client Secret, so the Client Secret must be stored securely on the server. ##### JWT Bearer The **[JWT (JSON Web Token) Bearer](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_oauth_jwt_flow.htm&type=5)** flow offers the advantages of both flows presented above, all while being simpler than the Authorization Code flow and more secure than the Client Credentials flow. It is also designed for server-to-server integrations, but lets the Client act on behalf of different users without requiring any interaction—users do not have to manually log into their account to grant access to their data to the Client. The steps required for this flow are the following: - The Client generates a [**JWT (JSON Web Token)**](https://www.jwt.io/introduction#what-is-json-web-token) containing information such as its identity, the user it wants to act on behalf of, and an expiration time - The Client signs the JWT using a **private key**. The corresponding **public key** (or certificate) has been previously registered with the Authorization Server - The Client sends the signed JWT to the Authorization Server. - The Authorization Server verifies the JWT's signature using the registered public key and checks its contents - If everything is valid, the Authorization Server returns an **Access Token** to the Client - The Client uses this access token to make requests to the Resource Server This flow can only be used if a trust has been established between the Client and the Authorization Server during the registration process, which involves [**public-key cryptography**](https://www.ssl.com/article/what-is-public-key-cryptography/). To request Access Tokens to the Authorization Server, the Client must provide its Client ID but not its Client Secret—it will prove its identity by using a **private key** instead. ### Configuring OAuth in an External Client App As explained in the previous section, OAuth 2.0 involves four actors with different roles. In this project, these actors are: - The **Client**: the Apache Airflow environment - The **Authorization Server**: the External Client App - The **Resource Server**: the Salesforce org The **Resource Owner** depends on the flow that is selected and the **policies** that are selected for the External Client App. #### Configuring OAuth settings The section to configure OAuth contains several subsections. ##### App Settings On top of this subsection can be found a button to access the **Consumer Key and Secret** of the External Client App. Accessing these credentials requires a verification code that is sent by email. The **Consumer Secret** must remain secret. In the **Callback URL** text area, the **callback** **URL(s)** of the External Client App can be filled in. As explained in the previous section of this chapter, a callback URL is only needed for the Authorization Code flow. As the connection between Apache Airflow and Salesforce is a **server-to-server integration**, this flow will not be used and therefore there is no need for a callback URL. However, this field is mandatory in Salesforce, no matter what flow is selected. To bypass this requirement, a dummy URL can be used—it can be anything, as long as it has the structure of a valid URL, otherwise Salesforce will display an error message and not save the configuration. This solution was chosen for the **proof of concept**. Under **OAuth** **Scopes**, the **scopes** of the External Client App can be selected. On the right, the list of all **available OAuth scopes** can be found. These scopes give access to different services, APIs, actions and/or data to third-party applications. By using the arrows, scopes can be moved from one list to another—to select a scope, it needs to be moved to the list on the left, '**Selected OAuth Scopes**'. Each External Client App will have different scopes selected according to various factors, including the capabilities of the third-party applications and the type of integration that is being built—a description of each available scope can be found [here](https://www.nudgesecurity.com/oauth-scopes/salesforce). However, for most server-to-server integration, **'Manage user data via APIs (api)**' or **'Full access (full)**' and **'Perform requests at any time (refresh\_token, offline\_access)'** should be sufficient. For the **proof of concept**, only **'Manage user data via APIs (api)'** and **'Perform requests at any time (refresh\_token, offline\_access)'** were selected. The two checkboxes, '**Introspect all Tokens**' and '**Configure ID** **token**', can be used to enable or disable settings related to the OpenID protocol. This protocol is not used in the **proof of concept** and will likely not be used in the future for this project, so the settings tied to these checkboxes are not presented more in detail in this documentation. If needed, more information can be found [here](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_oidc_token_introspection_endpoint.htm) and [here](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_oauth_tokens_scopes.htm). [![Screenshot 2026-06-01 at 1.19.11 PM.png](https://notes.its.uri.edu/uploads/images/gallery/2026-06/scaled-1680-/TpaEpE2mbaZZpVFX-screenshot-2026-06-01-at-1-19-11-pm.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-06/TpaEpE2mbaZZpVFX-screenshot-2026-06-01-at-1-19-11-pm.png) ##### Flow Enablement In this subsection, the **OAuth** **flow(s)** of the External Client App can be enabled. Five different flows are available: **Client** **Credentials**, **Authorization Code**, **Device**, **JWT Bearer** and **Token Exchange**. A single External Client App can enable several flows simultaneously—for this project, only one is needed. The **Client Credentials flow**, the **Authorization Code flow** as well as the **JWT Bearer flow** have already been presented in a previous section of this chapter. The **[Device flow](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_oauth_device_flow.htm)** can be used for IoT (Internet of Things) integrations and the **[Token Exchange flow](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_token_exchange_overview.htm)** can be used for complex integrations that involve multiple applications connected to each other. These flows are not relevant for this project, so they are not presented in further details in this documentation. Except for the JWT Bearer flow, which requires additional steps, enabling an OAuth flow is extremely straightforward: the checkbox associated to the flow simply has to be checked. As mentioned in the previous section, the connection between Apache Airflow and Salesforce is a **server-to-server** **integration**. Therefore, the Authorization Code flow will not be used in this project, and only two flows are left: **Client Credentials flow** and **JWT Bearer flow**. For the **proof of concept**, the Client Credentials flow was first selected. However, the Salesforce provider in Apache Airflow (explained in a following section on this page) does not support it. Even though this flow is very straightforward, it would have required to write the code to establish the connection, which would have taken a long time and would have increased the risk of vulnerabilities. Instead, the **JWT Bearer flow**, which is more secure and supported by the Salesforce provider in Airflow, was enabled. In the presentation of the JWT Bearer flow (see previous section), it is mentioned that a trust needs to be established between the Authorization Server and the Client during the registration process, which involves [**public-key cryptography**](https://www.ssl.com/article/what-is-public-key-cryptography/). Therefore, after the checkbox is checked, a **certificate** needs to be uploaded. An [**X.509 certificate**](https://www.ssl.com/faqs/what-is-an-x-509-certificate/) ties an identity to a **public key**. A certificate is usually signed by a trusted third-party, called a **certificate authority**, to prove its authenticity. For the JWT Bearer flow in Salesforce, a certificate is simply a practical way of encapsulating and storing a public key–Salesforce will not verify the signature. Therefore, what is called a **self-signed certificate** can be used. Before creating such a certificate, a **key pair** must be generated. By using [**OpenSSL**](https://www.openssl.org/) on Linux, MacOS or Windows with WSL, the key pair and the certificate can be generated in a single command: ```bash openssl req -x509 -newkey rsa:2048 -keyout key.pem -out cert.pem -sha256 -nodes -days 365 ``` The number of days (after `-days`) the certificate will be valid for can be increased or reduced if necessary. If the private key must be protected by a password, `-nodes` has to be removed—a prompt will ask to enter a password after the command is executed. After the command is executed, several prompts will ask to fill in some details about the certificate (address, organization name, common name, email address). In the context of the JWT Bearer flow in Salesforce, these details are complementary and most fields can be left blank—the only one that cannot be blank is the 'Common Name', but its content does not matter and anything can be entered. After the certificate has been generated, it can be uploaded to Salesforce. For the **proof of concept**, \[TO DO: indicate where the key pair and the certificate were generated as well as the names of the files\].

Salesforce does not offer the possibility to upload more than one certificate per External Client App. Since an X.509 certificate contains a single public key and each public key is tied to exactly one private key, this limitation creates challenges when multiple Apache Airflow environments running on different machines need to access the same External Client App. A private key is a sensitive piece of data that must remain confidential at all times. Once generated, it should be stored securely and never be moved or shared. Several Airflow environments running on the same machine or AWS account can safely share the same private key, as long as it is not duplicated or moved. However, a private key must never be reused across environments running on different machines or AWS accounts. When multiple Airflow environments need to access the same Salesforce org, the only secure solution is to create a separate External Client App for each environment, using identical settings but a dedicated key pair and certificate for each environment. As a best practice, it would be recommended to follow this approach even when the environments are running on the same machine or AWS account.

##### Security In this subsection, additional **security settings** for different OAuth flows can be enabled or disabled. If the first checkbox, '**Require secret for Web Server Flow**', is checked, the Consumer Secret (Client Secret) must be provided to the Authorization Server by the Client in order to retrieve an Access Token—[Web Server Flow](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_oauth_web_server_flow.htm) is another name given to the Authorization Code flow by Salesforce. As the **proof of concept** does not use this flow, this setting was not enabled. If the second checkbox, '**Require secret for Refresh Token flow**', is checked, the Consumer Secret (Client Secret) must be provided to the Authorization Server by the Client in order to retrieve a **Refresh** **Token**. A [Refresh Token](https://oauth.net/2/refresh-tokens/) is another type of OAuth token, valid for a longer period of time than an Access Token, which can be used by the Client to request a new Access Token without requiring any user interaction—the user does not have to log in again. This mechanism is mostly used by the Authorization Code flow, but it can be useful to any OAuth flow that requires users to log in manually for authentication—server-to-server integration flows, such as the Client Credentials flow or the JWT Bearer flow, do not need Refresh Tokens. In Salesforce, Refresh Tokens are used by the Authorization Code/Web Server flow and the User-Agent flow, but the latter has been deprecated. In the [Salesforce documentation](https://help.salesforce.com/s/articleView?id=xcloud.remoteaccess_oauth_refresh_token_flow.htm) and in the [OAuth documentation](https://oauth.net/2/grant-types/refresh-token/), the Refresh Token flow is considered a distinct flow, equivalent to the other flows mentioned on this page. The only difference is that it is not a standalone flow—it cannot be used to request and retrieve the first Access Token. The Refresh Token flow can only be used once a user has authenticated themselves and the Client has retrieved an Access Token as well as a Refresh Token using the Authorization Code/Web Server flow. As the **proof of concept** does not use this flow, this setting was not enabled. The third checkbox, '**Require Proof Key for Code Exchange (PKCE) extension for Supported Authorization Flows**', can be checked to enable the more secure version of the Authorization Code/Web Server flow, called [**Authorization Code Flow with Proof Key for Code Exchange** **(PKCE)**](https://auth0.com/docs/get-started/authentication-and-authorization-flow/authorization-code-flow-with-pkce), which is briefly mentioned in a previous section of this page. As the **proof of concept** does not use this flow, this setting was not enabled. The fourth checkbox, '**Enable Refresh Token Rotation**', can be checked to make the Refresh Token flow more secure—each time the Client requests an Access Token using a Refresh Token, a new one is issued and the old one is invalidated. The flow used by the **proof of concept** does not need Refresh Tokens, so this setting was not enabled. If the fifth checkbox, '**Issue JSON Web Token (JWT)-based access tokens for named users**', is checked, Salesforce will issue JSON Web Tokens (JWT) for Access Tokens. The OAuth standard does not specify a format for Access Tokens or Refresh Tokens—any application that uses OAuth can generate tokens in the format they want. With this setting enabled, Salesforce generates Access Tokens in the JWT format, which is widely used and standardized, instead of generating them in its own opaque format. This can be useful for some third-party applications with specific requirements—it is not needed for the **proof of concept**, so this setting was not enabled.

This setting is not related to the JWT Bearer flow and does not have to be enabled in order to use it.

[![Screenshot 2026-06-01 at 1.19.38 PM.png](https://notes.its.uri.edu/uploads/images/gallery/2026-06/scaled-1680-/0TJZNd09eBmSKrT6-screenshot-2026-06-01-at-1-19-38-pm.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-06/0TJZNd09eBmSKrT6-screenshot-2026-06-01-at-1-19-38-pm.png) The two last checkboxes, '**Limit Idle Refresh Token Time-to-Live (TTL) to 30 Days**' and '**Enforce Refresh Token IP** **Allowlist**', can be checked to add additional layers of security to Refresh Tokens. Once again, the flow used by the **proof of concept** does not need Refresh Tokens, so this setting was not enabled. For the setting related to the first checkbox, more information can be found [here](https://help.salesforce.com/s/articleView?id=xcloud.eca_limit_idle_refresh_token_ttl.htm). For the setting related to the second checkbox, see the section below. ##### Refresh Token IP Allowlist This subsection can be used to improve the security of the External Client App. With the '+' button located on the right, IP address ranges can be added to the list. Once the '**Enforce Refresh Token IP** **Allowlist**' checkbox has been checked (see previous section), only the IP addresses comprised within the ranges in the list can request tokens to the Authorization Server by using the Authorization Code/Web Server flow or the Request Token flow. The IP addresses that are not explicitly allowed will be blocked from receiving any token. As the flow used by the **proof of concept** does not need Refresh Tokens, the list in this subsection was left empty. ##### Trusted IP Ranges for OAuth Web Server Flow This subsection is similar to the previous one. With the '+' button located on the right, IP address ranges can be added to the list. Once at least one range has been added, all the IP addresses that are not comprised within the ranges in the list require verification in order to request tokens using the Authorization Code/Web Server flow. As the **proof of concept** does not use this flow, the list in this subsection was left empty. [![Screenshot 2026-06-03 at 1.01.57 PM.png](https://notes.its.uri.edu/uploads/images/gallery/2026-06/scaled-1680-/3cbpZDD4HHxdf6hc-screenshot-2026-06-03-at-1-01-57-pm.png)](https://notes.its.uri.edu/uploads/images/gallery/2026-06/3cbpZDD4HHxdf6hc-screenshot-2026-06-03-at-1-01-57-pm.png) #### Configuring OAuth policies The section to configure OAuth policies contains several subsections. ##### Plugin Policies This subsection is the most important because it defines what users within the Salesforce org (Resource Owners) are permitted to use OAuth in order to authorize the external application (Client) to access their data through the External Client App. In the '**Permitted Users**' drop-down menu, there are only two options: '**All** **users can self-authorize**' and '**Admin approved users are pre-authorized**'. When the first option is selected, all the users within the Salesforce org are permitted to use OAuth. Selecting this option implies that the users must be able to interact with the external application in order to complete the authorization process. For the server-to-server integrations, where there is no user interaction, the second option must be selected. With this option, the administrator of the External Client App can choose which users are pre-authorized. After a user is chosen, the External Client App will automatically authorize the external application to access their data—they do not have to do anything. The pre-authorized users can be selected in the '**App Policies**' section, which is presented below. As the integration built for the **proof of concept** is a server-to-server integration, the second option was selected. Next to the the '**Permitted Users**' drop-down is a text box labelled '**OAuth Start URL**'. This is only available when the start page of the External Client App is set to '**OAuth**' in the '**App Policies**' section—by default, it should be grayed out. More information is given in the next section. '**Custom Scopes**' and '**Apex Plugin Class**' are additional policies that were not needed for the **proof of concept** and that will not be needed for the rest of the project, so they are not explained in this documentation. ##### OAuth Flows and External Client App Enhancements This subsection is used to configure the policies of two specific OAuth flows: **Client Credentials** and **Token Exchange**. When these flows are not selected, this subsection is empty. When the **Token Exchange** flow is selected, a simple checkbox labelled '**Enable Token Exchange Flow**' appears, which can be checked, as its label implies, to enable the Token Exchange flow. When the **Client Credentials** flow is selected, another checkbox appears, labelled '**Enable Client Credentials Flow**', which can be checked, like the previous one, to enable the Client Credentials flow. After it is checked, a text box labelled '**Run As (Username)**' appears underneath. As explained previously, with the Client Credentials flow, the external application (Client) always accesses the data of the same user (Resource Owner). In the text box, the email address of that user must be entered.

The policies set in the 'Plugin Policies' subsection regarding permitted users and in the 'App Policies' section do not apply to the Client Credentials flow. If this is the only flow enabled for an External Client App, there is no need to configure them.

##### App Authorization In this subsection, additional settings for the External Client App can be configured—most of them are related to Refresh Tokens. As the **proof of concept** does not use the Refresh Token flow, these settings were left as default. The remaining settings in this subsection were not needed for the **proof of concept** and will not be needed for the rest of the project, so they were also left as default. ##### Custom Attributes This subsection can be used to add **custom attributes** to the External Client App, which can be used to 'transfer data that's unavailable in the OAuth exchange' to the external application (Client). No custom attribute was needed for the **proof of concept**, but if it becomes necessary in the future for the requirements or needs of the project, more information can be found [here](https://help.salesforce.com/s/articleView?id=xcloud.configure_custom_attributes_for_external_client_apps.htm&type=5). #### Configuring App policies As mentioned previously, once the 'Permitted Users' setting has been set to 'Admin approved users are pre-authorized' in the OAuth policies, the pre-authorized users can be selected by the administrator in the section to configure App policies. This is the main purpose of this section. The administrator of the External Client App cannot pre-authorize users by individually selecting them. Instead, they have to select **Profiles** or **Permissions Sets**. A **[profile](https://help.salesforce.com/s/articleView?id=platform.admin_userprofiles.htm&type=5)** is a set of settings and permissions which can be assigned to a user. Every Salesforce org includes predefined standard profiles, such as 'System Administrator' or 'Standard User', and can also define their own customized profiles. **[Permissions sets](https://help.salesforce.com/s/articleView?id=platform.perm_sets_overview.htm&type=5)** are the recommended way to extend users' permissions without changing their assigned profiles. There are four lists in the App policies section: two under the label '**Select Profiles**' and two under the label '**Select Permission Sets**'. The two lists on the left, labelled '**Available Profiles**' and '**Available Permission Sets**', contain all the profiles and permission sets available in the Salesforce org. By using the arrows, these profiles and permission sets can be moved from one list to another. Once a profile/permission set has been moved to the list labelled '**Selected Profiles**'/'**Selected Permission Sets**', all the users within the Salesforce org which were assigned this profile/permission set are pre-authorized for the External Client App.

To make the External Client App as secure as possible, it is important to follow the principle of **least** **privilege**. For server-to-server integrations, such as the one built for this project, the option '**Admin approved users are pre-authorized**' must be selected and a dedicated user should be created. This user should also have a dedicated profile, which should only contain the settings and permissions that the external application absolutely needs to function properly. To start building this dedicated profile, a good practice is to first clone an existing profile with restricted access, such as '**Minimum Access - API Only Integrations**'. The dedicated user should be the only one to have this profile, to make sure that no other user will be accidentally pre-authorized in the App policies section. This is why it is better to clone 'Minimum Access - API Only Integrations', create a dedicated profile based on it and select that profile in the App policies section rather than directly selecting 'Minimum Access - API Only Integrations', which could have been assigned to multiple users. [This Salesforce blog page](https://www.salesforce.com/blog/architecting-inbound-governance-external-client-apps/#h-creating-external-client-apps) gives more details on how to make External Client Apps secure.

For the **proof of concept**, \[TO DO: indicate the username of the dedicated user that was created, give details on its configuration, indicate the name of the dedicated profile and/or permissions sets that were assigned to them, give details on the permissions and authorizations that were enabled for the dedicated profile\]. Above the lists, a drop-down menu labelled '**Start Page**' can be found. By default, it should be set to '**None**'. There are two other options: '**Custom**' and '**OAuth**'. When '**Custom**' is selected, the start URL must be entered in the text box labelled '**???**', which appeared to the right of the drop-down menu. When '**OAuth**' is selected, the start URL must be entered in the text area labelled '**OAuth Start URL**', located in the OAuth policies section. According to the [Salesforce documentation on this topic](https://help.salesforce.com/s/articleView?id=xcloud.manage_eca_start_url.htm&type=5), "a start URL defines the page where users end up when they run an External Client App". Once a start URL has been entered, the External Client App appears in the App Manager. This setting is useful when the external application (Client) relying on the External Client App to interact with data on the Salesforce org has a user interface and/or uses the Authorization Code flow. When a user clicks on the External Client App in the App Manager, they are directed to the page located at the start URL; it can be any page of the external application's interface or, if the 'OAuth' option was selected, the page on which the user can initiate the OAuth authorization process. There is no user interface for the **proof of concept** and there will not be one for the rest of the project either. As a result, this setting was left on 'None'.

For the 'OAuth' option of the 'Start Page' setting, it is important to note that the **start URL** is not the same as the **callback URL**, defined in the OAuth settings. The start URL is the page on which users can initiate the OAuth authorization process; the callback URL is the page on which they are redirected once Salesforce (the Authorization Server) has authorized them.

In Salesforce, a user that wants to configure the App policies and manage the permitted users of External Client Apps needs to have a profile with the appropriate permission: '**Manage Profiles and Permission Sets**'. This permission can be granted to a profile by an administrator in the 'System Permissions' of the profile (in the 'Users' section, at the bottom of the page), which can be found in the 'Setup' section, under 'Administration' > 'Users' > 'Profiles'.

## Connecting an Apache Airflow environment to a Salesforce External Client App Once an External Client App has been created and configured on Salesforce, connecting an Apache Airflow environment to that app is relatively straightforward. However, before this connection can be established, the [Salesforce provider](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/index.html) needs to be installed in the Airflow environment. ### Installing the Salesforce provider in the Airflow environment A [provider](https://airflow.apache.org/docs/apache-airflow-providers/) is a package that can be installed in an Airflow environment to extend its capabilities. > Providers can contain operators, hooks, sensors, and transfer operators to communicate with a multitude of external systems, but they can also extend Airflow core with new capabilities. > > > [Apache Airflow - Providers](https://airflow.apache.org/docs/apache-airflow-providers/) #### Editing the requirements file To install the Salesforce provider, the following lines need to be added to the requirements file of the environment: ``` apache-airflow-providers-salesforce>=5.1.0 simple-salesforce>=1.0.0 ``` The first line corresponds to the provider package, and the second corresponds to a PIP package called [simple-salesforce](https://pypi.org/project/simple-salesforce/), which the provider depends on. Although the provider has several other package dependencies, only simple-salesforce needs to be explicitly listed in the requirements file. At the end of each line, the version of the package to be installed can be specified. This is not mandatory but highly recommended to prevent errors and ensure compatibility across all providers and the Airflow environment. The version of each package can be strictly enforced by using a double equal sign (`==`). However, it is recommended to only enforce the **oldest** version that can be installed by using the greater than or equal sign (`>=`)—this way, a newer version will be automatically installed if available. The version of the provider depends on the latest release available as well as the version of Apache Airflow being used. The version of the simple-salesforce package must satisfy the [requirements](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/index.html#requirements) of the provider.

As a reminder, the requirements file is located in the S3 bucket linked to the Airflow environment for MWAA, and in the `requirements` [folder](https://github.com/aws/aws-mwaa-local-runner#requirementstxt) at the root of the `aws-mwaa-local-runner` directory (which was cloned from the [GitHub repository](https://github.com/aws/aws-mwaa-local-runner)) for the local runner.

#### Updating the Airflow environment Once the requirements file has been edited, the Airflow environment must be updated in order for the provider and its dependencies to be installed. The process to update an environment in MWAA is described in [this section](https://bookstack.foundation.uri.edu/link/13#bkmrk-if-the-requirements-) of the page 'Setting up Apache Airflow', and the process to update an environment in the local runner is described in [this section](https://bookstack.foundation.uri.edu/link/13#bkmrk-when-the-requirement) of the same page.

When updating an MWAA environment, in the `DAG Code in Amazon S3` section, if the configuration is set to a specific version of the requirements file, the newly edited version must be explicitly selected, otherwise the requirements will remain unchanged. If no specific version is selected (the drop-down list still displays `Choose a version`), nothing needs to be done, as the latest version of the requirements file will be selected automatically.

### Configuring the connection After the Salesforce provider has been installed and the Airflow environment updated, the last step is to create a new connection and configure it properly. Connections are managed from the 'Connections' page, which can be accessed from the 'Admin' tab, located in the main navigation bar. [![Screenshot 2026-07-14 at 11.07.54 AM.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/scaled-1680-/8kmWzhDSuzbXVKdl-screenshot-2026-07-14-at-11-07-54-am.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/8kmWzhDSuzbXVKdl-screenshot-2026-07-14-at-11-07-54-am.png) *In the user interface of Airflow environments using version 3.0.6 and above, the navigation bar is located on the left.* [![Screenshot 2026-07-14 at 11.10.24 AM.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/scaled-1680-/8cbSirjY2bhLJ5JN-screenshot-2026-07-14-at-11-10-24-am.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/8cbSirjY2bhLJ5JN-screenshot-2026-07-14-at-11-10-24-am.png) *In the user interface of Airflow environments using version 2.10 (as mentioned in the '[Setting up Apache Airflow](https://bookstack.foundation.uri.edu/books/summer-internship-program-2026/page/setting-up-apache-airflow)' page, this is latest available version for the local runner) and below, the navigation bar is located on the top.* On the 'Connections' page, a new connection can be created by clicking on the 'Add Connection' button (in Airflow 3.0.6 and above) or the '+' button (in Airflow 2.10 and below). After this button is clicked, a window should appear on the screen. The first two fields that must be completed are the '**Connection ID**' and the '**Connection Type**'—these fields are mandatory for every connection. - The '**Connection ID**' is the name of the connection. It can be anything, but it must be unique. If there are no other Salesforce connections in the environment, it is recommended to use `salesforce_default`, which is the [default value of the `conn_id` parameter](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/_api/airflow/providers/salesforce/hooks/salesforce/index.html#airflow.providers.salesforce.hooks.salesforce.SalesforceHook.conn_name_attr) of the Salesforce [Hook](https://airflow.apache.org/docs/apache-airflow/stable/authoring-and-scheduling/connections.html#hooks). - The '**Connection Type**', as its name indicates, is simply the type of the connection. The type '**Salesforce**' must be selected. If it does not appear in the drop-down list, then the Salesforce provider might have not been installed properly.

In the local runner, a default connection is automatically created for each connection type. As a result, the connection named `salesforce_default` should already exist. This connection can be edited instead of creating a new one.

Once these two fields have been completed, the connection can be configured. There are many fields in a Salesforce connection, which are all described [on this page](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/connections/salesforce.html), but none of them are mandatory. The fields that must be completed depend on the configuration of the External Client App the Airflow environment must connect to, especially the OAuth flow that was enabled. For this project and the **proof of concept**, the **JWT Bearer flow** is used. According to the page of the [Salesforce provider documentation](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/index.html) dedicated to the [Salesforce Hook](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/_api/airflow/providers/salesforce/hooks/salesforce/index.html), this flow only requires the following fields to be completed: - '**Consumer Key**' - '**Private** **Key**' or '**Private Key File Path**' This page also indicates that if a Salesforce sandbox is being used, which is the case for the **proof of concept**, the value '**test**' must be entered for the '**Domain**' field. The Airflow environment will connect to the External Client App as a specific user registered on the Salesforce org. In addition to the three fields presented above, the **username** of this user must also be provided. As a reminder, their assigned **profile and/or **permission sets** must be **pre-authorized** by the administrator in the '[App policies](https://bookstack.foundation.uri.edu/books/summer-internship-program-2026/page/connecting-apache-airflow-to-salesforce#bkmrk-configuring-app-poli)' section of the External Client App. It is also important to verify that they follow the [security recommendations](https://bookstack.foundation.uri.edu/link/15#bkmrk-to-make-the-external) listed higher on this page. Completing the '**Username**', '**Domain**' and '**Consumer Key**' fields is extremely straightforward: the value—respectively, the **username** of the Salesforce user, '**test**' and the **consumer key** of the External Client App–must simply be entered in the appropriate text box. The consumer key can be retrieved from the 'App settings' section of the External Client App, as described in [this section](https://bookstack.foundation.uri.edu/link/15#bkmrk-1%29-app-settings). For the **proof of concept**, the username of the Salesforce user is \[TO DO: indicate the username of the dedicated user\]. Completing the '**Private Key**' or the '**Private Key File Path**' field requires additional steps. The purpose of these two fields is to register the private key that the Airflow environment will use in order to prove its identity to the External Client App during the JWT Bearer flow. As a reminder, the public key derived from this private key must have been encapsulated in a [**self-signed certificate**](https://bookstack.foundation.uri.edu/link/15#bkmrk-an-x.509-certificate), and this certificate must have been uploaded to the External Client App in the '[Flow Enablement](https://bookstack.foundation.uri.edu/books/summer-internship-program-2026/page/connecting-apache-airflow-to-salesforce#bkmrk-2%29-flow-enablement)' section. These two fields offer different ways of providing the private key to the Airflow environment. With the 'Private Key File Path' field, the path to the file in which the private key is stored securely must be entered in the text box. During the JWT Bearer flow, the environment will read the key from this file. With the 'Private Key' field, the private key must be pasted directly into the text box. This way, the environment can retrieve it directly during the JWT Bearer flow. The 'Private Key File Path' is not a viable option for multiple reasons. Firstly, it is not possible to store files in an Airflow environment. Secondly, environments in MWAA are isolated and cannot directly access files stored in an external file system. It is possible for environments running in the local runner, as each Docker container comes with its own file system. However, it would require a long and careful configuration to make sure the private key file is stored securely. Furthermore, it would also require to move or to duplicate the file, which, [as mentioned earlier on this page](https://bookstack.foundation.uri.edu/link/15#bkmrk-salesforce-does-not-), is not recommended. Using the 'Private Key' field is the best option, as it is easier and more secure. However, the private key cannot be directly copied from its file and pasted into the text box. First, it needs to be properly formatted in order for the Airflow environment to be able to read it. To do so, the following command needs to be run: ``` awk '{printf "%s\\n", $0}' key.pem ``` The output of this command must be copied and pasted into the 'Private Key' field. If it does not work (DAGs fail with an error indicating that the key cannot be parsed), it can also be pasted between the quotation marks of the 'private\_key' value in the 'Extra Fields JSON' section. During the process for reintegrating Salesforce and Airflow after the Sandbox reset in July 2026, there were some issues adding in the private key. When pasted it will take on a similar form (note, this is not any fragment of the actual key, this is an example and not a security issue): ``` "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQ...\n-----END PRIVATE KEY-----" ``` When pasting in the private key, it must contain the portions showing where dashes for key beginning and ending. and most importantly **there should be no trailing "\\n" character**. Airflow will not be able to parse this given string. Once the private key has been pasted, the configuration of the connection can be saved.

As the private key is a sensitive piece of data, it is not displayed inside the text box of the 'Private Key' field when the connection is edited, unlike the other fields (the text box will be blank or display '\*\*\*'). However, it does not mean that it was not saved. If it needs to be replaced, the new private key can simply be pasted into the text box, the same way as explained above.

When creating or editing a connection in the local runner, all the fields are grouped in the same section. In MWAA, if using a version of Airflow superior or equal to 3.0.6, the 'Username' field is located in the 'Standard Field' subsection and the other fields are located in the 'Extra Fields' subsection.

### Using the Salesforce connection in a DAG The last section of this page gives a simple example of a DAG that retrieves the Salesforce connection using the [Salesforce Hook](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/_api/airflow/providers/salesforce/hooks/salesforce/index.html) in order to connect to the External Client App and interact with data stored on the Salesforce org using the [Salesforce provider](https://airflow.apache.org/docs/apache-airflow-providers-salesforce/stable/index.html) or the [simple-salesforce](https://pypi.org/project/simple-salesforce/) library. ```python from datetime import datetime from airflow.decorators import dag, task # The Salesforce Hook needs to be imported in order to be used from airflow.providers.salesforce.hooks.salesforce import SalesforceHook @dag( dag_id="salesforce_dag_example", schedule=None, start_date=datetime(2026, 1, 1), catchup=False, ) def salesforce_dag_example(): @task def salesforce_task_example(): # By default, the Salesforce Hook retrieves the connection named 'salesforce_default'. # This connection must be of type 'Salesforce'. If it was named differently than # 'salesforce_default', then the name must be specified. # The Hook retrieves the fields (username, private key, etc.) and uses them to # establish a connection with the External Client App. hook = SalesforceHook() # or hook = SalesforceHook(conn_id='salesforce_connection_name') # After the connection is established, the SalesforceHook object can be used directly # to call various functions in order to interact with data on the Salesforce org # through the External Client App. result = hook.make_query("SELECT Id, Name FROM Account LIMIT 5") ... # If the functions of the SalesforceHook class are not sufficient, # a Salesforce instance (from the simple-salesforce library) with # the connection to the External Client App already established can # be retrieved from the SalesforceHook object. # With this instance, all the functions included in the library # can be used in order to interact with data on the Salesforce org # through the External Client App. conn = hook.get_conn() conn.Account.create({"Name": "Doug Las"}) ... salesforce_task_example() salesforce_dag_example() ``` The full documentation of the simple-salesforce library can be found [here](https://simple-salesforce.readthedocs.io/en/latest/index.html). # Connecting PostgreSQL to Salesforce --- *Last updated: 07/24/2026* --- Once a PostgreSQL database has been created and configured to accept incoming connections, connecting an Apache Airflow environment to that database is very straightforward. However, before this connection can be established, however, the [PostgreSQL provider](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/index.html) must first be installed in the Airflow environment. ## Installing the PostgreSQL provider in the Airflow environment A [provider](https://airflow.apache.org/docs/apache-airflow-providers/) is a package that can be installed in an Airflow environment to extend its capabilities. ### Editing the requirements file To install the PostgreSQL provider, the following line must be added to the requirements file of the environment: ```text apache-airflow-providers-postgres>=6.3.0 ``` At the end of each line, the version of the package to be installed can be specified. This is not mandatory but highly recommended to prevent errors and ensure compatibility across all providers and the Airflow environment. The version of each package can be strictly enforced by using a double equal sign (`==`). However, it is recommended to only enforce the **oldest** version that can be installed by using the greater than or equal sign (`>=`)—this way, a newer version will be automatically installed if available. The version of the provider depends on the latest release available as well as the version of Apache Airflow being used.

As a reminder, the requirements file is located in the S3 bucket linked to the Airflow environment for MWAA, and in the `requirements` [folder](https://github.com/aws/aws-mwaa-local-runner#requirementstxt) at the root of the `aws-mwaa-local-runner` directory (which was cloned from the [GitHub repository](https://github.com/aws/aws-mwaa-local-runner)) for the local runner.

### Updating the Airflow environment Once the requirements file has been edited, the Airflow environment must be updated in order for the provider to be installed. The process to update an environment in MWAA is described in [this section](https://bookstack.foundation.uri.edu/link/13#bkmrk-if-the-requirements-) of the page 'Setting up Apache Airflow', and the process to update an environment in the local runner is described in [this section](https://bookstack.foundation.uri.edu/link/13#bkmrk-when-the-requirement) of the same page.

When updating an MWAA environment, in the `DAG Code in Amazon S3` section, if the configuration is set to a specific version of the requirements file, the newly edited version must be explicitly selected, otherwise the requirements will remain unchanged. If no specific version is selected (the drop-down list still displays `Choose a version`), nothing needs to be done, as the latest version of the requirements file will be selected automatically.

## Configuring the connection After the Salesforce provider has been installed and the Airflow environment updated, the last step is to create a new connection and configure it properly. Connections are managed from the 'Connections' page, which can be accessed from the 'Admin' tab, located in the main navigation bar. [![Screenshot 2026-07-14 at 11.07.54 AM.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/scaled-1680-/8kmWzhDSuzbXVKdl-screenshot-2026-07-14-at-11-07-54-am.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/8kmWzhDSuzbXVKdl-screenshot-2026-07-14-at-11-07-54-am.png) *In the user interface of Airflow environments using version 3.0.6 and above, the navigation bar is located on the left.* [![Screenshot 2026-07-14 at 11.10.24 AM.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/scaled-1680-/8cbSirjY2bhLJ5JN-screenshot-2026-07-14-at-11-10-24-am.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/8cbSirjY2bhLJ5JN-screenshot-2026-07-14-at-11-10-24-am.png) *In the user interface of Airflow environments using version 2.10 (as mentioned in the '[Setting up Apache Airflow](https://bookstack.foundation.uri.edu/books/summer-internship-program-2026/page/setting-up-apache-airflow)' page, this is latest available version for the local runner) and below, the navigation bar is located on the top.* On the 'Connections' page, a new connection can be created by clicking on the 'Add Connection' button (in Airflow 3.0.6 and above) or the '+' button (in Airflow 2.10 and below). After this button is clicked, a window should appear on the screen. The first two fields that must be completed are the '**Connection ID**' and the '**Connection Type**'—these fields are mandatory for every connection. - The '**Connection ID**' is the name of the connection. It can be anything, but it must be unique. If there are no other PostgreSQL connections in the environment, it is recommended to use **`postgres_default`,** which is the [default value of the `postgres_conn_id` parameter](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/hooks/postgres/index.html#airflow.providers.postgres.hooks.postgres.PostgresHook) of the PostgreSQL [Hook](https://airflow.apache.org/docs/apache-airflow/stable/authoring-and-scheduling/connections.html#hooks). - The '**Connection Type**', as its name indicates, is simply the type of the connection. The type '**Postgres**' must be selected. If it does not appear in the drop-down list, then the Salesforce provider might have not been installed properly.

In the local runner, a default connection is automatically created for each connection type. As a result, the connection named **`postgres_default`** should already exist. This connection can be edited instead of creating a new one.

Once these two fields have been completed, the connection can be configured. Several fields must be completed in order for the Airflow environment to be able to establish a connection to the database: - '**Host**' - '**Port**' - '**Database**' - '**Login**' - '**Password**' The '**Host**' field specifies the **host name** or the **IP address** of the PostgreSQL server. The Airflow environment must be able to reach this host over the network. For the **proof of concept**, the server is hosted and running on an **[Amazon EC2 instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/concepts.html)**, publicly accessible over the Internet, named **`foundation-etl-poc-server`** and located in the '**ITS shared non-prod**' account.

The EC2 instance used for the proof of concept can only be reached using SSH with a key pair that was registered beforehand. It cannot be reached using a password.

The '**Port**' field specifies the **TCP port** used by the PostgreSQL server. The default PostgreSQL port is **5432**, which should be used unless the server has been configured to listen on another port. For the **proof of concept**, this default port is used. The '**Database**' field specifies the name of the **PostgreSQL database** the Airflow environment must connect to. For the **proof of concept**, a database named **`snowflake_mock`** was created. The Airflow environment will connect to the database as a specific user. The '**Login**' field specifies the **username** of that user. In the PostgreSQL server used for the **proof of concept**, a dedicated user named `airflow` was created.

For security reasons and to follow the principle of least privilege, it is highly recommended to not use the database administrator (usually named `postgres`) and to instead create a dedicated user with minimum permissions. This user can then be granted only the permissions required by the Airflow environment, and only on the databases it needs to access.

The '**Password**' field specifies the **password** associated with the PostgreSQL user whose username was entered in the '**Username**' field.

As the password is a sensitive piece of data, it is not displayed inside the text box of the 'Password' field when the connection is edited, unlike the other fields (the text box will be blank or display '\*\*\*'). However, it does not mean that it was not saved. If it needs to be replaced, the new password can simply be pasted into the text box, the same way as explained above.

## Using the PostgreSQL connection in a DAG The last section of this page gives a simple example of a DAG that retrieves the Postgres connection using the [Postgres Hook](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/hooks/postgres/index.html#airflow.providers.postgres.hooks.postgres.PostgresHook), in order to connect to the database and interact with the data using the [Postgres provider](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/index.html). ```python from datetime import datetime from airflow.decorators import dag, task from airflow.providers.postgres.hooks.postgres import PostgresHook @dag( dag_id="postgres_dag_example", schedule=None, start_date=datetime(2026, 1, 1), catchup=False, ) def postgres_dag_example(): @task def postgres_task_example(): # By default, the PostgresHook retrieves the connection # named 'postgres_default'. If it was named differently than # 'postgres_default', then the name must be specified. hook = PostgresHook() # or hook = PostgresHook(postgres_conn_id='postgres_connection_name') # Execute a SQL query records = hook.get_records("SELECT id, first_name, last_name FROM employees LIMIT 5") ... postgres_task_example() postgres_dag_example() ``` If more advanced database operations are required, the Postgres Hook also provides direct access to the underlying database connection and cursor with the [`get_conn()` function](https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/hooks/postgres/index.html#airflow.providers.postgres.hooks.postgres.PostgresHook.get_conn), allowing any SQL statement supported by PostgreSQL to be executed. # Salesforce DAGs ## Overview --- The DAGs produced in this section were some of the first DAGs made during the internship. The DAGs here are made for Contact objects and Account objects, which are native to Salesforce. The DAGs originally were made during May-June of 2026 and intended for use in the Sandbox environment which only used test data. As of July 31st, 2026, the DAGs are being refit to function in a production environment. TODO: LINK HERE All of the code can be accessed in AWS and as well in the GitHub repository. The general structure of each DAG is the following, each DAG follows an "ETL" or Extract, Transfer, Load design. Since PostgreSQL was chosen as one of the endpoints during the internship, data is fed between PostgreSQL or Salesforce and then fed into the intermediary service of Airflow. In Airflow, the data is "transformed" in whatever manner is necessary, and then is fed to the other service. If data was taken from PostgreSQL, then it would typically flow back out to Salesforce, and if data was taken from Salesforce it would generally go back to PostgreSQL. Many of the DAGs contain their own respective documentation, however additional documentation is added here such that they can be understood at a higher level and more context can be given with respect to their uses in Salesforce. --- ## Visual Studio Code AWS Extension The Visual Studio Code (VSC) extension for AWS makes modifying and creating existing DAGs extremely simple. The utility allows users to create, delete, upload, and modify files in AWS' file hierarchy as if it were a normal file explorer in VSC. For example consider the current working directory for DAGs: [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/scaled-1680-/NT5Htx4CCsMmJkHR-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/NT5Htx4CCsMmJkHR-image.png) More information about the plugin can be found **[here.](https://aws.amazon.com/visualstudiocode/)** Installing the plugin in VSC and working with it is relatively simple. In VSC, the extension can easily be found by searching "**AWS**" into the extension marketplace. The top result "AWS Toolkit" should be installed. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/scaled-1680-/2ElZHXdhLZORX7vv-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-07/2ElZHXdhLZORX7vv-image.png) Once installed, there will appear an "AWS" icon on the bottom left sidebar of VSC. Click on it. In order to sign in once the sign in process is configured, begin by heading into **[myapps](https://myapps.microsoft.com/)** in URI's SSO and select AWS. Next, on the bottom left hand corner of VSC there will appear a red bar. Click on that bar. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/9fykVDyMCHOymU1g-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/9fykVDyMCHOymU1g-image.png) After clicking on the bar, a menu will bring up the option to access "**IAM Identity Center**", click on it. This will bring up a dialogue box with the option to open a link to AWS which will verify your credentials and permit you access to AWS' services through VSC again. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/VKMq1qLQTwjQPWNQ-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/VKMq1qLQTwjQPWNQ-image.png) Once the site has been correctly accessed, the following screen will appear, which will allow the user to close it and return to VSC. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/KOl2ZWLIXLaKXMNq-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/KOl2ZWLIXLaKXMNq-image.png) Then, click IAM Identity Center again. If you have the corresponding permissions for AWS, then you should be able to hit AWS Full Permission, which at least should give you full access to AWS. Note that you will not be able to run Airflow inside this environment, merely modify files, delete them, and upload files in AWS from your VSC environment. I have opted for the following file hierarchy for the Airflow environment (this will not change how it is viewed and accessed from inside Airflow) since it separates DAGs into their respective functions quite well: ```text S3 bucket (foundational-etl-poc-bucket) |___ requirements.txt |___ dags/ |___ create_tables/ |___ salesforce_dags/ |___ account_dags/ |___ contact_dags/ |___ affinaquest_dags/ |___ education_dags/ ``` --- ## Bulk API In Salesforce, there are four primary bulk operations, insertion, deletion, hard delete, updating, and upserting (update+inserting). Insertion adds a new entry to salesforce given some relevant fields about the object. An object hard deleted in Salesforce is immediately deleted, whereas an object which is deleted in Salesforce lingers in the recycle bin for 15 days. Airflow has bulk methods which make insertion and deletion of massive amounts of records efficient. I opted to use Simple Salesforce, which also has bulk operators that can be used in Airflow as well. For Further information on Bulk APIs in Simple Salesforce can be found here: [https://github.com/simple-salesforce/simple-salesforce](https://github.com/simple-salesforce/simple-salesforce) Rather than adding 50 objects individually, many are added at the same time. Each object in Salesforce has bulk methods. The bulk methods are all essentially identical in how they operate. --- ## Test Connection DAG This DAG simply tests that there is an existing Airflow connection and is entitled "salesforce\_test\_dag". This DAG is useful since it simply reads from Salesforce, which proves that Salesforce is connected without making any unexpected modifications to it or PostgreSQL. In the event that a connection must be re-established between Airflow and Salesforce, this DAG can be used to test that the connection is valid **safely**. The DAG does not follow the ETL structure as it is just used to verify that a connection exists. ```python from datetime import datetime from airflow.decorators import dag, task from airflow.providers.salesforce.hooks.salesforce import SalesforceHook @dag( dag_id="salesforce_test_dag", schedule=None, start_date=datetime(2026, 1, 1), catchup=False, ) def salesforce_test_dag(): @task def test_connection(): hook = SalesforceHook() # Test with a simple query result = hook.make_query("SELECT Id, Name FROM Account LIMIT 5") print(f"Successfully connected to Salesforce!") print(f"Retrieved {len(result['records'])} accounts") for record in result["records"]: print(f" - {record['Name']}") return result["records"] test_connection() salesforce_test_dag()=6.3.0 ``` --- ## Create Table DAGs These aren't much DAGs as much as they are files inside Airflow which are run following executions of DAGs for the corresponding objects. The Contact object has an associated PostgreSQL table for extracting information, and for loading in Contact info from salesforce. These tables are respectively known as "**contact\_extract**" and "**contact\_load**" For the Accounts object there is a similar associated "**account\_extract**" and "**account\_load**" table in PostgreSQL. ## Contact DAGs **[Contact objects in Salesforce](https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/sforce_api_objects_contact.htm)** represent an individual associated with a business account, and as the name would suggest, contains that person's name, phone number, email address, and other useful fields by which that person can be contacted. Currently the only fields supported in the Contact DAGs are, "FirstName", "LastName", "Email", and "MobilePhone". All of these fields are self explanatory. By default, every object in Salesforce has its own unique ID, which is unique to each object. There is no strict uniqueness on any of these fields, and duplicate objects with the exact same fields can exist for Contacts. An object's ID can be obtained via a SOQL query. [SOQL ](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm)is Saleforce's Query Language, which is similar to SQL in many respects but with notable differences such as lacking any wildcard operator ( the symbol "\*", for instance in the query "SELECT \* From Contacts" to select all fields from Contacts ). It is thus necessary to determine the identity of an object by writing queries which will select fields that **should** be unique in general, like a phone number or an email, or to match as many fields as possible against the objects. Using a single field like a phone number or email has the benefit of making a much more readable and plain query to work with, whereas using all fields makes selection of elements much quicker and queries easier to write. Due to general limitations on the size of queries in SOQL of 100,000 characters, and for more readable queries, I opted to only select the emails of each test account. There are some DAGs which do not make use of Bulk. Most of these DAGs are not practical and were made to show that code could be successfully executed in Airflow. The relevant DAGs at this time are "**poc\_contacts\_bulk\_delete**","**poc\_contacts\_bulk\_insert**", "**poc\_contacts\_bulk\_update**", "**poc\_contacts\_bulk\_upsert**", and "**poc\_contacts\_dag\_reverse**". ### PostgreSQL For Contacts There are two tables in the "**mock\_snowflake**" PostgreSQL database for Contacts, namely a "**contacts\_extract**" table for DAGs which begin in PostgreSQL and move data into Salesforce, and a "**contacts\_load**" for DAGs which start in Salesforce and end in PostgreSQL. The queries used to write these tables are respectively, and are executed in Airflow from the "**build\_contacts\_tables**" DAG. ```SQL CREATE TABLE contacts_extract ( FirstName VARCHAR(255), LastName VARCHAR(255), Email VARCHAR(255), MobilePhone VARCHAR(20) ); ``` ```SQL CREATE TABLE contacts_load ( FirstName VARCHAR(255), LastName VARCHAR(255), Email VARCHAR(255), MobilePhone VARCHAR(20) ); ``` Here is the resulting data for "**contacts\_extract**" after it has been created: ```SQL snowflake_mock=# SELECT * FROM contacts_extract;   firstname   |  lastname   |    email    |   mobilephone --------------+-------------+-------------+-----------------  1_First_Name | 1_Last_Name | 1@gmail.com | +1-111-111-1111  2_First_Name | 2_Last_Name | 2@gmail.com | +2-222-222-2222  3_First_Name | 3_Last_Name | 3@gmail.com | +3-333-333-3333  4_First_Name | 4_Last_Name | 4@gmail.com | +4-444-444-4444 (4 rows) ``` ```SQL snowflake_mock=# SELECT * FROM contacts_load; firstname | lastname | email | mobilephone -----------+----------+-------+------------- (0 rows) ``` In order to reset both tables, run the "**build\_contacts\_tables**" DAG. It will drop either of the tables if detected, and reconstruct both tables with the above associated fields. Secondly, it will add in the default information into "contacts\_extract". ### Testing All Contact DAGs In order to verify that all of the Contact DAGs are working, it is recommended that the DAGs are run in a specific order. Make sure that you know how to find and access these records in the frontend of Salesforce. It is vital that the records added to Salesforce are visible. Begin with the "**build\_contacts\_tables**" table in order to reset the PostgreSQL table for loading and extracting. Secondly, run the "**poc\_contacts\_bulk\_insert**" DAG. This will populate Salesforce with entries from the PostgreSQL load table. Secondly, run the "**poc\_contacts\_bulk\_update**" DAG, this will change each DAG to have a different name indicating they were updated by this function. Thirdly, run "**poc\_contacts\_bulk\_upsert**" which will add 4 entries to Salesforce and then modify the 4 existing entries. Fourthly, run "**poc\_contacts\_reverse**". Check the PostgreSQL database in the "**contacts\_load**" page, it should include 5 different records that may or may not include the newly added information. Lastly, run the "**poc\_contacts\_bulk\_delete**" DAG, which will remove all of the entries added during this test. #### Bulk Contact Insert DAG This DAG has tests that records from PostgreSQL can be uploaded to Salesforce. The ETL pipeline for this DAG is simple. We extract records from PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we load them into Salesforce via bulk. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the contact objects. There should at least be four, depending on whether or not previous records had been cleared from Salesforce. These records at minimum should be under Contacts owned by the Airflow Account TODO GET AIRFLOW ACCOUNT NAME [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/2ji0KjGqwhLa9Zsi-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/2ji0KjGqwhLa9Zsi-image.png) #### Bulk Contact Update DAG This DAG has tests that records from PostgreSQL already uploaded to Salesforce can have some of their fields modified. The ETL pipeline for this DAG is simple. We extract records from PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we modify the "**FirstName**" field in Contacts and update them in Salesforce via bulk. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the contact objects. Each contact object in PostgreSQL should have a "**FirstName**" field corresponding to "**ModifiedViaUpdate**". This obviously is meant to indicate that the Update DAG was responsible for this modification and not say insert or upsert. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/LvgiA56mdfPRGA4K-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/LvgiA56mdfPRGA4K-image.png) #### Bulk Contact Upsert DAG This DAG has tests that records from PostgreSQL already uploaded to Salesforce can have some of their fields modified and also upload new records simultaneously. This DAG assumes that the insert function was run **previously**. If it was not run previously, then it will not be able to update records, only upload them. The ETL pipeline for this DAG is simple. We extract records from PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we modify the "**FirstName**" field in Contacts and update them in Salesforce via bulk. We also add in the same four objects into Salesforce as insert. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the contact objects. Each contact object in PostgreSQL should have a "**FirstName**" field corresponding to "**ModifiedViaUpsert**". There should be 4 more objects from PostgreSQL now too: [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/CDSVEXNaj5rjSSDk-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/CDSVEXNaj5rjSSDk-image.png) #### Contact Reverse DAG This DAG tests if records from Salesforce can be loaded into PostgreSQL. The ETL pipeline for this DAG is simple. We extract records from Salesforce, transform them to make sure the fields are comprehensible for the PostgreSQL, and then we load them to PostgreSQL. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, SSH into the EC2 instance with the following command from Linux or WSL. You will need AWS permissions, and also need to have your IP/machine added to AWS for access in order to execute this series of commands: ```bash ssh ubuntu@3.82.8.34 ``` Once successfully inside, execute ```bash sudo -u postgres psql ``` Which connects you to PostgreSQL. Secondly execute the following command to connect to the **snowflake\_mock** table: ```SQL \c snowflake_mock ``` And lastly the following command to list all entries in that table: ```SQL SELECT * FROM contacts_load; ``` Note that your entries will likely **NOT** look the same. The only thing to verify here is that the four fields are loaded in properly and that 5 records in total are displayed. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/l7qGtkXTscP7UVPn-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/l7qGtkXTscP7UVPn-image.png) #### Bulk Contact Delete DAG This DAG assumes that the insert function was run **previously**. If it was not run previously, then it will not delete any records and will possibly return with an error. The ETL pipeline for this DAG is simple. We extract records from PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we modify the "**FirstName**" field in Contacts. Then, we need to find the Salesforce ID for each object, and then delete them in Salesforce via bulk. This DAG matches the entries by Email. Anything with an email used in the PostgreSQL test base is removed. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the contact objects. Each contact object in PostgreSQL should be gone. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/AnCrmDZCLL7jOVNe-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/AnCrmDZCLL7jOVNe-image.png) --- ## Account DAGs > The Account object represents an individual shopper. When using person accounts, an account of the Person Account record type represents an individual shopper. When not using person accounts, a standard account and contact together represent an individual shopper. A business account buyer or shopper always has a standard account and a contact. An "Account" is an object natively support in Salesforce. The fields the DAGs currently support for it are the following, "Name", "Phone", "BillingStreet", "BillingCity", "BillingState", "BillingPostalCode", and "BillingCountry". The Name field is clearly the person's name associated with the account, and the phone represents the phone number associated with it. The several different Billing fields are parts of the Account's billing address, separated into their street, city, state, postal code, and lastly country. For more information on Accounts in Salesforce consult the following link **[here](https://help.salesforce.com/s/articleView?id=commerce.om_account_objects.htm&type=5)**. Just like with Contact objects, and for that matter any other object in Salesforce, the primary key for this object is its "Id" field, which is how Salesforce identities the object and distinguishes it from others, even if two objects have identical fields everywhere else. In order to find an Account's ID in Salesforce, a SOQl query must be executed such that some/all of its fields are matched. I will assume for the sake of these DAGs, that the email of each user is unique, and only match the email. This might not be best practice overall, but for the sake of the POC demos it will be how I proceed. ### PostgreSQL For Accounts There are two tables in the "**mock\_snowflake**" PostgreSQL database for Accounts, namely an "**accounts\_extract**" table for DAGs which begin in PostgreSQL and move data into Salesforce, and a "**accounts\_load**" for DAGs which start in Salesforce and end in PostgreSQL. The queries used to write these tables are respectively, and are executed in Airflow from the "**build\_account\_tables**" DAG. ```SQL CREATE TABLE accounts_extract ( name VARCHAR(255), phone VARCHAR(255), billingstreet VARCHAR(255), billingcity VARCHAR(255), billingstate VARCHAR(255), billingpostalcode VARCHAR(255), billingcountry VARCHAR(255) ); ``` ```SQL CREATE TABLE accounts_load ( name VARCHAR(255), phone VARCHAR(255), billingstreet VARCHAR(255), billingcity VARCHAR(255), billingstate VARCHAR(255), billingpostalcode VARCHAR(255), billingcountry VARCHAR(255) ); ``` Here is the resulting data for "**accounts\_extract**" after it has been created: ```SQL snowflake_mock=# SELECT * FROM accounts_extract; name | phone | billingstreet | billingcity | billingstate | billingpostalcode | billingcountry --------------+-----------------+------------------+----------------+-----------------+-----------------------+------------------- 1_First_Name | +1-111-111-1111 | 1 Billing Street | 1 Billing City | 1 Billing State | 1 Billing Postal Code | 1 Billing Country 2_First_Name | +2-222-222-2222 | 2 Billing Street | 2 Billing City | 2 Billing State | 2 Billing Postal Code | 2 Billing Country 3_First_Name | +3-333-333-3333 | 3 Billing Street | 3 Billing City | 3 Billing State | 3 Billing Postal Code | 3 Billing Country 4_First_Name | +4-444-444-4444 | 4 Billing Street | 4 Billing City | 4 Billing State | 4 Billing Postal Code | 4 Billing Country (4 rows) ``` ```SQL snowflake_mock=# SELECT * FROM accounts_load; name | phone | billingstreet | billingcity | billingstate | billingpostalcode | billingcountry ------+-------+---------------+-------------+--------------+-------------------+---------------- (0 rows) ``` In order to reset both tables, run the "**build\_account\_tables**" DAG. It will drop either of the tables if detected, and reconstruct both tables with the above associated fields. Secondly, it will add in the default information into "**account\_extract**". ### Testing All Account DAGs In order to verify that all of the Account DAGs are working, it is recommended that the DAGs are run in a specific order. Make sure that you know how to find and access these records in the frontend of Salesforce. It is vital that the records added to Salesforce are visible. Begin with the "**build\_account\_tables**" table in order to reset the PostgreSQL tables for loading and extracting. Secondly, run the "**poc\_accounts\_bulk\_insert**" DAG. This will populate Salesforce with entries from the PostgreSQL load table. Secondly, run the "**poc\_accounts\_bulk\_update**" DAG, this will change each DAG to have a different name indicating they were updated by this function. Thirdly, run "**poc\_accounts\_bulk\_upsert**" which will add 4 entries to Salesforce and then modify the 4 existing entries. Fourthly, run "**poc\_accounts\_reverse**". Check the PostgreSQL database in the "**accounts\_load**" page, it should include 5 different records that may or may not include the newly added information. Lastly, run the "**poc\_accounts\_bulk\_delete**" DAG, which will remove all of the entries added during this test. #### Bulk Account Insert DAG This DAG has tests that Accounts records from "accounts\_extract" in PostgreSQL can be uploaded to Salesforce. The ETL pipeline for this DAG is simple. We extract records from PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we load them into Salesforce via bulk. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the Accounts objects. There should at least be four, depending on whether or not previous records had been cleared from Salesforce. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/J40OnF4PNnhN9kOa-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/J40OnF4PNnhN9kOa-image.png) #### Bulk Account Update DAG This DAG has tests that records from PostgreSQL already uploaded to Salesforce can have some of their fields modified. The ETL pipeline for this DAG is simple. We extract records from "accounts\_extract" in PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we modify the "**Name**" field in Contacts and update them in Salesforce via bulk. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the Accounts objects. Each contact object in PostgreSQL should have a "**Name**" field corresponding to "**ModifiedViaUpdate**". This obviously is meant to indicate that the Update DAG was responsible for this modification and not say insert or upsert. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/njwWugpRWsIDeyX5-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/njwWugpRWsIDeyX5-image.png) #### Bulk Account Upsert DAG This DAG has tests that records from PostgreSQL already uploaded to Salesforce can have some of their fields modified and also upload new records simultaneously. This DAG assumes that the insert function for Accounts was run **previously**. If it was not run previously, then it will not be able to update records, only upload them. The ETL pipeline for this DAG is simple. We extract records from "accounts\_extract" in PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we modify the "**FirstName**" field in Accounts and update them in Salesforce via bulk. We also add in the same four objects into Salesforce as insert. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the Account objects. Each contact object in PostgreSQL should have a "**Name**" field corresponding to "**ModifiedViaUpsert**". There should be 4 more objects from PostgreSQL now too: [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/O3jx8zXEtQ5GPnBB-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/O3jx8zXEtQ5GPnBB-image.png) #### Account Reverse DAG This DAG tests if records from Salesforce can be loaded into PostgreSQL. The ETL pipeline for this DAG is simple. We extract records from Salesforce in the Account section, transform them to make sure the fields are comprehensible for the PostgreSQL, and then we load them to PostgreSQL in the "accounts\_load". In order to see how to access the contents in the PostgreSQL page, execute the commands in the "**Bulk Contact Delete DAG**", the sequence of commands is essentially the same except for the SQL query made which should be to the "accounts\_load" table: ```SQL SELECT * FROM accounts_load; ``` Here is the resulting output (these should not necessarily match the results of your query) [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/TpG3zbVUVibuR5mh-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/TpG3zbVUVibuR5mh-image.png) #### Bulk Account Delete DAG This DAG assumes that the insert function was run **previously**. If it was not run previously, then it will not delete any records and will possibly return with an error. The ETL pipeline for this DAG is simple. We extract records from PostgreSQL, transform them to make sure the fields are comprehensible for the Salesforce API, and then we modify the "**Name**" field in Account. Then, we need to find the Salesforce ID for each object, and then delete them in Salesforce via bulk. This DAG matches the entries by Phone number. Anything with a phone number used in the PostgreSQL test base is removed. In order to verify that this DAG worked, all three tasks in the execution finish with a "success" status, go to the Salesforce page and look for the contact objects. Each contact object in PostgreSQL should be gone. [![image.png](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/scaled-1680-/bUTjafuZWDWOcu3T-image.png)](https://bookstack.foundation.uri.edu/uploads/images/gallery/2026-08/bUTjafuZWDWOcu3T-image.png) # Affinaquest DAGs TODO