Background

In today's business landscape, the collection, storage, and analysis of data play a pivotal role in driving the progress and success of an organization. However, businesses face numerous challenges in effectively managing this crucial process. One particular challenge that arises is the inherent complexity involved, making it far from a straightforward endeavor.

Consider the example of a fruit trading business, which relies on the ability to analyze various aspects such as the speed of fruit sales, customer purchasing patterns, perishability rates, and profitability of different fruit types. Achieving these insights necessitates several interconnected processes:

1. Collection and storage of transactional data: Gathering comprehensive data on each transaction and recording the condition of the products involved.
2. Integration of stored data: Merging the collected data into a consolidated and accessible database or table.
3. Generation of regular reports: Compiling and generating daily, weekly, and monthly reports by leveraging the consolidated data table.

However, the manual execution of these processes poses a significant time-consuming challenge. The sheer volume and complexity of the data require considerable human effort and resources, resulting in delays that impede timely analysis. As a result, crucial data from the previous day, week, or month might not be readily available for analysis until the following day, week, or month, hampering decision-making and business responsiveness.

This situation underscores the urgent need for an innovative solution that streamlines and automates the data collection, storage, and analysis processes. By addressing this challenge head-on, our organization can significantly enhance operational efficiency, expedite decision-making, and capitalize on real-time insights to drive business growth and success.

How Can the Data Warehouse Team Automate This?

To achieve automation in this process, the implementation of a computerized system becomes essential. One such solution is leveraging Airflow, an exemplary tool for automating data workflows.

By utilizing Airflow, we can establish a streamlined and efficient data management system. The database's data can be seamlessly uploaded into BigQuery through automated workflows orchestrated by Airflow. This integration empowers our organization to automate the data upload process, significantly reducing manual effort and ensuring timely availability of data for analysis.

Emerging Challenge in BigQuery

Now that our data is stored in BigQuery and readily available, the next step is to perform insightful analysis. Let's consider the scenario where we aim to analyze the duration of fruit decay. To accomplish this, we need to create a new table in BigQuery specifically designed to store data related to the decay time of fruits.

However, a challenge arises when we have extensive analysis requirements, such as understanding the correlation between fruit sales and decay rates. Each time we request this report, we must combine these two tables into a unified entity within BigQuery. While merging one or two tables may be a relatively fast process, the situation becomes more complex when numerous tables need to be merged. Unfortunately, this can result in a substantial delay, with the report taking up to 30 minutes to generate. Clearly, this waiting time is far from ideal and not conducive to efficient decision-making.

Unlocking Rapid Insights

To overcome this challenge, a viable solution is to create a consolidated table by utilizing a query to merge multiple tables. By preparing a pre-made table, the process of generating the report becomes exceptionally fast and efficient.

Empowering Limited Access Data Analysts

As a data analyst with limited access to the fruit selling company's infrastructure, automating the process of table formation becomes a significant challenge. However, I have made a groundbreaking advancement in addressing this issue. I have developed an automation platform within the Google environment, eliminating the need for additional expenses or server rentals.

The image above showcases the workflow involved in automating table merging. The query, responsible for joining the two tables, is stored in Gitlab. Leveraging Gitlab's CI/CD feature, the query is then uploaded to Google Drive. Utilizing the Apps Script extension, the query is further integrated into Google Sheets. Finally, with the assistance of the Apps Script extension, a routine trigger is set to execute the query in BigQuery using the BigQuery API.

Trigger routines, known as schedules, play a pivotal role in executing queries at specific time intervals, such as daily, weekly, or monthly. However, when dealing with extensive time intervals, the process of backfilling can become time-consuming. To overcome this challenge, the backfilling process can be intelligently divided into smaller segments, ensuring efficient execution and timely completion.

Conclusion

By utilizing this method, as a data analyst, I can provide regular and automatic daily reports on the rate of fruit sales in relation to fruit decay. These reports are derived from the fruit metrics table and can be conveniently accessed and visualized using Google Data Studio or Google Spreadsheets. This integration enables me to deliver timely insights without the need for manual report generation.

Plus and Minus

The key advantage of this approach is that it eliminates the need to rent an additional server, resulting in cost savings for automation. However, it is important to note that proficiency in programming is required to successfully implement this solution. While programming knowledge is necessary, it opens up possibilities for customization and fine-tuning. It empowers individuals to tailor the automation process according to specific needs and achieve desired outcomes efficiently.

You arrived at the end

If you found the article enjoyable or valuable, you can support my work by making a donation. Your contribution would be greatly appreciated. Thank you for your generosity! 🙏

https://www.buymeacoffee.com/eugeniusw