Automating SOX and Internal Controls Monitoring with Snowflake

With the surge of IPOs, fast-changing business environments, and increasing regulatory requirements, Sarbanes-Oxley (SOX) compliance reporting has become more time consuming and costly. An effective data-driven analytics approach with actionable insights is the preferred way to increase efficiency and greater confidence in compliance and risk management. At Snowflake, the Data Cloud has been critical to managing SOX compliance and internal controls. 

Challenges to the traditional ways of managing SOX compliance

An increased volume of, and variation in, data sets has made it harder for audit, risk, and compliance functions to identify and monitor controls for operating effectiveness. In addition, traditional audit techniques involving time-consuming evidence collection and sampling no longer provide sufficient risk coverage or real-time visibility into costly exceptions and inefficiencies. 

Some common use cases where we see challenges:

• User access and security over key systems, such as unauthorized access to sensitive roles or segregation of duties violations
• Key configurations setup and deviations from company policies and procedures
• Journal entry analysis, especially given large volume and complexity seen in transactional data 

Additionally, internal audit, controllership, and compliance teams are unable to act and respond in real time. Given the limited adoption of real-time data analysis and alerting, teams are often surprised with deficiencies highlighted during an external audit.

How we architected and built a solution on Snowflake 

As we embarked on our IPO journey and SOX readiness efforts, we noted a lot of manual activities in both performance and testing of certain key controls. By “thinking big,” one of our core values at Snowflake, we saw an opportunity to automate some common SOX controls across our in-scope financial reporting processes using Snowflake. 

Snowflake is a highly scalable, cost-effective platform that can ingest and store many types of data, including semi-structured data, in one place to help monitor controls effectiveness: queries, flagging exceptions, alerting, and reporting compliance posture in near real time. So we went about building out Snowflake analytics for SOX controls monitoring over the course of a few weeks, focusing on a few use cases that met this criteria:

• Data availability: Control population is easily accessible and can be validated for consistency and completeness.
• Stable process and data knowledge: The process and controls are not expected to change in the near future, and there is enough knowledge about the risks and policies associated with the data
• Large volume and complex data sets: Snowflake is purpose-built to perform complex queries on large data sets from multiple data sources, so use cases with huge volumes of data would be good candidates for automation.

The following architecture is being used to automate SOX controls monitoring. It shows how we ingest data and analyze exceptions through real-time alerting:

At a high level, here are key steps to automating SOX controls monitoring:

• Identify the key use cases that would provide useful insights to the business.
• Ingest required data into Snowflake using connectors.
• Design and implement queries (using SQL) to visualize and analyze the data.
• Implement monitoring and alerting for anomalies to alert the concerned teams in real time so they can take the required actions (e.g., Slack alerts, ServiceNow or JIRA ticket created and assigned to concerned teams if a journal is posted without approvals).

3 SOX automation use cases

Using the steps above, we have automated the following three use cases:

1. Journal entry analysis 
2. User access management as part of IT general controls
3. Segregation of duties analysis

In this section, we’ll highlight some key challenges and value derived from the Snowflake solution for each of these use cases.

Journal entry analysis 

Data analysis is essential to understanding processes and controls over journal entries and adjustments, especially when dealing with complex ERPs, multiple data sources or sub-ledgers, and large volumes of data. Despite multiple tools in the market, it is still time consuming to easily ingest data and analyze potential errors or fraud in real time; however, with Snowflake’s  core computing capability, we were able to analyze over 2 million transactions from multiple journal entry sources in a matter of seconds.

We developed a few use cases (shown below) that have been really beneficial to address audit concerns over journal entry controls, and also provide insights into potential operational inefficiencies. 

Key advantages:

• Manage audit risk: Enabling SOX compliance teams with impactful insights to ensure sufficient coverage over key controls such as configurations, workflows, data interfaces, and unauthorized access to journals. This also expands risk coverage by monitoring the entire population instead of just reviewing samples.
• Real-time exception monitoring: Empowering our accounting and compliance teams with complete, real-time visibility into various journal entry transactions and anomalies.
• Reduce cost and process inefficiencies: We use Snowflake to reduce the audit burden associated with documenting evidence as well as to help analyze journals to see if the frequency of posting for low monetary journals needed to be revisited to ensure an efficient financial close process.

As shown above, some example use cases that are analyzed include:

• Unusual journals: Journals posted on weekends or non-working days, high dollar amount journals, etc.
• Unexpected users: Reviewed journal creators/approvers and identified if there were any journals created and posted by the same user, or created and posted by users not part of appropriate security groups. 
• Unusual account combinations: Account combinations like capitalized expenses or unusual revenue entries.

User access management 

Insider threats via unauthorized access to critical systems is a major risk to any organization and can lead to significant reputational and financial damage. With Snowflake, we were able to automate monitoring over SOX access management controls by building queries to flag real-time exceptions. As part of this implementation, we first ingested user access logs, then built logic for monitoring and alerting exceptions. The key to designing this logic was understanding the access data model (e.g., specific roles, authorization concept and access) within the application and associated access policy. 

The key use cases we automated include:

