Federal Job Market Analytics Pipeline (Python, SQLite, Streamlit)

Overview

This project builds a fully automated data pipeline that collects, transforms, and loads federal job posting data from the USAJobs Search API into a SQLite database. A Streamlit dashboard deployed on Streamlit Community Cloud provides interactive exploration of hiring trends, salary distributions, geographic demand, and agency-level breakdowns across nine analyst and technical role categories in the federal workforce.

Business Context

The federal job market is one of the largest and most structured employers in the United States, but its data is scattered across thousands of individual postings on USAJobs.gov. Job seekers targeting federal analyst and data roles lack a consolidated view of which agencies are hiring, what salaries look like by role and location, and where demand is concentrated. Workforce planners and career advisors face the same gap when guiding candidates toward federal opportunities. This pipeline aggregates that data into a single, queryable source with daily refreshes.

Objectives

• Build an automated API integration that collects federal job postings daily using GitHub Actions, with pagination and retry logic to handle API limits reliably
• Design an ETL pipeline that flattens nested JSON responses, deduplicates records by control number, classifies roles into categories, and standardizes salary data including hourly-to-annual conversion
• Implement a normalized SQLite database with upsert logic and pipeline run logging to support incremental data accumulation over time
• Deploy an interactive, multi-page Streamlit dashboard on Streamlit Community Cloud for exploring hiring volume, salary analysis, geographic demand, and agency breakdowns
• Automate the full pipeline end-to-end so it runs daily without manual intervention, building a growing historical dataset

Tools & Skills Demonstrated

• Python: Core language for all pipeline stages and the dashboard application
• requests: API calls with pagination, retry logic, and timeout handling
• pandas: Data manipulation, aggregation, and filtering for dashboard analytics
• SQLite: Lightweight relational database with upsert logic and pipeline logging
• Streamlit: Interactive dashboard framework with multi-page navigation and sidebar filters
• Plotly: Interactive charts including bar, histogram, scatter geo, pie, heatmap, and line charts
• GitHub Actions: Scheduled daily pipeline execution (cron at 6 AM UTC)
• Git: Version control for pipeline code and automated database commits

ETL Pipeline Flow

The pipeline runs daily on a schedule through GitHub Actions. Each stage is a standalone Python module that can also be run independently for testing.

GitHub Actions (cron: 6 AM UTC)
        |
        v
  collect.py
  - Calls USAJobs Search API
  - 9 role keywords, paginated
  - Retry logic (3 attempts per request)
  - Saves raw JSON to data/raw/
        |
        v
  transform.py
  - Reads latest raw JSON
  - Flattens nested API structure
  - Deduplicates by control_number
  - Classifies roles by title/keyword
  - Parses and annualizes salaries
  - Returns clean record list
        |
        v
  load.py
  - SQLite upsert (INSERT OR IGNORE)
  - Pipeline run logging
  - Outputs summary counts
        |
        v
  jobs.db (SQLite)
  - 23-column job_postings table
  - pipeline_log table
        |
        v
  app.py (Streamlit)
  - Deployed on Streamlit Community Cloud
  - 4 dashboard pages + data explorer
  - Sidebar filters for role, state, department

Stage Breakdown

1. Collection (collect.py)

Loops through nine search keywords against the USAJobs Search API. Each keyword request handles pagination automatically (up to 500 results per page) and retries failed requests up to three times with a delay between attempts. Raw JSON responses are saved with timestamps for traceability.

2. Transformation (transform.py)

Reads the latest raw JSON file and flattens the deeply nested API response structure into flat dictionaries matching the database schema. Records are deduplicated by control_number (the unique posting identifier). Each posting is classified into a role category based on title text matching with keyword fallback. Salary values are parsed from formatted strings, and hourly rates are annualized using the OPM standard of 2,087 work hours per year.

3. Loading (load.py)

Creates the SQLite database and tables if they do not exist. Uses INSERT OR IGNORE to handle duplicate control numbers across runs, so existing records are preserved and only new postings are added. Each pipeline run is logged with timestamps, record counts, and insert/skip tallies.

4. Dashboard (app.py)

