New York Times Archive Cloud Data Warehouse (GCP + BigQuery + Airflow + dbt)

This project builds an end-to-end cloud data warehouse for the New York Times Article Archive using Google Cloud Platform.

The pipeline:

• Extracts historical articles from the NYT Archive API.
• Lands raw JSON into Google Cloud Storage (GCS).
• Loads and models data in BigQuery using Airflow and dbt.
• Performs data cleaning, SQL analytics, and machine learning for insights.
• Is designed as a portfolio-ready example of:
  • Data Engineering & Cloud Warehousing
  • Data & Business Analytics
  • Basic ML on real-world news data

---

1. Project Overview

Real news archives are rich but messy. The goal of this project is to:

1. Build a reproducible pipeline to ingest NYT articles at scale.
2. Store data in a query-friendly warehouse schema on BigQuery.
3. Enable analytical SQL for editors, analysts, and product teams.
4. Explore ML use cases such as topic trends and article popularity.

Key questions the warehouse can answer:

• How has coverage of specific topics (e.g., “climate change”, “elections”) evolved over time?
• Which sections and keywords are most common in different decades?
• What patterns appear in article length, sections, or publication volume?

---

2. Architecture

High-level architecture:

1. Ingestion (Python + NYT API)

   • scripts/archive_extraction.py calls the NYT Archive API for each year/month.
   • Raw JSON responses are stored in GCS.

2. Landing & Staging (GCS + BigQuery)

   • scripts/bigquery_pipeline.py loads raw JSON from GCS into BigQuery landing tables.
   • Schema inference and basic normalization.

3. Orchestration (Apache Airflow)

   • dags/nyt_archive_dag.py coordinates:
     • Archive extraction
     • GCS load
     • BigQuery staging/transformations

4. Transformations (dbt + SQL)

   • dbt/models.sql (placeholder for dbt models) defines warehouse-friendly tables:
     • Cleaned articles
     • Dimensions (section, keyword, date)
     • Aggregated fact tables

5. Analytics & ML (Notebooks + SQL)

   • notebooks/data_cleaning.ipynb performs data exploration & cleaning logic.
   • notebooks/machine_learning_analysis.ipynb explores ML use cases such as:
     • Topic clustering
     • Article popularity modelling
   • sql/sql_query_analysis.sql contains curated analytical queries for stakeholders.

6. Documentation

   • reports/GROUP5_REPORT.pdf – full written project report.
   • reports/GROUP5_DB_PRESENTATION.pptx – presentation slides.

Cloud stack:

• GCP: BigQuery, GCS
• Orchestration: Apache Airflow
• Modeling: dbt + SQL
• Analysis: Jupyter / Python / SQL

---

3. Repository Structure

nyt-archive-cloud-warehouse/
├── dags/
│   └── nyt_archive_dag.py         # Airflow DAG for end-to-end pipeline
├── scripts/
│   ├── archive_extraction.py      # NYT Archive API → GCS
│   └── bigquery_pipeline.py       # GCS → BigQuery landing/staging tables
├── notebooks/
│   ├── data_cleaning.ipynb        # Data understanding & cleaning
│   └── machine_learning_analysis.ipynb
├── dbt/
│   └── models.sql                 # dbt models for cleaned & aggregated tables
├── sql/
│   └── sql_query_analysis.sql     # Analytical SQL queries on the warehouse
├── reports/
│   ├── GROUP5_REPORT.pdf          # Detailed project report
│   └── GROUP5_DB_PRESENTATION.pptx
├── README.md
├── requirements.txt               # Python dependencies
└── .gitignore

Note: Raw data files and credentials are not stored in this repository for security and size reasons.

---

4. Data Pipeline Details

4.1 NYT Archive Extraction (archive_extraction.py)

Responsibilities:

• Call the NYT Archive API for a range of years/months.
• Handle pagination, retry logic, and simple error handling.
• Write raw responses to GCS as JSON or newline-delimited JSON.

Key config (loaded via environment variables):

• NYT_API_KEY – NYT API key
• GCP_PROJECT_ID – GCP project
• GCS_BUCKET_NAME – target bucket for raw data
• GCS_CREDENTIALS_BLOB – service account JSON blob name (or use ADC)

Example run (local):

export NYT_API_KEY="YOUR_API_KEY"
export GCP_PROJECT_ID="your-project-id"
export GCS_BUCKET_NAME="your-bucket"
export GCS_CREDENTIALS_BLOB="service-account.json"

python scripts/archive_extraction.py \
  --start_year 1980 \
  --end_year 2020

(Arguments are illustrative; adjust to match the script’s CLI.)

