antonrevyako

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.

Hi hackers!

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.

Could you tell us a little more about your experience, if that's acceptable? Maybe in personal messages, FB/linked?

Yes, absolutely. You can check requests from pg_stat_statements, slow query log, and any other source as you wish. If the SQL repository stores query in separate files, you can check these queries (e.g., before they are merged into the master).

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.

Thank you for the feedback! The service will be put into commercial operation as saas in the coming months. I do not plan to open the source code, unfortunately.

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 :)