PROJECT-002Status: Still thinking

My First End-to-End Data Engineering Take-Home Challenge

A two-part data engineering project combining GCP architecture design, Terraform infrastructure, BigQuery, dbt Core, data-quality tests and GitHub Actions CI using the public Bitcoin Cash blockchain dataset.

Building My First End-to-End Data Platform With Terraform, BigQuery and dbt

A note before you start: this project is long and deliberately detailed. That is precisely why it lives in the Projects section of NotFinalDev: it documents not only the final result, but also the decisions, mistakes, implementation details and learning process behind it.

I had seen data-platform architecture diagrams at university and at work.

I had never designed one by myself, provisioned its cloud resources, built the transformations and connected the result to a pull-request workflow.

That was my first reaction when I opened the Astrafy Data Engineer take-home challenge:

Okay. I have never done this before by myself.

There was also a time constraint, so I knew I would use AI.

I could have handed the entire assignment to an agent, collected the generated files and hoped they worked. Instead, I spent two days following the implementation step by step, returning to anything I did not understand, reading the code, inspecting logs and comparing decisions with documentation.

The result was not only a submitted architecture and two working repositories.

I also ended up with a 25-step guide explaining the commands, technical choices, assumptions and production considerations behind the solution.

This project is the record of what I built, what I understood, what was still assisted heavily by AI, and what I would change before calling any of it production-ready.

This case study describes my own implementation and public repositories. It does not reproduce the original challenge document.

Project at a glance

Area	Implementation
Platform design	Google Cloud analytical platform for databases and SaaS applications
Infrastructure	Terraform
Source data	Public Bitcoin Cash dataset in BigQuery
Warehouse	BigQuery
Transformations	dbt Core
Data model	Three-month staging table and current address-balance mart
Testing	Eight dbt data-quality tests
Continuous integration	GitHub Actions on pull requests
Authentication	Terraform-provisioned Google Cloud service account
Documentation	Architecture, repository READMEs and a 25-step implementation guide

The work was split into two distinct parts:

Design a target data platform for a company with multiple operational databases and business applications.
Implement a smaller analytics pipeline using Terraform, BigQuery, dbt Core and GitHub Actions.

The first part tested architectural reasoning.

The second part made some of that reasoning concrete.

How I approached unfamiliar tools

How I worked through the challenge

procedure

Before the assignment, I had encountered most of these tools or ideas separately.

I understood what infrastructure as code meant. I knew what a data warehouse was. I had seen CI/CD pipelines and architecture diagrams. But knowing the vocabulary is different from connecting the components yourself and debugging the result when they do not cooperate.

The assignment forced me to connect several ideas at once:

Terraform resources become real cloud infrastructure.
IAM determines what an automated identity may actually do.
BigQuery is both a storage and execution environment.
dbt turns SQL files into a dependency-aware, documented and tested project.
A pull request can trigger work outside GitHub.
A green workflow means more than code that merely parses.

Part 1 — Designing the target platform

The design scenario included several types of data sources:

PostgreSQL
MySQL
MongoDB
SAP
Salesforce
SurveyMonkey

The target platform had to run on Google Cloud, favor open-source technologies where practical and give serious attention to GitOps and DataOps.

My design separated the platform into five main paths:

Data sources
Ingestion
Raw storage
Transformation and modelling
Consumption

A separate control plane handled delivery, orchestration, security, quality, lineage and monitoring.

Why put Cloud Storage before BigQuery?

I chose Cloud Storage as an immutable raw layer because ingestion should not be coupled too tightly to transformation.

Keeping source-aligned files creates a point from which the platform can:

Replay a failed load
Recompute downstream tables
Audit what was received from a source
Compare transformed results with the original payload
Preserve history independently of warehouse models
Investigate whether a problem began during ingestion or transformation

Without a retained raw layer, a broken transformation may require the source system to be queried again. That may be expensive, slow or impossible when the source has changed since the original load.

