Data Dictionary

Generated from the live warehouse schema by scripts/gen_data_dictionary.py - do not edit by hand; rerun the script after schema changes. Lineage columns (source_id, run_id, raw_hash, retrieved_at, parser_version, data_class) implement the honesty contract on every fact.

dim_company (table)

Grain: one row per canonical company entity.

Column	Type	Not null	PK
canonical_id	TEXT		yes
entity_key	TEXT	yes
canonical_name	TEXT	yes
website	TEXT
city	TEXT	yes
ags	TEXT	yes
industry	TEXT	yes
size_category	TEXT	yes
first_seen	TEXT	yes
last_seen	TEXT	yes
data_class	TEXT	yes

dim_location (table)

Grain: one row per configured municipality (AGS).

Column	Type	Not null	PK
ags	TEXT		yes
name	TEXT	yes
nuts3	TEXT	yes
municipality_type	TEXT	yes

dim_skill (table)

Grain: one row per canonical skill (taxonomy version stamped).

Column	Type	Not null	PK
skill_id	TEXT		yes
label	TEXT	yes
category	TEXT	yes
taxonomy_version	TEXT	yes

dim_source (table)

Grain: one row per registered source.

Column	Type	Not null	PK
source_id	TEXT		yes
name	TEXT	yes
publisher	TEXT	yes
access_method	TEXT	yes
license_note	TEXT	yes
catalogue_ref	TEXT	yes
enabled	INTEGER	yes
live	INTEGER	yes

dim_theme (table)

Grain: one row per transformation theme.

Column	Type	Not null	PK
theme_id	TEXT		yes
label	TEXT	yes
taxonomy_version	TEXT	yes

fact_change_event (table)

Grain: one row per detected company change.

Column	Type	Not null	PK
run_id	TEXT	yes	yes
event_type	TEXT	yes	yes
canonical_id	TEXT	yes	yes
snapshot_from	TEXT	yes
snapshot_to	TEXT	yes	yes
detail	TEXT	yes	yes
created_at	TEXT	yes

fact_company_snapshot (table)

Grain: one row per (snapshot date, canonical company); content-hashed.

Column	Type	Not null	PK
snapshot_date	TEXT	yes	yes
canonical_id	TEXT	yes	yes
content_hash	TEXT	yes
description	TEXT	yes
run_id	TEXT	yes
raw_hash	TEXT	yes
retrieved_at	TEXT	yes
parser_version	TEXT	yes
data_class	TEXT	yes

fact_data_quality_run (table)

Grain: one row per (run, dataset, rule).

Column	Type	Not null	PK
run_id	TEXT	yes	yes
dataset	TEXT	yes	yes
rule_id	TEXT	yes	yes
severity	TEXT	yes
passed	INTEGER	yes
failed_count	INTEGER	yes
message	TEXT	yes
created_at	TEXT	yes

fact_employment (table)

Grain: one row per (statistic, Kreis, reference date).

Column	Type	Not null	PK
statistic_code	TEXT	yes	yes
ags	TEXT	yes	yes
ref_date	TEXT	yes	yes
value	INTEGER
marker	TEXT	yes
unit	TEXT	yes
source_id	TEXT	yes
run_id	TEXT	yes
raw_hash	TEXT	yes
retrieved_at	TEXT	yes
parser_version	TEXT	yes
data_class	TEXT	yes

fact_job_posting (table)

Grain: one row per deduplicated job posting.

Column	Type	Not null	PK
posting_id	TEXT		yes
title	TEXT	yes
title_normalized	TEXT	yes
company	TEXT	yes
company_normalized	TEXT	yes
city	TEXT	yes
ags	TEXT	yes
published	TEXT	yes
employment_type	TEXT	yes
seniority	TEXT	yes
seniority_method	TEXT	yes
occupation_group	TEXT	yes
occupation_method	TEXT	yes
source_id	TEXT	yes
run_id	TEXT	yes
raw_hash	TEXT	yes
retrieved_at	TEXT	yes
parser_version	TEXT	yes
data_class	TEXT	yes

fact_research_project (table)

Grain: one row per regionally linked research project.

Column	Type	Not null	PK
project_id	TEXT		yes
acronym	TEXT	yes
status	TEXT	yes
title	TEXT	yes
start_date	TEXT	yes
end_date	TEXT	yes
programme	TEXT	yes
total_cost_eur	INTEGER
objective	TEXT	yes
source_id	TEXT	yes
run_id	TEXT	yes
raw_hash	TEXT	yes
retrieved_at	TEXT	yes
parser_version	TEXT	yes
data_class	TEXT	yes

fact_source_run (table)

Grain: one row per (run, source).

Column	Type	Not null	PK
run_id	TEXT	yes	yes
source_id	TEXT	yes	yes
mode	TEXT	yes
status	TEXT	yes
records_raw	INTEGER	yes
records_bronze	INTEGER	yes
records_silver	INTEGER	yes
message	TEXT	yes
started_at	TEXT	yes
finished_at	TEXT	yes

fact_tender (table)

Grain: one row per procurement notice.

