Bitcoin Cash is a cryptocurrency that allows more bytes to be included in each block relative to its common ancestor Bitcoin. There is a public dataset on BigQuery that contains the blockchain data in their entirety (dataset ID: bigquery-public-data.crypto_bitcoin_cash) with data pre-processed to be human-friendly and to support common use cases such as auditing, investigating, and researching the economic and financial properties of the system.
In this project, we will create staging table from the raw table "transactions" that only selects the last three months of data from this raw table. Then we will, materialize a datamart table that gives the current balance for all addresses and exclude addresses that had at least one transaction on Coinbase. Finally we will display a graph of daily transactions. We will use dbt cloud to materialize the two tables aforementioned and a Google colab notebook using Python to plot the chart of daily transactions from last 3 months.
- A Google Cloud account with access to BigQuery and Cloud Storage.
- A dbt Cloud account.
To get started with this project, follow these steps:
- Create a new Google Cloud project.
- Configure your Google Cloud and dbt Cloud
- Use dbt Cloud to materialize the staging and datamart tables.
- Create a notebook to plot the transactions of last 3 months.
dbt run --models STG_bitcoin_cash_transactions_3M
To Run datamart model
dbt run --models DTM_bitcoin_cash_transactions_3M
To use SQLFluff on Github Actions, you need to create a new workflow file in your repository like this one lint_sqlfluff.yml
This workflow uses the sqlfluff package to lint any SQL files in your /models directory that have been added or modified in the latest push or pull request on the main branch. It also uses the yuzutech/annotations-action to annotate your Github pull request with any lint errors found by SQLFluff.
To use this workflow in your own repository, you need to make sure to update the file paths and any other parameters such as --dialect bigquery to match your specific project.
Terraform is an open-source infrastructure as code (IaC) tool that allows you to manage your infrastructure, with the possibility to works with many popular cloud providers, including AWS, Azure, Google Cloud Platform. If you want to set up a Terraform based infrastucture you can follow these 2 steps:- Install Terraform
- Terraform - Get Started with GCP
- Add Terraform to your path
- Create a terraform.tfvar file and replace GCP project ID and the path to your key file:
project = "<PROJECT_ID>"
credentials_file = "<FILE>"
- Go to
terraform-docker-container/and execute./setup.bash - Anytime you modify your configuration execute
terraform apply
Once you are finished with Terraform, don't forget to terminate resources by executing terraform destroy
As we are dealing with transactions, we have some incomes and somes outcomes. This is why I decided to plot 3 graphs : incomes, outcomes and the global balance.
