TL;DR:
Cloud infrastructure is a central component of modern data solutions. Reliable and sustainable operation requires a series of recurring administrative tasks, such as rights and role management or the administration of external data connections. With the SnowForm module for the Snowflake ecosystem, we offer a freely available Terraform module designed specifically for this type of administration, which handles repetitive platform management tasks in a transparent and easy-to-follow manner.
This article is a (partly) automated translation of the German original.
Snowflake has established itself as the industry standard for data solutions, extending far beyond pure data warehousing and use as a data lake. Snowpark, in particular, opens up opportunities to leverage the Python-based ecosystem for machine learning applications within the Snowflake platform and to rapidly bring even complex data science use cases to production. At the same time, Snowflake manages much of the required infrastructure behind the scenes, which is why teams of application developers can operate without extensive knowledge or skills in cloud engineering.
This fundamental flexibility regarding features, combined with the reduced workload for account administration, makes it possible to provide small- to medium-sized teams with their own accounts so they can tailor specific applications to their individual needs.
When such an approach is adopted within a company, a number of recurring challenges arise that affect all accounts operated in this manner.
Basic Infrastructure, Database Structures
First, it makes sense to establish organization-wide security standards to ensure that the company-wide Snowflake ecosystem is not exposed to security vulnerabilities. A corresponding security strategy can generally include rules regarding users, such as management via AAD/EntraID, synchronization with IAM systems, multi-factor authentication, standards for technical users, and so on.
As a fundamental tool for managing structures within the respective Snowflake accounts, Terraform (or OpenTofu) has established itself as the dominant tool in the Infrastructure as Code (IaC) space. Its configuration files provide a declarative method for managing and deploying cloud infrastructure, including specific Snowflake objects and integrations with external hyperscaler resources (such as S3/GS buckets).
A sustainable account architecture includes a division into different development environments (e.g., DEV, STAGING, and PROD) and a star schema, each with its own layers for data preparation and enrichment (e.g., RAW, PROCESSED, EXPORT, and ARTIFACTS).
Permissions and Role Management
Permissions Model
To establish the principles of least privilege and need-to-know across the board, granular permissions and data management are required for all these environments, whereby consistent structures should be created across all accounts (e.g., READ, READ & WRITE, and FULL permissions for individual databases).
Such permissions are granted in a transparent and straightforward manner via an Identity & Access Management (IAM) system coupled with a Role-Based Access Control (RBAC) mechanism. IAM-managed users can thus be assigned individual RBAC roles (e.g., Consumer & Developer). These roles are then granted the corresponding privileges in Snowflake. Combined, this could result, for example, in a role for an application developer in the STAGING environment who requires read and write access to database objects across all layers. For a business reporting use case, on the other hand, read-only access to the PROD data will typically be required.
To distribute these calculations appropriately, our first step was to develop snowform_access_roles as a Terraform module. This module makes it possible to assign privileges to roles in a hierarchical manner, iteratively across the specified layers. This results in centralized permission management that allows for quick adjustments and additions. The module can be easily integrated into a custom Snowflake infrastructure repository:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
module "access_roles" { source = "https://github.com/inovex/snowform_access_roles/local" db = snowflake_database.db schemas = [ { name = "YOUR_SCHEMA_1" }, { name = "YOUR_SCHEMA_2" } ] depends_on = [ snowflake_database.db, snowflake_schema.your_schema_1, snowflake_schema.your_schema_2 ] providers = { snowflake.useradmin = snowflake.useradmin snowflake.sysadmin = snowflake.sysadmin snowflake.securityadmin = snowflake.securityadmin } } |
Schemas can be specified as a list in the configuration. During deployment, Terraform first iterates over the specified configurations and creates a map of all combinable objects:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
variable "schemas" { description = “The data layers for which the access roles are defined.” type = list(object({ name = string } ) ) default = [ { name = "YOUR_SCHEMA_1" }, { name = "YOUR_SCHEMA_2" } ] ] } locals { schema_map = { for schema in var.schemas : schema:name => schema} } |
Using a for-loop (for_each), uniform resources are then created for these and assigned to the roles (using a “Reader” role as an example here):
|
1 2 3 4 5 |
resource "snowflake_account_role" “r” { for_each = local.schema_map provider = snowflake.useradmin name = “${var.db.name_${each.value.name}_R” } |
Similarly, the system then iterates through a set of required permissions for each role and assigns them, including permissions for future objects.
Data Sharing
Another key concept in Snowflake data management is designed to make it easy to share data with other accounts. To this end, Snowflake has developed the concepts of listings and data shares. Simply put, shares provide a collection of views for a consumer account, with content identical to the underlying data sources from the provider account, which acts as the data owner.
Particularly in the case of an enterprise-wide architecture consisting of a number of smaller teams with their own accounts and data products, it is desirable to integrate this data into other accounts with minimal effort and in a traceable manner. We enable precisely this integration through the snowform_import_listing module.
In a two-step process, databases are first created for the respective shares, and the required privileges are then automatically assigned to the previously defined roles. This allows shared data to be easily managed within the consumer accounts. New data sources can be added as variables simply by specifying the name of the share and the new database to be created.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
module "snowflake_shared_databases" { source = "github.com/inovex/snowform_import_listing.git?ref=0.0.2" providers = { snowflake.sysadmin = snowflake.sysadmin } snowflake_shares = [ { database_name = "IMPORTED_DB_NAME" share_name = "YOUR_ORG.SOME_ACCOUNT.SHARE_NAME” }, ] account_roles = [ "ANALYST_ROLE", "DATA_ENGINEER_ROLE", ] } |
Organizing Shares
A particular challenge arises in this type of architecture, which involves many small accounts and shared data: An increasing number of shared databases makes it difficult for developers and analysts to maintain an overview when they need to search for data across a large number of databases. If the data comes from different providers and is used simultaneously for a common use case, we believe it makes sense to group this data logically. The snowform_logical_import_layer module enables precisely this logical organization of shared data across consumer accounts. At its core, this module consists of a series of schemas into which the existing shared views are organized, aggregated by business domain:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
module "logical_import_layer" { source = "github.com/inovex/snowform_logical_import_layer.git?ref=1.0.0" # Map source objects to logical layer targets source_to_target_mappings = { "X.YOUR_SCHEMA_1.TABLE_V3" = { target_database = "LOGICAL_PRODUCT_DATA" target_schema = "PRODUCT_DATA" target_name = "PRODUCT_MASTER" } "Y.YOUR_SCHEMA_1.TABLE_V1" = { target_database = "LOGICAL_PRODUCT_DATA" target_schema = "PRODUCT_DATA" target_name = "MATERIAL_UNITS" } |
Practical Use Cases
We demonstrate the practical benefits of data engineering at dmTECH, a subsidiary of dm-drogerie markt GmbH & Co. KG. As dm’s technology company, dmTECH develops and operates digital products and IT solutions for both dm customers and the teams at dm stores and at dialogicum. In the Enterprise Resource Planning (ERP) environment, autonomous product teams work on a wide variety of use cases and processes. Each team is responsible for its own products and develops tailor-made solutions for the company’s diverse requirements. Across a wide range of business areas, SnowForm enables us to quickly set up the basic infrastructure, allowing us to then focus on developing various use cases until they are ready for production. For implementing business logic—which consists of a mix of ETL and ML processes—a Spark implementation like Snowpark is naturally the ideal choice, offering the well-known advantages of a Python-based ecosystem.
AI-Powered Product Matching
We have developed an LLM-based approach for comparing similar but not identical products. For people with domain expertise, it is easy to compare similar products from different manufacturers based solely on product information. For an algorithm, however, selecting the best possible match among several very similar items often involves a high degree of uncertainty. A combination of Snowflake’s Arctic model, an additionally integrated OpenAI model, and a domain-specific set of rules enables us to identify suitable product matches.
Data Quality Platform for Master Data
Another use case we are actively working on is a data quality platform for master data. Using a Snowflake account, we integrate all master data from the ERP system and build data products from it that are shared company-wide. At the same time, we are establishing standardized data quality checks and analyses to continuously ensure data quality. Of particular interest is the use of machine learning to detect complex or even unknown data quality issues and to prepare automated corrections for bulk data maintenance.
Supply Chain Optimization
Another example is a Snowflake account serving as a central hub for analytical use cases focused on optimizing dm’s logistics supply chain. What’s particularly exciting here is that many different teams with a wide variety of needs across the supply chain are supplied with data from this account and also have the ability to work independently on dedicated warehouses using dedicated schemas. Using SQL or Snowpark, a wide range of use cases—from ad hoc analyses to simple automations (e.g., using tasks or external tools) to complex, highly automated processes—are implemented and operated on the account.
Conclusion and Outlook: The Path to a Self-Service Platform
An important aspect of all these examples is designing part of our Snowflake account as a self-service platform to empower business units to work with data more quickly and directly. With our small teams, implementing all these aspects in parallel within a domain-specific Snowflake account was only possible because we automated and abstracted extensive foundational elements of role management and Snowflake shares using Terraform modules.
An extension we’re already using also allows us to integrate data from outside the Snowflake context. Using so-called “external stages”—that is, Snowflake objects that point to storage buckets in Azure or GCP—we retrieve data from other domains to use it in our use cases. Since this also involves a list of regularly recurring (Snowflake) resources, it makes sense to modularize these as well in the future.