The raw zone therefore acts less like a polished analytics layer and more like evidence.

Why Airbyte and Debezium?

The architecture had to support databases and SaaS applications with very different interfaces.

Airbyte provided a connector-based approach for batch extraction from databases, APIs and common business applications. That reduces the amount of source-specific ingestion code the team has to maintain.

Debezium was included for change-data capture from transactional databases. Instead of repeatedly copying complete tables, CDC can publish inserts, updates and deletes as they occur.

The intent was:

Airbyte for broad connector coverage and scheduled ingestion
Debezium where database-level change capture is justified
Source-specific APIs only when existing connectors are insufficient

I considered this easier to extend than building a separate custom ingestion service for every source.

Why GKE?

The challenge preferred a Google Cloud environment while also encouraging open-source technologies.

GKE appeared to be a reasonable runtime for self-hosted tools such as Airbyte, Debezium and Argo CD because it keeps the operational environment inside Google Cloud while preserving portability at the application layer.

That does not mean GKE is automatically the correct answer for every company.

For a smaller platform, Kubernetes could introduce more operational work than the ingestion volume justifies. I would first validate whether managed services, a smaller container runtime or a simpler batch process could meet the same requirements.

The architecture represents a target platform, not a recommendation to deploy every component on day one.

Understanding the BigQuery layers

I initially described the layers as staging, warehouse and department-specific data lakes.

That last term was wrong.

A data mart is not a department-specific data lake. It is a curated, structured model designed around a business domain or analytical use case.

The layers in the architecture are better understood as:

Staging

Staging models remain close to the source.

Typical work includes:

Renaming fields
Casting types
Filtering unusable records
Standardizing timestamps
Flattening source structures
Applying light source-specific cleaning

The purpose is to make raw data predictable without embedding too much business meaning.

Core

The core layer contains validated and reusable business entities.

This is where the platform can define consistent concepts such as:

Customers
Products
Orders
Accounts
Transactions
Shared dimensions and measures

The core layer reduces the risk that every department defines the same concept differently.

Data marts

Data marts reshape trusted data for a specific team or use case.

Examples could include:

Finance reporting
Sales performance
Marketing attribution
Operational monitoring
Machine-learning features

They are designed for consumption rather than for preserving the source structure.

Separate consumption paths for BI and ML

I separated Looker from Vertex AI because dashboards and machine-learning systems consume data differently.

Looker represents governed business intelligence:

Shared metrics
Department dashboards
Controlled access
Analyst exploration
Business-user reporting

Vertex AI represents the machine-learning path:

Model training
Batch predictions
Online endpoints
Model deployment
Integration with applications

Both paths depend on curated data, but they do not have the same workloads or users.

The control plane I did not fully understand yet

The architecture also included:

Cloud Build
Artifact Registry
Argo CD
GKE
Cloud Composer
OpenLineage or OpenMetadata
Cloud Monitoring and Logging
dbt tests and additional data-quality checks
IAM, service accounts, Secret Manager and Cloud KMS

I cannot honestly claim that I designed this entire chain from practical experience.

This was the most AI-assisted part of the architecture.

At the time, I understood the individual names better than the full sequence connecting them. I knew the platform needed version control, validation, deployment, orchestration, lineage, alerting and access control. I did not yet have enough operational experience to design that control plane confidently from memory.

The intended flow was approximately:

Part 2 later helped some of these ideas become less abstract.

GitHub Actions showed me how a code event can trigger validation and execution. Terraform showed me how infrastructure changes can be reviewed as code. dbt showed me how analytical models and tests can be automated.

The diagram came first.

Part of the understanding came afterward.

Part 2 — What I actually implemented

The coding challenge was intentionally smaller than the target architecture.

It used the public Bitcoin Cash dataset hosted in BigQuery and required:

A Google Cloud project managed with Terraform
BigQuery staging and data-mart datasets
A service account with the necessary permissions
A staging model for the latest three available months
A current address-balance mart
Exclusion of addresses linked to coinbase transactions
dbt data-quality tests
A GitHub Actions workflow running dbt on pull requests

I separated the solution into two repositories:

Provisioning Google Cloud with Terraform

The infrastructure repository provisions:

The Google Cloud project configuration
Required Google Cloud APIs
The staging BigQuery dataset
The data_mart BigQuery dataset
A service account for dbt CI
BigQuery permissions
A service-account key for GitHub Actions
Terraform outputs used during configuration

The basic Terraform workflow became:

terraform init
terraform fmt -recursive
terraform validate
terraform plan
terraform apply

Once the infrastructure existed, I verified the datasets with the BigQuery CLI and ran another plan to check that Terraform detected no drift.

bq ls --project_id=astrafy-bch-xlofi-2026
terraform plan

Seeing No changes. Your infrastructure matches the configuration. was one of the first points where infrastructure as code stopped feeling theoretical.

Terraform was not only creating resources.

It was comparing the declared state of the project with the real cloud environment.

IAM: the part I initially delegated

The AI handled much of the first IAM implementation.

I did not immediately understand why the service account needed permissions at different levels.

The final configuration gave the dbt CI identity:

roles/bigquery.jobUser at project level
roles/bigquery.dataEditor on the staging dataset
roles/bigquery.dataEditor on the data-mart dataset

resource "google_project_iam_member" "dbt_job_user" {
  project = google_project.bitcoin_cash.project_id
  role    = "roles/bigquery.jobUser"
  member  = "serviceAccount:${google_service_account.dbt_ci.email}"
}

resource "google_bigquery_dataset_iam_member" "staging_editor" {
  project    = google_project.bitcoin_cash.project_id
  dataset_id = google_bigquery_dataset.staging.dataset_id
  role       = "roles/bigquery.dataEditor"
  member     = "serviceAccount:${google_service_account.dbt_ci.email}"
}

resource "google_bigquery_dataset_iam_member" "data_mart_editor" {
  project    = google_project.bitcoin_cash.project_id
  dataset_id = google_bigquery_dataset.data_mart.dataset_id
  role       = "roles/bigquery.dataEditor"
  member     = "serviceAccount:${google_service_account.dbt_ci.email}"
}

The distinction eventually became clearer.

BigQuery Job User allows the service account to submit query jobs using the project.

BigQuery Data Editor allows it to create and update tables in the datasets where dbt materializes models.

The service account did not need to become project owner or project editor. Its write permissions were limited to the two datasets it was responsible for building.

That is what least privilege looked like in this small project: enough access for the workflow to succeed, but not broad administrative control over the project.

The service-account key compromise

For the challenge, Terraform created a service-account key that was stored as a GitHub secret.

That satisfied the requirement and allowed GitHub Actions to authenticate, but it is not the approach I would choose for a production system.

The private key is a long-lived credential. It is also present in Terraform state, even when the corresponding output is marked as sensitive.

That means the local state file needs to be protected as carefully as the credential itself.

For production, I would replace this with Workload Identity Federation so GitHub Actions could obtain short-lived credentials without storing a permanent Google Cloud private key.

I would also move Terraform state from the local machine to a secured remote backend with:

Encryption
Restricted access
Versioning
State locking
Audit logging

Exploring the Bitcoin Cash source

The public source table was:

bigquery-public-data.crypto_bitcoin_cash.transactions

The latest available transaction data I found ended on May 13, 2024.

That meant the latest three available calendar months were:

March 2024
April 2024
May 2024

The result surprised me at first because I expected “latest” to mean relative to the current date.

The dataset itself was older.

This was an important reminder that a pipeline should reason from the data that actually exists, not from an assumption about freshness.

Building the staging model

The staging model materializes a BigQuery table partitioned by day on block_timestamp.