• Access provisioning: Users who were granted access before approvals or without approvals, segregation of duties violations based on identified rules, unused licenses, etc.
• Access deprovisioning: Identified untimely access removal for termed users. We were also able to perform an impact assessment for this use case by checking for last login dates and any unauthorized activity.
• User access reviews: Track if any new roles or permissions were added or removed between quarters, or access granted after the recent access review but not aligned with authorized role-based policy and job responsibilities. 

Key advantages:

• Manage security risk: Helping SOX/Compliance and IT teams to identify and mitigate risks for instances of unauthorized access to sensitive data in real time. With our automated dashboards, we can also see an audit trail of security access violations for a given time period, which provides assurance over controls monitoring versus testing samples.
• Reduce audit cost: Significantly reduce audit burden in getting populations for testing and requesting screenshots for approvals. With automation, this can all be monitored at once.
• License monitoring and optimization: Application login dashboards also help monitor license usage and optimize it, resulting in cost savings.

These dashboards help our teams rectify failures before they become SOX deficiencies. They also help provide visibility into potential process improvements in controls design and access policies for certain applications. For example, we’ve now revisited the access roles and system abilities in one of our key sales applications, which has made quarterly user access reviews a much more efficient process.

Segregation of duties 

Implementing and testing segregation of duties (SoD) controls is an important element of internal control frameworks, and also a key focus of external audit firms. Without comprehensive SoD policies and advanced analytics that detect violations across thousands of application access points, SoD control implementation, testing, remediation, and mitigation can be extremely difficult to achieve.

This is where Snowflake helps. As part of our internal audit, we were able to identify our high-risk rules within and across our key applications to detect hidden SoD conflicts at both the role and user level. We then configured these rules within Snowflake and built queries to identify any roles/users that violated defined SoD rules. This included both IT and business SoD rules.

These dashboards have helped our teams to identify SoD conflicts in our environment and update the access as required to quickly remediate the issue. 

Key advantages:

• Manage security and fraud risk: Helping SOX/Compliance and IT teams to identify and mitigate risks for instances of cross-system or intra-system access that violates defined SoD rules. This access is monitored in real time and corrective action is taken, which could include access removal or risk mitigation.
• Maintain a centralized view of access governance: Leveraging Snowflake data lake as a centralized repository for access logs across systems provides a centralized single source of truth and a holistic, enterprise-wide perspective.
• Reduce compliance costs: Monitoring SoD risks at role level prevents proliferation of violations through user access assignment of such roles, which in turn helps reduce compliance costs.

Key enablers for successful and effective automation of controls monitoring

Building SOX monitoring automations through data analytics isn’t just about data and technology. To be able to rely on the automations, it must also deliver business value; be embedded into the audit methodology; and have people with the right skills to design, build, and monitor exceptions. Additionally, ensuring appropriate controls are in place is also a key factor for auditors and management to be able to realize the advantages highlighted above. 

Key success factors for effective automation reliance include:

• Program governance and cross-functional alignment: Clear identification and alignment on design requirements with key stakeholders: control owner(s) and Internal Audit, IT, and Compliance teams. To govern these automations, we’ve built a core team with defined roles and responsibilities:
  • Identify and align on the automation use cases that will drive maximum benefit.
  • Design requirements for automating (combine collective knowledge of audit, data, systems, and process).
  • Build requirements with sufficient quality checks. 
  • Design and testing of relevant controls both during and post implementation.
  • Develop tools and templates to consistently implement such automations.

• Analytics development controls: Similar to application development lifecycle controls, the development, testing, and deployment of these analytics should be owned by respective control owners and not just IT (or relevant team-building analytics). It is important that development and testing procedures be established and followed, and that they include all appropriate personnel from IT, Data Science, Audit/Compliance teams and process owners.

• Post-development IT controls: To ensure auditors can rely on these automations post-implementation, it is important that applicable policies and IT controls are implemented to manage access and change management, just like any key automations scoped out for SOX compliance. This is where the Audit and Compliance teams can provide guidance on evidence requirements for auditors to be able to rely on such automations. Thinking through and embedding these audit and compliance requirements within the automation framework early on will help organizations realize value.

• Ongoing monitoring process: Similar to designing IT controls, it is important to also consider monitoring controls to ensure timely and effective resolution of exceptions. Control owners should establish clear SLAs for monitoring, tracking, and reporting exceptions, which would be important for audit purposes. Existing service desk and alerting tools could be used to integrate with Snowflake to facilitate monitoring. At Snowflake, we enabled this by alerting real-time exceptions to concerned teams for them to take the required actions as per agreed-upon SLAs.

Some key takeaways as you decide to embark on a similar automation journey

• Know that it’s doable and focus on value proposition: Automating controls monitoring is not just limited to superhero risk and compliance teams. The key is to start with one or two high-impact use cases and align with relevant stakeholders on design and build requirements. Having a methodical approach and focusing on a few use cases first works versus trying to automate everything at once.

• Real-time visibility can drive accountability and actionable insights: Having day-to-day visibility into operation of key controls allows functional leaders to focus on what matters, proactively identifying and fixing errors versus being reactive to when issues arise.

• Automation should be fun: Our project using Snowflake was enabled by a culture of thinking big and not fearing failure. We enhanced our queries as we learned more about the process and tested the data. It was a few weeks of focused teams with an important mission: to drive value for our key stakeholders!