Streamlining snowflake management with Terraform: Our journey

Why we needed a better way

As Bitpanda continues to grow and become more data-driven, efficient data infrastructure management has become essential. Snowflake is central to our operations, but our manual approach to managing resources and permissions was limiting us, making scaling difficult and auditing near impossible. Managing Snowflake objects - such as databases, warehouses and policies - manually across multiple environments led to inconsistencies and increased the risk of human error, especially with sensitive resources like masking and network policies.

Our manual process quickly became unsustainable, creating an urgent need for a solution that would improve efficiency, auditability and scalability.

Terraform proved to be an ideal choice, with automation capabilities that allowed us to manage Snowflake objects and grants consistently, providing built-in standardisation and version control. But beyond building a technically robust system, we wanted to make it accessible for all users - both technical and non-technical - so they could manage resources independently. Achieving this required an intuitive, user-friendly way to interface with Terraform.

We adopted a configuration-based approach, creating simple configuration files that made the process accessible to teams with minimal technical expertise. This setup allowed anyone to create objects or request privilege assignments without needing deep knowledge of Snowflake or Terraform, further streamlining operations across the organisation and enabling teams to manage their resources more effectively.

Object configuration: simplifying Snowflake management

The first step in our journey, aside from configuring the Terraform provider to connect to our Snowflake instances, was importing the existing objects to reflect the current state of our environments, staging and production. This process ensured that all the manual configurations and objects already in use could be easily transferred and managed within the Terraform state.

After that, we focused on designing a solution that had to be consistent for both objects and grant creation. That’s where a configuration-based approach came into play, offering the flexibility to automate Snowflake objects and grants management while keeping the system intuitive and maintainable for our users.

We started by defining core administrative Snowflake resources -such as databases, warehouses, network policies, masking policies and storage integrations -in configuration files. With a straightforward structure, these files made resources accessible without needing to write DDL (Data Definition Language) statements.

Here’s an example of a configuration file used for a database:

We simply define in the configuration file a list of databases with several attributes like name, description and any related configuration. When the configuration is deployed through Terraform, the database is automatically provisioned. Updating the system is effortless - adding, modifying or dropping a database is as simple as adjusting the configuration.. If a new attribute needs to be assigned to one specific object but not others, we make it optional in the code and adjust the configuration file accordingly. This keeps the process efficient, minimising effort for both the development team and the end user.

Another of the primary advantages of this approach is that it eliminates configuration drift between our staging and production environments. Without a standardised, automated process, objects can easily end up being created in one environment but not in another, making it challenging to identify and address discrepancies over time. By defining all resources in configuration files, we ensure that every deployment aligns with the intended setup across all environments. This approach keeps the process efficient, reducing the effort required from both the development team and end users.

Managing permissions and grants: a modular approach

If managing objects manually was already a hassle, handling grants was even more challenging. Ensuring the correct privileges for each role - following Snowflake’s RBAC (Role-Based Access Control) approach - meant managing hierarchies and applying grants across multiple layers like databases, schemas and tables. This complex and essential administrative task relied, again, on manual processes with limited version control, making it nearly impossible to maintain consistency or effectively track changes over time.

While our configuration files provided an intuitive solution for creating and managing objects, developing a similar approach for grants presented a challenge. Unlike object creation, assigning grants involves intricate privilege statements and hierarchical role assignments, which are far less intuitive - especially for non-technical users. We needed a solution that could simplify both the use of configurations and the complexity of assigning grants and permissions.

Our solution needed to address two main challenges: it had to support roles with different access levels across multiple resources, including current and future grants on databases, schemas, tables and more. Additionally, it needed to provide flexible permissions that could adapt to the specific needs of each role - for instance, some roles would only require read access, while others needed permissions to perform DML operations.

To meet these requirements, we explored and evaluated with pros and cons of two possible approaches for organising grants:

1. Organise by resource type
Each file represents a resource type (for example databases), containing all grants for that type.

• Pros:

  • Centralises permissions by resource, making it easier to track access levels across similar resources.

  • Simplifies the application of consistent permissions across all resources of a given type.

• Cons:

  • As permissions grow, files can become large and difficult to manage, reducing readability.

  • Limits flexibility for unique permissions, making resource-specific changes more complex.

2. Organise by role
Each file is dedicated to a specific role, detailing all resources that the role can access.

• Pros:

  • Simplifies role-based management by containing all permissions for a role in one file, making updates straightforward.

  • Aligns well with Snowflake’s RBAC model, supporting centralised role management.

• Cons:

  • Makes auditing access for a specific resource type more complex, as permissions for a resource are spread across multiple role files.

In the end, we chose to structure files by role because it best supported our goal of empowering teams to manage their own access needs. This role-based structure made it easier for users to identify their roles and understand their associated permissions, creating a more self-service experience.

Let’s take a look at how this setup works with an example for the role example_role. We define a file named example_role.yaml with the following structure:

We begin with viewer_privileges, a reusable permission set that provides consistent read-only access to resources within EXAMPLE_DB, including future schemas and tables. By referencing viewer_privileges, we apply this access to example_role without duplicating permissions. The role is then inherited by EXAMPLE_ROLE_CHILD and directly granted to the EXAMPLE_USER user.

While implementing this approach, we initially found that it met our needs. However, as we developed it further, we recognised that each new role required replicating similar privilege groups within its configuration file, leading to redundancy. Beyond efficiency, this duplication posed a governance challenge, as it made it harder to enforce consistent access standards across roles.

To address both issues, we introduced access levels - specifically, admin, operator, and viewer - that additionally aligned with data governance standards. We grouped these privileges into configuration files like viewer.yaml for easy reuse:

These grouped privileges are then referenced in role-specific files, such as role_example.yaml, keeping the configuration slim and easy to maintain:

This approach reduced redundancy while reinforcing governance by ensuring that permissions were applied consistently across the organisation. By grouping access levels in dedicated files, we streamlined the setup, simplified auditing, maintained strong oversight of permissions across all roles, and made it easy for teams to manage their own access needs in a self-service model.

Conclusion

In our journey to streamline Snowflake management with Terraform, we achieved more than just an automated solution. Through a configuration-driven setup, we significantly improved efficiency by reducing manual work and streamlining updates across environments. Using version-controlled configuration files has enhanced trust by ensuring consistent, reliable states across resources, while providing clear auditability for greater confidence in our data environment. Most importantly, this approach has fostered empowerment: teams can now create their own objects and manage grants independently, enabling a more self-service model that supports growth and autonomy.