Engagement Realization and Margin Leakage Audit Pipeline

Role: Lead Professional Services Analytics Architect specializing in practice financial forensics.

Context

• Target practice unit: {{practice_area}}
• Time and billing schema: {{timesheet_schema}}
• Master fee schedules: {{billing_rate_cards}}
• Baseline project margin target: {{target_margin_threshold}}
• Historical audit window: {{variance_window_months}}
• Target database dialect: {{output_db_dialect}}

Task

Write an optimized, production-grade {{output_db_dialect}} and Python extraction script that calculates engagement-level realization rates, identifies unbilled fee leakage, and flags professional services projects that fail to meet {{target_margin_threshold}}.

Method

1. Extract raw billable hours, non-billable overhead, and partner write-downs from {{timesheet_schema}} spanning the last {{variance_window_months}}.
2. Join logged consultant seniority tiers against standard rates defined in {{billing_rate_cards}} to establish baseline book revenue versus actual billed revenue.
3. Compute effective hourly rate (EHR), realization percentage, and project-level margin deviations for all engagements within {{practice_area}}.
4. Calculate write-off velocity to differentiate between scope-creep write-downs and client invoice renegotiations.
5. Implement an anomaly detection filter identifying engagements where hours logged exceed agreed milestone caps without corresponding change orders.
6. Aggregate realization benchmarks grouped by engagement partner, project lead, and client tier.
7. Format the complete transformation logic into an idempotent {{output_db_dialect}} query and wrap it in a parameterized Python execution script with logging and transaction control.

Constraints

• MUST implement defensive zero-division guards and null handling across all rate and margin computations.
• MUST NOT rely on hardcoded environment parameters, credentials, or deprecated library functions.
• The script must include full PEP 484 type annotations and modular function docstrings.
• Queries must be optimized to prevent table scans on large transactional billing tables.

Output format

• Component 1: Pipeline architecture and schema dependency summary (150-200 words)
• Component 2: Complete executable script including configuration dictionary, SQL transformation queries, and error handling
• Component 3: Data validation tests and automated alerting thresholds (100-150 words)

Self-review

• Verify all 6 variables are referenced accurately in the generated script logic.
• Ensure realization formulas mathematically account for both pre-bill write-downs and post-bill credits.
• Confirm syntax conforms strictly to {{output_db_dialect}}.