The entire concept of column-level data lineage as you know it is a lie. It's broken, and no one is willing to admit it.
17 karma · joined January 17, 2020
Today we proudly present the first release of our new project: dwh.dev - dataops collaborative platform for snowflake engineers.
We build our platform using in-depth integration of:
- End-to-end data lineage automation tools
- Architecture and logical issues detection
- ETL/ELT pipelines visual management tools
- Data-ops and doc-ops tools
The core element - the visual DB schema modeler tool is already up and running.
You can import existing DB schema using DDL commands or set the remote server connection and synchronize schema automatically.
We support VIEWs as well. At the moment you already can see all your VIEWS and very soon we will add SQL-editor for 2-way editing support.
Check out our roadmap dwh.dev/#roadmap to see how many useful features are on the way! We welcome your feedback and suggestions.
For example:
CREATE TABLE B (ID INT PRIMARY KEY); CREATE TABLE A (ID INT REFERENCES B (ID)); SELECT GET_DDL ('DATABASE', 'TEST', TRUE); will return
create or replace TABLE TEST.PUBLIC.A ( ID NUMBER (38,0), foreign key (ID) references TEST.PUBLIC.B (ID) ); create or replace TABLE TEST.PUBLIC.B ( ID NUMBER (38,0) NOT NULL, primary key (ID) ); Such DDL script could not be executed due to the wrong order of the objects.
This tool that will reorder objects based on their relatedness.
Applicable to all Snowflake objects (including VIEW), supports CREATE and ALTER, and respects FOREIGN KEYS, CLONE, SELECT AS, and other kinds of dependencies. Knows how to identify cyclical dependencies.
This tool also available to use in our API: https://docs.parsers.dev/#snowflake-get_ddl-function-result-...
It is crucial that you add the actual database schema (DDL) before checking.
This functionality will appear soon (client cabinet, API).
Please note that analysis is performed using static methods only! No connection to the database is required. In particular, statistics and other environmental parameters that may affect the query performance are not taken into the analysis. I would like to be able to enrich the data obtained by static methods with data from the database production, but at the moment, this is a very far-reaching plan.
The tool will make recommendations that are necessary, but not enough.
The actual database parameters affect the real query plan. There are a lot of such settings, and the impact changes over time.
Keeping an eye on these parameters and adjust them - there is a significant layer of work that is not a priority at this stage. Excellent tools have already been made for this purpose, such as postgres.ai.
DBA work is divided into two parts - to monitor the quality of queries and database schema and configure the parameters of production servers.
My tool covers the needs of the first part and does not cover the second part.
I talked to product owners of companies producing products or services related to the second part, and they agreed to integrate our products when I come ready.
But custdev showed that companies do not care about it.
They are ready to take Django/RoR/Laravel developers who know only ORM. Such developers are cheaper, they are easier to hire. Some people also think that it's faster to develop this way :) They prefer to start dealing with problems after they get on production DB.
They shift developer problems to DBA. There is research saying that the price of fixing a bug in production is on average 400 times the price of fixing it at the development stage.
Ok, the boss call the shots :) I had to make a pivot to DBA needs. It was a bit upsetting at first, but then I realized that it was even simpler. You need to do a lot more tools for developers to make them feel comfortable.
A few words about open source. At first, I used this AST parser for PostgreSQL https://github.com/lfittl/libpg_query But it's frozen on Postgresql 10. And we don't know if there'll be any updates.
No official AST parser for MySQL.
There's a hard way to get a bison/ANTLR grammar parser.
Two weeks ago there was a parser like this for MySQL: https://github.com/stevenmiller888/ts-mysql-parser.
There's also a parser from vitess: https://github.com/vitessio/vitess/tree/master/go/vt/sqlpars...
The only problem is that the grammar for these parsers was written by hand and is not related to the official MySQL repository. For example, the vitess parser does not support the syntax of MySQL 8.0, and MySQL 5.7 does not support more than 40%.
There is also such a tool https://www.jooq.org/. It supports some of its own generalized SQL syntax, which does not take into account the specifics of different databases.
Look, there is another tool that uses libpg_query - sqlc: https://news.ycombinator.com/item?id=21765689.
And the author of ts-mysql-parser also offers an analyzer: https://github.com/stevenmiller888/ts-mysql-analyzer There are only four rules at the moment...
Anyway, all hard work starts after you have the right AST parser in your hands. And if you don't spend all your time on it, it will be difficult for you to do something really interesting :(
PS: I removed the types exporting tool from the site a few days ago, along with texts explaining all the advantages of this tool to the developers :) In a couple of weeks, I will transfer it to a separate domain, as a separate project.
I am open to personal communication outside this site, all my contacts are at https://holistic.dev/en/contacts/
The idea is that the tool will only work with the sources of SQL queries, and I had to work hard to implement it.
The work consists of several steps 1) get the AST (Abstract Syntax Tree) database schema (DDL). At this stage, only Postgresql up to version 10 is supported. Soon I will deal with the parser from Postgresql 13. This is not a trivial task. I have to build everything from the source code of Postgresql :)
2) Building a database model. We parse all DDL-commands one by one and apply changes. For example, apply all ALTER TABLE to the table described above, add user-defined functions to the list of built-in functions, and so on. It is necessary to have a complete overview of all types of tables, indexes, and each table described in a DDL script.
3) get an AST query (DML) and build a model of the result. This is the most complex and interesting part :) The task is to get a list of field names and their types, which will be returned after the query execution. You need to consider CTE and the list of tables specified in FROM. You need to understand what function will be called and what result will be output. For example, function ROUND is described in three variants, from different arguments and with varying types of result, function ABS - in six variants. I.e., it is required to understand the types of arguments before selecting a suitable one :) In the process, implicit type casting is considered if necessary.
The same is valid for operators. An operator in Postgresql is a separate entity that you can create yourself. Postgresql 11, for example, describes 788 operators.
Various types of syntax are taken into account, for example - SELECT , t1., id, public.t2.name, t3.* FROM t1, t2, myschema.t3 - will be parsed correctly.
But even this is not the most challenging thing :) The most exciting thing is to be able to understand two things: A. whether each of the fields can be NULL or not. It depends on many factors, such as - how the JOIN of the table with the source column is made, whether there is a FOREIGN KEY, what type of JOIN is used, what conditions are described in the ON section, what is written in the WHERE conditions. B. How many records will return the query described in the NONE, ONE, ONE OR NONE, MANY, MANY OR NONE categories. Again, this is affected by the conditions described in JOIN and WHERE, whether there are aggregation functions, whether there is GROUP BY, whether there are functions that return multiple records.
This function, by the way, is also used in the first step - to get types for VIEW.
This was a brief description of the first part of the service ;). It can already be a service in itself. It is possible to generate types and all code of microservices, including JSON-schema and tests, based on DLL and DML set. But as I wrote above, most people prefer to use an ORM such as Django or RoR. :( For this reason, I've removed this functionality from a playground and will take it to a separate project when I get my hands on it. It will also include various tools, such as - information about all possible exceptions that may be thrown by request, automatic creation of migrations to CI if your DDL files are in the repository, whether there are unused indexes or fields and many other exciting things :)
And the second part of the service that I plan to promote in the first place is a tool to search for bugs, architectural, and performance problems automatically. The target audience here is DBA, who have to deal with forgeries RoR-developers and their colleagues :) This part is entirely based on all information obtained in the previous stages. Part of the errors can be understood by AST (linter principle), but the most interesting rules are based on knowledge about types, understanding of NULL/ IS NOT NULL, and the number of returned records.
There are more than a thousand such rules, but I suppose there will be about 5000 of them in the next couple of years :) Also described are about 200 rules that can lead to runtime errors, but they are not needed by DBA, because their job is to find problems in valid queries :) There will be more than 1000 such rules as well since Postgresql describes more than 1700 runtime errors.
And yes, this is all about Postgresql. After I start commercially using Postgresql, I plan to do the same for Mysql and then perhaps for Clickhouse if there are no special offers of cooperation :)
It's a useful tool for DBA to identify issues in SQL queries automatically. Only 50 rules for now, but more than 1000 described in backlog :)
Funny, but initially this tool aimed at developers' needs.
I've made a lot of microservices, which only started the database queries. I came up with the idea of making a tool that would automatically generate all the microservices based on SQL queries. The MVP of such a tool was implemented, which reduced the developer workload by at least a quarter. In this tool, it was required to write queries in a certain way that did not make it universal and did not give a connection with the database structure.
The next step was to create a system that relies on the text description of the database schema in SQL format (DDL) and automatically understands the types that will return the SQL query. Such a tool can automatically inform the developer about possible errors on the interface between the application and database when changing the structure or the SQL queries themselves. It can also be built into the CI to provide the automatic code review at the version control system level and prevent the erroneous code from entering the repository.
But the developers did not appreciate all the advantages, as most projects are developed using ORM :(
But at the same time, DBAs expressed interest in implementing a system of the automatic search for bad requests already on the production database.
Any questions are welcome :)