Column	Type	Not null	PK
notice_id	TEXT		yes
title	TEXT	yes
buyer	TEXT	yes
cpv	TEXT	yes
nuts	TEXT	yes
publication_date	TEXT	yes
deadline	TEXT
url	TEXT	yes
source_id	TEXT	yes
run_id	TEXT	yes
raw_hash	TEXT	yes
retrieved_at	TEXT	yes
parser_version	TEXT	yes
data_class	TEXT	yes

bridge_company_theme (table)

Grain: one row per (company, theme); matched keyword retained.

Column	Type	Not null	PK
canonical_id	TEXT	yes	yes
theme_id	TEXT	yes	yes
matched_keyword	TEXT	yes
method	TEXT	yes

bridge_job_skill (table)

Grain: one row per (posting, canonical skill); observed string retained.

Column	Type	Not null	PK
posting_id	TEXT	yes	yes
skill_id	TEXT	yes	yes
observed_string	TEXT	yes
method	TEXT	yes

bridge_project_organisation (table)

Grain: one row per (project, participant); canonical_id NULL = unmatched.

Column	Type	Not null	PK
project_id	TEXT	yes	yes
org_name	TEXT	yes	yes
org_city	TEXT	yes
org_country	TEXT	yes
role	TEXT	yes
canonical_id	TEXT

bridge_project_theme (table)

Grain: one row per (project, theme); matched keyword retained.

Column	Type	Not null	PK
project_id	TEXT	yes	yes
theme_id	TEXT	yes	yes
matched_keyword	TEXT	yes
method	TEXT	yes

bridge_tender_theme (table)

Grain: one row per (notice, theme); matched keyword retained.

Column	Type	Not null	PK
notice_id	TEXT	yes	yes
theme_id	TEXT	yes	yes
matched_keyword	TEXT	yes
method	TEXT	yes

company_entity_map (table)

Grain: one row per observed entity key -> canonical id, with match rule.

Column	Type	Not null	PK
entity_key	TEXT		yes
canonical_id	TEXT	yes
match_rule	TEXT	yes

company_merge_log (table)

Grain: one row per source record's entity assignment (audit trail).

Column	Type	Not null	PK
run_id	TEXT	yes	yes
record_id	TEXT	yes	yes
observed_name	TEXT	yes
canonical_id	TEXT	yes
rule	TEXT	yes
detail	TEXT	yes
created_at	TEXT	yes

company_review_queue (table)

Grain: one row per similar-but-unmerged canonical pair.

Column	Type	Not null	PK
canonical_a	TEXT	yes	yes
canonical_b	TEXT	yes	yes
similarity	REAL	yes
status	TEXT	yes
created_run	TEXT	yes

mart_company_hiring (table (mart))

Column	Type	Not null	PK
company_normalized	TEXT
company_display	-
month	-
postings	-

mart_company_overview (table (mart))

Column	Type	Not null	PK
canonical_id	TEXT
canonical_name	TEXT
city	TEXT
industry	TEXT
size_category	TEXT
first_seen	TEXT
last_seen	TEXT
themes	-
postings_linked	-
data_class	TEXT

mart_regional_employment (table (mart))

Column	Type	Not null	PK
ref_date	TEXT
ags	TEXT
region_name	TEXT
value	INT
marker	TEXT
data_class	TEXT
raw_hash	TEXT

mart_regional_employment_total (table (mart))

Column	Type	Not null	PK
ref_date	TEXT
value_sum	-
kreise_with_value	-
kreise_expected	-
complete	-

mart_research_projects (table (mart))

Column	Type	Not null	PK
project_id	TEXT
acronym	TEXT
title	TEXT
programme	TEXT
start_date	TEXT
end_date	TEXT
total_cost_eur	INT
regional_partners	-
partners_total	-
themes	-
data_class	TEXT

mart_skill_cooccurrence (table (mart))

Column	Type	Not null	PK
skill_a	TEXT
skill_b	TEXT
postings	-

mart_skill_monthly (table (mart))

Column	Type	Not null	PK
skill_id	TEXT
month	-
postings	-

mart_tender_activity (table (mart))

Column	Type	Not null	PK
cpv_division	-
month	-
notices	-

mart_tender_themes (table (mart))

Column	Type	Not null	PK
notice_id	TEXT
title	TEXT
buyer	TEXT
deadline	TEXT
theme_id	TEXT
matched_keyword	TEXT
data_class	TEXT

mart_top_skills (table (mart))

Column	Type	Not null	PK
skill_id	TEXT
label	TEXT
category	TEXT
postings	-
data_class	-

mart_transformation_indicators (table (mart))

Column	Type	Not null	PK
theme_id	TEXT
label	TEXT
companies_total	-
companies_current	-
postings_linked	-
data_class	-

vw_change_events (view (mart))

Column	Type	Not null	PK
event_type	TEXT
canonical_id	TEXT
snapshot_from	TEXT
snapshot_to	TEXT
detail	TEXT

vw_network_edges (view (mart))

Column	Type	Not null	PK
source_type	-
source_id	TEXT
target_type	-
target_id	TEXT
label	TEXT

vw_tender_deadlines (view (mart))

Column	Type	Not null	PK
notice_id	TEXT
title	TEXT
buyer	TEXT
nuts	TEXT
publication_date	TEXT
deadline	TEXT
url	TEXT
data_class	TEXT