Free AI web copilot to create summaries, insights and extended knowledge, download it at here

Abstract

target system. But one doesn’t work with classical relational or cube-based systems anymore. In this relation, NoSQL or hybrid and denormalized systems are in use.</p><p id="3b37">So you will end up loading every updated record from the source system. The solution here is to add metadata to guarantee data versioning.</p><p id="1756">See the example below:</p><figure id="d2ba"><img src="https://cdn-images-1.readmedium.com/v2/resize:fit:800/1*T-UEtPh0rdXyn0-ooFY3Ow.png"><figcaption>Table 1 — Result from BigQuery</figcaption></figure><p id="0e9e">I make sure to always reload every record completely into the system. However, the question of unnecessary costs and storage memory immediately arises. Furthermore, one might then ask whether it is not a data jumble. The answer to this question is no, since memory storage facilities are often super cheap in these technologies and, additionally, changes are good to recognize over data sets.</p><p id="b57a">The challenge here is to add meaningful metadata. I tried achieving that by using a DWH_ID and a DWH_LoadDate. If we assume that the ID comes from the source system and is unique, I can use the DWH_ID or the DWH_Timestamp to create views of the core data later, which I can then use to give the business and the analysts’ different perspectives on the data.</p><p id="c510">Let’s say that the internal revision receives a view. It shows that all data records over a period where more operational units have an overview only show the current status. The nice thing here is that I am not only dependent on cubes. I can always get the basic data directly from the data lake and quickly and easily adapt it for my analyses.</p><h2 id="3a3d">Using the Versioning in the Analytics Process</h2><p id="f55a">Now that we have understood the basic technical principle of enabling data versioning, I would like to show you how to use it as a business user or data analyst. With the following SQL statement, we can query the data and guarantee that only the current state of the data record is shown:</p><div id="0439"><pre><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">AS</span> <span class="hljs-keyword">VALUE</span> <span class="hljs-built_in">ARRAY_AGG</span>(t <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> string_field_1 <span class="hljs-keyword">DESC</span> <span class="hljs-keyword">LIMIT</span> <span class="hljs-number">1</span>)[<span class="hljs-keyword">OFFSET</span>(<span class="hljs-number">0</span>)]</pre></div><div id="c382"><pre>FROM triple-silo-<span class="hljs-number">282319</span>.Data.Test t</pre></div><div id="2d11"><pre><span class="hljs-keyword">GROUP</span> <span class="hljs-title"

Options

BY</span> string_field_2</pre></div><p id="823e">And here we go. Thanks to our data versioning and some basic logic realized with SQL, we have the desired output:</p><figure id="eb16"><img src="https://cdn-images-1.readmedium.com/v2/resize:fit:800/1*N-kmxXRfcbJeQKWMU3aYtA.png"><figcaption>Table 2 — Results from a View</figcaption></figure><p id="eec5">Only the valid and newest records are displayed. There are no duplicates. The nice thing here is, we can still analyze them if necessary because they are in another view or, at least, in our core Data Warehouse. So the controller is able to analyze operational document data, and an internal auditor might analyze the same records at a different point in time.</p><h2 id="a5eb">Versioning of Additional and External Resources</h2><p id="161e">We have learned the two basic layers of versioning. The technical one, which is indispensable in a Data Warehouse or Data Lake and must be implemented in the technical data integration process, and the data analyst layer, which takes place inside the Data Warehouse or in the data analysis process. Last but not least, there is another layer, the self-service analysis layer.</p><p id="4dab">For example, modern business intelligence tools can easily integrate external data via CSV, Excel, or Google Sheets. These data sources must also be visioned. To stay with my technology stack, there are options like Google’s Data Studio or Google Sheets, which have a versioning function by default. So you can easily create dashboards and share them with your team. Of course, it is important that the team also knows if and when something changes in the report and what the change is.</p><figure id="38a8"><img src="https://cdn-images-1.readmedium.com/v2/resize:fit:800/0*tQf4ai10UUxsdveK"><figcaption>Version Controls in Google Data Studio — Image by Author</figcaption></figure><p id="5918">In the above image, you can see a dashboard that I built via the self-service-BI tool, <a href="https://datastudio.google.com/u/0/">Google Data Studio</a>. And on the right, you see the different versions, which were automatically generated.</p><h2 id="6ba0">Summary</h2><p id="3b51">In conclusion, the process of data versioning is essential for correctly processed data sets. This article has given you an overview of how and within what frameworks data can be versioned. As an outcome, one can say that data should be versioned directly in the data process so you can easily use it for data analysis.</p><p id="3be0">Data versioning also plays an important role in the field of analytics since it allows for different departments to analyze the same data for their own needs and through their own perspectives.</p></article></body>

