Snowflake and data modeling tools: what's out there?
Let's look at three options available: Enterprise Architect, SqlDBM and Visual Paradigm
Not subscribed yet? Use the button below:
Hi, I’m Luigi. I share my notes, observations, analyses, and what I learned from the masters.
Opinions expressed are solely my own and do not express the views or opinions of my employer.
INTRO
Up to some years ago, the king of data modeling tools was ERwin. In multiple projects where a serious modeling effort was necessary, this was the tool popping up over and over again.
Today the market is overflooded with data management solutions and many modeling tools are available for us data engineers.
To emerge against the competition a lot of those tools tend to get very platform-specific and focus all their efforts on excelling in a specific set of use cases.
TODAY’S COMPETITORS
To make our way in this jungle of tools, we need first to define our requirements, a few, fundamental requirements that are show stoppers in our decision-making.
The ones I mostly took into consideration for this article are:
Integration with Snowflake
Logical and Physical data modeling.
Reverse engineering features (generate a data model in the tool using SQL)
Forward engineering features (generate a DDL script from a data model created in the tool)
Version control
Compatibility with Mac and Windows
I will mention only three of the tools I had the pleasure to try and give my personal feedback on them.
I won’t go into the details but hopefully, you will get an idea and decide to try them by yourself (or not).
Enterprise Architect
Enterprise Architect (EA) by Spark Systems is quite a popular tool that doesn’t promote itself only as a data modeling tool but is used for various purposes in software development, systems engineering, business process modeling, and enterprise architecture.
The tool is full of functionalities and offers UML and Class modeling capabilities.
Those might be useful in the initial design phase to help the business teams concretize the requirements in a class diagram form.
The tool allows to convert Class models into ER models with the possibility of generating DDL and create the designed database (forward engineering). There are also backward engineering features but unfortunately, they don’t cover Snowflake databases.
It can connect to Snowflake via ODBC but there is no way to get a complete integration between the two platforms.
According to their documentation, they offer support for version control through a set of dedicated settings, but honestly, I didn’t dig into the details of the configuration.
Talking about documentation, I must say that documentation is one of the main problems with this tool. There is a massive amount of documents that are mostly not clear or not enough detailed and very difficult to navigate.
The UI is also quite complex (EA has a huge amount of features) and the learning curve is pretty steep.
The software is very desktop-oriented and offers limited cloud support.
The price part is a big plus for EA. It’s the cheapest tool in the list offering the best value for money.
The licenses are perpetual and offer free updates for 12 months. There are 4 license tiers but even the highest one is quite affordable.
There is no installer for Mac but it can be used thanks to compatibility layer software like CrossOver.
SqlDBM
SqlDBM is for sure the tool I have been using the most and offers the most flawless experience when working with Snowflake (the two companies are partners).
The tool supports also other data platforms like Azure, Databricks, BigQuery, Redshift, Oracle, SQL Server, PostgreSQL, and MySQL.
The code generated using the Forward Engineering feature is always pretty accurate and usually doesn’t require any refinement.
The Reverse Engineering feature works well even if it doesn’t allow to connect to Snowflake databases when the 2FA is enabled.
The tool is specifically designed for modeling databases and it does it pretty well, however doesn’t offer other features like UML, Class diagrams, and similar.
The in-build versioning control system is well done, but there is also the possibility to connect SqlDBM directly to Git.
There is also an API that gives interesting integration possibilities with your models.
All the features are pretty well documented and the customer support is quite efficient (at least according to my experience).
The UI is intuitive and well-designed, some features are a bit more difficult to reach but all the main ones are at hand.
There are no OS compatibility issues since SqlDMB is only available online and doesn’t have a desktop installer (this can be a downside for data engineers who need to work offline).
SqlDBM can be quite pricey, there is no perpetual licensing rather only monthly/annual billing models. The company is also a bit mysterious about its prices since out of the 5 available license tiers, only 3 of them have publicly available price data.
Also, the features available in the layers are not stated very clearly, and you have often the feeling that what you need is always at the tier above.
There is no free “view only” user option rather a license is required, so the only way to share your models with business teams or people not directly involved in working with SqlDBM is to export them in SVG or PNG formats, which is annoying.
Visual Paradigm
Visual Paradigm (VP) is a versatile and multipurpose software designed to support various aspects of software development, system engineering, and business processes. It provides a range of features that cater to different modeling needs, including UML (Unified Modeling Language) diagrams, BPMN (Business Process Model and Notation) diagrams, database modeling, and more.
The learning curve for this tool is steep since it offers a lot of features (maybe too many) and this has an impact on the user experience. The UI is a bit overwhelming and makes it difficult (and time-consuming) to complete the desired tasks (at least as a beginner).
Since it supports UML and Class models it helps to close the gap between data engineers and business users who are not used to work directly with Logical and Physical models.
It allows you to convert Class models to ER models and ER models to DDL scripts that can be run on your database of choice. It offers a Snowflake connector out-of-the-box even if the generated code might need some fine-tuning before use.
Figuring out the steps to switch between those models was not straightforward.
The software comes with a desktop and online version, with some unexpected limitations, i.e. VP doesn’t allow to export projects from the desktop version of the software to VP Online (only the other way around is permitted).
There is an in-built version control system that can be set up for all the members of your team.
Setting up the version control configuration is a bit tricky but I decided not to go into details (let me know in the comments if you are interested in a step-by-step guide).
VP licenses can be bought as a one-time purchase, or with a subscription model. The prices and the features offered at each tier are clearly described on the VP website and from a cost perspective, it fits between EA and SqlDBM.
Conclusions
If you jumped directly to this section hoping to find a solution to your problems I am afraid you will be disappointed.
I won’t give a verdict about those three tools, since each of them targets specific use cases and requirements.
The purpose of this article is to give you an idea of what those tools have to offer and maybe help you find the right tool for you.