Before we start looking into what SOLE is, what it does, and how it works, we should first understand where it's needed in a DataOps project.
There are potentially many use cases for managing Snowflake objects. Therefore, so we will examine three of the most common and important ones:
- Core Snowflake Setup - How DataOps builds and maintains the basic components needed on top of a new Snowflake account
- Objects for Third-Party Tools - Maintaining the schemas and tables that external tools, orchestrated by DataOps, will use
- Roles and Grant Management - Managing role hierarchies in Snowflake and applying them to objects as grants
Core Snowflake setup
As SOLE is a core component of the DataOps platform, it is responsible for the standard Snowflake object setup and management for DataOps projects. The base DataOps project template includes the following objects that SOLE then manages:
- Main DataOps database, namespaced across multiple environments and cloned for feature branches
- Warehouses for data ingestion and transformation
- DataOps roles for read and read/write access
- DataOps users for delegating access
A typical DataOps project builds on these managed objects, often adding schemas and tables, additional roles/grants, etc.
Every DataOps pipeline will run a Setup Snowflake job to apply the latest configuration to the current branch environment.
Objects for third-party tools
In addition to the Core Snowflake Setup, many DataOps projects will orchestrate third-party tools for the ingestion and transformation of data, such as Talend, Matillion, and Fivetran. Although many of these tools can create and manage their tables and views, DataOps' best practices recommend that all database objects are managed from a Single Source of truth: The DataOps Project. Therefore, SOLE is configurable to include the schemas and tables that these tools require.
Everyday use cases for third-party tools include:
- Ingestion of data into Snowflake - SOLE can manage the target schemas and tables needed for ingestion.
- Processing and transforming data within Snowflake - SOLE can manage all the schemas/tables involved.
- Extraction of data into other platforms
Additionally, SOLE can manage the specific warehouses assigned to these tools, along with the user accounts, roles, and grants needed to allow third-party tools the correct access to Snowflake.
Roles and grant management
In addition to supporting the vast majority of first-class objects within Snowflake, SOLE can also manage the specific grants of object privileges to roles and granting roles to other roles (role hierarchy).
As well as managing the roles themselves, SOLE provides a
grants block within each object's configuration that is used
to specify which roles are granted which privileges on the object. Part of role management allows for roles to be granted to other roles and specified users, maintaining the Snowflake role hierarchy.