Collaborate Better with Data Versioning

How to keep your data up-to-date

Imagine you load incremental data into a Data Lake or a Data Warehouse and you do not version it — chaos would follow. Data versioning is important for Data Analysis. It is the only way to verify that the data is correct.

In this article, I will show you how to implement such versioning on a technical basis and provide some theoretical background. My technology stack is Google BigQuery (a Data Warehouse technology), which is based on SQL and can also be implemented in other technologies. I chose that stack because newer technologies such as this are getting more and more common.

Versioning in Your Data Integration Process

Data versioning usually takes place either directly in the data integration process or within the Data Warehouse.

Data Integration Process — Image by Author

In traditional systems, the versioning of master data is realized by checking each data for changes and thus creating a new version. Depending on whether a relational or a dimensional data model is used for the core, there are different variants of the versioning of master data.

Newer cloud-based and SaaS-based technologies also load data from source systems via CDC or messaging services over an ETL or ELT process into the target system. But one doesn’t work with classical relational or cube-based systems anymore. In this relation, NoSQL or hybrid and denormalized systems are in use.

So you will end up loading every updated record from the source system. The solution here is to add metadata to guarantee data versioning.

See the example below:

I make sure to always reload every record completely into the system. However, the question of unnecessary costs and storage memory immediately arises. Furthermore, one might then ask whether it is not a data jumble. The answer to this question is no, since memory storage facilities are often super cheap in these technologies and, additionally, changes are good to recognize over data sets.

The challenge here is to add meaningful metadata. I tried achieving that by using a DWH_ID and a DWH_LoadDate. If we assume that the ID comes from the source system and is unique, I can use the DWH_ID or the DWH_Timestamp to create views of the core data later, which I can then use to give the business and the analysts’ different perspectives on the data.

Let’s say that the internal revision receives a view. It shows that all data records over a period where more operational units have an overview only show the current status. The nice thing here is that I am not only dependent on cubes. I can always get the basic data directly from the data lake and quickly and easily adapt it for my analyses.

Using the Versioning in the Analytics Process

Now that we have understood the basic technical principle of enabling data versioning, I would like to show you how to use it as a business user or data analyst. With the following SQL statement, we can query the data and guarantee that only the current state of the data record is shown:

SELECT AS VALUE ARRAY_AGG(t ORDER BY string_field_1 DESC LIMIT 1)[OFFSET(0)]

FROM `triple-silo-282319.Data.Test` t

GROUP BY string_field_2

And here we go. Thanks to our data versioning and some basic logic realized with SQL, we have the desired output:

Only the valid and newest records are displayed. There are no duplicates. The nice thing here is, we can still analyze them if necessary because they are in another view or, at least, in our core Data Warehouse. So the controller is able to analyze operational document data, and an internal auditor might analyze the same records at a different point in time.

Versioning of Additional and External Resources

We have learned the two basic layers of versioning. The technical one, which is indispensable in a Data Warehouse or Data Lake and must be implemented in the technical data integration process, and the data analyst layer, which takes place inside the Data Warehouse or in the data analysis process. Last but not least, there is another layer, the self-service analysis layer.

For example, modern business intelligence tools can easily integrate external data via CSV, Excel, or Google Sheets. These data sources must also be visioned. To stay with my technology stack, there are options like Google’s Data Studio or Google Sheets, which have a versioning function by default. So you can easily create dashboards and share them with your team. Of course, it is important that the team also knows if and when something changes in the report and what the change is.

Version Controls in Google Data Studio — Image by Author

In the above image, you can see a dashboard that I built via the self-service-BI tool, Google Data Studio. And on the right, you see the different versions, which were automatically generated.

Summary

In conclusion, the process of data versioning is essential for correctly processed data sets. This article has given you an overview of how and within what frameworks data can be versioned. As an outcome, one can say that data should be versioned directly in the data process so you can easily use it for data analysis.

Data versioning also plays an important role in the field of analytics since it allows for different departments to analyze the same data for their own needs and through their own perspectives.