Partner Realization and Fee Earner Profitability dbt Pipeline Engine

Role: Principal Analytics Engineer specializing in billable utilization and partner equity metrics within professional services.

Context

• Target SQL dialect and warehouse: {{sql_dialect}}
• Production schema name: {{target_schema}}
• Source timecard entries table: {{billable_time_table}}
• Agreed and rack rate table: {{rate_card_table}}
• Profit margin performance benchmark: {{engagement_margin_target}}
• Incremental date clustering column: {{date_partition_col}}

Task

Author a modular, production-grade {{sql_dialect}} script structured as a modern dbt incremental model that ingests {{billable_time_table}}, joins {{rate_card_table}}, and computes fee-earner billable realization, effective hourly rate (EHR), write-downs, and practice group partner profitability against {{engagement_margin_target}}.

Method

1. Establish dbt configuration headers specifying incremental materialization, unique keys, and partition pruning on {{date_partition_col}}.
2. Construct CTEs isolating time entries from {{billable_time_table}} filtered to the incremental lookback window.
3. Implement point-in-time rate card resolution joining {{rate_card_table}} using non-equi window joins matching entry dates.
4. Calculate billed hours, unbilled WIP, standard value, invoiced value, and write-down percentages per fee-earner and project.
5. Compute Fee Earner Realization (Billed Amount / Standard Value) and Billing Realization (Collected Amount / Billed Amount).
6. Derive direct labor cost per hour and margin percentages, classifying engagements meeting or failing {{engagement_margin_target}}.
7. Aggregate partner distribution credits across shared engagement hierarchies with deterministic window partitions.
8. Emit clean dimensional attributes, metrics, and metadata timestamps in the final query projection.

Constraints

• MUST be fully syntactically valid in {{sql_dialect}} without pseudo-code.
• MUST use standard Common Table Expression (CTE) styling with single-responsibility steps.
• MUST NOT use correlated subqueries or unindexed scalar subqueries inside SELECT clauses.
• All division operations MUST include NULLIF or zero-division protections.

Output format

1. Header Configuration Block: {{ config(...) }} dbt block.
2. Complete SQL Script: Formatted CTEs terminating in a final SELECT * FROM final.
3. Inline Schema Test YAML: Schema documentation defining unique, not_null, and custom dbt-expectations tests.

Self-review

• Verify all zero-division edge cases across realization calculations return NULL instead of runtime exceptions.
• Ensure incremental logic correctly prunes partitions on {{date_partition_col}} during standard runs.
• Confirm fee-earner hierarchy rollups do not produce duplicate grain records.