[1/2] Providing quality ETLs on Vertica - Alexander Krasheninnikov - SmartData 2023 (Category Architecture)
Interesting. report From Sasha Krashennikov from T-Bank on how to provide business with high-quality data and not only on Vertica:) The report combines both process and technological aspects, and the report itself is based on Sasha’s experience, which he received outside T-Bank, but now uses for the benefit of our company:) Well, a bonus to Sasha's technical competence is an excellent style of presentation of the material - with humor and extremely affordable, I recommend watching in the original. But who is too lazy to look at the essence of something like this?
In fact, it all starts with the fact that the company is successful, there is a request for data analytics. To solve the problem, they decide to drive everything over a powerful MPP. (massive parallel processing) databases. As MPP base choose Vertica, which is a classic MPP databases (multi-node, column store)ACID Compatible, without SPOF (single point of failure)Fast and generous on system information (What can be used to improve processes). So far, everything sounds good, but I must add that it is proprietary and highly paid:) Processes look like data engineers bring raw materials, users write ETL and display it in the product. Everything works ... up to a point.
But then the trouble started. "Base slows down" - launching heavy queries that affect other requests. The solution through quotating is standard, but quotating does not solve the problems of reading from disk, transmission over the network, load skewing, "misuse" of the base. On top of the standard quotation, query watchdog is added to monitor and optimize queries. Part of the requests are shot by this service and a notification is sent to the author of the request.
- Separately, Sasha reveals what a "off-the-shelf" database use might look like - for example, it could be an OLTP-style load or single-threaded data integration. To identify queries in the style of OLTP, you can wind up the search for duplicate queries. And single-threaded integration leads to overheating of the node through which the request passes (For example, a coordinator in Greenplum). This can be detected by system information.
Next, Sasha talks about how they built the process of training users to use MPP. The key points of learning were
- Structured presentation of information
- Self-testing tests.
- Certification at the end of the course
In the course, the guys included.
- Introductory part about MPP Key differences from single-node databases What is used in the organization
- How to connect.
- Basic query language Storage: sharding, data-locality, sorting Optimization: types of joins, group by Optimization: Accurately distinct, argmax
Next, Sasha shows examples of challenges in the course + tells how to make the course mandatory for engineers who have roles with access to the MPP base. It is also cool to make it possible to launch a local base for experiments, as well as collect feedback on the course itself.
But requests remain.
- What's normal? Is the allocation of resources fair?
- Maybe it's time to pour iron on the problem.
To determine the boundaries of normality, it is necessary to collect telemetry on requests and cluster them, and then analyze anomalies. Experts analyze the OLAP cube and identify problems, communicate with code owners for correction. If the problem is not solved, lock the account and force the authors of the code to fix.
In general, it is necessary to rank users by defects. Defects are errors in the design of table structure and processes. Defects are divided into static and dynamic, the first do not affect the operation of the base, the second affect. Examples of static defects: using data types that are not optimized for text and numbers. Examples of dynamic defects: use of external functions that can lead to excessive use of base resources.
Specific examples of work with defects in post-continuation.
#Data #DWH #Processes #Management #Architecture #Software #SoftwareArchitecture