Reddit sentiment ETL architecture

Reddit sentiment ETL architecture An architecture diagram generated by Archify. .env secrets · Reddit · MySQL · Postgres · Architecture component .env secrets Reddit · MySQL · Postgres Airflow scheduler · daily · 1 retry / 2 min · Docker Compose on EC2 · custom Airflow image · Apache Airflow Airflow scheduler daily · 1 retry / 2 min Docker image · airflow 2.10.1 + JRE · Docker Compose on EC2 · custom Airflow image · Docker Docker image airflow 2.10.1 + JRE Reddit API · OAuth · 4 subreddits · Architecture component · Reddit Reddit API OAuth · 4 subreddits Extract task · PRAW → pandas · Docker Compose on EC2 · custom Airflow image · Python Extract task PRAW → pandas Staging · raw · new_posts.parquet · Docker Compose on EC2 · custom Airflow image Staging · raw new_posts.parquet MySQL · raw reddit_db.r_posts · Architecture component · MySQL MySQL raw reddit_db.r_posts Spark transform · VADER sentiment UDF · Docker Compose on EC2 · custom Airflow image · spark.apache.org Spark transform VADER sentiment UDF Staging · scored · transformed_data.parquet · Docker Compose on EC2 · custom Airflow image Staging · scored transformed_data.parquet PostgreSQL · analytics public.r_posts · Architecture component · PostgreSQL PostgreSQL analytics public.r_posts Power BI · sentiment dashboard · Architecture component · app.powerbi.com Power BI sentiment dashboard PRAW to_parquet insert SELECT * overwrite insert read daily run env_file build Docker Compose on EC2 · custom Airflow image

DAG · SentimentAnalysis-Reddit

  • • Reddit-API-Data-Extraction
  • • Data-Ingestion-to-MySQL
  • • Sentiment-Analysis-Powered-by-Spark
  • • Postgres-Ingestion-for-Analytical-Processing

Sentiment rules

  • • Only text_content longer than 20 chars
  • • VADER compound > 0.5 → Positive
  • • < -0.5 → Negative · else Neutral

Guarantees

  • • Loads skip rows whose id already exists
  • • Spark session stopped after each run
  • • Subreddits: python · career · dataengineering · data