Modelling employee history (SCD Type 2) in Qlik Compose from a custom HRMS source – best practices?
Hello everyone,
I work at Dev Technosys, where we do HR management software development, building custom HRMS platforms for our clients. One of our clients' data teams uses Qlik Compose for their data warehouse, and they'd like HR data from our platform to feed into it. Before we finalise our database design, I'd like to understand how to make the source data as Compose-friendly as possible.
Scenario:
Source: a relational HRMS database (PostgreSQL) with employees, departments, job titles, managers, locations, and compensation bands
Data would land through Qlik Replicate (CDC)
The client wants to report on employee history, for example, headcount by department at any point in time, promotion rates, and attrition
My questions:
Tracking employee history: From what I understand, attributes in the Compose model can be set to keep history (Type 2). For an employee entity, which attributes would you normally track historically (department, job title, manager, location), and which would you keep as Type 1?
Source design: Is it better for our HRMS to keep its own effective-dated history tables (for example, employee_job_history with valid_from and valid_to), or should we keep the source simple with current values only and let Compose build the history from CDC changes?
Backdated changes: HR data often includes backdated updates, such as a promotion entered today that was effective last month. How does Compose handle changes where the business effective date is different from the date the change was captured?
Manager hierarchy: What's the recommended way to model a reporting hierarchy (employee → manager) in Compose so that historical org charts can be reconstructed?
Sensitive data: Salary and personal details are sensitive. Is it better to exclude these columns at the Replicate level, or handle masking and access within the warehouse and data marts?
Any guidance, documentation links, or experience from similar HR data projects would be really appreciated.
