Position_Skills

Purpose

The Position.Skills table stores comprehensive information about employee skills, certifications, licenses, and competencies. It tracks skill levels, acquisition dates, expiration dates, and verification status. This table is essential for skills inventory management, compliance tracking, and workforce development planning.

Table Structure

Field Name	Data Type	Nullable	Description	Source Table	Source Field	Transformation Notes
SkillID	INT	NO	Primary key, unique identifier	SKILLS	SKILL_ID	Direct mapping
EmployeeID	INT	NO	Foreign key to Core.Employee	SKILLS	EMPL_ID	Direct mapping
SkillType	VARCHAR(50)	NO	Type of skill	SKILLS	SKILL_TYPE	Mapped from codes
SkillCode	VARCHAR(20)	NO	Skill classification code	SKILLS	SKILL_CODE	Direct mapping
SkillName	VARCHAR(100)	NO	Name of the skill	SKILLS	SKILL_NAME	Direct mapping
SkillDescription	VARCHAR(500)	YES	Detailed skill description	SKILLS	SKILL_DESC	Direct mapping
ProficiencyLevel	VARCHAR(20)	NO	Skill proficiency level	SKILLS	PROFICIENCY	Mapped from codes
AcquisitionDate	DATE	NO	Date skill was acquired	SKILLS	ACQUISITION_DATE	Direct mapping
ExpirationDate	DATE	YES	Skill expiration date	SKILLS	EXPIRATION_DATE	Direct mapping
VerificationDate	DATE	YES	Last verification date	SKILLS	VERIFICATION_DATE	Direct mapping
VerifiedBy	VARCHAR(100)	YES	Person who verified the skill	SKILLS	VERIFIED_BY	Direct mapping
IsRequired	BIT	NO	Required skill indicator	SKILLS	IS_REQUIRED	Direct mapping
IsExpired	BIT	NO	Expiration status indicator	SKILLS	IS_EXPIRED	Derived
IsVerified	BIT	NO	Verification status indicator	SKILLS	IS_VERIFIED	Derived
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: SKILLS Table

Contains skill information
Updated when skills change
Key fields mapped directly to Position.Skills

Secondary Sources:

SKILL_CODES Table
- Skill classification information
- Used for skill validation
- Updated when skill codes change
CERTIFICATIONS Table
- Certification details
- Used for certification validation
- Updated when certifications change

Data Transformation Rules

Skill Type Mapping

Source Code -> Target Type
'CERT' -> 'Certification'
'LIC' -> 'License'
'SKILL' -> 'Skill'
'COMP' -> 'Competency'
'LANG' -> 'Language'

Proficiency Level Mapping

Source Code -> Target Level
'BEG' -> 'Beginner'
'INT' -> 'Intermediate'
'ADV' -> 'Advanced'
'EXP' -> 'Expert'

Expiration Status Derivation

IsExpired = CASE
  WHEN ExpirationDate IS NOT NULL AND ExpirationDate < GETDATE() THEN 1
  ELSE 0
END

Verification Status Derivation

IsVerified = CASE
  WHEN VerificationDate IS NOT NULL AND VerifiedBy IS NOT NULL THEN 1
  ELSE 0
END

Data Quality Rules

Required Fields
- SkillID
- EmployeeID
- SkillType
- SkillCode
- SkillName
- ProficiencyLevel
- AcquisitionDate
- IsRequired
- IsExpired
- IsVerified
Validation Rules
- AcquisitionDate must be valid
- ExpirationDate must be after AcquisitionDate if present
- VerificationDate must be after AcquisitionDate if present
- SkillCode must be valid
- ProficiencyLevel must be valid
Business Rules
- Required skills must be verified
- Expired skills must be renewed or removed
- Skills must be verified periodically
- Skill types must be valid
- Proficiency levels must be appropriate for skill type

Usage Notes

Reporting Considerations
- Use SkillType for skills analysis
- Consider ProficiencyLevel for skills assessment
- Track skill expirations
- Monitor verification status
Performance Considerations
- Indexed on SkillID, EmployeeID, and SkillType
- Consider filtering on IsExpired for compliance
- Use CreatedDate/ModifiedDate for incremental loads
Integration Points
- Links to Core.Employee via EmployeeID
- Used in skills inventory views
- Source for compliance reporting
- Used in workforce planning

Maintenance

Refresh Schedule
- Daily updates for new skills
- Full refresh monthly
- Emergency updates for corrections
Archive Strategy
- Historical skills maintained in table
- Use ExpirationDate for historical filtering
- Skills history tracked for compliance
Monitoring
- Track skills by employee
- Monitor expiring skills
- Alert on validation rule violations
- Track verification status

Position.Skills Table Documentation