beiqisu10/crypto_data_engineering

Purpose and Entry Points

This repository implements a batch crypto analytics pipeline that joins Binance BTC/USDT market data, CoinGecko prices, and Fear & Greed sentiment to evaluate sentiment-versus-return behavior. Primary operational entry points are Makefile (make setup, infrastructure, Airflow, and dbt commands), Airflow DAGs, and standalone ingestion scripts in scripts/.

Infrastructure Boundary: GCP Resources

Terraform provisions the cloud storage and warehouse layer consumed by ingestion and transformation workloads: GCS is the raw landing/data-lake boundary, while BigQuery is the modeled analytics boundary. Infrastructure definitions are concentrated in:

• terraform/main.tf

• terraform/providers.tf

• terraform/variables.tf

The runtime requires a GCP project, enabled BigQuery/GCS APIs, and service-account credentials; Airflow uses a google_cloud_default connection and dbt uses its BigQuery profile.

Orchestration Layer

Airflow runs containerized workflows and establishes ordering between independently deployable ingestion pipelines:

• airflow/docker-compose.yaml

• airflow/dags/binance_ingestion_dag.py

• airflow/dags/market_data_dag.py

binance_spark_ingestion_v1 is the source-of-truth market-data workflow. The market metadata/sentiment DAG waits on its successful completion through an ExternalTaskSensor, preventing downstream sentiment processing against absent or stale price data. DAG tasks retry transient failures twice with five-minute delays and include failure-callback hooks.

Ingestion and Raw Data Flow

Python ingestion jobs retrieve external API data and deliver it into the GCS/BigQuery landing path:

• scripts/ingest_binance.py

• scripts/ingest_coingecko.py

• scripts/ingest_sentiment.py

The intended flow is API extraction → raw CSV landing in GCS → Spark/PySpark cleaning and Parquet conversion → BigQuery external or native raw/staging tables. External dependencies are Binance, CoinGecko, and Alternative.me Fear & Greed data; their rate limits and available historical windows constrain current coverage.

Transformation and Semantic Modeling

dbt is the warehouse transformation boundary. It converts landing sources into normalized staging models, then produces an analytics-ready fact table:

• dbt_crypto/models/staging/stg_crypto__btcusdt.sql

• dbt_crypto/models/staging/stg_crypto__prices.sql

• dbt_crypto/models/staging/stg_crypto__sentiment.sql

• dbt_crypto/models/marts/fct_crypto_daily_metrics.sql

The three staging streams converge by date in fct_crypto_daily_metrics, where SQL window logic derives volatility, moving measures, and forward-return-oriented metrics for sentiment analysis.

Serving and Warehouse Optimization

The final BigQuery fact model is designed for dashboard consumption, including Looker Studio visualizations of fear/return relationships and price-versus-sentiment trends:

• dbt_crypto/models/marts/fct_crypto_daily_metrics.sql

• dbt_crypto/models/marts/fct_crypto_daily_metrics.yml

It is partitioned daily on date_day to constrain time-range scans and clustered by sentiment_label for efficient sentiment-segment queries. dbt schema metadata defines core fact-table integrity expectations, including unique and non-null daily keys.

Runtime Topology

Airflow runs in Docker via the repository-specific image and Compose environment:

• airflow/Dockerfile

• airflow/docker-compose.yaml

• pyproject.toml

main.py is the top-level Python module, while Airflow is the primary scheduled execution surface. dbt execution is intentionally run from the Airflow container so warehouse permissions and dependencies align with orchestration runtime configuration.