D365 Silently Dropped 20 OData Columns: SCD2 Saved the Data
Diagnosed HRIS dashboard failures after Dynamics 365 stopped returning WorkerStatus, Gender, and MaritalStatus from its Workers OData entity, then recovered the values from an SCD2 history table that had been quietly capturing them for months.
ON THIS PAGE
D365 Silently Dropped 20 OData Columns
Around 21:00 EAT, every HRIS dashboard went dark. vw_hris_detailed — the main view feeding Power BI — threw:
Invalid column name 'workerstatus'
Invalid column name 'gender'
Invalid column name 'maritalstatus'These columns had been in hris.fct_d365_workers since the table was created. I queried the table directly and found data — 252 rows — but the schema had shrunk from 115 columns to 95. Twenty columns gone.
Nothing on my side had dropped them. The D365 OData Workers entity had simply stopped returning them. The pipeline pulls whatever D365 sends: Extract auto-creates staging tables from the OData response schema, Transform copies staging into Gold via a TabularTranslator that maps source to destination. When D365 stopped sending those 20 columns, they vanished from staging, then Gold, then every downstream view.
Both Extract and Transform reported success. No error. No warning.
The missing columns included WorkerStatus, Gender, MaritalStatus, WorkerType, IsDisabled, AllowRehire, IsFulltimeStudent, IsExpatriateRulingApplicable, IsDisabledVeteran, and 11 PrimaryContact*IsPrivate/IsMobile boolean fields.
First Instinct: Just Add Them Back
My first impulse was ALTER TABLE hris.fct_d365_workers ADD WorkerStatus NVARCHAR(50). That would have broken the next Transform run. ADF’s TabularTranslator with typeConversion: true is strict about column parity in both directions:
- Extra source column not in destination: error
- Extra destination column not in source: error
Adding a column to Gold without a matching column in the transform_query triggers TabularTranslatorExtraDestinationColumn. Manual DDL was out.
The Fix: LatestWorkerDemographics CTE
The data was not actually lost. hris.hris_detailed_history — an SCD Type 2 table — had been capturing WorkerStatus, Gender, and MaritalStatus for months. A stored procedure (sp_update_hris_detailed_history) snapshots vw_hris_detailed on a schedule and tracks changes via SHA2_256 hash comparison, expiring old records and inserting new ones.
The values from before D365 dropped those columns were all preserved in this table.
I rewired four views — vw_hris_detailed, vw_hris_detailed_terminated, vw_hris_historical, and vw_employee_position_history — to source those three columns from the history table instead of the Workers fact table:
LatestWorkerDemographics AS (
SELECT
EmployeeID,
WorkerStatus,
Gender,
MaritalStatus,
ROW_NUMBER() OVER (
PARTITION BY EmployeeID
ORDER BY report_date DESC, effective_start_date DESC
) AS rn
FROM hris.hris_detailed_history
)Each view joins on EmployeeID with rn = 1 to get the most recent known values.
The critical change was in vw_hris_detailed’s WHERE clause. It had been:
WHERE w.WorkerStatus = 'Employed'Referencing the Workers table. Changed to:
WHERE lwd.WorkerStatus = 'Employed'Referencing the CTE. If I had missed this, the view would have returned zero rows — w.WorkerStatus no longer existed.
Deployed all four views. vw_hris_detailed returned 258 employed workers. Dashboards came back.
The Circular Dependency
There is a subtle circularity in this architecture that initially concerned me. vw_hris_detailed now reads from hris_detailed_history, which is populated by sp_update_hris_detailed_history, which reads from vw_hris_detailed.
This is stable because the history table already contains data from previous runs. The stored procedure compares the current view output against existing history records using a hash, then inserts or expires rows as needed. It does not truncate. As long as the history table has data, the cycle bootstraps itself.
But if someone truncates hris_detailed_history, the cycle breaks. vw_hris_detailed would return zero rows (no WorkerStatus to filter on), so the stored procedure would snapshot zero rows, and the table stays empty permanently.
The Risk That Remains
If someone’s WorkerStatus actually changes in D365 — Employed to Terminated, for example — I will not capture it unless D365 restores the column to its OData entity. The values in hris_detailed_history are frozen at whatever was last captured before the column disappeared.
For now, this is an accepted risk. The alternative was no data at all.
What This Taught Me
OData APIs can silently drop columns. There was no deprecation notice, no error, no versioning change. The entity just started returning fewer fields. Any pipeline that auto-creates schemas from API responses is vulnerable to this.
SCD2 tables are insurance. Without hris_detailed_history, the loss of WorkerStatus, Gender, and MaritalStatus would have been permanent. The stored procedure had been quietly doing its job for months, and that saved the data.
TabularTranslator is unforgiving. You cannot patch Gold table schemas independently of their transform queries. Any schema change must be reflected in both the transform_query in config_adf_pipeline_settings and the Gold table DDL simultaneously. This is a feature, not a bug — it prevents silent column mismatches — but it means quick fixes like ALTER TABLE are off the table.
Check production before editing. I almost deployed view changes without checking what was running in production first. The views in the repo were outdated v1 definitions. Production had v3 views with different column lists, different CTEs, different WHERE clauses. If I had deployed the repo version, I would have overwritten working views with broken ones.
Discussion