Hi @huydna@Thi_Nguyen@huyleminh ,
I am really happy, as this is the first modeling tool that introduced “Data Dependency (Lineage)” along with an ER Model. If you can find my previous comment, I always mentioned that this is the best feature of this modeling tool.
But because lineage comes with more challenges than a simple ERD, you guys need to work on these features too.
Union needed: There are 3 ways to map source to target Lookup (which is already satisfied by the ERD); Second is CONTACT like Name is combination of FName, MName, LName which is also working as Dep{S.fName → T.Name \n S.LName → T.Name \n S.MName → T.Name}; But last part Union is missing; T.ID need all Data from S1.ID and S2.ID.
If possible, create a new line (different color, like pink this time, as blue is already used) for the union. As we are already using → for union => can be used. So Union identification will be easily highlighted.
Let me know your views.
@huydna@Thi_Nguyen@huyleminh
See, can this be introduced easily?
And can your team include another icon, like |>, and a different color of line for “Coalesce”
Because UNION and Coalesce both play an important role in lineage (Dependency Model).
@Ashish_Bajpai Hi, our team is planning to support further many dependency specializations like union so you can easily specify these in your lineage & also import/export them from/to sql.
If possible, are there any other special kinds of dependencies that you want to be supported in DBML? We can consider your input when we’re designing this feature.
Apologies for the delayed response, as I was tied up with office work. Regarding the question raised about the type of lineage (technical rather than operational), here are my thoughts based on my experience:
As you know, defining lineage within a data model differs from a standard Entity-Relationship (ER) diagram. While an ER diagram typically consists of tables and views, “Lineage” encompasses a broader range of inputs, including procedures, files, variables, tables, views, or cloud APIs. Furthermore, depending on the join types, lineage can be categorized as Lookup (matching an ID with a master table to retrieve a name or vice versa), Direct (1:1 mapping), Union (combining multiple sources), or Coalesce (fallback handling between sources).
Current Status vs. Expected Enhancements
1. Source Types
Current State:
Tables are currently supported for Lookup and Direct lineage.
Variables are currently being handled as tables (It is working for me).
Expected Enhancements:
Views: Display the same as tables.
Procedures: Display the same as tables.
Files & APIs: Must include header information and display the same as tables.
Variables: Need explicit name and value definitions.
(Note: The definition syntax will remain identical to tables; however, neither ref constraints nor other table constraints will apply in all of the above 4, but we may use a different type of box or color to highlight.)
2. Pipeline Lineage (Visuals)
Current State:
Currently limited to a single blue line for direct mappings (-> ).
Expected Enhancements:
Introduce distinct color coding or symbols for different lineage types to make this an ultimate technical lineage tool.
Suggested notation style: Use -> for Coalesce , +> for Union , and => for Direct (currently we have ->).
I think distinguishing Union and Coalesce visually would make lineage diagrams a lot easier to understand, especially once a model has several upstream sources.
One thing I’d consider is keeping the transformation type separate from the visual notation.
For example, a Coalesce dependency isn’t just two inputs pointing to one output. The order of those inputs matters because the first non-null value wins.
That information could be easy to lose if the diagram only uses different arrows or colors.
Maybe the DBML representation could preserve the transformation type and input order explicitly, while the diagram handles how to display it.
That would also make import/export more predictable if SQL support is added later.
Regarding the COALESCE condition, it doesn’t matter which input comes first, as the dependency model provides the option to display either the query or the notes.
I specifically used the term COALESCE instead of IS NULL because there could be more than two input values.
That said, having different colours for these elements, such as COALESCE, UNION (ALL) and direct mapping, would definitely make the model more visually appealing and easier to understand.
Regarding SQL DML generation, I think it may not be feasible at this stage for two reasons:
Data pipelines usually involve a significant amount of transformation logic, which goes beyond simply generating SQL DML statements.
Pipelines can be developed using different technologies, such as SQL, Python/Pandas, PySpark, Scala, or other frameworks.
DDL, on the other hand, is mostly SQL-based, so generating DDL is relatively straightforward. However, DML is not necessarily SQL-based.