ETL Weekly Data Refresh #52
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: ETL Weekly Data Refresh | |
| on: | |
| schedule: | |
| - cron: "0 8 * * 1" | |
| workflow_dispatch: | |
| permissions: | |
| contents: read | |
| actions: read | |
| concurrency: | |
| group: etl-pipeline | |
| cancel-in-progress: false | |
| env: | |
| PYTHON_VERSION: "3.11" | |
| DATA_WRITE_LEGACY_CSV: "1" | |
| DATA_WRITE_LATEST_CSV: "1" | |
| DATA_WRITE_HISTORY_CSV: "1" | |
| EXPORT_HISTORY_BRIDGE_JSON: "1" | |
| USE_PUBLIC_RUN_MANIFEST: "1" | |
| REQUIRE_FRONTEND_METADATA: ${{ github.ref_name == 'main' && '1' || '0' }} | |
| FRONTEND_ASSETS_POLICY_MODE: "strict" | |
| TREND_SCORE_ENGINE: "duckdb" | |
| FRONTEND_BRIDGE_REMOTE_DIR: "datos/metadata/remote_assets" | |
| jobs: | |
| job_github: | |
| name: Source - GitHub | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 20 | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v6 | |
| - name: Set up Python | |
| uses: actions/setup-python@v6 | |
| with: | |
| python-version: ${{ env.PYTHON_VERSION }} | |
| cache: pip | |
| cache-dependency-path: backend/requirements.txt | |
| - name: Install dependencies | |
| run: | | |
| pip install --upgrade pip | |
| pip install -r backend/requirements.txt | |
| - name: Run GitHub ETL | |
| env: | |
| GITHUB_TOKEN: ${{ secrets.GH_PAT }} | |
| run: python backend/github_etl.py | |
| - name: Upload GitHub artifacts | |
| if: always() | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: github-data | |
| if-no-files-found: warn | |
| path: | | |
| datos/github_repos_2025.csv | |
| datos/github_lenguajes.csv | |
| datos/github_ai_repos_insights.csv | |
| datos/github_commits_frameworks.csv | |
| datos/github_correlacion.csv | |
| datos/latest/github_repos_2025.csv | |
| datos/latest/github_lenguajes.csv | |
| datos/latest/github_ai_repos_insights.csv | |
| datos/latest/github_commits_frameworks.csv | |
| datos/latest/github_correlacion.csv | |
| datos/history/**/github_repos_2025.csv | |
| datos/history/**/github_lenguajes.csv | |
| datos/history/**/github_ai_repos_insights.csv | |
| datos/history/**/github_commits_frameworks.csv | |
| datos/history/**/github_correlacion.csv | |
| job_stackoverflow: | |
| name: Source - StackOverflow | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 20 | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v6 | |
| - name: Set up Python | |
| uses: actions/setup-python@v6 | |
| with: | |
| python-version: ${{ env.PYTHON_VERSION }} | |
| cache: pip | |
| cache-dependency-path: backend/requirements.txt | |
| - name: Install dependencies | |
| run: | | |
| pip install --upgrade pip | |
| pip install -r backend/requirements.txt | |
| - name: Run StackOverflow ETL | |
| env: | |
| STACKOVERFLOW_KEY: ${{ secrets.STACKOVERFLOW_KEY }} | |
| run: python backend/stackoverflow_etl.py | |
| - name: Upload StackOverflow artifacts | |
| if: always() | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: stackoverflow-data | |
| if-no-files-found: warn | |
| path: | | |
| datos/so_volumen_preguntas.csv | |
| datos/so_tasa_aceptacion.csv | |
| datos/so_tendencias_mensuales.csv | |
| datos/latest/so_volumen_preguntas.csv | |
| datos/latest/so_tasa_aceptacion.csv | |
| datos/latest/so_tendencias_mensuales.csv | |
| datos/history/**/so_volumen_preguntas.csv | |
| datos/history/**/so_tasa_aceptacion.csv | |
| datos/history/**/so_tendencias_mensuales.csv | |
| job_reddit: | |
| name: Source - Reddit | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 20 | |
| outputs: | |
| status: ${{ steps.reddit_run.outputs.status }} | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v6 | |
| - name: Set up Python | |
| uses: actions/setup-python@v6 | |
| with: | |
| python-version: ${{ env.PYTHON_VERSION }} | |
| cache: pip | |
| cache-dependency-path: backend/requirements.txt | |
| - name: Install dependencies | |
| run: | | |
| pip install --upgrade pip | |
| pip install -r backend/requirements.txt | |
| - name: Reset Reddit workspace outputs | |
| shell: bash | |
| run: | | |
| rm -f \ | |
| datos/reddit_sentimiento_frameworks.csv \ | |
| datos/reddit_temas_emergentes.csv \ | |
| datos/interseccion_github_reddit.csv \ | |
| datos/latest/reddit_sentimiento_frameworks.csv \ | |
| datos/latest/reddit_temas_emergentes.csv \ | |
| datos/latest/interseccion_github_reddit.csv | |
| rm -rf \ | |
| datos/history/reddit_sentimiento \ | |
| datos/history/reddit_temas \ | |
| datos/history/interseccion | |
| - name: Run Reddit ETL (non-blocking) | |
| id: reddit_run | |
| env: | |
| REDDIT_CLIENT_ID: ${{ secrets.REDDIT_CLIENT_ID }} | |
| REDDIT_CLIENT_SECRET: ${{ secrets.REDDIT_CLIENT_SECRET }} | |
| shell: bash | |
| run: | | |
| set +e | |
| python backend/reddit_etl.py | |
| code=$? | |
| if [ $code -ne 0 ]; then | |
| echo "status=failed" >> "$GITHUB_OUTPUT" | |
| echo "Reddit ETL failed (non-critical); aggregate will continue with existing data fallback." >> "$GITHUB_STEP_SUMMARY" | |
| exit 0 | |
| fi | |
| missing=0 | |
| for expected in \ | |
| datos/latest/reddit_sentimiento_frameworks.csv \ | |
| datos/latest/reddit_temas_emergentes.csv \ | |
| datos/latest/interseccion_github_reddit.csv; do | |
| if [ ! -f "$expected" ]; then | |
| missing=1 | |
| fi | |
| done | |
| if [ "$missing" -ne 0 ]; then | |
| echo "status=failed" >> "$GITHUB_OUTPUT" | |
| echo "Reddit ETL produced no fresh latest/history outputs; aggregate will continue with previous aggregate fallback." >> "$GITHUB_STEP_SUMMARY" | |
| exit 0 | |
| fi | |
| echo "status=ok" >> "$GITHUB_OUTPUT" | |
| - name: Stage Reddit artifact payload | |
| if: always() | |
| shell: bash | |
| run: | | |
| mkdir -p artifact_payload/reddit | |
| printf '{"status":"%s"}\n' "${{ steps.reddit_run.outputs.status || 'unknown' }}" > artifact_payload/reddit/reddit_status.json | |
| for file in \ | |
| datos/reddit_sentimiento_frameworks.csv \ | |
| datos/reddit_temas_emergentes.csv \ | |
| datos/interseccion_github_reddit.csv \ | |
| datos/latest/reddit_sentimiento_frameworks.csv \ | |
| datos/latest/reddit_temas_emergentes.csv \ | |
| datos/latest/interseccion_github_reddit.csv; do | |
| if [ -f "$file" ]; then | |
| mkdir -p "artifact_payload/reddit/$(dirname "$file")" | |
| cp "$file" "artifact_payload/reddit/$file" | |
| fi | |
| done | |
| for dir in \ | |
| datos/history/reddit_sentimiento \ | |
| datos/history/reddit_temas \ | |
| datos/history/interseccion; do | |
| if [ -d "$dir" ]; then | |
| mkdir -p "artifact_payload/reddit/$(dirname "$dir")" | |
| cp -R "$dir" "artifact_payload/reddit/$dir" | |
| fi | |
| done | |
| - name: Upload Reddit artifacts | |
| if: always() | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: reddit-data | |
| if-no-files-found: error | |
| path: artifact_payload/reddit | |
| job_aggregate: | |
| name: Aggregate + Quality Gate | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 20 | |
| needs: | |
| - job_github | |
| - job_stackoverflow | |
| - job_reddit | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v6 | |
| - name: Set up Python | |
| uses: actions/setup-python@v6 | |
| with: | |
| python-version: ${{ env.PYTHON_VERSION }} | |
| cache: pip | |
| cache-dependency-path: backend/requirements.txt | |
| - name: Install dependencies | |
| run: | | |
| pip install --upgrade pip | |
| pip install -r backend/requirements.txt | |
| - name: Download GitHub artifacts | |
| uses: actions/download-artifact@v4 | |
| with: | |
| name: github-data | |
| path: artifacts/github | |
| - name: Download StackOverflow artifacts | |
| uses: actions/download-artifact@v4 | |
| with: | |
| name: stackoverflow-data | |
| path: artifacts/stackoverflow | |
| - name: Download Reddit artifacts | |
| uses: actions/download-artifact@v4 | |
| with: | |
| name: reddit-data | |
| path: artifacts/reddit | |
| - name: Snapshot repo Reddit baseline | |
| shell: bash | |
| run: | | |
| mkdir -p repo_baseline | |
| for file in \ | |
| datos/reddit_sentimiento_frameworks.csv \ | |
| datos/reddit_temas_emergentes.csv \ | |
| datos/interseccion_github_reddit.csv \ | |
| frontend/assets/data/reddit_temas_history.json \ | |
| frontend/assets/data/reddit_interseccion_history.json; do | |
| if [ -f "$file" ]; then | |
| mkdir -p "repo_baseline/$(dirname "$file")" | |
| cp "$file" "repo_baseline/$file" | |
| fi | |
| done | |
| - name: Download latest valid aggregate history (main) | |
| continue-on-error: true | |
| env: | |
| GITHUB_TOKEN: ${{ github.token }} | |
| GITHUB_REPOSITORY: ${{ github.repository }} | |
| run: | | |
| python scripts/download_valid_aggregate_artifact.py \ | |
| --repo "${GITHUB_REPOSITORY}" \ | |
| --workflow etl_semanal.yml \ | |
| --branch main \ | |
| --artifact-name aggregate-data \ | |
| --output-dir prev_artifacts \ | |
| --max-runs 20 | |
| - name: Detect previous aggregate snapshot | |
| id: previous_history | |
| shell: bash | |
| run: | | |
| if [ -d "prev_artifacts/datos/history" ] || [ -d "prev_artifacts/history" ]; then | |
| echo "expect_previous_history=1" >> "$GITHUB_OUTPUT" | |
| else | |
| echo "expect_previous_history=0" >> "$GITHUB_OUTPUT" | |
| fi | |
| - name: Restore previous aggregate outputs (if any) | |
| shell: bash | |
| run: | | |
| if [ -d "prev_artifacts" ]; then | |
| python scripts/materialize_etl_artifacts.py --project-root . prev_artifacts | |
| fi | |
| - name: Hydrate previous aggregate history seed | |
| if: ${{ steps.previous_history.outputs.expect_previous_history == '1' }} | |
| shell: bash | |
| run: | | |
| python scripts/hydrate_aggregate_history_seed.py --project-root . | |
| - name: Materialize source outputs | |
| shell: bash | |
| run: | | |
| python scripts/materialize_etl_artifacts.py --project-root . artifacts/github artifacts/stackoverflow artifacts/reddit | |
| - name: Verify artifact handoff | |
| shell: bash | |
| run: | | |
| missing=0 | |
| for required in \ | |
| github_repos_2025.csv \ | |
| github_lenguajes.csv \ | |
| github_commits_frameworks.csv \ | |
| github_correlacion.csv \ | |
| so_volumen_preguntas.csv \ | |
| so_tasa_aceptacion.csv \ | |
| so_tendencias_mensuales.csv; do | |
| if [ ! -f "datos/${required}" ]; then | |
| echo "::error::Missing required artifact file datos/${required}" | |
| missing=1 | |
| fi | |
| if [ ! -f "datos/latest/${required}" ]; then | |
| echo "::error::Missing required artifact file datos/latest/${required}" | |
| missing=1 | |
| fi | |
| done | |
| for optional in \ | |
| reddit_sentimiento_frameworks.csv \ | |
| reddit_temas_emergentes.csv \ | |
| interseccion_github_reddit.csv; do | |
| if [ ! -f "datos/${optional}" ]; then | |
| echo "::warning::Optional artifact missing (degraded mode may continue): datos/${optional}" | |
| fi | |
| if [ ! -f "datos/latest/${optional}" ]; then | |
| echo "::warning::Optional artifact missing (degraded mode may continue): datos/latest/${optional}" | |
| fi | |
| done | |
| if [ "$missing" -ne 0 ]; then | |
| exit 1 | |
| fi | |
| - name: Restore Reddit source baseline on fallback | |
| if: ${{ always() && needs.job_reddit.outputs.status == 'failed' }} | |
| shell: bash | |
| run: | | |
| python scripts/restore_reddit_baseline.py \ | |
| --project-root . \ | |
| --candidate-root repo_baseline \ | |
| --candidate-root prev_artifacts \ | |
| --mode source | |
| - name: Run Trend Score | |
| run: python backend/trend_score.py | |
| - name: Sync CSVs to frontend assets | |
| run: python backend/sync_assets.py | |
| - name: Restore previous Reddit bridges on source fallback | |
| if: ${{ always() && needs.job_reddit.outputs.status == 'failed' }} | |
| shell: bash | |
| run: | | |
| python scripts/restore_reddit_baseline.py \ | |
| --project-root . \ | |
| --candidate-root repo_baseline \ | |
| --candidate-root prev_artifacts \ | |
| --mode bridges | |
| - name: Validate CSV contract headers | |
| run: python backend/validate_csv_contract.py | |
| - name: Verify aggregate outputs | |
| shell: bash | |
| run: | | |
| missing=0 | |
| for required in \ | |
| datos/trend_score.csv \ | |
| frontend/assets/data/trend_score.csv \ | |
| frontend/assets/data/github_lenguajes.csv \ | |
| frontend/assets/data/github_commits_frameworks.csv \ | |
| frontend/assets/data/github_correlacion.csv \ | |
| frontend/assets/data/so_volumen_preguntas.csv \ | |
| frontend/assets/data/so_tasa_aceptacion.csv \ | |
| frontend/assets/data/so_tendencias_mensuales.csv \ | |
| frontend/assets/data/reddit_sentimiento_frameworks.csv \ | |
| frontend/assets/data/reddit_temas_emergentes.csv \ | |
| frontend/assets/data/interseccion_github_reddit.csv; do | |
| if [ ! -f "$required" ]; then | |
| echo "::error::Missing aggregate output $required" | |
| missing=1 | |
| fi | |
| done | |
| if [ "${EXPORT_HISTORY_BRIDGE_JSON}" = "1" ]; then | |
| for bridge_file in frontend/assets/data/history_index.json frontend/assets/data/trend_score_history.json; do | |
| if [ ! -f "$bridge_file" ]; then | |
| echo "::error::Missing bridge output $bridge_file" | |
| missing=1 | |
| fi | |
| done | |
| fi | |
| if [ "${USE_PUBLIC_RUN_MANIFEST}" = "1" ]; then | |
| if [ ! -f "frontend/assets/data/run_manifest.json" ]; then | |
| if [ "${REQUIRE_FRONTEND_METADATA}" = "1" ]; then | |
| echo "::error::Missing required public manifest frontend/assets/data/run_manifest.json" | |
| missing=1 | |
| else | |
| echo "::warning::Public manifest missing (soft mode): frontend/assets/data/run_manifest.json" | |
| fi | |
| fi | |
| fi | |
| if [ "$missing" -ne 0 ]; then | |
| exit 1 | |
| fi | |
| - name: Enforce frontend assets policy (strict) | |
| run: python scripts/check_frontend_assets.py --mode strict --root . | |
| - name: Enforce bridge integrity gate | |
| run: | | |
| python scripts/check_bridge_integrity.py \ | |
| --project-root . \ | |
| --expect-previous-history "${{ steps.previous_history.outputs.expect_previous_history }}" | |
| - name: Upload aggregate artifacts | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: aggregate-data | |
| if-no-files-found: error | |
| path: | | |
| datos/*.csv | |
| datos/latest/*.csv | |
| datos/history/**/*.csv | |
| datos/metadata/*.json | |
| datos/metadata/**/*.json | |
| frontend/assets/data/*.csv | |
| frontend/assets/data/*.json | |
| - name: ETL aggregate summary | |
| if: always() | |
| run: | | |
| echo "## ETL Aggregate Summary" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- GitHub job: ${{ needs.job_github.result }}" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- StackOverflow job: ${{ needs.job_stackoverflow.result }}" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- Reddit job: ${{ needs.job_reddit.outputs.status || 'ok' }}" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- Aggregate status: ${{ job.status }}" >> "$GITHUB_STEP_SUMMARY" | |
| job_publish: | |
| name: Publish Data | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 10 | |
| needs: | |
| - job_aggregate | |
| - job_reddit | |
| if: ${{ needs.job_aggregate.result == 'success' }} | |
| permissions: | |
| contents: write | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v6 | |
| - name: Download aggregate artifacts | |
| uses: actions/download-artifact@v4 | |
| with: | |
| name: aggregate-data | |
| path: artifact_payload | |
| - name: Restore aggregated files into workspace | |
| shell: bash | |
| run: | | |
| python scripts/materialize_etl_artifacts.py --project-root . artifact_payload | |
| - name: Commit updated data | |
| id: commit_data | |
| shell: bash | |
| run: | | |
| TARGET_BRANCH="${GITHUB_REF_NAME}" | |
| git config user.name "github-actions[bot]" | |
| git config user.email "github-actions[bot]@users.noreply.github.com" | |
| git add datos/ frontend/assets/data/ | |
| if git diff --staged --quiet; then | |
| echo "changed=false" >> "$GITHUB_OUTPUT" | |
| echo "No data changes to commit." >> "$GITHUB_STEP_SUMMARY" | |
| exit 0 | |
| fi | |
| git commit -m "data: refresh CSV outputs" | |
| git pull --rebase origin "$TARGET_BRANCH" | |
| git push origin "HEAD:$TARGET_BRANCH" | |
| echo "changed=true" >> "$GITHUB_OUTPUT" | |
| - name: Publish summary | |
| if: always() | |
| run: | | |
| echo "## ETL Publish Summary" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- Aggregate gate: ${{ needs.job_aggregate.result }}" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- Reddit status: ${{ needs.job_reddit.outputs.status || 'ok' }}" >> "$GITHUB_STEP_SUMMARY" | |
| echo "- Data committed: ${{ steps.commit_data.outputs.changed || 'false' }}" >> "$GITHUB_STEP_SUMMARY" |