Completed end-to-end case study
Azure Data Engineering + BI Analyst & GIS Specialist
A production-minded data solution that transforms official Polish census and residence data into validated analytical models, an interactive Power BI report, and an ArcGIS spatial view of foreign population across Poland.
01 — One project, two disciplines
The value is not only the dashboard or the pipeline. It is the trustworthy connection between both.
API and Excel ingestion, immutable evidence, multilingual standardization, automated QA, Parquet outputs, and conformed analytical models.
Semantic models, defensive DAX, executive KPIs, comparable-period analysis, and spatial exploration by citizenship and voivodeship.
02 — Architecture
Every layer has a clear responsibility: preserve, standardize, model, validate, and communicate.
Usual-residence population by citizenship and sex for Poland and all 16 voivodeships.
Applications, decisions, outcomes, and valid residence documents from annual and periodic reports.
03 — Semantic modeling
Annual administrative flows, H1 comparisons, and census geography are connected—but never forced into invalid comparisons.
A fact constellation connects applications, decisions, and valid-document snapshots to shared citizenship, period, source, residence-type, and outcome dimensions.
A dedicated H1 model keeps partial-period facts separate, enabling a fair January–June 2026 versus January–June 2025 comparison.
Census population facts connect country, geography, period, and source dimensions while national totals remain isolated from regional detail.
04 — Dashboard story
The report moves from executive context to citizenship behavior, current change, and finally the geographic pattern behind the population.
A decision-ready view of the latest complete year: 563K applications, 462K decisions, 32K negative decisions, and 955K valid residence documents, with annual trends and citizenship filters.
Ranks citizenship groups across applications and valid documents, tracks the positive-decision rate, and lets the user move from the national picture to a selected continent, country, or residence type.
Compares equivalent six-month periods only. Applications rose 18.6% while decisions fell 16.4%, with the change decomposed by residence type and decision outcome.
05 — GIS spotlight · ArcGIS for Power BI
The ArcGIS page maps proportional population markers for all 16 voivodeships. Continent and citizenship filters recalculate the distribution, the leading region, and the number of regions with residents in the selected context.
Methodological guardrail: GUS census population and UdSC administrative flows are intentionally presented as different statistical concepts.
06 — QA & data validation
The dedicated QA page makes completeness, reconciliation, and period logic reviewable before a business user trusts the insights.
2021–2025 are flagged complete; 2026 remains partial. Annual KPI cards therefore resolve to 2025.
The H1 page compares January–June with January–June, never a partial year against a full year.
The sum of regional GUS values matches the national population exactly: difference 0, status PASS.
Census stocks and administrative flows are labeled separately because they do not represent the same cohort.
Schema drift, translations, PK/FK relationships, grains, row counts, and official totals are checked before Gold upload.
07 — DAX evidence
These documented calculations provide a compact technical audit trail for annual, mid-year, and geographic logic.
Prevents incomplete 2026 data from contaminating annual KPIs.
Measures like-for-like change between H1 2026 and H1 2025.
Calculates the decision outcome rate for the latest complete year.
Finds and labels the leading region under the active GIS filters.
Counts regions with a positive population value in the current context.
08 — Full dashboard walkthrough
A complete silent walkthrough of the working Power BI report, from QA to the geographic exploration.
09 — Completed case study
This project demonstrates ownership across data acquisition, engineering, modeling, QA, BI delivery, and geographic analysis.
Review the complete repository ↗