Hands-On Analytics Engineering

Now that we’ve discussed quite a lot of theory in the previous chapters, your hands might be itching to get to work with some technology! In this chapter, you will put theory into practice by helping an old friend gain analytical insights into their fledgling company. Along the way, you will use several tools for the heavy lifting and get acquainted with different parts of the analytical engineering stack. Additionally, you can access the book’s GitHub repository, which contains step-by-step instructions for setting up the tooling that will be used in the chapter.

In this chapter, we will discuss the following topics:

• Understanding the Stroopwafelshop use case
• Preparing Google Cloud
• ELT using Airbyte Cloud
• Modeling data using data build tool (dbt) Cloud
• Visualizing data with Tableau

Technical requirements

For this chapter, you will need the following:

• Internet connection: All the tools we’ll be using are cloud-based, so a reliable internet connection is essential.
• Google account: To use the tooling in this chapter, you will need to create some accounts unless you have them already. To use Google BigQuery (https://cloud.google.com/bigquery/docs/introduction) and Google Sheets (https://www.google.com/sheets/about/), you will need a Google account. You will need payment information to make use of BigQuery’s full features, but since we will only make use of the free credits, there is no cost to using Google’s products.
• Other accounts: Similarly, you will need an account for Airbyte Cloud (https://cloud.airbyte.com/signup), dbt Cloud (https://www.getdbt.com/signup), and Tableau (https://www.tableau.com/products/trial). You can leverage your Google account for Airbyte Cloud using SSO, but you will need to create an account for dbt...

Understanding the Stroopwafelshop use case

A few months ago, your friend Jan founded a business in Amsterdam. He lucked out on a prime location near the center of Amsterdam. There, Jan decided to start selling the Dutch delicacy of Stroopwafels, a cake consisting of two layers of waffles with caramel-like syrup in the middle. Tourists and Dutch residents love these traditional treats. You decide to visit him in the store and try one of his now-famous Stroopwafels over a cup of coffee. Delicious!

While the two of you talk, Jan tells you that they have gained a lot of customers in a short time – so many that they have been feeling overwhelmed lately. As the number of customers grows, so does the information load. Jan would love to get a better insight into the business’s performance, but since he is so busy managing the store, he only finds a little time to explore insights. Besides, his data is scattered over multiple systems, and he only has a little experience working...

Preparing Google Cloud

Since we will use Google BigQuery as our data warehouse, we will need a Google account to prepare BigQuery for loading the Stroopwafelshop data. For this, you will need the following:

• A new Google Cloud Trial account with $300 in free credits. Alternatively, you can reuse your existing Google Cloud account (in that case, any costs that are incurred, although likely minimal, are at your own risk)!
• A Google Cloud project named stroopwafelshop.
• A BigQuery dataset named stroopwafelshopdata.
• Three service accounts, assigned with IAM roles:
  • A service account named airbyte that’s been assigned the BigQuery Data Editor and BigQuery User roles.
  • A service account named dbt-cloud that’s also been assigned the BigQuery Data Editor and BigQuery User roles.
  • A service account for Tableau, including the BigQuery Data Viewer role for read-only access
• For each service account, you will need to create and download a key in JSON format. These will...

Modeling data using dbt Cloud

With the raw data in BigQuery, you might want to jump right in and start uncovering those key business insights. But before you start building those queries, take a moment to think about the tools and strategies available. Remember, there is often more than one way to tackle data analysis.

The shortcomings of conventional analytics

BigQuery and platforms of its kind are built to manage massive data volumes at impressive speeds. Still, this does not iron out every hurdle for analysts. They frequently receive ad hoc requests for data queries, such as dissecting sudden changes in sales patterns, forecasting next week’s inventory requirements, or explaining yesterday’s customer behavior. Analysts address these tasks diligently aiming to enhance business insights. Often, they might pull SQL from one of their past analyses and repurpose it for a new one. This new analysis could be stored away in a Word document on a shared network drive or...

Visualizing data with Tableau

Once the data has been cleaned and transformed and is available in your data warehouse, it is time to use this data to generate insights. In this section, we will walk you through the basics of using Tableau and how you can create dashboards. Additionally, we will expand on how the Stroopwafelshop can use dashboards effectively to answer different business questions.

Why Tableau?

Tableau stands out in the business and analytics world for its straightforward but powerful data visualization features. Its ease of use appeals to a wide range of users, not just tech experts, enabling them to create engaging and interactive charts and graphs. It is versatile, working seamlessly with various data sources and offering advanced functions like trend analysis and forecasting. Tableau’s active online community is a big plus, offering learning and networking opportunities. Furthermore, constant updates keep Tableau at the forefront of data visualization...

Selecting the KPIs

You proudly tell Jan that all the tooling is now in place to create a dashboard! The most important thing now is to get his input on what to start measuring. As stated earlier in this chapter, Jan values healthy financial growth and customer satisfaction. He can also see the importance of monitoring these objectives as they evolve. After careful consideration, Jan has decided which KPIs are the most important for his business and how he is going to monitor them in a dashboard.

Here’s an overview of the KPIs that have been selected:

• Sales revenue: The total revenue generated during a given period. This is a fundamental KPI for any retail business, providing a basic measure of its financial performance.
• Sales volume: The total number of waffles sold during a given period. It should be tracked in total and broken down by different waffle types.
• Gross profit: This is calculated as sales revenue minus the product’s unit price (cost of...

Summary

In this chapter, we transitioned from theory to practice by focusing on analytics engineering in a real-world context. The Stroopwafelshop case study guided you in assisting the store’s owner, Jan, with understanding and analyzing his business data. This case study served as a practical example, demonstrating how to apply analytics engineering techniques and tools in a business context.

Powerful tools such as Airbyte Cloud, Google BigQuery, dbt Cloud, and Tableau were introduced and used. This hands-on approach has not only equipped you with the necessary skills to tackle real-life analytics engineering challenges but also culminated in the creation of an insightful dashboard for the Stroopwafelshop. This combination of theory and practice has offered a glimpse into the daily activities of an analytics engineer, underscoring the importance of comprehending metrics and KPIs to add value to the business.

In the next chapter, you will delve into the importance of...