Understanding the Importance of Data Lineage in Modern Data Management

Data lineage is a component of modern data management that helps organizations understand the origins, transformations, and movement of their data. It is like a road map that shows us where our data has been, how it has changed, and where it is going, just like tracking the journey of a package: from the person who sent it (the source) to the places it passes through, and finally to the person who receives it.

The concept of data lineage has been around for many years, but it has become increasingly important in recent years due to the growth of big data and the increasing complexity of data processing systems.

One of the earliest examples of data lineage can be found in the work of Dr. Donald Knuth, who developed a system called TeX in the 1970s. TeX is a typesetting system that uses a complex set of rules to generate high-quality printed output. Knuth developed a system called Metafont to track the flow of data through TeX. Metafont allowed Knuth to identify and fix errors in his typesetting rules.

In the 1980s, data lineage began to be used in the financial services industry to track the flow of financial transactions. This was due to the increasing need for financial institutions to comply with regulations such as the Gramm-Leach-Bliley Act.

In the 1990s, data lineage began to be used in the healthcare industry to track the flow of patient data. This was due to the increasing need for healthcare providers to comply with regulations such as the Health Insurance Portability and Accountability Act (HIPAA).

In today’s data-driven world, organizations are collecting and storing more data than ever before. This data is important for making informed business decisions, fueling innovation, and enabling organizations to better understand their customers and markets.

It provides a detailed record of data’s path, showcasing how it is created, modified, used, and transformed throughout various processes within an organization. Data lineage helps understand the data’s quality, lineage, and business relevance. It can be represented in graphical or tabular formats, allowing stakeholders to comprehend complex data relationships and dependencies easily.

Data lineage has three main components that help us understand how it works. They include:

Eager Aggregation in SQL queries

Aggregation is a widely used way to summarize the content of a database. It is usually expressed with GROUP BY clause or just using aggregate functions (like COUNT or SUM). When the database engine executes a query with aggregations, it produces individual rows need to compute the required output and then performs the aggregation as (almost) last step. We discuss in this article how to re-write a query manually so that the order of operations will be different and when it can be beneficial.

We start with remainder that SQL is a declarative language, that is, a properly written query specifies what should be included into result but does not specify how to calculate this result. There are several ways (called execution plans) to do that for almost any query. All execution plans for a query produce same results but may utilize different amounts of computing resources. An optimizer tries to choose the best plan for execution. Usually, state-of-the-art optimizers do their job well but sometimes they fail to choose a good plan. This may happen for different reasons:

• The data statistics and/or cost model are imprecise.
• The optimizer does not consider some classes of plans.

In this article we discuss one type of query transformation that most optimizers do not use. Because of this, it can be beneficial for you to rewrite a query to help the optimizer order operations in a way that can be beneficial.

Introducing the MongoDB Document

MongoDB is a document database. As such, the data is stored as individual documents. A document is a data structure made up of one or more field/value pairs. Nearly everything you do in MongoDB is either directly or indirectly related to the documents that you store in a database or move in and out of a database. The better you understand how documents work, the more effectively you can write queries and manage the data.

In my previous article, which was the first in this series, I introduced you to MongoDB and described how to get started with MongoDB Atlas and MongoDB Compass. Atlas provides a cloud-based database service comparable to on-premises MongoDB, and Compass serves as a client interface for connecting to MongoDB and working with document data.

As part of this discussion, I also covered some of the basics of MongoDB documents, demonstrating how to create them and add them to your database. In this article, I expand on this discussion to give you a better sense of how documents are constructed and the different types of data they can contain. To help with this process, I provide several examples that demonstrate some of the ways you can define documents to meet your specific business needs.

Note: For the examples in this article, I used the same MongoDB Atlas and MongoDB Compass environments I set up for the first article. If you want to try out these examples and are uncertain how to connect to Atlas, refer to the first article for more information.

Counting more efficiently

Nearly a decade ago, I wrote a post called “Bad habits : Counting rows the hard way.” In that post, I talked about how we can use SQL Server’s metadata to instantly retrieve the row count for a table. Typically, people do the following, which has to read the entire table or index:

