# data_eng_project — Data Engineering Architecture

**Source system:** `data_jobs` (shared, read-only, hosted via MotherDuck; originally synced from BigQuery by its owner; updated periodically/unpredictably)  
**Target system:** `data_eng_project` (this project's own MotherDuck database — full read/write ownership)  
**Engine:** DuckDB (via MotherDuck)  
**Audience:** Data analysts and data scientists consuming the views; future engineers maintaining the pipeline

---

## 1. System Overview

This project rebuilds a shared, read-only job postings dataset into a governed, constraint-enforced, normalized analytical warehouse. The source `data_jobs` database has no formal integrity constraints and contains several data quality issues baked into raw columns. The target `data_eng_project` database is a fully-owned, cleaned, and normalized reconstruction designed so analysts and data scientists can query clean, resolved, correctly-grained data without repeating cleaning or joining logic.

### Ownership Boundaries

| System | Ownership | Access | Purpose |
|--------|-----------|--------|---------|
| `data_jobs` | External (Luke Barousse) | Read-only | Source of truth for raw job postings |
| `data_eng_project` | This project | Full read/write | Governed analytical warehouse for downstream consumption |

### Architectural Purpose

The target system exists to:

- Enforce referential integrity through primary and foreign key constraints
- Normalize bounded categorical attributes into dimension tables
- Resolve many-to-many relationships into bridge tables
- Preserve raw source values for traceability alongside cleaned representations
- Expose semantic views at explicit, documented grains for analysts
- Support incremental, idempotent loading instead of full rebuilds
- Provide stable, analytics-ready datasets through automated publishing

---

## 2. Architectural Principles

The following principles are derived from actual design decisions in this system:

| Principle | Evidence in Design |
|-----------|-------------------|
| **Preserve raw source values for provenance** | `_raw` columns retained alongside cleaned/normalized representations (`job_via_raw`, `job_schedule_type_raw`, `salary_rate_raw`) |
| **Normalize bounded categorical attributes** | `country_dim`, `job_title_dim`, `search_location_dim`, `job_via_dim`, `schedule_type_dim` |
| **Resolve many-to-many relationships into bridge tables** | `job_schedule_type_bridge`, `skills_job_dim` |
| **Enforce referential integrity** | PK/FK constraints declared and verified through intentional violation tests |
| **Keep analyst-facing views at explicit grains** | Three semantic views with documented grain: `job_postings_readable`, `job_postings_skills_flat`, `job_postings_schedule_flat` |
| **Prefer evidence-based performance decisions** | Secondary index tested via `EXPLAIN ANALYZE`, found unused, removed; standing rule against speculative indexing |
| **Use incremental loading instead of full rebuilds** | Watermark-based ETL using `job_id`, verified gapless and monotonic |
| **Validate assumptions before relying on them** | Gaplessness verified (`MAX - MIN + 1 = COUNT`), monotonicity verified against `job_posted_date`, FK/PK enforcement tested with violations |
| **Keep source and target ownership separate** | All writes go to `data_eng_project`; `data_jobs` remains read-only |

---

## 3. System Architecture

### Logical Flow

```text
┌──────────────────────────┐
│    data_jobs (source)    │
│    MotherDuck (read-only)│
│    ~1.6M job postings    │
└────────────┬─────────────┘
             │
             │ Incremental ETL
             │ Watermark: job_id
             │ Dimension-first loading
             ▼
┌──────────────────────────┐
│   data_eng_project       │
│   MotherDuck (read/write)│
│                          │
│   Fact + dimension tables│
│   Bridge tables          │
│   PK/FK constraints      │
│   Semantic views         │
│   ETL/DQ logs            │
└────────────┬─────────────┘
             │
             │ Export to Parquet
             │ GitHub Release assets
             ▼
┌──────────────────────────┐
│   GitHub Releases        │
│   (data-latest tag)      │
│   Parquet files          │
│   manifest.json          │
└────────────┬─────────────┘
             │
             │ Stable download URLs
             ▼
┌──────────────────────────┐
│   GitHub Pages           │
│   Static data portal     │
│   index.html             │
│   manifest.json          │
└────────────┬─────────────┘
             │
             ▼
    Analysts / Data Scientists
```

### Data Ownership and Write Boundaries

- **Source (`data_jobs`):** Read-only. Cannot execute `CREATE`, `ALTER`, or `INSERT`. All queries must reference this catalog explicitly or attach it read-only.
- **Target (`data_eng_project`):** Full read/write. All DDL and DML operations occur here. Foreign keys reference tables within this catalog only.
- **Distribution:** Data files (Parquet) are published as GitHub Release assets, not committed to Git. Only `manifest.json` (metadata) is git-tracked.

---

## 4. Data Model

### Central Fact Table

**`job_postings_fact`** is the central fact table with grain: **one row per job posting** (1,615,930 rows).

All dimension tables have a 1-to-many relationship into the fact table. Two bridge tables handle many-to-many relationships:

- `job_schedule_type_bridge`: A posting can have multiple schedule types
- `skills_job_dim`: A posting can have multiple skills

### Entity Relationship Diagram

```mermaid
erDiagram
    company_dim ||--o{ job_postings_fact : "company_id"
    country_dim ||--o{ job_postings_fact : "country_id"
    job_title_dim ||--o{ job_postings_fact : "job_title_short_id"
    search_location_dim ||--o{ job_postings_fact : "search_location_id"
    job_via_dim ||--o{ job_postings_fact : "job_via_id"
    
    job_postings_fact ||--o{ job_schedule_type_bridge : "job_id"
    schedule_type_dim ||--o{ job_schedule_type_bridge : "schedule_type_id"
    
    job_postings_fact ||--o{ skills_job_dim : "job_id"
    skills_dim ||--o{ skills_job_dim : "skill_id"
```

### Identifier Strategy

The project uses three types of identifiers:

| ID Type | Tables | Source | Portability |
|---------|--------|--------|-------------|
| **Source-provided IDs** | `company_dim.company_id`, `skills_dim.skill_id`, `job_postings_fact.job_id` | Reused directly from source system | Portable only if source system IDs remain stable |
| **Surrogate IDs** | `country_dim.country_id`, `job_title_dim.job_title_short_id`, `search_location_dim.search_location_id`, `job_via_dim.job_via_id`, `schedule_type_dim.schedule_type_id` | Generated internally via `ROW_NUMBER()` or manual assignment | **Meaningful only within `data_eng_project`** — not portable to other systems |
| **Composite Keys** | `job_schedule_type_bridge (job_id, schedule_type_id)`, `skills_job_dim (job_id, skill_id)` | Combination of fact and dimension keys | Valid only within this schema |

**Important:** Internally generated surrogate IDs are meaningful only inside `data_eng_project`. Do not treat them as portable source-system identifiers.

---

## 5. Table Reference

### 5.1 Fact Tables

#### `job_postings_fact`

**Grain:** One row per job posting  
**Row count:** 1,615,930  
**Primary key:** `job_id` (INTEGER)  
**Foreign keys:** `company_id`, `job_title_short_id`, `search_location_id`, `job_via_id`, `country_id`

| Column | Type | Notes |
|--------|------|-------|
| `job_id` | INTEGER, PK | Source-provided, gapless, monotonically increasing. Used as incremental load watermark. |
| `company_id` | INTEGER, FK → `company_dim` | |
| `job_title_short_id` | INTEGER, FK → `job_title_dim` | Normalized from `job_title_short` VARCHAR (10 distinct values) |
| `job_title` | VARCHAR | Free text, NOT normalized (457,950 distinct values — genuine free text) |
| `job_location` | VARCHAR | Free text, NOT normalized (28,181 distinct values — city-level, not bounded) |
| `job_via_raw` | VARCHAR | Original scraped source string, preserved untouched (e.g., `"via LinkedIn"`, `"LinkedIn"`) |
| `job_via_id` | INTEGER, FK → `job_via_dim` | Resolved from `job_via_raw` after stripping `"via "` prefix only |
| `job_schedule_type_raw` | VARCHAR | Original compound string, preserved untouched (e.g., `"Full-time, Contractor, and Internship"`) |
| `job_work_from_home` | BOOLEAN | |
| `search_location_id` | INTEGER, FK → `search_location_dim` | Normalized (176 distinct values) |
| `job_posted_date` | TIMESTAMP | Confirmed monotonically non-decreasing with `job_id` |
| `job_no_degree_mention` | BOOLEAN | |
| `job_health_insurance` | BOOLEAN | |
| `country_id` | INTEGER, FK → `country_dim` | Normalized from `job_country` VARCHAR (160 distinct values) |
| `salary_rate_raw` | VARCHAR | Original value, preserved untouched — includes 916 known-invalid entries |
| `salary_year_avg` | DOUBLE | Unchanged from source |
| `salary_hour_avg` | DOUBLE | Unchanged from source |
| `salary_rate_clean` | ENUM(`year`,`hour`,`month`,`week`,`day`) | Cleaned version — NULL if raw value wasn't one of the 5 valid categories |

### 5.2 Dimension Tables

| Table | Grain | Row count | Key | Notes |
|-------|-------|-----------|-----|-------|
| `company_dim` | 1 row per company | 215,940 | `company_id` (source-provided) | Reuses source ID directly |
| `country_dim` | 1 row per country | 160 | `country_id` (surrogate) | Generated via `ROW_NUMBER()` |
| `job_title_dim` | 1 row per short title category | 10 | `job_title_short_id` (surrogate) | Bounded category (10 values) |
| `search_location_dim` | 1 row per search region | 176 | `search_location_id` (surrogate) | Normalized from search location |
| `job_via_dim` | 1 row per cleaned platform name | 14,054 | `job_via_id` (surrogate) | Reduced from 16,452 distinct values |
| `schedule_type_dim` | 1 row per schedule category | 7 | `schedule_type_id` (manually assigned 1–7) | Canonical set of 7 values |
| `skills_dim` | 1 row per skill | 262 | `skill_id` (source-provided) | Reuses source ID directly |

### 5.3 Bridge (Junction) Tables

| Table | Grain | Row count | Keys | Purpose |
|-------|-------|-----------|------|---------|
| `job_schedule_type_bridge` | 1 row per (job, schedule_type) | 1,651,922 | Composite PK `(job_id, schedule_type_id)`, FK → both parents | Resolves many-to-many schedule types |
| `skills_job_dim` | 1 row per (job, skill) | 7,193,426 | Composite PK `(job_id, skill_id)`, FK → both parents | Resolves many-to-many skills |

### 5.4 Semantic Views

| View | Grain | Row count | Purpose |
|------|-------|-----------|---------|
| `job_postings_readable` | 1 row per posting | 1,615,930 | Browse/lookup — all dimensions resolved to readable labels, `skills`/`schedule_types` as arrays via `LIST()` |
| `job_postings_skills_flat` | 1 row per (job, skill) | 7,193,426 | Correct grain for skill-level aggregation — no unnesting needed |
| `job_postings_schedule_flat` | 1 row per (job, schedule_type) | 1,651,922 | Correct grain for schedule-type-level aggregation — no unnesting needed |

**Design note:** All views exclude `_raw` provenance columns and internal ID columns — analysts see only resolved, human-readable fields.

### 5.5 Control / ETL Tables

| Table | Purpose |
|-------|---------|
| `etl_load_log` | One row per incremental load run: watermark range, rows loaded, status |
| `_etl_watermark` | Scratch table, overwritten each load run — holds the frozen `last_loaded_job_id` for that run only |

---

## 6. Data Quality Architecture

### 6.1 `salary_rate` → `salary_rate_raw` / `salary_rate_clean`

**Decision:** Preserve raw value, add cleaned ENUM column with NULL for invalid entries.

| Aspect | Details |
|--------|---------|
| **Problem** | 916 rows (out of 82,230 non-null) contained salary *amounts* (`"21K"`, `"1.2M"`, `"a"`) instead of rate categories |
| **Evidence** | 916 rows with invalid values; all have NULL `salary_year_avg` and `salary_hour_avg` |
| **Root cause** | Concentrated in non-US/non-English job board sources: Indeed regional variants (Malaysia, Singapore, Nigeria, South Africa, Pakistan, UAE), وظائف (Arabic job board), AI-Jobs.net, MyCareersFuture. Spread across 159 distinct days over ~6 months — systematic parsing issue tied to how those sources present salary |
| **Transformation** | Left `salary_rate_raw` untouched. Added `salary_rate_clean` as DuckDB ENUM for 5 valid categories (`year`, `hour`, `month`, `week`, `day`), NULL for invalid rows |
| **Validation** | ENUM constraint enforces valid categories; NULL indicates unparseable |
| **Remaining limitation** | 916 rows' actual salary values are NOT recoverable — currency and period convention unknown without further research per job board |

### 6.2 `job_schedule_type` → `job_schedule_type_raw` + Bridge + Dimension

**Decision:** Parse compound values into many-to-many bridge table with canonical dimension.

| Aspect | Details |
|--------|---------|
| **Problem** | Not atomic — 57,897 rows (3.6%) contained compound values like `"Full-time, Contractor, and Internship"` (1NF violation). Also 2 non-English variants: `"Kontraktor"` (3 rows), `"Pekerjaan tetap"` (59 rows) |
| **Evidence** | 57,897 compound rows; 62 locale variant rows |
| **Root cause** | Multi-valued attribute stored in single column; locale-specific terminology |
| **Transformation** | Parsed and split into bridge table. Canonical set reduced to 7 values: Contractor, Full-time, Internship, Part-time, Per diem, Temp work, Volunteer. Locale variants folded into English equivalents |
| **Validation** | Lossless: `COUNT(DISTINCT job_id)` with non-null `job_schedule_type_raw` = `COUNT(DISTINCT job_id)` in bridge table, exactly |
| **Remaining limitation** | None — transformation is complete and validated |

### 6.3 `job_via` → `job_via_raw` + `job_via_id`

**Decision:** Strip only `"via "` prefix; defer regional-suffix and long-tail normalization.

| Aspect | Details |
|--------|---------|
| **Problem** | 16,452 distinct values, many inconsistently prefixed (`"via LinkedIn"` vs `"LinkedIn"`) and/or region-suffixed (`"via BeBee Singapore"`, `"BeBee GB"`) |
| **Evidence** | 16,452 distinct raw values |
| **Root cause** | Inconsistent scraping/presentation across sources |
| **Transformation** | Stripped `"via "` prefix only — safe, mechanical, unambiguous. Region-suffix variants and long tail (~16,000 mostly-singleton values) left as-is |
| **Validation** | Cardinality reduced from 16,452 to 14,054 distinct values |
| **Remaining limitation** | Regional and long-tail variants not normalized — deliberate scope boundary to avoid over-merging distinct sources without clear rules |

### 6.4 `job_country` → `country_dim`

**Decision:** Direct normalization — no cleaning required.

| Aspect | Details |
|--------|---------|
| **Problem** | None identified |
| **Evidence** | 166 distinct via `approx_count_distinct`, 160 exact via `COUNT(DISTINCT)` — divergence due to approximation, not data issue |
| **Root cause** | N/A |
| **Transformation** | Moved into dimension table with surrogate ID |
| **Validation** | Atomic, consistent values confirmed on inspection |
| **Remaining limitation** | None |

### 6.5 `job_title_short` → `job_title_dim`

**Decision:** Direct normalization — bounded categorical attribute.

| Aspect | Details |
|--------|---------|
| **Problem** | None — genuine bounded category |
| **Evidence** | 10 distinct values, each mapping to tens of thousands of distinct free-text `job_title` values |
| **Root cause** | N/A |
| **Transformation** | Moved into dimension table with surrogate ID |
| **Validation** | Confirmed as bounded category |
| **Remaining limitation** | None |

---

## 7. Provenance and Raw Data Preservation

The system retains `_raw` columns alongside cleaned or normalized representations:

- `job_via_raw` preserved alongside `job_via_id`
- `job_schedule_type_raw` preserved alongside bridge table
- `salary_rate_raw` preserved alongside `salary_rate_clean`

**Purpose:**

- **Traceability:** Original source values remain accessible for audit or re-investigation
- **Auditability:** Cleaning decisions can be validated against raw data
- **Reproducibility:** Transformations can be re-run or adjusted without losing original values
- **Protection against irreversible decisions:** Raw columns allow future engineers to revisit transformations if requirements change

This pattern ensures no source information is permanently lost during cleaning.

---

## 8. Normalization Strategy

### Normalized (Bounded Categorical Attributes)

| Field | Dimension | Reason |
|-------|-----------|--------|
| `job_country` | `country_dim` | 160 distinct values — bounded set |
| `job_title_short` | `job_title_dim` | 10 distinct values — genuine category |
| `search_location` | `search_location_dim` | 176 distinct values — bounded set |
| `job_via` (cleaned) | `job_via_dim` | 14,054 distinct values after prefix stripping |
| `job_schedule_type` | `schedule_type_dim` + bridge | 7 canonical values, multi-valued per posting |

### Not Normalized (Free Text)

| Field | Reason |
|-------|--------|
| `job_title` | 457,950 distinct values — genuine free text, not a category |
| `job_location` | 28,181 distinct values — city-level, not a bounded category |

### Multi-Valued Attributes (Bridge Tables)

| Field | Bridge Table | Dimension |
|-------|--------------|-----------|
| `job_schedule_type` | `job_schedule_type_bridge` | `schedule_type_dim` |
| Skills | `skills_job_dim` | `skills_dim` |

---

## 9. Integrity Enforcement

All primary key and foreign key constraints were deliberately tested with intentional violations before being trusted.

### Constraint Tests

| Test | Expected Result | Actual Result | Conclusion |
|------|-----------------|---------------|------------|
| FK violation (`company_id = -1`, non-existent) | Rejected | Rejected by DuckDB | FK enforcement is real, not cosmetic |
| PK violation (duplicate `skill_id = 0`) | Rejected | Rejected by DuckDB | PK enforcement is real |

**Conclusion:** MotherDuck/DuckDB's PK and FK constraints ARE actively enforced in this environment. Safe to rely on the database as a data quality gate, not just documentation of intended structure.

---

## 10. Performance and Indexing Strategy

### Evidence-Based Decision

**Decision:** Do not create secondary indexes speculatively. Rely on PK/FK-backed indexes and native zonemap pruning.

| Aspect | Details |
|--------|---------|
| **Reason** | DuckDB is a columnar OLAP engine, fundamentally different from row-store databases |
| **Evidence** | Created `idx_jpf_company_id` on `job_postings_fact.company_id`, ran `EXPLAIN ANALYZE` before and after. DuckDB optimizer never used the index — chose `Sequential Scan` both times, near-identical timing. Index was dropped. |
| **Trade-off** | No secondary indexes means some highly selective queries may not benefit from index acceleration |
| **Future consideration** | Only add an index if a *specific* query is measured via `EXPLAIN ANALYZE` to be slow AND highly selective — decide per-query, with evidence |

### DuckDB Architecture Relevant to Indexing

- **Zonemaps (automatic, free):** Min/max per row-group, enables row-group skipping on filters, no index needed
- **ART indexes:** Automatically created behind PK/UNIQUE constraints; used for point lookups
- **Explicit `CREATE INDEX`:** Tested and found unused in this workload

**Standing rule:** Do not create secondary indexes speculatively in this schema.

---

## 11. DuckDB / MotherDuck Operational Constraints

The following behaviors have been confirmed in this environment and must be accounted for in schema changes and ETL design.

| Constraint | Observed Behavior | Workaround / Pattern |
|------------|-------------------|---------------------|
| **Cannot `ALTER TABLE` on FK-referenced tables** | Any structural change (including `ADD COLUMN`) to a table another table's FK points to fails | Drop dependent(s) → rebuild target → swap → recreate dependent(s) (see §13) |
| **Read-only attached databases** | `CREATE`/`ALTER`/`INSERT` on a database attached read-only fails with `Cannot execute statement ... attached in read-only mode` | All writes go to `data_eng_project`; `data_jobs` is read-only |
| **FK constraints cannot reference across catalogs** | Even with correct three-part qualification on both table and FK target | Use `USE <database>;` then create table and FK targets **unqualified**, all within same batch/execution |
| **Two-part names parsed as `catalog.schema`** | `database.table` is parsed as `catalog.schema`, not `database.table` | Use three-part name: `catalog.schema.table` (e.g., `data_eng_project.main.job_postings_fact`) |
| **`information_schema` not cross-catalog** | Not addressable across catalogs as expected | Use `duckdb_columns()`, `duckdb_indexes()`, `duckdb_types()`, `duckdb_databases()` — DuckDB's own metadata functions with `database_name` filter |
| **Session default catalog (`USE`) does not persist** | `USE` does not reliably persist across separate query executions | (a) Run related DDL/DML as one batch, (b) Fully qualify table names when in doubt |

---

## 12. Schema Change and Rebuild Procedure

When a locked/FK-referenced table needs structural change, use this reusable pattern:

### Procedure

1. `DROP TABLE` every table with an FK pointing at the target
2. Build any new dimension tables the change requires
3. `CREATE TABLE <target>_new (...)` with the full new structure
4. `INSERT INTO <target>_new SELECT ... FROM <target>` (or from source, mapping old columns to new)
5. Validate row counts match, and validate any new FK joins are lossless (check for unexpected NULLs on the new FK column, cross-referenced against whether the source value was legitimately NULL)
6. `DROP TABLE <target>; ALTER TABLE <target>_new RENAME TO <target>;`
7. Recreate every dependent table dropped in step 1, pointing at the rebuilt target
8. Re-validate final row counts across all affected tables

### Why This Is Required

DuckDB cannot `ALTER TABLE` (including `ADD COLUMN`) on a table that has FK-referencing dependents. Any structural change to a table another table's FK points to requires dropping dependents, rebuilding the target, and recreating dependents.

---

## 13. Incremental ETL Architecture

### Watermark Strategy

**Decision:** Use `job_id` as the incremental load watermark.

| Aspect | Details |
|--------|---------|
| **Reason** | `job_id` is source-provided, gapless, and monotonically non-decreasing with `job_posted_date` |
| **Evidence** | Gaplessness verified: `MAX - MIN + 1 = COUNT` (zero missing IDs in full range). Monotonicity verified: zero out-of-order rows against `job_posted_date` |
| **Trade-off** | Assumes pure inserts (new `job_id`s only). Does not detect updates/deletes to existing rows |
| **Future consideration** | Would require full-row hash comparison if source ever corrects or removes existing postings |

### Load Order (Dependency-Respecting)

1. **Capture watermark:** `MAX(job_id)` currently in `job_postings_fact` into scratch table `_etl_watermark`, once, before any writes — prevents watermark from shifting mid-script
2. **Insert new dimension values:** Companies, skills, countries, titles, search locations, via-sources — each checks for existence before inserting, so new categories referenced by new postings get rows before the fact table needs them
3. **Insert new fact rows:** Re-run exact same transformation logic as original build (salary category validation → ENUM, `"via "` prefix strip, dimension ID resolution)
4. **Insert new bridge rows:** `skills_job_dim`, `job_schedule_type_bridge`
5. **Log the run:** Record in `etl_load_log`

### Idempotency

**Test:** End-to-end test with zero new source rows — completed successfully with `rows_loaded = 0` across all tables.

**Conclusion:** Safe to re-run at any time when no new data exists.

### Limitations

The current design assumes pure inserts (new `job_id`s only). It does NOT detect or handle updates/deletes to existing rows in the source. If the source owner ever corrects or removes an existing posting, this pipeline won't catch it — would require a different strategy (e.g., full-row hash comparison) if that turns out to matter.

---

## 14. Data Export and Distribution Architecture

### Decision: Parquet over CSV, GitHub Releases over Git

**Decision:** Export as Parquet files to GitHub Release assets, not committed to Git.

| Aspect | Details |
|--------|---------|
| **Problem 1** | GitHub hard-blocks any single file over 100MB from being pushed via normal git — several exported files exceeded this |
| **Problem 2** | CSV not fit for purpose at this scale: `job_postings_skills_flat` has 7,193,426 rows; Excel's hard cap is 1,048,576 rows per sheet — CSV would silently truncate |
| **Reason** | Parquet is smaller, typed, and natively supported by analyst/scientist tools (pandas, polars, DuckDB) |
| **Evidence** | First real export produced files ranging from 59MB to 844MB |
| **Trade-off** | Upload-to-release step is currently manual (drag files into GitHub Releases UI) rather than scripted |
| **Future consideration** | Automate via GitHub CLI (`gh release upload ... --clobber`) once manual flow is proven reliable |

### Distribution Pattern

- **Data files:** Published as GitHub Release assets under fixed tag `data-latest` (supports up to 2GB/file, doesn't bloat repo history)
- **Download URLs:** Stable and predictable: `github.com/<owner>/<repo>/releases/download/<tag>/<filename>` — no API calls needed
- **`manifest.json`:** Small, metadata-only, remains git-tracked
- **Large binaries:** Not committed to Git — every version ever committed stays in repo permanently, bloating every future clone

---

## 15. Export Integrity and Incident Response

### Incident: Inflated Row Counts from Stale `COUNT(*)`

**What happened:** The first automated GitHub Actions publish run produced wildly inflated row counts — `job_postings_readable` reported 27,470,810 rows (17x the true 1,615,930), with different multipliers for other datasets (10x and 9x).

**Impact:** Would have published incorrect metadata, misleading consumers about dataset sizes.

**Detection:** Multipliers were suspiciously exact integers and didn't match any plausible legitimate source update — caught before being trusted.

### Investigation

| Check | Result | Ruled Out |
|-------|--------|-----------|
| Re-checked views in MotherDuck browser SQL editor | Correct counts | Actual data corruption in warehouse |
| Standalone diagnostic script (same Python `duckdb` client / `md:` connection) | All counts correct, including three repeated calls | Flakiness, duplicate table names, catalog resolution issues |
| Byte-size plausibility | "Inflated" file was *smaller* than correct one despite claiming 17x more rows | 27M real rows written — physically impossible |
| `read_parquet(...)` on already-exported file | 1,615,930 rows, correct | Export logic's data path |

**Root cause:** Transient, MotherDuck-side inconsistency affecting `COUNT(*)` specifically (likely a fast-path using cached/stale statistics rather than a full scan) — not a bug in schema, views, DuckDB catalog resolution, or export logic's data path.

### Fix

**Design flaw identified:** Trusting two separate, independently-computed numbers (standalone `COUNT(*)` and separate `COPY` operation) to agree.

**Implementation change:** `export.py` now derives `row_count` from the Parquet file it just wrote (`read_parquet(...)` on the output), never from a standalone query — making the manifest self-consistent with the actual published artifact by construction.

### Preventive Control

`export.py` now compares each new row count against the last published manifest and aborts the entire run (nonzero exit, no manifest written) if any dataset swings by more than 50% — a deliberate judgment-call threshold, same pattern as `known_bounds` checks in `dq_check_log`.

On GitHub Actions, a failed script step halts the workflow automatically, so the release upload and manifest commit never run. This means even an *unrelated* future anomaly gets caught before publishing.

---

## 16. Semantic Layer

### Analyst-Facing Views

| View | Grain | Purpose | Key Transformations |
|------|-------|---------|---------------------|
| `job_postings_readable` | 1 row per posting (1,615,930) | Browse/lookup — all dimensions resolved to readable labels | Skills/schedule_types as arrays via `LIST()`; excludes `_raw` and internal IDs |
| `job_postings_skills_flat` | 1 row per (job, skill) (7,193,426) | Correct grain for skill-level aggregation — no unnesting needed | Flattened many-to-many relationship; excludes `_raw` and internal IDs |
| `job_postings_schedule_flat` | 1 row per (job, schedule_type) (1,651,922) | Correct grain for schedule-type-level aggregation — no unnesting needed | Flattened many-to-many relationship; excludes `_raw` and internal IDs |

### Why Use Views Instead of Raw Tables

- **Explicit grain:** Each view has a documented grain, preventing accidental row multiplication
- **Resolved dimensions:** Analysts see human-readable labels, not internal IDs
- **Hidden complexity:** No need to reconstruct joins or understand bridge table logic
- **Excluded internals:** `_raw` columns and internal IDs hidden from consumers
- **Stable interface:** Views provide a stable contract even if underlying schema evolves

---

## 17. Data Consumer Contract

Analysts and data scientists consuming the semantic views should expect:

| Aspect | Expectation |
|--------|-------------|
| **Grain** | Each view has a documented, explicit grain — use the appropriate view for your aggregation level |
| **Human-readable fields** | All dimension values resolved to readable labels (e.g., country name, not `country_id`) |
| **Resolved dimensions** | No need to join dimension tables — views handle this |
| **Array fields** | `job_postings_readable` exposes skills/schedule_types as arrays via `LIST()` |
| **Flat analytical views** | `job_postings_skills_flat` and `job_postings_schedule_flat` provide correct grain for aggregation without unnesting |
| **Excluded internal IDs** | Surrogate keys and source IDs not exposed unless semantically meaningful |
| **Excluded raw provenance fields** | `_raw` columns hidden — use warehouse directly if traceability needed |

**Important:** Do not join raw tables directly unless you understand the grain and many-to-many relationships. Use the appropriate view for your use case.

---

## 18. Open and Deferred Items

| Item | Status | Reason |
|------|--------|--------|
| `job_via` long-tail and regional-suffix normalization | Deferred | Deliberately scoped out — would require fuzzy-matching/clear rules to avoid over-merging distinct sources |
| Monitoring/alerting on load failures or data quality drift | Planned | Next priority after manual flow proven reliable |
| Orchestration/scheduling | Planned | Incremental script currently run manually |
| Update/delete detection in source | Limitation | Current watermark strategy detects only new `job_id`s; would require full-row hash comparison |

---

## 19. Appendix: DuckDB Metadata Functions

Use these instead of `information_schema` for cross-catalog queries:

| Function | Purpose |
|----------|---------|
| `duckdb_columns()` | List columns across tables/databases |
| `duckdb_indexes()` | List indexes |
| `duckdb_types()` | List types (including ENUMs) |
| `duckdb_databases()` | List attached databases |

All accept a `database_name` filter directly.

---

## 20. Appendix: Key Validation Queries

### Gaplessness Verification

```sql
-- Verify job_id is gapless
SELECT 
    MAX(job_id) - MIN(job_id) + 1 AS expected_count,
    COUNT(*) AS actual_count,
    MAX(job_id) - MIN(job_id) + 1 = COUNT(*) AS is_gapless
FROM job_postings_fact;
```

### Monotonicity Verification

```sql
-- Verify job_id is monotonically non-decreasing with job_posted_date
SELECT COUNT(*) AS out_of_order_rows
FROM (
    SELECT job_id, job_posted_date,
           LAG(job_posted_date) OVER (ORDER BY job_id) AS prev_date
    FROM job_postings_fact
) 
WHERE job_posted_date < prev_date;
```

### Lossless Schedule-Type Transformation

```sql
-- Verify bridge table is lossless
SELECT 
    (SELECT COUNT(DISTINCT job_id) FROM job_postings_fact WHERE job_schedule_type_raw IS NOT NULL) AS fact_count,
    (SELECT COUNT(DISTINCT job_id) FROM job_schedule_type_bridge) AS bridge_count;
```

### FK Violation Test

```sql
-- Test FK enforcement (should fail)
INSERT INTO job_postings_fact (job_id, company_id, ...)
VALUES (-999, -1, ...);  -- company_id = -1 does not exist in company_dim
```

### PK Violation Test

```sql
-- Test PK enforcement (should fail)
INSERT INTO skills_dim (skill_id, skill_name)
VALUES (0, 'Duplicate Test'), (0, 'Duplicate Test 2');  -- duplicate skill_id
```