karim-bhalwani Data Deprecation Analysis
Expert in analyzing ODBC/database access logs to identify technical debt, unused data structures, and security risks for safe deprecation.
When to Use This Skill
Use when:
- Auditing data warehouses for unused tables, views, or stored procedures
- Planning data platform modernization or migration projects
- Identifying legacy application dependencies on data assets
- Detecting stale data structures that can be safely deprecated
- Analyzing access patterns to prioritize modernization efforts
- Building technical debt reports for leadership
- Identifying security risks from legacy or orphaned data objects
Core Capabilities
- Dead Data Detection - Analyze access logs to tier data objects by recency (Ghost/Cold/Cool/Active)
- Legacy Pattern Recognition - Identify deprecated technologies (old ODBC drivers, legacy Excel, Access databases)
- Dependency Mapping - Trace which applications, users, and service accounts access which objects
- Risk Assessment - Flag security concerns (orphaned objects, stale service accounts, unoptimized queries)
- Deprecation Planning - Generate actionable reports for safe data asset retirement
- Query Pattern Analysis - Detect anti-patterns (SELECT *, unfiltered scans, legacy joins)
Workflow / Process
Phase 1: Data Collection
- Identify access log sources (SQL Server Audit, Splunk, application logs, ODBC traces)
- Define key variables: Timestamp, Application_Name, Database_Object, User_Account, Query_Text
- Establish analysis time window (typically 6-24 months)
- Extract and normalize log data into analysis-ready format
Phase 2: Dead Data Detection
- Group access records by Database_Object
- Calculate Max_Access_Date (last access) for each object
- Assign tier based on access recency:
- Ghost Tier: No access in >18 months
- Cold Tier: No access in >12 months
- Cool Tier: No access in >6 months
- Active: Accessed within last 6 months
- Prioritize false negatives: Any access in 6 months = Active
Phase 3: Legacy Pattern Recognition
- Filter for legacy Application_Name indicators:
- Keywords: "Access", "Excel 2013", "ODBC 3.0", "Python Script", ".Net App"
- Identify high-frequency queries from legacy applications
- Flag anti-patterns:
SELECT *, unfiltered table scans, deprecated syntax - Map legacy dependencies to data objects
Phase 4: Risk Assessment & Reporting
- Cross-reference Ghost/Cold objects with User_Account for contact information
- Identify orphaned objects (no recent access, no known owner)
- Flag security risks (stale service accounts, excessive permissions)
- Generate tiered deprecation report with recommendations
Outputs & Deliverables
- Primary Output: Tiered deprecation report (Ghost/Cold/Cool/Active counts with object details)
- Secondary Output: Legacy dependency map, contact list for last-known users, SQL/Python analysis scripts
- Success Criteria: All objects categorized, Ghost tier objects have owner contact info, actionable deprecation timeline
- Quality Gate: Zero false negatives (any recent access = Active), verified with stakeholders before deprecation
Analysis Patterns
Tiering Thresholds
| Tier | Last Access | Risk Level | Action |
|---|---|---|---|
| Ghost | >18 months | Low (safe to deprecate) | Archive and remove |
| Cold | >12 months | Medium | Flag for review, contact owners |
| Cool | >6 months | Medium-High | Monitor, investigate usage |
| Active | <6 months | N/A | Keep, optimize if needed |
Legacy Application Indicators
| Indicator | Concern | Recommended Action |
|---|---|---|
| "Microsoft Access" | End-of-life technology | Migrate to modern BI tool |
| "Excel 2013" or older | Legacy Office version | Upgrade or migrate workflow |
| "ODBC 3.0" | Outdated driver | Update connection strings |
| Generic "Python Script" | Unknown/undocumented automation | Document and modernize |
| ".Net App" (unnamed) | Untracked application | Identify owner, document dependency |
Query Anti-Patterns
| Pattern | Detection | Risk |
|---|---|---|
SELECT * |
Regex: SELECT\s+\* |
Unoptimized, fragile to schema changes |
| No WHERE clause | Full table scan detection | Performance, cost concerns |
| Deprecated syntax | += joins, non-ANSI joins |
Compatibility risk |
| Hardcoded dates | Regex: date literals in queries | Maintenance debt |
Standards & Best Practices
Data Collection
- Collect minimum 12 months of access logs for accurate tiering
- Normalize timestamps to UTC for consistent analysis
- Include both successful and failed access attempts
- Capture full query text when possible for pattern analysis
Analysis Integrity
- Prioritize False Negatives: If accessed once in 6 months, mark as Active
- Validate Object Existence: Cross-reference with current catalog (some logged objects may be already dropped)
- Account for Seasonality: Some objects accessed only quarterly (fiscal reports)
- Consider Indirect Access: Views/procedures may access tables not directly queried
Deprecation Safety
- Never deprecate without contacting last-known User_Account
- Archive to cold storage before permanent deletion
- Maintain deprecation log for audit trail
- Set grace period (30-90 days) between deprecation notice and removal
- Test downstream dependencies before removal
Security Considerations
- Flag orphaned service accounts with data access
- Identify objects accessed by terminated employees
- Review excessive permissions on Cold/Ghost objects
- Document any PII/sensitive data in deprecated objects
Integration Points
| Phase | Input From | Output To | Context |
|---|---|---|---|
| Collection | ops-manager, data-pipeline-engineer | — | Log extraction and normalization |
| Analysis | — | senior-data-engineer | Technical debt assessment |
| Reporting | — | principal-data-engineer | Strategic modernization planning |
| Remediation | senior-data-engineer | guardian | Security risk validation |
| Execution | — | ops-manager | Archive and removal automation |
Constraints
Technical Constraints:
- Requires access to database audit logs or ODBC traces
- Analysis accuracy depends on log completeness and retention
- Cannot detect objects accessed via dynamic SQL not captured in logs
Scope Constraints:
- In Scope: Tables, views, stored procedures, functions with access logs
- Out of Scope: Application code refactoring, infrastructure changes, data migration execution
Governance Constraints:
- All deprecation decisions require stakeholder approval
- Maintain audit trail of deprecated objects and approval chain
- Comply with data retention policies before permanent deletion
Common Pitfalls
- Seasonal Access Missed: Quarterly/annual reports appear inactive. Fix: Use 18+ month window, verify with business calendar.
- Dynamic SQL Not Logged: Objects accessed via EXEC/sp_executesql may not appear in logs. Fix: Enable extended events or query store.
- Service Account Mapping: Generic service accounts hide true ownership. Fix: Cross-reference with application inventory, contact team leads.
- View Dependencies Ignored: Table appears unused but accessed via views. Fix: Trace view dependencies before deprecating underlying tables.
- False Confidence in Ghost Tier: Object not accessed but still referenced in code. Fix: Grep codebase for object references before deprecation.
- No Backup Before Removal: Data permanently lost after deprecation. Fix: Always archive to cold storage with 90-day retention.
Reference Files
Detailed implementation guides available in references/ folder:
- access-log-analysis.md - SQL and Python scripts for log analysis
- deprecation-report-template.md - Standard report format for stakeholders
- legacy-pattern-detection.md - Regex and detection rules for legacy applications
Dependencies
- Depends On: senior-data-engineer, sql-optimization-patterns, guardian
- Related Skills: principal-data-engineer (strategic planning), data-pipeline-engineer (log extraction), ops-manager (removal automation)