An end-to-end data engineering pipeline that transforms raw job postings data into governed, analytics-ready datasets. The project ingests approximately 1.6 million job postings from a shared database, applies data modeling and integrity constraints, investigates and resolves data quality issues, implements incremental watermark-based loading, builds semantic views with defined grain, and publishes stable Parquet snapshots through an automated GitHub Actions workflow.
Live data portal: https://james-muguro.github.io/job-data-engineering/
Job market data is valuable for workforce planning, competitive intelligence, and talent strategy, but raw job posting data presents several practical challenges:
- Scale: Large volumes (1.6M+ records) require efficient storage and query patterns
- Data quality: Inconsistent formatting, parsing errors, and source-specific issues affect reliability
- Complexity: Multi-valued attributes (skills, schedules) and varied data structures complicate analysis
- Access stability: Downstream users need stable snapshots rather than querying live transformation pipelines
Without proper engineering, analysts face duplicated effort reconstructing joins, accidental row multiplication from many-to-many relationships, and uncertainty about data quality. This project addresses these challenges by building a governed analytical warehouse with validated, analytics-ready datasets published through an automated pipeline.
The project uses the shared data_jobs database created by Luke Barousse for his SQL for Data Engineering course. The source contains approximately 1.6 million job postings collected from multiple job boards.
Access method: Read-only MotherDuck share
Attribution: This project does not claim ownership of the original dataset or its collection process. The engineering work focuses on the implementation built on top of that source data.
The source data includes:
- Job posting facts with varied structures across international sources
- Multi-valued attributes (skills, schedule types) requiring many-to-many relationships
The solution follows a logical data engineering workflow:
Data is loaded incrementally from the shared MotherDuck source using a watermark-based approach. This ensures only new or changed data is processed rather than reloading the entire dataset on each run.
A normalized relational schema is designed with:
- Fact tables: Job posting facts at the appropriate grain
- Dimension tables: Repeated low-cardinality values stored separately
- Many-to-many bridge tables: Multi-valued attributes (skills, schedules) represented through bridge tables to avoid duplication
- High-cardinality free text: Remains at the appropriate grain
Integrity constraints are enforced and verified:
- Primary key constraints on fact and dimension tables
- Foreign key constraints to maintain referential integrity
- Constraint-violation testing to verify rules rather than relying on declarations alone
Data quality issues are investigated at their root cause. For example, a salary category field containing raw salary amounts was traced to parsing behavior affecting five international job board sources. The focus was on identifying why the issue existed, correcting the affected data, and documenting the finding.
Data is transformed into semantic views with defined grain. Each view is purpose-built for specific analytical use cases so users do not need to reconstruct complex joins or risk accidental row multiplication.
Stable data snapshots are published as Parquet files through an automated GitHub Actions workflow. Parquet was selected because the data is large and intended for analytical workloads.
┌──────────────────────────┐
│ data_jobs (source) │
│ MotherDuck │
│ │
│ Shared, read-only source │
└────────────┬─────────────┘
│
│ Incremental load
│ Watermark-based
▼
┌──────────────────────────┐
│ data_eng_project │
│ MotherDuck │
│ │
│ Fact + dimension tables │
│ M:M bridge tables │
│ Integrity constraints │
│ Semantic-layer views │
│ DQ and ETL logs │
└────────────┬─────────────┘
│
│ Automated publishing
│ GitHub Actions
▼
┌──────────────────────────┐
│ GitHub Release assets │
│ │
│ Published Parquet files │
└────────────┬─────────────┘
│
│ Updated manifest
▼
┌──────────────────────────┐
│ GitHub Pages │
│ │
│ Static data portal │
│ index.html │
│ manifest.json │
└────────────┬─────────────┘
│
▼
Analysts and data scientists
The architecture separates data processing from data consumption:
- The warehouse handles loading, transformation, validation, and modeling
- Consumers access published snapshots rather than querying the live warehouse directly
- This keeps downstream access stable and prevents users from reading data during active loads or transformations
The load process uses a watermark-based approach to process new data incrementally. The pipeline is designed to be:
- Idempotent: Safe to run multiple times without duplicating data
- Repeatable: Produces consistent results across runs
- Logged: Each execution records its details
- Testable: Execution details and data quality results are tracked
Each run records its execution details and data quality results in ETL load logs.
The production publishing workflow runs on GitHub Actions:
MotherDuck
↓
Export semantic views
↓
Write Parquet files
↓
Upload to GitHub Release
↓
Update manifest.json
↓
Commit manifest
↓
GitHub Pages
The workflow:
- Connects to MotherDuck
- Exports the published semantic views
- Writes the datasets as Parquet files
- Uploads them to the
data-latestGitHub Release - Updates
data/manifest.json - Commits the new manifest to the repository
This approach avoids storing large binary files in Git history while keeping the published datasets accessible through stable release asset URLs.
The governed warehouse enforces:
- Primary key constraints: Ensure uniqueness on fact and dimension tables
- Foreign key constraints: Maintain referential integrity between related tables
- Constraint-violation testing: Deliberately tests constraints to verify they work as intended
Data quality issues are investigated at their root cause rather than applying surface-level fixes.
The pipeline maintains:
- ETL load logs: Track each execution's details
- Data quality logs: Record validation results and issues found
The project exposes purpose-built views for different analytical use cases. Each published dataset has a defined grain so users do not need to reconstruct complex joins or risk accidental row multiplication.
| Dataset | Grain | Rows |
|---|---|---|
job_postings_readable |
1 row per job posting | 1,615,930 |
job_postings_skills_flat |
1 row per job posting and skill pair | 7,193,426 |
job_postings_schedule_flat |
1 row per job posting and schedule type pair | 1,651,922 |
The datasets are published as Parquet files through the data portal. Parquet was selected because the data is large and intended for analytical workloads.
The project produces:
- A governed analytical warehouse (
data_eng_project) with fact and dimension tables, many-to-many bridge tables, and integrity constraints - Three semantic views with defined grain for analytical use
- Automated publishing workflow via GitHub Actions
- Published Parquet files stored as GitHub Release assets
- A static data portal hosted on GitHub Pages with a manifest for dataset discovery
- Warehouse schema: Normalized relational model with primary and foreign key constraints
- Semantic views: Purpose-built views preventing accidental row multiplication
- Published datasets: Three Parquet files totaling over 10 million rows across different grains
- Automation: GitHub Actions workflow for repeatable, logged publishing
- Documentation: Full technical architecture and engineering decisions in
docs/ARCHITECTURE.md
| Layer | Technologies |
|---|---|
| Database | DuckDB, MotherDuck |
| Programming | Python, SQL |
| Data Format | Parquet |
| Automation | GitHub Actions |
| Distribution | GitHub Releases, GitHub Pages |
| Frontend | HTML, CSS, JavaScript |
.
├── .github/
│ └── workflows/
│ └── publish.yml
│
├── scripts/
│ └── diagnose.py
│
├── docs/
│ └── ARCHITECTURE.md
│
├── data/
│ └── manifest.json
│
├── index.html
├── export.py
├── requirements.txt
└── .env
| Path | Purpose |
|---|---|
.github/workflows/publish.yml |
Automated publishing workflow |
scripts/diagnose.py |
MotherDuck connection troubleshooting |
docs/ARCHITECTURE.md |
Full technical architecture and engineering decisions |
data/manifest.json |
Metadata consumed by the data portal |
index.html |
Static data portal |
export.py |
Local publishing and testing tool |
.env.example |
Environment variable template |
Note: Only manifest.json is tracked in Git. Published Parquet files are stored as GitHub Release assets.
The automated GitHub Actions workflow is the primary publishing method.
For local testing and development:
# Create virtual environment
python -m venv .venv
source .venv/bin/activate # On Windows: .venv\Scripts\activate
# Install dependencies
pip install -r requirements.txt
# Configure environment
cp .env.example .envAdd your MotherDuck token to .env, then run:
python export.pyThe script generates the Parquet files and updates the dataset manifest.
After running the export script or the automated workflow:
- Parquet files are written to the local directory (for local testing)
data/manifest.jsonis updated with the latest dataset metadata- For automated publishing, files are uploaded to the
data-latestGitHub Release and the manifest is committed to the repository
- Source data access: The project depends on a shared MotherDuck database. Access requires appropriate credentials and the shared database to remain available.
- Local testing: Local publishing requires manual environment configuration and does not replicate the full automated workflow.
- Data freshness: The published snapshots reflect the state of the source data at the time of publishing. Continuous updates depend on manual or scheduled workflow runs.
Where the project could be improved:
- Scheduled publishing: Add a cron-based schedule to the GitHub Actions workflow for automatic periodic updates
- Data quality dashboards: Expose ETL and data quality logs through the data portal for transparency
- Schema evolution handling: Add logic to detect and handle schema changes in the source data
- Testing framework: Add automated tests for the export script and publishing workflow
- Documentation expansion: Add data dictionary documentation for each published dataset
Data Source: The source dataset used in this project is the shared data_jobs database created by Luke Barousse for his SQL for Data Engineering course.
This project accesses the source through a read-only MotherDuck share. It does not claim ownership of the original dataset or its collection process.
The work in this repository focuses on the data engineering implementation built on top of that source data, including warehouse design, data modeling, integrity constraints, data quality investigation, incremental loading, semantic views, and data publishing.
MIT. See LICENSE.