{{ config(
    materialized = "table",
    partition_by = {
      "field": "block_timestamp",
      "data_type": "timestamp",
      "granularity": "day"
    }
) }}

The source is already partitioned by block_timestamp_month, so the model uses fixed partition boundaries that BigQuery can prune efficiently:

with filtered_transactions as (

    select
        transactions.* except (`hash`),
        transactions.`hash` as transaction_hash

    from {{ source("bitcoin_cash", "transactions") }} as transactions

    where transactions.block_timestamp_month >= date("2024-03-01")
      and transactions.block_timestamp_month < date("2024-06-01")
)

select *
from filtered_transactions

The date range uses an inclusive lower bound and an exclusive upper bound:

2024-03-01 ≤ block_timestamp_month < 2024-06-01

That captures exactly three calendar months without having to account for different month lengths.

I also renamed the generic hash field to transaction_hash. The new name is clearer in downstream models and avoids identifier-quoting problems in generated dbt tests.

The model currently materializes:

5,136,399 transactions
Three monthly source partitions
Data from March 1 to May 13, 2024

What I would change

The fixed range is appropriate for a reproducible take-home submission after inspecting the source.

A reusable production model should probably derive its boundaries from the maximum available source month rather than hard-code March to May 2024.

That would preserve the intended “latest three available months” behavior as the source evolves.

The modelling decision that mattered most

The staging table contains only three months because that was an explicit requirement.

The balance mart does not use only those three months.

Bitcoin Cash follows the UTXO model: balances are derived from transaction outputs that exist and have not yet been spent.

An address may still own an unspent output created years before March 2024. Restricting the balance calculation to the staging table would therefore omit valid historical holdings.

The mart reads the complete transaction history from the public source.

This is one of the most important assumptions in the project:

The staging requirement limits one model. It does not redefine what “current balance” means.

Calculating balances from unspent outputs

The model follows five main stages.

1. Flatten every transaction output

Bitcoin Cash outputs and their addresses are nested arrays in BigQuery.

The first CTE expands them into one row per transaction output and address:

all_outputs as (

    select
        transaction.`hash` as transaction_hash,
        output.index as output_index,
        address,
        output.value

    from {{ source("bitcoin_cash", "transactions") }} as transaction,
    unnest(transaction.outputs) as output,
    unnest(output.addresses) as address

    where address is not null

)

Each output is identified by:

The transaction hash that created it
Its index inside that transaction

2. Identify outputs referenced by inputs

A later transaction spends an earlier output by referencing its transaction hash and output index.

The model extracts those references from the nested inputs:

spent_outputs as (

    select distinct
        input.spent_transaction_hash as transaction_hash,
        input.spent_output_index as output_index

    from {{ source("bitcoin_cash", "transactions") }} as transaction,
    unnest(transaction.inputs) as input

    where input.spent_transaction_hash is not null

)

3. Keep only outputs that were never spent

The model left-joins all created outputs to the list of spent references.

Rows without a matching spent reference are UTXOs:

unspent_outputs as (

    select
        outputs.address,
        outputs.value

    from all_outputs as outputs

    left join spent_outputs as spent
      on outputs.transaction_hash = spent.transaction_hash
     and outputs.output_index = spent.output_index

    where spent.transaction_hash is null

)

4. Aggregate by address

The remaining output values are summed by address:

address_balances as (

    select
        address,
        sum(value) as balance_satoshis

    from unspent_outputs
    group by address

)

The final model exposes the value in both satoshis and BCH:

safe_divide(balance_satoshis, 100000000) as balance_bch

5. Exclude coinbase-transaction recipients

In this context, a coinbase transaction is the special transaction that creates a mining reward.

It does not refer to the Coinbase exchange.

The public dataset exposes mining-reward transactions through is_coinbase.

The model collects every address that received an output from at least one such transaction:

