How to Build a Robust ELT Pipeline

I’ll start by saying that this is the first in a series of posts on building an enterprise-grade data pipeline.

To kick things off, I’ve got a pet project that’s perfect for illustrating these concepts and best practices. And yes, folks, we’re talking about Clash of Clans data. Let’s take this step-by-step: today we will focus on the first two steps: extract and load (EL).

In modern cloud architectures, this ELT pattern has taken off due to the massive compute capacity offered by cloud Data Warehouses and their overall scaling potential. Before diving into the GCP stack we’ll use (spoiler alert: BigQuery is front and center), we need to make a quick initial diagnosis.

Current Situation

The Clash of Clans API is pretty straightforward. It lets you fetch data via GET requests. To make that happen, you need an API key. But here’s the catch: for security reasons, you have to whitelist the specific IP address making the requests.

Objective

Create a resilient extraction flow that runs daily.

Resilience

What does resilience actually mean in our case? Specifically:

1. Third-party APIs are out of our control: The response body can change without warning, and our code must handle that gracefully.
2. Network hiccups happen: We need to monitor and reprocess failed requests in a timely manner without causing duplicate records down the line.
3. Corrupt or anomalous payloads shouldn’t break the system: If we ingest a weird file, it should fail silently but be fully traceable so we can diagnose and fix it.

What else should you keep in mind for enterprise pipelines?

1. Rate Limit Management: Clash of Clans doesn’t explicitly document its limits, but since we’re only querying data for a single clan, throttling shouldn’t be an issue. Still, in production-grade environments, handling rate limits is non-negotiable.
2. Downtime & 5xx Errors: We must build in retry logic with exponential backoff. Keep in mind that some data changes fast (like transactional logs), while Clash of Clans data barely changes throughout the day. If something fails, we can just check the logs and trigger it manually-remember, you don’t need a sledgehammer to crack a nut (or as we say in Spanish, you don’t need a cannon to kill a fly).

The Stack

For this use case, we can build a highly resilient pipeline on a shoestring budget by taking full advantage of GCP’s serverless offerings. The architecture is detailed below:

A few key takeaways from the diagram:

1. We route outbound traffic through Cloud NAT to get a static public IP for our whitelist. It’s a secure, enterprise-grade way to handle external API constraints.
2. Google Cloud Workflows acts as our serverless orchestrator. Right now, it handles the Bronze layer ingestion, but we’ll add Silver and Gold layers later.
3. A Cloud Run Job triggers the daily extraction and inserts the raw records directly into BigQuery.
4. Down the road, we’ll introduce transformation pipelines using Dataform; for now, we can skip that.
5. Fun fact: the Data Warehouse and the extraction pipeline live in separate GCP projects. This is standard practice in companies with strict data governance policies.

The Code

You can check out the repository here: github.com/JoseChavezUriarte/coc_elt. In the meantime, here are a few highlights:

1. In models.py, we avoid strict validation on the API fields. We want our models to be flexible so that if the Clash of Clans API adds new attributes, our ingestion pipeline doesn’t crash. Ensuring the extraction succeeds is our number-one priority.

from typing import Any, List
from pydantic import BaseModel, ConfigDict, model_validator

def normalize_envelope(data: Any) -> Any:
    if isinstance(data, dict):
        if "items" in data:
            return data
        raise ValueError(f"Expected pagination envelope with an 'items' array. Received keys: {list(data.keys())}")
    if isinstance(data, list):
        return {"items": data}
    raise ValueError(f"Root payload must be a dictionary envelope or a list. Received type: {type(data).__name__}")

class ClanRecord(BaseModel):
    model_config = ConfigDict(extra='allow')
    tag: str
    name: str

class MemberRecord(BaseModel):
    model_config = ConfigDict(extra='allow')
    tag: str
    name: str

class MemberListResponse(BaseModel):
    items: List[MemberRecord]

    @model_validator(mode='before')
    @classmethod
    def validate_envelope(cls, data: Any) -> Any:
        return normalize_envelope(data)

class WarRecord(BaseModel):
    model_config = ConfigDict(extra='allow')
    state: str

class CapitalRaidRecord(BaseModel):
    model_config = ConfigDict(extra='allow')
    state: str
    startTime: str

class CapitalRaidListResponse(BaseModel):
    items: List[CapitalRaidRecord]

    @model_validator(mode='before')
    @classmethod
    def validate_envelope(cls, data: Any) -> Any:
        return normalize_envelope(data)

class LeagueGroupRecord(BaseModel):
    model_config = ConfigDict(extra='allow')
    state: str
    season: str

class WarLeagueWarRecord(BaseModel):
    model_config = ConfigDict(extra='allow')
    state: str

1. The entire infrastructure is managed as code using Terraform (IaC).
2. I drove this migration with a bit of help from AI, verifying our implementation plans using the EARS notation.

What Do the Output Tables Look Like?

The final output consists of simple tables with just two columns: timestamp and payload. Each table is partitioned by day (timestamp).

Final Thoughts

Before we label this design “enterprise-grade”, there two cost and design trade-offs to keep in mind:

1. Cloud NAT costs around $32 USD per month. If this is the only job running, it’s not very cost-effective. We’re assuming Cloud NAT will be shared with other services across the organization.
2. We opted for a pragmatic ingestion approach: if a job is rerun, we allow duplicate records in the Bronze layer. We’re using an APPEND-ONLY pattern instead of over-engineering idempotency keys. Given the low data volume, deduplicating to find the “freshest” record in the transformation layer is cheap, easy, and keeps our infrastructure simple.

References

• Cloud NAT
• Dataform
• Clash of Clans API