Increasing database efficiencies with SqlDBM and Snowflake

About the company

Sophos is a worldwide leader and innovator of advanced cybersecurity solutions, including Managed Detection and Response (MDR) and incident response services and a broad portfolio of endpoint, network, email, and cloud security technologies that help organizations defeat cyberattacks.

As one of the largest pure-play cybersecurity providers, Sophos defends more than 500,000 organizations and more than 100 million users globally from active adversaries, ransomware, phishing, malware, and more.

Recently, their leadership pushed for a digital transformation to keep up with their growing need for data insights. A key step in that push was maximizing their data warehouse, and a prerequisite step to that was modeling and documentation. SqlDBM plays a key role here.

Before SqlDBM, the data teams Sophos saw themselves being held back by a lack of visibility and understanding of shared data across multiple workstreams. No centralized repository existed to discover shared data assets and communicate changes across the data landscape. 

Sophos knew they needed a modeling tool but did not know which would be the best fit. While many modeling solutions provide robust technical functionality, SqlDBM tipped the scales with its cloud-native design and collaboration features.

Their first first step towards better governance was to reverse engineer their existing Snowflake database. The initial model took SqlDBM seconds to recreate, and after a few styling and formatting adjustments, it became the single source of truth for discovering and understanding the hundreds of existing tables. 

Besides providing a one-stop shop for data discovery and documentation, a single data modeling tool allows Sophos’ Data Architects to apply a unified set of modeling standards across multiple teams and workstreams. With the “Validate on Project Save” feature, standards like case and object naming are automatically applied and kept consistent without manual review or intervention. 

Given the size and dynamic nature of Sophos’ data landscape, the ability to coordinate multiple workstreams is essential. With this comes the challenge of keeping the right people informed and giving everyone the right level of access.

The data team at Sophos achieves this through the strategic use of projects and subject areas. Projects allow Sophos to work in concentrated teams and control access for both modelers and viewers. Within a project, designs and projections can be further classified into subject areas, which group diagrams into a folder-like hierarchy. This gives the teams added flexibility to group designs by business team or department to keep things organized while maintaining a centralized object catalog at the project level. This way, objects can be used across multiple diagrams and subject areas and remain synchronized. 

SqlDBM offers the Sophos data team the ability to go beyond pure relational diagrams through the use of logical objects like views and procedures. Not only can such objects be displayed on the diagram alongside physical tables, but they also benefit from the rest of SqlDBM’s project features like version control, alter scripts, and git integration.

However, the Snowflake-specific that piqued their interest the most is our Column Lineage Analysis. This feature gives the developers visual column-level lineage for Snowflake views to help them instantly identify sources, joins, and transformations while also allowing them to drill into the underlying formulas associated with each column and trace it up and down the model. 

One developer estimated that SqlDBM easily saves his team 30-40 hours of manual effort per month. A large portion of this efficiency comes from our ability to quickly inform their users on their model’s context. No longer are they working within a bunch of SQL code, now they have visual representations of how their tables and views interrelate within their model.

The end goal for Sophos is to give the business team a one-stop solution to allow them to achieve data as a service. Just as the data team exists to support the business, so too should their modeling solution. It should quickly orient a user within the organization’s data landscape and help them understand the underlying business context. 

SqlDBM’s database documentation screen was designed for technical and functional profiles alike. Users can complete object-level descriptions from a single screen without having to write SQL and search through many objects: from object names to column names and even the descriptions themselves. They can even communicate with their data teammates directly inside the tool to comment on requirements and ask questions.

For increased visibility and reduced administrative overhead, their team is leveraging our Conflucne integration. It allows them to embed their project on a wiki page - giving everyone instant access to the latest project revision. This gives the users a single point of reference for navigating their data landscape and lowers the learning curve for onboarding new team members. With this, they don’t have to learn how to navigate through SqlDBM.

After realizing the first benefits of visualizing the data model, Sophos embarked on fa new engineering endeavor. Using the self-service foundation that they initially set up, the data warehouse team began modeling a new business-focused integration layer that would bring together various business data domains into one governed data source for business users to consume. The project is in full swing currently - with SqlDBM and Snowflake at the forefront.