Core_Employee

Purpose

The Core.Employee table serves as the central repository for core employee information in the Employee Data Mart. It contains essential demographic, contact, and employment information for all employees, both current and historical.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
EmployeeID	INT	NO	Primary key, unique identifier	EMPLOYEES	EMPL_ID	Direct mapping
EmployeeNumber	VARCHAR(20)	NO	Employee's unique identifier	EMPLOYEES	EMPL_NUMBER	Direct mapping
FirstName	VARCHAR(100)	NO	Employee's first name	EMPLOYEES	FIRST_NAME	Direct mapping
LastName	VARCHAR(100)	NO	Employee's last name	EMPLOYEES	LAST_NAME	Direct mapping
MiddleName	VARCHAR(100)	YES	Employee's middle name	EMPLOYEES	MIDDLE_NAME	Direct mapping
BirthDate	DATE	NO	Employee's date of birth	EMPLOYEES	BIRTH_DATE	Direct mapping
Gender	CHAR(1)	NO	Employee's gender	EMPLOYEES	GENDER	Direct mapping
EmailAddress	VARCHAR(100)	YES	Employee's email address	EMPLOYEES	EMAIL_ADDRESS	Direct mapping
PhoneNumber	VARCHAR(20)	YES	Employee's phone number	EMPLOYEES	PHONE_NUMBER	Direct mapping
HireDate	DATE	NO	Employee's original hire date	EMPLOYEES	HIRE_DATE	Direct mapping
TerminationDate	DATE	YES	Employee's termination date	EMPLOYEES	TERMINATION_DATE	Direct mapping
EmploymentStatus	VARCHAR(50)	NO	Current employment status	EMPLOYEES	EMPL_STATUS	Mapped from status codes
IsActive	BIT	NO	Active status flag	EMPLOYEES	IS_ACTIVE	Derived from status
AddressLine1	VARCHAR(100)	YES	Primary address line	ADDRESSES	ADDRESS_LINE1	From current address
AddressLine2	VARCHAR(100)	YES	Secondary address line	ADDRESSES	ADDRESS_LINE2	From current address
City	VARCHAR(100)	YES	City	ADDRESSES	CITY	From current address
State	VARCHAR(50)	YES	State/Province	ADDRESSES	STATE	From current address
PostalCode	VARCHAR(20)	YES	Postal/ZIP code	ADDRESSES	POSTAL_CODE	From current address
Country	VARCHAR(50)	YES	Country	ADDRESSES	COUNTRY	From current address
EmergencyContactName	VARCHAR(100)	YES	Emergency contact name	EMERGENCY_CONTACTS	CONTACT_NAME	From primary contact
EmergencyContactPhone	VARCHAR(20)	YES	Emergency contact phone	EMERGENCY_CONTACTS	CONTACT_PHONE	From primary contact
EmergencyContactRelationship	VARCHAR(50)	YES	Relationship to employee	EMERGENCY_CONTACTS	RELATIONSHIP	From primary contact
CreatedDate	DATETIME	NO	Record creation date	N/A	N/A	System generated
ModifiedDate	DATETIME	NO	Last modification date	N/A	N/A	System generated

Source System Mapping

Primary Source: EMPLOYEES Table

Contains core employee information
Updated daily from HR system
Key fields mapped directly to Core.Employee

Secondary Sources:

ADDRESSES Table
- Current address information
- Updated when address changes occur
- Only current address is loaded
EMERGENCY_CONTACTS Table
- Emergency contact information
- Updated when contact information changes
- Only primary contact is loaded

Data Transformation Rules

Employment Status Mapping

Source Status -> Target Status
'A' -> 'Active'
'T' -> 'Terminated'
'L' -> 'On Leave'
'R' -> 'Retired'

Active Status Derivation

IsActive = CASE
  WHEN EmploymentStatus IN ('Active', 'On Leave') THEN 1
  ELSE 0
END

Address Selection
- Only current primary address is loaded
- Address type must be 'HOME'
- Most recent effective date is used
Emergency Contact Selection
- Only primary emergency contact is loaded
- Contact type must be 'EMERGENCY'
- Most recent effective date is used

Data Quality Rules

Required Fields
- EmployeeID
- EmployeeNumber
- FirstName
- LastName
- BirthDate
- Gender
- HireDate
- EmploymentStatus
- IsActive
Validation Rules
- EmployeeNumber must be unique
- BirthDate must be in the past
- HireDate must be in the past
- TerminationDate must be after HireDate if present
- EmailAddress must be valid format if present
Business Rules
- Active employees must have a valid email address
- Terminated employees must have a TerminationDate
- Address information must be complete if present

Usage Notes

Reporting Considerations
- Use IsActive for current employee counts
- Check EmploymentStatus for detailed status reporting
- Consider both HireDate and TerminationDate for historical analysis
Performance Considerations
- Indexed on EmployeeID and EmployeeNumber
- Consider filtering on IsActive for current employee queries
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Primary key for all employee-related tables
- Used in all employee-related views
- Source for employee dimension in data warehouse

Maintenance

Refresh Schedule
- Daily incremental updates
- Full refresh on weekends
- Emergency updates as needed
Archive Strategy
- Historical records maintained in table
- No archiving required
- Use IsActive for current vs. historical filtering
Monitoring
- Track record counts by status
- Monitor data quality metrics
- Alert on validation rule violations

Core.Employee Table Documentation