One Master Dataset, Two Consumers: Building a Governed Pipeline for BI + ML

Loh Kah Meng — Wed, 08 Jul 2026 06:01:51 +0000

The problem with two datasets

A recent project of mine called for two things that are easy to build separately and dangerous to build separately: a Power BI dashboard for exploring workforce demographics, and four tuned ML models predicting income bracket from the same population.

The tempting shortcut is two datasets — one imputed and encoded for modeling, one left more human-readable for BI. I deliberately avoided that.

Two datasets drift. A fix applied to one never reaches the other, and by the second refresh cycle, "the data" means two different things depending on who you ask. Six months in, someone's dashboard says one number and someone's model says another, and nobody can explain why without a forensic diff.

So instead, everything here traces back to one governed pipeline (build_master_dataset.py) that produces a single master table — adult_master.csv / .parquet — 48,790 rows, 26 columns, built from the UCI Adult Census Income dataset. Power BI reads the human-readable income_bracket and categorical columns directly. The ML pipeline reads the numeric income_target flag and the same underlying predictors. One source of truth, two consumers.

Missingness you can audit, not smooth over

The raw source data uses ? for missing categorical values — workclass (5.73% missing), occupation (5.75%), native_country (1.75%). The easy move is to impute these away with the mode or drop the rows. I didn't do either.

Every missing value is relabeled Unknown and paired with a boolean *_was_missing flag. That means the fact that a value was missing stays fully auditable downstream — in the BI layer, in the model, in any report — instead of being silently absorbed into "most common category" and forgotten.

A dashboard built around one reusable pattern

The Power BI layer isn't a KPI wall. It's built around one DAX pattern — High Income Rate vs Overall — that drops onto any breakdown (occupation, education, region, work-hours category) and immediately shows whether that segment sits above or below the population baseline. Twenty measures total, plus three sort-key columns so categories like "Part-time (<20)" through "Overtime (60+)" render in logical rather than alphabetical order.

What actually predicts income — according to four models, not one

Rather than trust a single model's built-in feature importance (which can vary wildly by algorithm), I computed permutation importance across all four tuned models on a held-out sample and looked for agreement.

The signal was consistent: relationship/marital status dominates, followed by capital gains, education, age, occupation, and hours worked.

Two findings were worth calling out explicitly rather than leaving buried in a model file:

Married individuals — specifically the "Husband" and "Wife" relationship categories — show a high-income rate near 45–47%, against roughly 1.5% for adults still classified as someone's child. That gap reflects age and career-stage confounding as much as marital status itself, and it's worth saying so rather than letting the number imply causation it doesn't have.
fnlwgt, a U.S. Census sampling weight included in the raw data, has effectively zero correlation with income (Pearson r = −0.006). It was excluded from modeling entirely rather than left in to add noise — a reminder that "more columns" isn't automatically "more signal."

Four models, one honest comparison

Model	Accuracy	Precision	Recall	F1	ROC-AUC
Logistic Regression	81.1%	0.567	0.845	0.679	0.907
Random Forest	81.7%	0.575	0.863	0.690	0.917
HistGradientBoosting	87.3%	0.776	0.652	0.708	0.927
XGBoost	83.4%	0.604	0.865	0.712	0.928

XGBoost edges out HistGradientBoosting on ROC-AUC — 0.928 vs 0.927 — but that's the wrong reason to call it the clear winner. HistGB actually has better accuracy, precision, and error rate. XGBoost's class-rebalancing (scale_pos_weight) trades precision for recall, catching more true high earners at the cost of more false positives.

Which one is "better" depends on whether the downstream decision punishes missed opportunities or wasted outreach more — a business question, not a modeling one. So that's what I wrote, instead of picking a winner by a 0.001 margin because it looks cleaner in a table.

The takeaway

None of this is exotic. There's no novel architecture here — it's a gradient-boosted tree model on tabular census data, the kind of problem that's been solved a thousand times. What I think is worth sharing isn't the modeling, it's the discipline around it: one pipeline instead of two, missingness that stays visible instead of vanishing, importance from four models instead of one, and a model choice explained in terms of the decision it serves rather than a leaderboard.

Full write-up, including the complete methodology and caveats, is on my site: Workforce Income Analytics — Case Study