Written by: Riley Venable

About the Author: Riley Venable is an experienced Atlassian consultant with deep expertise in cloud migration, ITSM, and workflow optimization. With a strong background in helping organizations maximize their use of Atlassian tools, Riley is dedicated to driving efficiency and delivering tailored solutions for clients across various industries. Passionate about the latest developments in the Atlassian ecosystem, Riley shares insights on how teams can leverage technology to achieve their goals.

---

Required Snowflake Configurations

• Access to ACCOUNTADMIN Role

• Warehouse (One to One)

• Database (One to One)

• Schemes (One to Many)

• If Network Policies allow outgoing Atlassian IP addresses

• Recommended a read-only user and role for Atlassian Analytics

---

Creating a read-only Role in SnowFlake for Atlassian Analytics

1. Open your Snowflake Worksheets page and choose your worksheet to run the following statements in the console.

2. Create the role by running the following statement, which will create a role called ATLASSIAN_ANALYTICS_READ_ONLY: CREATE ROLE IF NOT EXISTS ATLASSIAN_ANALYTICS_READ_ONLY;

3. Grant privileges by running each GRANT statement sequentially to grant the appropriate privileges to the read-only role.

   • To grant usage on a warehouse, run the following statement: GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;

   • To grant usage on a database, run the following statement: GRANT USAGE ON DATABASE <database_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;

   • To grant usage on a schema, run the following statements for each schema you’d like to grant Atlassian Analytics access to.

     • To grant usage on a schema, run the following statement while having your database selected in the Worksheet: GRANT USAGE ON SCHEMA <schema_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;

     • To grant usage on all schemas, run the following statement: GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;

     • To grant access to all tables in a schema: GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO ROLE <role_name>;

     • To grant access to all tables in a database, run the following statement: GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;

---

Create a read-only user in SnowFlake for Atlassian Analytics

Now, create a new user who will be assigned to the Atlassian Analytics read-only role. In a Workbook, run the following statement: CREATE USER IF NOT EXISTS <username> PASSWORD = '<password>' MUST_CHANGE_PASSWORD = FALSE DEFAULT_ROLE = READ_ONLY; GRANT ROLE READ_ONLY TO USER <username>;

Also, verify in SnowFlake that the user was added correctly to the role.

1. Navigate to the Admin section, select user and roles, and navigate to roles.

   

2. Inside the ATLASSIAN_ANALYTICS_READ_ONLY role, validate that your Atlassian Analytics read-only user is granted access to the role.

   

---

Add Snowflake to Atlassian Analytics

All connection details are case-sensitive. Snowflake stores object names in uppercase unless you wrap the names in double quotes ("") when you create the object. This applies to warehouse names, database names, and schema names.

To add Snowflake to Atlassian Analytics:

1. Select Data from the global navigation.

2. Select Add data source > Snowflake.

3. Fill out the required fields of the connection form.

   1. Account identifier

      1. 
      2. 

      3. The first part of your Snowflake URL: account-name.region (.snowflakecomputing.com will be filled automatically DO NOT INCLUDE THIS)

   2. Account user - The username of the read-only user you created.

   3. User password - The password of the read-only user.

   4. Database name - The database you granted access to.

   5. Warehouse name - The warehouse you granted access to.

   6. Schemas - Select the schemas you granted access to.

4. Select Connect.

---

Restricting Data Source Access in Atlassian Analytics

Once you create your connection in Atlassian Analytics, you can restrict who can access the data source.

1. Data Source Access Permissions

People with access to this connection can build charts using any data included in it. You can manage who has access to the data source by setting data source permissions.

2. Restrict Who Can Query the Data Source

• Only Specific People: You can restrict data source access to only specific individuals who need to build charts or perform analyses.

• Anyone with Access to an Enterprise Plan: Alternatively, anyone with access to a product that has an Enterprise plan and Atlassian Analytics can be given access to query the data source.

To manage data source access effectively, ensure that permissions are granted only to those who require it to prevent unauthorized data usage and maintain data security.

---

Getting More Granular with Configuration

For users looking to have more granular control over their Snowflake connection, consider the following steps to further refine your configuration:

1. Granting Permissions at a More Granular Level

• Instead of granting access to all tables or schemas, you can specify individual tables or views that Atlassian Analytics can access. For example: GRANT SELECT ON TABLE <schema_name>.<table_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;

  This allows you to limit data exposure by granting access only to specific tables that are necessary for analysis.

• You can also create different roles for different teams or use cases. For example, create separate roles for financial data and operational data, each with distinct permissions:

  CREATE ROLE FINANCE_ANALYTICS_READ_ONLY;
  GRANT SELECT ON TABLE finance_db.finance_table TO ROLE FINANCE_ANALYTICS_READ_ONLY;

2. Using Row Access Policies

• If you need to control access at the row level, consider implementing Row Access Policies in Snowflake. This allows you to restrict the rows a user can access based on certain criteria (e.g., department or region).

  CREATE ROW ACCESS POLICY region_policy AS (user_region STRING) RETURNS BOOLEAN ->
  CASE
    WHEN current_role() IN ('ATLASSIAN_ANALYTICS_READ_ONLY') THEN region = user_region
    ELSE false
  END;

  Attach this policy to the tables you want to enforce row-level security on.

3. Masking Sensitive Data

• To protect sensitive data, you can use Dynamic Data Masking in Snowflake. This will help ensure that sensitive fields, such as personal or financial information, are obfuscated unless the user has explicit permissions.

  CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING ->
  CASE
    WHEN current_role() != 'DATA_ANALYST' THEN 'XXX-XX-XXXX'
    ELSE val
  END;

  Apply this masking policy to columns containing sensitive information.

Known Limitations of Atlassian Analytics

• Query Timeout: SQL queries can only run for up to 5 minutes. Longer queries will time out. To mitigate this, consider optimizing your queries to reduce execution time.

• Row Limit: SQL queries cannot return more than 1,000,000 rows. If more data is required, consider breaking your query into smaller parts.

• Data Limit for Charts: The maximum size for data returned by a chart’s queries is 20 MB. Consider filtering data, reducing the number of rows, or choosing fewer columns to stay within this limit.

---

Full Quickstart Worksheet

CREATE ROLE IF NOT EXISTS ATLASSIAN_ANALYTICS_READ_ONLY;
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;
GRANT USAGE ON DATABASE <database_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;
GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE <database_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;
GRANT SELECT ON ALL TABLES IN DATABASE <database_name> TO ROLE ATLASSIAN_ANALYTICS_READ_ONLY;
CREATE USER IF NOT EXISTS <username> PASSWORD = '<password>' MUST_CHANGE_PASSWORD = FALSE DEFAULT_ROLE = READ_ONLY;
GRANT ROLE READ_ONLY TO USER <username>;