data modeling tools hero" width="752" height="435" />The world of data modeling has lots of tools. Here are some to get you started.
Written by Stephen Gossettdata modeling tools hero" width="752" height="435" />
The core essence of data modeling is plotting and illustrating the relationship between various entities, whether that’s inside an application database or within a data warehouse architecture. You can do that with pen and paper. But more often than not, you’ll want data modeling tools to get the job done.
Data modeling tools help organizations understand how their data can be grouped and organized — and how it relates to larger business initiatives. Finding the right one is essential to improving software development, analytics and business intelligence processes and documentation.
The question of what data modeling tools to consider can be challenging though. Marta Vovchenko would say it’s contextual.
“It’s a question of the right tools for the right situation,” Vovchenko, former director of business intelligence at DoorDash, told Built In. “You have to keep in mind that, if you’re connecting different solutions to your databases, that means you might be opening up your databases.”
With the help of three experts — Vovchenko; Ajay Gupta, former manager of data architecture at Appnovation; and Mang-Git Ng, founder and CEO of Anvil — we compiled a list of 20 data modeling tools you should know.
Think of Draw.io as the bridge between pen and paper or whiteboarding to something a little more formalized. It’s a free, intuitive, browser-based flowchart builder in which users can drag and drop entity shapes (including the ellipses and parallelograms common to data models) onto a canvas, then join them with connector lines. It’s a handy way to get something down quickly, even for someone lacking access to high-powered software.
It might not make sense in contexts that deal with a lot of unstructured, schema-on-read data, like event-based data sets, but it’s smooth for things like finance models, which are highly structured, Vovchenko said.
“The very first model that was drawn for finance [at DoorDash] was actually drawn in Draw.io,” she said.
An SQL plugin, which automatically builds entity shapes and attributes from SQL code, is also available.
Price: Free
Key Features:
Lucidchart is a diagram builder similar to Draw.io, but it promises more complex flows and stronger data protection. Unlike Draw.io, Lucidchart’s unlimited plans carry a cost. Lucidchart links to SQL, which means users can pipe their canvases into their actual database management system. Lucidchart supports collaboration as it can be integrated with Google Workspace, Atlassian, Microsoft Office, Asana and Slack. It also helps teams work together with built-in chat and co-authoring and comment capabilities.
It’s part of DoorDash’s toolkit too. “Historically, we’ve had a lot of microservices based on PostgreSQL databases,” Vovchenko said. “Using Lucidcharts was helpful because you can connect it directly to those databases, then read the schema and work with the already pre-documented diagram.”
Price: Contact sales on the company website
Key Features:
There are three overarching considerations when working with data models, according to Vovchenko: agility, consistency and data integrity. Agility effectively translates to a willingness to accept the fact that no single data-modeling tool is going to suffice across all use cases.
DoorDash has utilized SQuirreL, a free, open-source graphical tool that’s supported by most major relational databases. Described by Vovchenko as easy to install and deploy, it lets users see database structures and draw entity-relationship diagrams from existing schemas, among other features.
Open-source options may be particularly attractive to net-new businesses, since, as Ajay Gupta noted, connecting those applications to older data environments can sometimes require the added step of front-ending an open database connectivity (ODBC) API.
Price: Free
Key Features:
Datensen’s Luna Modeler is a database design tool that’s compatible with SQL Server, PostgreSQL and MariaDB. The tool allows users to draw ER diagrams as well as reverse engineer them by importing existing databases. Luna can also create reports in a variety of formats and generate both custom and SQL scripts.
Data modelers can also compare external projects to the database created in Luna to better understand what tables, indexes and relationships are missing from either the database or project and update them accordingly. Luna also allows for colorized items and HTML notes.
Price: Tiered price options; starts at $99
Key Features:
This free, open-source and cross-platform data-modeling GUI layer for MySQL databases has a reputation for intuitiveness and ease of use. The tool is designed for database architects, developers and database administrators and is available on Windows, Linux and Mac OS X. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.
Vovchenko said she previously used MySQL Workbench for structured data alongside the MicroStrategy analytics platform.
“Having the ability to use Workbench to develop a model, then overlay it with this extensive logical model to use for reporting purposes, was very helpful for us,” she said. In fact, her team formerly had a regularly scheduled exercise where it would re-create the full model with Workbench and then examine how it fit into the environment. “That’s a really good tool,” she added.
Price: Free
Key Features:
Perhaps the most important trend in data in recent years is the rise of data catalogs, spurred in no small part by privacy regulations like GDPR and CCPA. That push has touched data modeling, too. Vovchenko said the line between data lineage tools and data modeling tools will only continue to blur.
Exemplifying the trend is Amundsen, the metadata discovery platform developed and made available by Lyft. This tool allows data analysts and data scientists to access data within their organization through a simple text search and identify when databases were last updated. The tool also hosts a “frequently used” feature, which allows users to see queries are common amongst their co-workers as well as what data they use, own and save.
“Amundsen models metadata entities as a graph, which makes it easy to extend the model when more entities are introduced,” wrote Lyft engineer Tao Fang when the company made the open-source announcement in 2019. “All of the entities — such as tables, columns and schemas — are connected via edges that represent the relationships between them.”
Price: Free
Key Features:
DbSchema is a database design tool that has both free and priced options and is supported on many databases. DbSchema allows for data modeling techniques like building visual queries, schema synchronizing and automating database tasks like schema deployment and more. Data modelers can also use DbSchema to arrange tables in various layouts, create reports in HTML or PDF format and compare different databases or scripts to identify inconsistencies.
Price: Free and tiered pricing options
Key Features:
Erwin runs on MySQL and PostgreSQL — the kinds of free databases toward which so much of the tech world has gravitated in recent years. But it also supports the likes of Teradata, Oracle and Netezza — which is why it’s perhaps more associated with enterprise legacy shops, where data models tend to be more thoroughly optimized than in some deliver-features-fast startups.
That said, organizations that accept the cost tradeoff get some very robust capabilities. Benefits include the ability to both forward and reverse engineer, generate DDL statements and “help you identify diffs, to keep the history of your schema evolution,” Vovchenko said.
Price: Request pricing on the company website
Key Features:
Along with erwin, ER/Studio is another powerful, long-running, enterprise-friendly market leader in data modeling. It supports forward and reverse engineering, and also runs the gamut in terms of databases supported. The tool — which is Windows-only — is available in four versions, with the top model supporting Jira integration, conceptual mapping and schema generation on database, among other features.
Both erwin and ER/Studio have weathered ownership changes since arriving in the late ’90s. Those shakeups may have factored into occasional stability issues for both products, Gupta said, but the fact that both can connect to so many different data sources and database platforms should counteract lingering fears of bugginess. Both are quality options for enterprises that want to avoid vendor lock-in, he added.
Price: Request a quote from the company website
Key Features:
Developed by IBM, InfoSphere Data Architect is a collaborative enterprise data modeling and design solution ideal for business intelligence and data management. Some of the tool’s features include querying capabilities, the ability to import and export constant mappings and create logical and physical data models. InfoSphere Data Architect is compatible with IBM Db2, IBM Informix, Oracle, Sybase, Microsoft SQL Server, MySQL and Teradata source systems.
Price: Request a consultation on the company website
Key Features:
PgModeler is an open-source database modeling tool for PostgreSQL that can be used on Windows, Linux and macOS. Although this tool is free for data modelers who want to compile its source code themselves, those who want to buy a version with pre-compiled binary packages can choose from a variety of options.
This tool is ideal for people looking for a range of capabilities from basic column organization to user-defined languages. PgModeler allows users to restore previous versions of the database, generate models and scripts based on existing databases and generate columns and constraints automatically by identifying available relationships in the database.
Price: Free and ready-to-use priced package options
Key Features:
For those looking for an accessible, Windows-compatible tool, SQL Server Management Studio might be a good option as it comes standard with Microsoft’s SQL Server. The tool allows users to configure, monitor and administer instances of SQL.
Data modelers can use SSMS to deploy, monitor and upgrade data-tier parts used by applications. SSMS can also be used to manage databases as well as to design and query models on both a local computer and in the cloud.
Price: Free
Key Features:
Created in 2009, Oracle’s SQL Developer Data Modeler is a free data modeling tool that allows users to create relational, physical and multidimensional models. Users can also apply the tool to forward and reverse engineer data. The data modeler has had over 20 iterations and releases and future enhancements plan to include compatibility for MySQL, TimesTen, Sybase, and SQL Server 8. This tool can be used on both local computer and cloud-based systems.
Price: Free
Key Features:
Tech culture can create pretty sharp dividing lines when it comes to who reaches for what data-modeling tools. Mang-Git Ng is the founder of Anvil, a startup focusing on paperwork automation. For him, enterprise tools like erwin and ER/Studio might as well exist on another planet.
“I didn’t really see a need for a more advanced data modeling tool, if you already understand the data model that you’ve built,” he said.
So what does someone like Ng use? Postico isn’t exactly a data modeling tool; it’s a GUI for PostgreSQL that lets users see the tables and views in their database in an intuitive way. Ng said it’s a handy way to look under the hood of your database without getting into the command-line weeds.
Price: Tiered pricing options; starts at $21
Key Features:
Navicat is a comprehensive platform that offers a variety of solutions for database development. Navicat’s data modeler allows users to build conceptual, logical and physical data models. It works to visually design database structures, perform reverse and forward engineering processes, import models from ODBC data sources, and print models to files.
The tool also strives to simplify data modeling by generating the SQL script compatible with MySQL, MariaDB, Oracle, SQL Server, PostgreSQL and SQLite.
Price: Tiered subscription options; starts at $69.99 per month
Key Features:
