ETL Pipeline for Job Posting Skill Mentions

Automated and fully reproducible ETL pipeline for extracting and standardizing job posting skill mentions, loaded and normalized in a SQL Server database.

Explore architecture

Diagram of ETL Pipeline for Job Posting Skill Mentions

Project Overview

Core Features

Key Technologies

Pipeline Metrics

Figure 1. ETL pipeline for job posting skill mentions including input documents, Python extraction, skill standardization workbook, and SQL Server staging and core schemas.

Run demo pipeline (GitHub)

(Instructions in README)

Documentation Overview

The pipeline documentation is organized into the following sections:

Real & Synthetic Input Datasets
Skill Standardization Pipeline
Python Data Extraction
SQL Server Database Design
Data Loading & Transformation
Validation Framework
Pipeline Automation & Reproducibility
End-to-End Example Walkthrough

1. Real & Synthetic Input Datasets

The ETL pipeline takes a collection of annotated job postings as input and extracts the metadata and highlighted skill mentions from each posting. The original dataset comprises 36 job postings that are kept private in order to comply with copyright and Terms of Service regulations.

Instead, a functionally identical dataset of 36 synthetic job postings was carefully engineered, producing the same pipeline output as the real dataset. The synthetic dataset ensures that the end-to-end ETL pipeline is publicly shareable and fully reproducible, without exposing copyrighted material.

Example of diversely phrased skill mentions mapping to a single preliminary
skill

Figure 2. Example of a real versus synthetic job posting producing the same extracted skill mentions, demonstrating pipeline consistency and copyright compliance.

Highlighted skill mentions

Skill mentions are identified and highlighted manually within each job posting, using turquoise to highlight hard skills and pink to highlight soft skills.

A Python script parses and extracts the highlighted skill mentions, independent of the surrounding sentence context. Consequently, a real and a synthetic job posting can produce an identical set of extracted skill mentions despite having different surrounding text.

In the example below, the pipeline extracts the record JP_25, analysing and interpreting data, hard from both the real and synthetic job postings, while the synthetic version avoids redistributing copyrighted material.

• JP_25 (real): You will specialise in analysing and interpreting data, providing actionable insights to inform strategic decisions for both our clients & internal operations.
• JP_25 (synthetic): Assist in analysing and interpreting data using SQL and Power BI to leverage data to drive insights that inform strategic decisions.

Synthetic dataset engineering

For every real job posting (36 in total), a synthetic version was carefully constructed. A fictional company name and a new location is assigned to every real company in the original dataset. This enables company-level frequency analysis without exposing real organizations.

The Python extraction script would first process the real job posting, and the list of extracted skill mentions would then be used to create a synthetic job posting using the following Chat GPT prompt template:

Generate a completely fictional Data Analyst job posting.

- The company, business context, and wording must be entirely fictional.
- Do not imitate or paraphrase any existing job posting.
- Use every supplied skill phrase exactly once, preserving the wording of each phrase.
- Do not introduce additional identifiable hard or soft skills beyond those supplied.
- Make the posting read naturally.
- The primary objective is to produce a realistic job posting. Preserve every supplied skill phrase exactly once, but prioritize natural, recruiter-like wording when connecting the supplied phrases. Ordinary connecting language, fictional business context, and general job posting phrases may be added freely.

// List of skills, e.g.:
SQL
Python
stakeholder management
analytical mindset
multiple tasks simultaneously
data models
...

Verification of dataset equivalence

The equivalence between the real and synthetic datasets is verified using an Excel workbook that imports the extracted CSV files from both datasets and checks that every skill mention extracted from the synthetic dataset exists in the real dataset, and vice versa.

Figure 3. Excel workbook verifying that the real and synthetic job posting datasets produce identical ETL pipeline outputs.

2. Skill Standardization Pipeline

Skill requirements across job postings are expressed in highly variable surface forms. Differences in formatting (e.g., “Power BI” vs “PowerBI”) or phrasing (e.g., “multi-tasking” vs “handling multiple tasks concurrently”) cause misleading frequency counts and unreliable aggregations.

As a result, extracted skill mentions cannot be used directly for structured skill demand analysis without a prior standardization process.

To address this issue, I designed a taxonomy-driven standardization system in Excel for downstream analytical use that transforms raw skill mentions into standardized skills enriched with hierarchical skill classification.

The full documentation is available in the dedicated Skill Standardization Pipeline project page.

Skill standardization pipeline architecture

Figure 4. Skill standardization pipeline with input/output schemas, mapping stages, and skill taxonomy.

Workflow overview

The skill standardization pipeline transforms raw skill mentions into standardized skills through a two-stage mapping workflow.

For each raw extracted_skill, the user first enters a preliminary standardization. Then, for each unique preliminary_skill, the user selects a standardized_skill from a controlled skill taxonomy. The taxonomy can be expanded as needed with additional standardized skills classified by their skill type (hard or soft), category, and subcategory.

The final standardized skill is fetched automatically via the preliminary mapping layer, which helps prevent inconsistent mappings and enables systematic updates.

Example of a single preliminary mapping being assigned to diverse job posting
extractions

Figure 5. Example of diverse raw skill mentions mapping to a smaller number of preliminary skills that converge into a single standardized skill.

Standardization output

The skill standardization pipeline produces a standardized analytical dataset that transforms variable skill mentions into validated, taxonomy-classified records, suitable for downstream analysis and visualization.

Each standardized record is defined by the following fields:

• job_id: Unique job posting identifier
• extracted_skill: Exact skill phrase extracted from the original job posting
• standardized_skill: Standardized skill phrase
• skill_type: Standardized skill type (hard or soft)
• skill_category: Main skill category
• skill_subcategory: Skill subcategory

Additionally, each record contains validation metadata used for excluding invalid records from analysis:

valid_mapping = TRUE
valid_standardized_skill = TRUE 
valid_classification = TRUE

The following interactive dashboard was built directly in Excel using the standardized dataset, enabling multidimensional exploration of skill demand across the analyzed job postings.

Figure 6. Dashboard overview.

3. Python Data Extraction

Two Python scripts automate extraction from the annotated Word job posting documents and the Excel skill standardization workbook.

The two extraction scripts together produce the structured data required for downstream analysis:

•
Job posting metadata identifying each job posting (e.g. company, location).
•
Raw skill mentions that occur in specific job postings (e.g. “PowerBI” in JP_27).
•
Standardized skills mapping each raw skill mention to its standardized form (e.g. PowerBI → Power BI).
•
Skill classifications for each standardized skill (e.g. Power BI → hard skill → Tool / Technology → Analytics / BI tools).

The resulting CSV files form a structured, reproducible dataset used as input for the SQL Server database and serve as the basis for subsequent skill-demand analysis.

Figure 9. Python extraction layer within the broader ETL pipeline, showing the source documents, extraction scripts, and resulting CSV files.

Job posting extraction

This Python script loops over a directory of annotated job posting (.docx) files and for each job posting:

extracts job posting metadata from the document header;
extracts highlighted skill phrases (hard and soft) from the job posting body;

then writes the results to two separate CSV files.

Figure 10. CSV files generated by the Python job posting extraction script: job posting metadata (top) and skill mentions (bottom).

Skill mentions are highlighted manually during the annotation process and are then extracted programmatically from each job posting. Highlight color determines the observed_skill_type, using turquoise for hard skills and pink for soft skills. Job postings often list the same skill twice but the script ignores duplicates since it tracks whether a skill appears in a job post, rather than the number of times it is listed in a single document.

A predefined metadata schema is used for job posting extraction and validation. Every job posting must have the following mandatory fields: job_id, company, and job_title. Files missing any required job posting metadata are rejected and logged in the console output.

Queue status indicator updating as an already mapped raw skill gets modified

Figure 10. Batch job posting extraction running in real time, showing progress logging, the extraction run summary, and rejected-file reporting.

Skill standardization extraction

This Python script extracts three named tables from the Excel skill standardization workbook and writes the results to three separate CSV files:

extracted skill mentions mapped to standardized skills;
standardized skills with a classification hierarchy of skill type, category, and subcategory;
approved skill type, category, and subcategory combinations.

Figure 11. Extracted skill standardization tables, showing the relationships between skill mappings, the taxonomy, and approved classification combinations.

The tables contain row-level validation metadata: valid_mapping, valid_standardized_skill, and valid_classification. These fields are included in the CSV exports and later used in SQL Server to filter out invalid records from analyses.