A multi-page Streamlit application that reads directly from the SQLite database. Sidebar filters for role category, state, and department apply across all pages. Deployed on Streamlit Community Cloud with automatic reloads when the database updates.

USAJobs Search API

The pipeline uses the USAJobs Search API, a free, public-domain data source maintained by the U.S. Office of Personnel Management. The API returns currently open federal job postings in JSON format. No authentication barriers exist beyond a free API key and email registration.

Role Categories Tracked

The pipeline searches for nine role categories covering the analyst, data, and technical job families most relevant to the data analytics field:

• Data Analyst
• Data Scientist
• Data Engineer
• Business Intelligence
• Business Analyst
• Statistician
• Program Analyst
• Management Analyst
• IT Specialist

Database Schema

The job_postings table contains 23 columns:

• Primary key: control_number (unique posting identifier from USAJobs)
• Position fields: position_title, organization_name, department_name, sub_agency
• Compensation: job_grade, pay_plan, salary_min, salary_max, salary_mid, salary_interval
• Location: location_name, city, state, country, latitude, longitude
• Metadata: position_url, open_date, close_date, role_category, search_keyword, collected_date

Data Volume

The initial collection pulled 1,632 raw postings across all nine keywords. After deduplication by control_number (the same posting can match multiple search keywords), 1,131 unique job postings were loaded into the database.

The Executive Overview page provides a high-level snapshot of the federal job market across all tracked role categories. KPI cards at the top summarize total postings, average salary, number of agencies hiring, and states with active postings.

Key Insights

• IT Specialist dominates posting volume: IT Specialist postings far outnumber other role categories, reflecting the federal government's broad use of this classification for technology-related positions.
• Department of Veterans Affairs leads hiring: The VA is the top hiring department by volume, consistent with its status as the largest civilian federal employer.
• Salaries cluster around $100K-$150K: The salary distribution histogram shows a clear concentration in the $100,000 to $150,000 range, reflecting the GS-12 to GS-14 pay bands that most analyst roles fall into.
• DC, Virginia, and Maryland lead in postings: The top states by posting volume mirror the concentration of federal agencies in the Washington, D.C. metro area.

The Salary Analysis page breaks down compensation patterns by role category, state, and job grade. Summary statistics at the top show median, average, minimum, and maximum salary values across all filtered postings.

Key Insights

• Data Scientists and Data Engineers command higher salaries: These two categories sit at the top of the salary ranking, reflecting the specialized technical skills required and competitive hiring pressure from the private sector.
• DC metro area states top the salary charts: The District of Columbia, Virginia, and Maryland consistently rank highest in median salary, driven by locality pay adjustments for the Washington metro area.
• Clear salary progression by GS grade: The salary-by-grade chart shows a predictable step pattern from lower grades through GS-15, confirming the structured nature of the federal pay system.
• Wide salary ranges within role categories: Even within a single role category, salary ranges span $50,000 or more, reflecting differences in grade level, location, and agency.

The Geographic Demand page maps job posting locations across the United States and ranks states and cities by hiring volume. A location type breakdown shows the proportion of single-location, multiple-location, and negotiable postings.

Key Insights

• Heavy concentration in the DC metro area: The map shows a dense cluster of postings around Washington, D.C., which is expected given that most federal agency headquarters are located there.
• Virginia and Maryland benefit from proximity to federal agencies: Both states rank among the top hiring locations, driven by the large number of federal offices and contractors in Northern Virginia and the Maryland suburbs of DC.
• Some postings offer remote or negotiable locations: A portion of postings list "Negotiable" or "Multiple Locations" as the duty station, indicating growing flexibility in federal hiring for certain roles.
• Secondary hubs exist outside DC: States like Texas, California, and Georgia show meaningful posting volumes, reflecting regional federal installations and VA medical centers distributed across the country.

The Agency Analysis page examines hiring patterns at the organization and department level. It surfaces which agencies hire the most, how salary varies by department, and which role categories each department prioritizes.

Key Insights

• Veterans Affairs dominates hiring volume: The VA posts significantly more analyst and technical roles than any other department, driven by the scale of its healthcare, benefits, and IT operations.
• Defense-related agencies are major employers: The Department of Defense and its sub-agencies (Army, Navy, Air Force) collectively represent a large share of postings, particularly for IT Specialist and Program Analyst roles.
• Salary varies significantly by department: Average salaries differ by $20,000 or more between departments, reflecting differences in grade levels, locality pay, and the types of roles each agency hires for.
• Role category mix differs by department: The heatmap reveals that some departments hire heavily for IT Specialist roles while others focus on Management Analyst or Program Analyst positions, suggesting distinct workforce needs across agencies.

The pipeline is built from four Python modules, each handling a distinct stage. Below are key excerpts showing the core logic.

API Collection with Pagination and Retry Logic (collect.py)

The fetch_keyword function handles paginated API requests with automatic retry on failure. Each keyword can return hundreds of results spread across multiple pages.

def fetch_keyword(keyword, max_retries=3, delay=5):
    """
    Fetch all job postings for a given keyword.
    Handles pagination and retries.
    """
    all_items = []
    page = 1

    while True:
        params = {
            "Keyword": keyword,
            "ResultsPerPage": RESULTS_PER_PAGE,
            "Page": page,
        }

        # Retry logic
        for attempt in range(1, max_retries + 1):
            try:
                response = requests.get(
                    BASE_URL, headers=HEADERS, params=params, timeout=30
                )
                response.raise_for_status()
                data = response.json()
                break
            except (requests.RequestException, json.JSONDecodeError) as e:
                print(f"  Attempt {attempt}/{max_retries} failed: {e}")
                if attempt < max_retries:
                    time.sleep(delay)
                else:
                    return all_items

        items = data.get("SearchResult", {}).get("SearchResultItems", [])
        if not items:
            break

        all_items.extend(items)

        total_results = int(
            data.get("SearchResult", {}).get("SearchResultCountAll", 0)
        )
        if page * RESULTS_PER_PAGE >= total_results:
            break

        page += 1
        time.sleep(1)  # Rate limiting

    return all_items

Deduplication and Salary Parsing (transform.py)

Records are deduplicated by control_number as they are processed. Salary strings are parsed into numeric values, and hourly rates are annualized using the OPM standard of 2,087 work hours per year.

def parse_salary(salary_str):
    """Extract numeric salary from string like '$72,553.00'"""
    if not salary_str:
        return None
    cleaned = re.sub(r'[^\d.]', '', salary_str)
    try:
        value = float(cleaned)
        return value if value > 0 else None
    except (ValueError, TypeError):
        return None


def transform_raw_file(filepath=None):
    """
    Reads raw JSON, flattens all records, and deduplicates by control_number.
    """
    if filepath is None:
        filepath = get_latest_raw_file()

    with open(filepath, "r", encoding="utf-8") as f:
        raw_data = json.load(f)

    all_records = []
    seen_control_numbers = set()

    for keyword, items in raw_data.items():
        for item in items:
            record = flatten_job(item, keyword)

            # Deduplicate by control number
            cn = record["control_number"]
            if cn and cn not in seen_control_numbers:
                seen_control_numbers.add(cn)
                all_records.append(record)

    return all_records

Database Upsert and Pipeline Logging (load.py)

The load stage uses INSERT OR IGNORE for safe upserts, preventing duplicate records across daily runs. Each run is logged with counts for auditing.

