Application Architecture

The Coalesce application consists of a GUI frontend and the accompanying back-end cloud.

Frontend

The frontend component of the Coalesce Cloud serves static assets such as JavaScript, CSS and other files needed for customers to use the GUI web client for editing Coalesce projects.

Template Renderer

The template renderer is an API endpoint used to process Jinja templates for the frontend. Because Jinja is a python library, an API endpoint is used to render Jinja templates when requested by the JavaScript frontend.

Backend

The backend served as a direct proxy to the customer's Git Provider and Snowflake instances. Because of browser CORS policies, it is not possible to directly access Snowflake instances from a browser, and thus, a direct proxy is used.

Upon successful authentication of a request by the backend, the industry standard credential manager is used to retrieve git personal access tokens (PAT) or per-user Snowflake credentials (unless browser storage is specified by the user), and a subsequent request to the resource is made. For more information, please refer to the Snowflake SQL Execution and Git Browser Integration sections.

Industry Standard Credential Manager

The industry standard credential manager is used to store OAuth tokens for the Snowflake OAuth security integration, per-user git personal access tokens (PAT) and as well as per-user Snowflake credentials (unless browser storage is specified by the user).

When using the Snowflake OAuth integration, Snowflake passwords are never stored in the industry standard credential manager. Coalesce users authenticate directly with Snowflake. Coalesce only stores refresh tokens that expire in 90 days by default and access tokens that rotate every 10 minutes.

Data is encrypted in transit with TLS and at rest with AES-256-bit encryption keys.

Job Engine

When a user submits a job via the API, the job engine in the Coalesce cloud is used to run jobs on their behalf. Each job is run to completion in an isolated environment, similarly to using coa. Refer to CLI Architecture (below) for more details regarding the contents and architecture of each job execution.

Metadata Repository

Coalesce stores the metadata format for each data warehouse environment in a highly scalable NoSQL database. This metadata never stores Snowflake data or passwords, it is solely used for storing the metadata configuration of your data warehouse and storing results of jobs.

Metadata Repository Security Rules

Upon user authentication, security rules are enforced such that a user may only read, write and update the metadata for organizations of which they are a member. Requests without a valid user, or for a user not part of a organization, are rejected.

API Architecture

The REST API exposed by Coalesce can be used by customers to access their metadata programmatically or submit jobs. API calls are authenticated via tokens that prevent unauthorized access.

If a user chooses to submit jobs to the Coalesce API, the jobs are run in an isolated environment on the Jobs Engine, similarly to the CLI architecture.

CLI Architecture

The coa CLI application is used for executing Coalesce jobs and deployments via the command-line. coa bundles the template renderer and database execution engine into a downloadable npm package. The tool can be integrated with CI/CD tools and leveraged for high-security environments requiring database traffic to originate from in-network environments. An outgoing internet connection is required for authentication to the Coalesce platform and access to Snowflake.