coinbase_addresses as (

    select distinct
        address

    from {{ source("bitcoin_cash", "transactions") }} as transaction,
    unnest(transaction.outputs) as output,
    unnest(output.addresses) as address

    where transaction.is_coinbase = true
      and address is not null

)

The final query removes those addresses and retains positive balances:

select
    balances.address,
    balances.balance_satoshis,
    safe_divide(balances.balance_satoshis, 100000000) as balance_bch

from address_balances as balances

left join coinbase_addresses
    using (address)

where coinbase_addresses.address is null
  and balances.balance_satoshis > 0

The table is clustered by address, which supports address-based lookups and filtering.

Adding data-quality tests

The project contains two models and eight dbt tests.

Staging tests

Column	Tests
`transaction_hash`	`not_null`, `unique`
`block_timestamp`	`not_null`
`block_timestamp_month`	`not_null`

A duplicate transaction hash would challenge the assumption that each transaction is represented once.

A null timestamp would make partitioning and date filtering unreliable.

Data-mart tests

Column	Tests
`address`	`not_null`, `unique`
`balance_satoshis`	`not_null`
`balance_bch`	`not_null`

The mart expects one row per address, so address uniqueness is part of the model contract.

The final dbt test run passed all eight tests.

These tests are basic, but they convert assumptions into executable checks.

Production improvements could include:

A custom test ensuring balances are strictly positive
A test confirming no coinbase-recipient address remains
Reconciliation checks between satoshi and BCH values
Source freshness checks
Volume-anomaly tests
Referential or accepted-range tests where appropriate

Running dbt on pull requests

The GitHub Actions workflow runs when a pull request targeting main is:

Opened
Reopened
Updated with new commits

on:
  pull_request:
    types:
      - opened
      - synchronize
      - reopened
    branches:
      - main

The workflow then performs the following sequence:

Pull-request validation

procedure

The authentication step uses the service-account JSON stored in GitHub Secrets:

- name: Authenticate to Google Cloud
  uses: google-github-actions/auth@v3
  with:
    credentials_json: ${{ secrets.GCP_SERVICE_ACCOUNT_KEY }}
    create_credentials_file: true
    export_environment_variables: true

The workflow installs the project dependencies, validates the dbt profile and then runs both transformations and tests:

- name: Install dbt packages
  run: dbt deps

- name: Validate dbt configuration
  run: dbt debug --target ci

- name: Run dbt models
  run: dbt run --target ci

- name: Run dbt tests
  run: dbt test --target ci

This was the part that made CI/CD feel concrete.

A pull request was no longer only a request for someone to review code.

It became an event that could authenticate to a cloud environment, validate a project, execute transformations and reject a change when tests failed.

The IntelliJ problem that did not block the project

The most frustrating technical problem was trying to connect BigQuery directly to IntelliJ IDEA.

I never managed to add the database successfully inside the IDE.

The CLI worked.

dbt worked.

BigQuery worked.

The infrastructure and CI workflow worked.

The missing IDE connection was inconvenient, but cosmetic.

That distinction mattered. I had to recognize that the platform itself was functioning and that one development interface was not essential to completing the task.

Not every unresolved issue is a blocker.

Sometimes the correct response is to use the working interface and continue.

How I used AI without making the project a black box

I used AI because the assignment had a time constraint and required several tools I had never combined alone.

That is not something I want to disguise.

I also do not want to describe the result as if AI independently completed the challenge while I watched.

I could have asked an agent to generate everything.

Instead, I used AI to move through unfamiliar territory faster, then spent the saved time reading the implementation, revisiting unclear steps, checking logs and building a guide I could use to reproduce the process.

Some decisions, particularly the target architecture’s control plane and the first IAM configuration, were more AI-assisted than others.

The coding challenge helped me understand parts of those suggestions afterward.

The lesson was not that AI removes the need to learn Terraform, BigQuery, dbt or CI/CD.

It was this:

The time saved by AI is only valuable when I reinvest some of it into understanding the system.

