Reflections and Considerations When Implementing an ELT Pipeline

This is an end-to-end project to implement a production-ready ELT process. We implemented a medallion architecture, storing data in different states: raw (bronze), denormalized (silver), and aggregated (gold).

Throughout the previous three chapters, we completed a hands-on implementation, but I want to pause to highlight several aspects that I consider fundamental.

The Problem

All data extraction flows seek to solve a problem, which is generally formulated as one or several questions. This question can belong to different domains: business, science, politics, economics, etc.

Before even starting to outline any pipeline, we must ensure we understand the problem. In the case of this project, we are solving a management problem. And while focusing on a video game might seem trivial, it helps me draw parallels with the real corporate world.

In our specific case, we have a clan leader who wants to know who is supporting the clan’s growth. To achieve this, the leader defined an activity criterion. Essentially, it seeks to answer the question “Which members are active?” in order to subsequently “manage promotions or kicks.” Once this point is clarified, we can start thinking about the strategy to build the pipeline.

Understanding Data Sources

1. Data sources and their characteristics. - Do we have documentation for the sources? Are they structured, semi-structured, or unstructured data sources? How are we going to process them?
2. Information volume. - How many records will we generate per day on average?
3. Update frequency. - How often is new data generated?

With these variables in mind, we can determine for this project:

• The data comes in a semi-structured JSON format, as we consume an API.
• The information volume depends on the dimension, with the player troops dimension containing the highest granularity.
• Whenever the API is queried, we retrieve the “current state”; however, this state does not change frequently, so we can consume it daily. This way, we avoid spending excessively on compute without sacrificing data freshness.

Tooling

We must understand which tools we have at our disposal and which ones are the most suitable. We have countless alternatives in the Google Cloud ecosystem (or other cloud solutions). Now that we understand the sources, we can determine the pipeline architecture:

1. Cloud Run (job) to extract information daily.
2. Cloud NAT to link the API with our static IP.
3. Workflows to orchestrate the entire flow in one place.
4. Dataform to version, add metadata, and manage our data transformations.
5. BigQuery to store information following a medallion architecture.
6. Data Studio to report information, answering the business question.

General Best Practices

• Application of the principle of least privilege using service accounts.
• Use of Terraform applying IaC (Infrastructure as Code).
• Creation of custom VPCs defining subnets.
• Adding metadata to Dataform definitions.
• Credentials management using Secret Manager.

Summary

We achieved a resilient data transformation pipeline to answer business questions, and we deployed the information in Data Studio so stakeholders have a daily report of weekly performance to manage accordingly.

Review Previous Chapters

This post is the fourth part of the series on building an enterprise-grade data pipeline. If you haven’t read the previous entries yet, I recommend checking them out in order:

1. How to Build a Robust ELT Pipeline - Data ingestion and Bronze layer.
2. Data Transformation: Structuring Data with Dataform - Incremental modeling and cleaning in the Silver layer.
3. Tracking Clash of Clans Member Activity with Dataform and BigQuery - Metric design and Gold layer aggregations.