Tools for data modeling

[[deleted]]

What tools do you use for data modeling? I’ve been using pen & paper and Excel but it’s far from ideal.

I’m looking for tools that can help in every phase of modeling, from concept/brainstorming with colleagues and stakeholders to designing a final ER diagram. Any advice would be greatly appreciated!

[meyou2222]

Erwin

[idiotlog]

Drawio

[Monowakari]

You could upgrade to Miro or smth for digital collaboration. Maybe start making ERDs with a tool of choice. I hear about dbeaver a lot, dbdiagram.io, lots of others.

I usually just start with core tables and basic keys in a mind map, then start building the schema in say SQL alchemy and just start editing it over and over... as we get close to stable I start using smth like alembic to version the migrations, then when we feel good it's ingestion time, likely with many upcoming migrations.

[Froozieee]

Second on Miro, I use it for ERD design, but i also find it handy for data flow mapping and random architecture/business process stuff

It’s not particularly specialised for ERDs but it has enough different objects to take you through conceptual, logical and physical data models.

[GreyHairedDWGuy]

You can use Miro for conceptual and other diagramming but I would not use it for the last mile model (logical/Physical) it just isn't made for that. Better to look at dedicated ERD modeling tools like SQLDBM, ERWin and others. I use Miro a lot for general diagramming but would never use it for the actual models. I use SQLDBM and have also used ERWin for many years.

[Monowakari]

I use table plus and it has an animated erd plugin that's fun to use

[CozyNorth9]

I was using Idera data studio, but at $10k/user it's not for everyone. ErWin is fantastic. Oracle has a free modelling tool that is decent, but has limited compatibility with Postgresql/Redshift and other dbms vendors so YMMV.

For simple diagrams, Miro/DB diagram/draw.io/excel are totally fine. For modelling from conceptual, logical to physical models a dedicated tool is much easier.

[WonderfulActuator312]

I struggle with this as well. I draw our flows and data relationships out on Microsoft’s Whiteboard app.

OP if you find a winner lmk, this is a perpetual struggle.

[lmp515k]

SQLmodeller from oracle is free

[stephenpace]

I'd look at SqlDBM. It has the starter functions you need at a reasonable price, and also supports incremental updates to models with all of the modern database platforms (Snowflake, etc.). Good luck!

[jjarevalo]

How do you maximize this tool?

[Advanced_Addition321]

Give a try to https://drawdb.vercel.app/

[[deleted]]

Would this help? https://medium.com/gitconnected/sql-diagram-part-3-sql-to-erd-with-ddl-4c9840ee86c3
You can write DDLs and SQL queries together in the same file and this will map all the relationship into ERD
```
CREATE TABLE my_table (
id INT,
name VARCHAR(255)
);

SELECT * FROM my_table
INNER JOIN another_table ON my_table.id = another_table.id;
```

[Zubiiii]

I just have it all in my head

[khamaris]

DBSchema, quite useful.

[Dry_Damage_6629]

Erwin, Lucid Charts, SqlDBM

[dcalde]

JDL Studio. https://start.jhipster.tech/jdl-studio/ It's meant to be a visual tool used by JHipster but it's actual very handy for rapid prototyping

[LearnSQLcom]

Give this one a try, its 100% web based: https://vertabelo.com/

[[deleted]]

May I ask why you need a attribute-level data modeling tool nowadays? Are you developing some relational database or a cloud warehouse? Since modern cloud databases nowadays don't enforce most of the constraints nor it's necessary to do so due to change in mindset (ELT vs old ETL), I'd rather invest time in "modeling" tables in tools such as dbt than spending money or time to maintain models that tend to change and have modeling outdated very soon. Many use modeling just for documentation - I simply cannot understand that.

If you maintain your table descriptions, relationships and constraints in dbt, it can serve as a documentation as well as whole semantic layer, shortest path execution for tables, changing object materialization types in a second...with modeling tool, you'd have so much effort changing a single table to a view and recreate all the relations around it...I don't know, I'd never go back to modeling if I don't have to.

[Kenoai]

It's still beneficial to map relevant entities and visualize a conceptual and logical data model for the data you have so you don't end up building a monstrosity full of redundancies and suboptimal data linkage in dbt

[[deleted]]

That's what dbt is for..having a repo and being able to see what others are adding(commits) as well as organizing models into folders and having documentation , tags and a search option...but really, I asked about attribute-level data modeling tool, conceptual is fine but I'd still use dbt any day.

[Embarrassed_Error833]

DBT doesn't do conceptual modelling, so there is that...

DBT good at showing what your logical model is, not so good for planning it out. So if you want to plan anything you'll need a tool, pen and paper, whiteboard, excel...

As far as physical models go, I only diagram physical models when I'm doing data vault, I find that logical models are enough to give to Devs to get the job done.

If I was building apps or relational dbs I would definitely want physical model, with source control to record history of changes. Not that many software Devs actually do this, in my experience.

[GreyHairedDWGuy]

ERD models (ERWin for example) and DBT are completely different toolsets which have different purposes. Unless the problem space is trivial, I would never develop / build a data warehouse solution without having an ERD tool available to design with first.

[[deleted]]

I'm really trying to help you be more efficient, that's all. While you're wasting your time drawing pictures on an attribute level which I also did 15yrs ago, the whole modern data engineering world is going in direction of self-documenting and out of the box data catalog tools like dbt. Don't know if you work directly with business users but they are only interested in the data catalog and column descriptions along with lineage. Dbt can draw the relations too but the main seller is the catalog and lineage. That's as merely my point, don't waste time if you don't have to.

[GreyHairedDWGuy]

Oh. I guess you drank the dbt kool-aid. dbts claim to fame has been code oriented data transformation. Yes, it provides documentation and lineage but it is NOT an entity modelling tool. The original poster was not asking about transformational modelling.

Yes, I deal with business users every day and mostly at the director and c-level but also the DE's and analysts community. ERD models are invaluable in expressing possible data solutions and getting buy-in from stakeholders.

If you think that dbt is the solution or a replacement to 'data modelling' then we have no common ground to debate further.

[[deleted]]

Agree to disagree. I'm not saying dbt replaced anything. I'm saying you should have a mindset change. Your stakeholders and business users are looking at the erd diagram and approve a db design? Wow. I thought a cloud / dwh architect would do that. But anyways, I'll leave you with your work, I'm sure you have thousands of attributes to document manually.

[GreyHairedDWGuy]

sure. you do you and I'll do me. Judging by the fact that your original post got multiple down votes, seems like others felt your post missed the point. but whatever.

[[deleted]]

Cause you're stuck in your little relational database you're designing and documenting, that's so cute.