AI helped me reach working code sooner.

The learning came from refusing to stop there.

What I would change for production

This project satisfies the challenge, but several decisions were intentionally optimized for a small take-home implementation.

Authentication

Replace the long-lived service-account JSON key with Workload Identity Federation.

Terraform state

Move local state to a secured remote backend with encryption, access control, versioning and locking.

Environments

Separate development, staging and production projects or datasets.

CI isolation

Run pull-request models in isolated schemas or temporary datasets rather than sharing the same destinations as local development.

Dynamic time windows

Calculate the latest three available months from source metadata rather than hard-code the 2024 boundaries.

dbt execution

Consider dbt build to execute models and tests through one dependency-aware command.

Testing

Add business-rule, reconciliation, freshness and anomaly tests beyond basic null and uniqueness checks.

Cost controls

Add query-cost monitoring, budgets, alerts and stricter partition-filter enforcement.

Deployment controls

Run Terraform formatting, validation and plan checks automatically in pull requests. Require review before applying infrastructure changes.

Observability

Capture job failures, model duration, freshness, row-count changes and lineage in a shared monitoring layer.

Model strategy

Evaluate incremental materializations where they reduce repeated full-history processing without compromising correctness.

What worked

By the end of the challenge:

Terraform provisioned the required Google Cloud resources.
The staging and data-mart datasets existed in BigQuery.
The dbt service account could run jobs and write to the intended datasets.
The staging model materialized the three latest available source months.
The address-balance mart calculated balances from UTXOs.
Mining-reward recipient addresses were excluded.
All eight dbt tests passed.
GitHub Actions executed dbt on pull-request events.
Both repositories documented assumptions, commands and production limitations.
The architecture PDF and public repositories were ready for submission.

I am most proud that the complete system worked at all.

Not because every decision was perfect, and not because I had mastered every component after two days.

It worked because I was able to enter an unfamiliar technical environment, use AI to accelerate the work, inspect what it produced, debug the result and understand enough of the underlying mechanics to explain the solution.

That is a skill I expect to matter increasingly for junior engineers.

The value is not typing every line without assistance.

The value is being able to judge, verify, adapt and take responsibility for the system that emerges.

What I learned

Terraform became more than a collection of .tf files.

It became a way to declare infrastructure, inspect changes and detect drift.

BigQuery became more than a place to run SQL.

It became the execution environment, warehouse and permission boundary for the project.

dbt became more than SQL organized into folders.

It became a model graph with materializations, environments, tests, documentation and CI behavior.

GitHub Actions became more than workflow YAML.

It became the connection between a pull request and an automated cloud execution.

IAM remains the area where I have the most to learn, but the division between job permissions and dataset permissions now makes more sense than it did when I started.

Most importantly, I learned that not knowing how to build something yet is not the same as being unable to build it.

The 25-step guide

Alongside the submission, I created a 25-step implementation guide covering:

Repository setup
Google Cloud authentication
Terraform initialization and deployment
BigQuery verification
Service-account permissions
dbt installation and configuration
Source inspection
Model execution
Test execution
GitHub Secrets
Pull-request CI
Common errors
Cleanup
Production improvements

I plan to publish that guide separately as the practical companion to this project.

This page explains the reasoning and the experience.

The guide explains how to reproduce the implementation.

Repositories

Terraform infrastructure

github.com/XLofi/astrafy-bitcoin-cash-infrastructure

dbt analytics and CI

github.com/XLofi/astrafy-bitcoin-cash-dbt

Current status

The challenge has been submitted.

I am currently waiting for feedback.

That makes this project both a completed implementation and an unfinished learning record.

The code works.

The tests pass.

The next useful information will come from someone reviewing the choices I made and asking me to defend them.

Connected work

Documents

Connecting the dots between dbt, BigQuery and Terraformnote
It started here

Continued in

Working Is Not the Same as Understanding During a technical interviewnote
Positive review