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 to Know
- SQuirreL SQL Client
- MySQL Workbench
- erwin Data Modeler
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.
20 Top Data Modeling Tools to 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.
- SQL plugin available
- Ideal for financial models
- Drag and drop capabilities for entity shapes
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
- Integrates with various chat and workflow tools
- Ability to co-author databases and comment back and forth with colleagues within the platform
3. SQuirreL SQL Client
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.
- Supported by most major databases
- Continuously updated open source code
- Users can draw entity-relationship diagrams from existing schemas
4. Luna Modeler
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
- Lets users create reports in various formats
- Offers ability to leave custom HTML notes in the database
- You can compare projects to the database to identify inconsistencies
5. MySQL Workbench
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.
- Allows forward and reverse engineering of databases
- Lets you overlay databases with external models
- Includes DBDoc which allows for point-and-click database documentation
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.”
- Includes a data discovery search bar tool
- The frequently used feature allows users to see common queries and data that is repeatedly saved.
- Metadata entities are modeled as graphs and connected by the relationship between them
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
- Visual query builder
- Generates documentation across formats
- Runs automated database tasks
8. Erwin Data Modeler
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
- Forward and reverse engineering capabilities
- Generates DDL statements
- Supported on a variety of databases and operating systems
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
- Users can incorporate Jira integration
- Supports forward and reverse engineering
- Allows for conceptual mapping
10. InfoSphere Data Architect
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
- Using Java Database Connectivity, the tool creates native data queries
- Ability to automatically import and export constant mappings to and from a csv file
- Compatible with a variety of source systems
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
- Ability to restore previous database versions and temporary databases
- Generates models and scripts based on previous versions
- Creates columns and constraints automatically
12. SQL Server Management Studio
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.
- Analysis Services Scripts feature allows users to re-create databases and cubes
- The Template Explorer feature lets users create and deploy build boilerplate text to run queries and scripts
- Users can design and run queries locally and on the cloud
13. Oracle SQL Developer Data Modeler
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.
- Relation, physical and multidimensional model capabilities
- Supports forward and reverse engineering
- Has local and cloud access
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
- Database objects like columns, indexes and constraints can be seen in a single editor view
- Feedback button feature allows users to request support or raise issues with the platform
- Continuous software updates run without notifying the user
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
- Supports conceptual, logical and physical data models
- Runs forward and reverse engineering
- Allows users to import external models
The open-source Metabase is a graphic data visualization tool equipped with analytics features, making it ideal for a variety of users. More advanced users can access the SQL editor feature within the tool to adjust code and edit the database. Its main selling point is cross-team data access. But it also lets users join tables and define entity relationships.
Ng said of his experience with the tool: “I essentially could make a copy of our database, hook it up to Metabase, then slice and dice the data however I wanted. I could make SQL queries on top of the data and do table joins, all from within the UI — which is pretty nice.”
Along with the intuitive UI, Ng said he was impressed with Metabase’s ease of deployment. He got it up and running at full speed within half a day.
Price: Free and priced options available
- Provides accessible data across teams
- Allows users to integrate with email and Slack
- Both basic and advanced features are ideal for all levels of users
Quest’s Toad Data Modeler tool is compatible with over 20 different databases and can help users deploy accurate changes to data structures. The data modeling tool allows users to construct logical and physical data models, compare and synchronize models, quickly generate complex SQL/DDL, create and modify database schema and scripts as well as reverse and forward engineer both databases and data warehouse systems.
The goal of Toad Data Modeler is to give data modelers, DBAs and developers access to a tool that requires minimal training and allows for fast installation and integration.
Price: $377 per seat for a 12-month license
- Supports logical and physical data model construction
- Supports forward and reverse engineering
- Users can import, compare and synchronize existing models
Short for Data Build Tool, dbt allows users to visualize data lineage and complete SQL-based data modeling. The tool can help users layer data models with the ref() function and supports models ranging from tables to incremental tables as well as views or a custom design.
Price: Book a demo on the company website
- Supports ML modeling and operational analytics
- Compatible with SQL and Python code
- Users can have tables, incremental tables, views as well as custom materializations of databases
DataGrip is popular among database administrators because it allows users to add, remove, edit and clone data rows as well as use text search to easily find information displayed. This data modeling tool also helps users write SQL code with features like context-sensitive, schema-aware code completion and auto-generating code for changing objects like tables and columns based on UI. Queries can be run with features like a query console with each console being able to support schema switching and provide a historical record of your activity and work.
Price: Tiered pricing options; starts at $229
- Includes a text search bar tool
- Stores historical records of work and tasks performed in the database
- Query console feature allows for multiple consoles with different schemas and queries
20. Ab Initio
Ab Initio is an enterprise data platform with a variety of capabilities. The platform’s goal is to provide automation and self-service data solutions that can easily adapt and solve issues that arise in data processing and data management.
The platform’s cataloging capabilities are one aspect data modelers can look at as it can mine data from data lakes, the cloud, an operational system or a data warehouse. Data modelers can use Ab Initio to identify datasets and curate the information in them, all while using automation. Users can then search the data and explore it to form models and reviews.
Price: Contact sales on the company website
- Allows users to mine and model data from data lakes, the cloud, operational systems and data warehouses
- Includes a searchable data tool
- Automates database monitoring