def load_records(records, source="daily"):
    """
    Load clean records into the database.
    Uses INSERT OR IGNORE to skip duplicates already in the database.
    """
    conn = get_connection()
    create_tables(conn)

    placeholders = ", ".join(["?"] * len(columns))
    column_names = ", ".join(columns)
    sql = f"INSERT OR IGNORE INTO job_postings ({column_names}) VALUES ({placeholders})"

    inserted = 0
    skipped = 0

    for record in records:
        values = tuple(record.get(col) for col in columns)
        cursor = conn.execute(sql, values)
        if cursor.rowcount > 0:
            inserted += 1
        else:
            skipped += 1

    # Log the pipeline run
    conn.execute("""
        INSERT INTO pipeline_log (run_timestamp, records_collected,
                                   records_after_dedup, records_inserted,
                                   records_skipped, source)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (
        datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
        len(records) + skipped, len(records), inserted, skipped, source
    ))

    conn.commit()
    conn.close()

    return {"inserted": inserted, "skipped": skipped}

Streamlit Dashboard with Cached Data and Dynamic Filters (app.py)

The dashboard uses Streamlit's TTL-based caching to load data from SQLite, applies multi-select sidebar filters across all pages, and renders KPI metric cards with interactive Plotly visualizations.

@st.cache_data(ttl=3600)
def load_data():
    conn = sqlite3.connect(DB_PATH)
    df = pd.read_sql_query("SELECT * FROM job_postings", conn)
    conn.close()
    return df

df = load_data()

# --- Sidebar Filters ---
st.sidebar.title("🔍 Filters")

all_roles = sorted(df["role_category"].dropna().unique())
selected_roles = st.sidebar.multiselect("Role Category", all_roles, default=all_roles)

all_states = sorted(df["state"].dropna().unique())
selected_states = st.sidebar.multiselect("State", all_states)

all_depts = sorted(df["department_name"].dropna().unique())
selected_depts = st.sidebar.multiselect("Department", all_depts)

# Apply filters
filtered = df.copy()
if selected_roles:
    filtered = filtered[filtered["role_category"].isin(selected_roles)]
if selected_states:
    filtered = filtered[filtered["state"].isin(selected_states)]
if selected_depts:
    filtered = filtered[filtered["department_name"].isin(selected_depts)]

# --- Executive Overview KPI Cards ---
col1, col2, col3, col4 = st.columns(4)
with col1:
    st.metric("Total Postings", f"{len(filtered):,}")
with col2:
    avg_salary = filtered["salary_mid"].dropna().mean()
    st.metric("Avg Salary (Mid)", f"${avg_salary:,.0f}" if pd.notna(avg_salary) else "N/A")
with col3:
    unique_agencies = filtered["organization_name"].nunique()
    st.metric("Agencies Hiring", f"{unique_agencies:,}")
with col4:
    unique_states = filtered["state"].dropna().nunique()
    st.metric("States with Postings", f"{unique_states}")

Source Code

• collect.py -- API collection with pagination and retry logic
• transform.py -- JSON flattening, deduplication, role classification, salary parsing
• load.py -- SQLite upsert loading and pipeline run logging
• app.py -- Streamlit dashboard application
• Full GitHub Repository

• Live Dashboard: usajobs-analytics-pipeline.streamlit.app
• GitHub Repository: github.com/nadeaujonny/usajobs-analytics-pipeline
• Pipeline Code: collect.py | transform.py | load.py | app.py

Current Limitations

• No historical backfill: The USAJobs Historical API does not support filtering by keyword or role category, so the database starts from the pipeline's launch date. There is no way to retroactively collect postings that were open before the pipeline began running.
• Only open postings are captured: The Search API returns only currently open job postings. Once a posting closes, it is no longer returned by the API. The pipeline preserves previously collected records in the database, but any posting that opens and closes between daily runs may be missed.
• Grade format parsing issue: The "Salary by Job Grade" chart filters for grades that are purely numeric (e.g., "13", "14"). Some postings use non-standard grade formats (e.g., "13/14" for promotion ladders) that are excluded from the grade chart, reducing its coverage.

Future Enhancements

• Trend analysis over time: As the database accumulates daily snapshots, add a "Trends Over Time" dashboard page showing how posting volume, salary distributions, and role category mix change week over week and month over month.
• New posting alerts: Implement email or webhook notifications when new postings match specific criteria (e.g., "Data Scientist" roles above GS-13 in a target state), useful for active job seekers.
• PostgreSQL migration: If data volume grows significantly over time, migrate from SQLite to PostgreSQL for better concurrent access and more robust querying under load.
• Improved grade parsing: Handle promotion ladder grades (e.g., "12/13/14") by extracting the target grade or displaying the full range, increasing coverage of the salary-by-grade analysis.