4.2 BigQuery Load (bigquery_pipeline.py)

Responsibilities:

• Read raw JSON from GCS.
• Create or update landing and staging tables in BigQuery.
• Apply initial flattening and type casting.

Example run:

python scripts/bigquery_pipeline.py \
  --dataset raw_nyt \
  --table archive_raw

---

5. Orchestration with Airflow

dags/nyt_archive_dag.py wires together:

1. Extract NYT archive → GCS
2. Load raw JSON → BigQuery
3. Run staging / transformation steps (optionally calling dbt)

Typical Airflow concepts used:

• PythonOperator – for calling extraction & load functions.
• Scheduling (e.g., monthly runs for new archives).
• Dependency management between tasks.

Once deployed to an Airflow environment (e.g., Cloud Composer or local Airflow), the DAG can be triggered manually or on a schedule.

---

6. Warehouse Modeling & Analytics

6.1 dbt Models (dbt/models.sql)

Example logical models (conceptual):

• articles_clean – cleaned article records with normalized date, section, and headline fields.
• dim_section – section dimension with human-readable labels.
• fact_article_counts – counts of articles by date, section, keyword, etc.

These models:

• Simplify downstream queries.
• Enforce consistent transformations.
• Support BI tools and analysts.

6.2 Analytical SQL (sql/sql_query_analysis.sql)

Contains example questions such as:

• Article count by section and year.
• Most frequent keywords over time.
• Distribution of article word counts.
• Trends for selected topics (e.g., “economy”, “climate”, “elections”).

This file is written in standard SQL targeting BigQuery.

---

7. Machine Learning & Data Analysis

The goal of the ML component is not to build a production model, but to show:

• How a cloud warehouse feeds into ML workflows.
• How to explore textual news data for insights.

7.1 notebooks/data_cleaning.ipynb

• Inspects raw data structure (JSON fields, nested arrays).
• Handles missing values, long text fields, and normalization.
• Defines reusable cleaning logic applied later in dbt or scripts.

7.2 notebooks/machine_learning_analysis.ipynb

Possible analyses include:

• Topic modeling or clustering on article abstracts/lead paragraphs.
• Basic classification or regression (e.g., predicting popularity proxies).
• Time-series plots of topic frequency.

These notebooks connect directly to BigQuery using the google-cloud-bigquery client, treating the warehouse as the single source of truth.

---

8. Setup & Installation

8.1 Prerequisites

• Python 3.9+

• A GCP project with:

  • BigQuery enabled
  • GCS bucket created

• NYT Archive API key

• Airflow environment (for DAG execution) – local or managed

• (Optional) dbt installed and configured for BigQuery

8.2 Install Python dependencies

From the project root:

python -m venv venv
source venv/bin/activate        # On Windows: venv\Scripts\activate
pip install -r requirements.txt

Example requirements.txt (simplified):

pandas
requests
google-cloud-bigquery
google-cloud-storage
apache-airflow
dbt-core
dbt-bigquery
jupyter

Adjust as needed to match the actual imports in your environment.

8.3 Environment Variables

Set the following before running scripts or Airflow tasks:

export GCP_PROJECT_ID="your-project-id"
export NYT_API_KEY="your-nyt-api-key"
export GCS_BUCKET_NAME="your-bucket"
export GCS_CREDENTIALS_BLOB="your-service-account.json"

In production, use a more secure mechanism (Secret Manager, Airflow Connections, etc.).

---

9. How This Maps to Different Roles

Data Engineering / Cloud

• Built a full ETL/ELT pipeline: API → GCS → BigQuery.
• Used Airflow for orchestration and scheduling.
• Designed warehouse layers (raw, staging, modeled) using SQL and dbt.

Data / Business Analytics

• Designed SQL queries to answer concrete editorial and product questions.
• Produced aggregated tables suitable for BI tools and dashboards.
• Showed how different dimensions (section, keyword, date) drive insights.

Machine Learning / Data Science

• Used warehouse data in notebooks for topic analysis and basic ML experiments.
• Demonstrated model-ready feature extraction from a cloud warehouse.

---

10. Future Improvements

• Add automated tests for extraction and transformations.
• Expand dbt project into separate models for each dimension/fact table.
• Integrate a BI layer (e.g., Looker Studio) on top of BigQuery.
• Add incremental loading for newly published articles instead of full historic runs.
• Build alerting around pipeline failures and data quality checks.

---

11. Acknowledgments

• New York Times Article Search & Archive APIs.
• Google Cloud Platform (BigQuery, GCS).
• dbt, Airflow, and the Python ecosystem for data engineering.