DECLARE @c int = (SELECT COUNT(*) FROM dbo.TableName);

To largely avoid size-of-data constraints, we can instead use sys.partitions.

Note: We could use OBJECT_ID() instead of a join, but that function doesn’t observe isolation semantics, so can cause blocking – or be a victim. A potential compromise is to create standardized metadata views, but I’ll leave that as an exercise for the reader.

DECLARE @object int = (SELECT o.object_id 
    FROM sys.objects AS o
      INNER JOIN sys.schemas AS s
        ON o.[schema_id] = s.[schema_id]
      WHERE o.name = N'TableName'
        AND s.name = N'dbo');

 DECLARE @c int = (SELECT SUM([rows]) 
    FROM sys.partitions
      WHERE index_id IN (0,1)
        AND object_id = @object);

That’s great when you want to count the whole table without size-of-entire-table reads. It gets more complicated if you need to retrieve the count of rows that meet – or don’t meet – some criteria. Sometimes an index can help, but not always, depending on how complex the criteria might be.

Using a SQL Tokenizer

Imagine this: you have several directories full of SQL script files, and you need to know where a certain table is used. You’d rather like the context too, so you can check the whole SQL Expression and work out why it is running so slowly. Maybe, from that same daunting set of several directories, you need to search for a comment, either end of line, or block comment, perhaps structured. It could be that you just need to execute each query or statement in turn to check performance.

It’s not unusual to want to search just within strings. Although simple searches can be effective, you will at some point need a tool that is able to recognise and return a collection of strings representing the SQL code, divided up into the respective components of the SQL language.

For this article, we’ll use a PowerShell cmdlet, called Tokenize-SQLString, which is in my GitHub repository.

Just so you can get the feel for what a tokenizer does, here is an example, where someone has embedded SQL statements that might mislead the unwary.

@'
/* Select * from dbo.othertable */
drop view if exists dbo.Book_Purchases_By_Date; 
--drop the Book_Purchases_By_Date view
Select 'create table dbo.YetAnothertable'
'@|Tokenize-SQLString|Out-GridView

This will provide the following stream of objects and finds the reference:

Using Spark Jobs for Multiple Lakehouse Maintenance in Microsoft Fabric

I have published videos and articles before about Lakehouse maintenance. In this article I want to address a missing point for a lot of Fabric administrators: How to do maintenance on multiple lakehouses that are located in different workspaces.

One of the videos I have published explains the maintenance of multiple lakehouses, but only addresses maintenance in a single workspace. Is it a good idea to keep multiple lakehouses in the same workspace? Probably not.

The main maintenance tasks you would generally execute on Lakehouses are VACUUM and OPTIMIZE. I already went in details about them on the videos and articles linked below.

In the future, depending on Microsoft Fabric advances on the enterprise, many more maintenance tasks may also become needed.

Notebooks can’t be used for this maintenance because of one principle of the technology at the moment:

Notebooks can’t loop through lakehouses in different workspaces

MySQL Error Log Management in DevOps Operations

When it comes to the development and operations (DevOps), one thing stands out as a critical aspect and that is troubleshooting. The primary goal of a DevOps team is to ensure that the product experiences zero to no downtime because every moment is crucial. Therefore, smooth delivery and uninterrupted uptime are paramount. To achieve this, DevOps teams monitor the product’s performance using logs, and databases, like MySQL, are no exception to this practice.

MySQL provides error logs that serve as a comprehensive record of significant events. These events include activities like the MySQL server starting up or shutting down and any critical issues that arise while it’s running. These logs are indispensable diagnostic tools that are super helpful for database administrators and experts to quickly pinpoint and address problems. By diving deep into these error logs, they can maintain a stable and efficient database, ensuring its reliability and top-notch performance.

In this article, we’ll explore the world of MySQL error logs. We’ll discuss their importance in DevOps, the challenges they bring, and how to manage them effectively. You’ll be well-equipped to turn these challenges into opportunities to improve your DevOps operations.