Payroll Dashboard Pipeline (NEW_ Views)
Snowflake payroll reporting path that feeds Power BI from two scheduled modeled loads and one direct-source manual-detail view using TimeQey and EngageHR upstream data.
Overview
Publishes four NEW_ report views by combining consolidated payroll facts, activity-level TimeQey aggregates, and direct-source manual-entry detail.
Business Purpose
Provide payroll operations, employee context, and manual-entry quality signals in a single governed semantic path for dashboard analysis and exception follow-up.
Technology Stack
Business Impact
Missed task runs, incorrect period scoping, or source-side delays can immediately surface as stale payroll totals, incomplete attributes, or manual-entry lag inaccuracies.
View Contract
4 NEW_ views power report reads: payroll data, employee attributes, TimeQey aggregate, and manual details.
Execution Cadence
Daily tasks run at 04:30 ET (TimeQey aggregate) and 04:50 ET (all-geo payroll consolidation).
Processing Modes
Delete/insert refresh for scoped pay periods in both modeled tables, plus direct-source CTE evaluation for manual details.
Run Governance
Modeled procedures log via DATASTORE.MODELED.SP_LOG_PROCEDURE_RUN for monitoring and replay analysis.
NEW_ Report View Lineage
| Report View | Immediate Source | Generation Path | Task |
|---|---|---|---|
| NEW_VW_PAYROLL_DATA_ALL | TBLPAYROLLDATA_ALL | SP_GETPAYROLL_ALL scoped delete/reload by pay period | TASK_UPDATEPAYROLLDATA_ALL at 04:50 ET |
| NEW_VW_PAYROLL_EMPLOYEE_ATTRIBUTES | TBLPAYROLLDATA_ALL plus DIM and lookup joins | Same payroll base load; historical/current attributes resolved at query time | TASK_UPDATEPAYROLLDATA_ALL at 04:50 ET |
| NEW_VW_TIMEQEY_AGGREGATE | TBLTIMEQEYAGGRIGATE | SP_UPDATE_TIMEQEY_AGGREGATE scoped delete/reload by pay period | TASK_UPDATE_TIMEQEY_AGGREGATE at 04:30 ET |
| NEW_VW_TIMEQEY_MANUAL_DETAILS | Direct TimeQey and employee source joins | View CTEs query pay period, timesheet, history, and employee systems directly | No PAYROLLDB task/procedure feeder |
End-to-End Processing Flow
- TASK_UPDATE_TIMEQEY_AGGREGATE runs SP_UPDATE_TIMEQEY_AGGREGATE and rebuilds TBLTIMEQEYAGGRIGATE for targeted pay periods.
- TASK_UPDATEPAYROLLDATA_ALL runs SP_GETPAYROLL_ALL and rebuilds TBLPAYROLLDATA_ALL for targeted pay periods across supported geographies.
- NEW_VW_PAYROLL_DATA_ALL and NEW_VW_PAYROLL_EMPLOYEE_ATTRIBUTES read the refreshed consolidated payroll grain.
- NEW_VW_TIMEQEY_AGGREGATE reads the refreshed TimeQey aggregate fact table.
- NEW_VW_TIMEQEY_MANUAL_DETAILS resolves on demand from direct source systems without modeled PAYROLLDB staging.
- Builds pay-period windows for CAN, IND, PHL, TTO, USA, and COL source rules.
- Builds TimeQey supplemental hours from TBLEMPLOYEETIMESHEET with pay-period alignment and valid DLA employee filters.
- Builds geo-specific payroll sets, with pre-aggregation for PHL and COL paths to prevent duplicate amplification.
- Unions geo outputs, deletes affected pay periods from TBLPAYROLLDATA_ALL, and inserts refreshed rows with conversion and hierarchy fields.
- Performs orphan and non-DLA cleanup checks and logs run details through SP_LOG_PROCEDURE_RUN.
Consumes raw payroll source tables, TimeQey activity, EngageHR employee eligibility, DIM_EMPLOYEETS enrichment, and FX-rate lookups.
- Builds country-mapped pay period scope from PROQOR.DBO.TBLPAYPERIOD.
- Supports two scope modes: default last two pay periods per country or month-based backfill with P_MONTHS_BACK.
- Deletes existing rows in TBLTIMEQEYAGGRIGATE for the scoped periods and inserts refreshed aggregates at pay period, domain, country, and activity grain.
- Splits manual versus system metrics by SOURCEID and computes out-of-cycle manual lag indicators above 72 hours.
- Logs run status and timing through SP_LOG_PROCEDURE_RUN.
Produces the activity-level source for NEW_VW_TIMEQEY_AGGREGATE.
- No modeled PAYROLLDB loader table exists between source and report view for this dataset.
- CTEs build pay period scope and latest history rows from TimeQey history tables.
- Final grouped output joins timesheet, activity, employee, EngageHR, and pay-period sources for manual-entry detail and approval-lag reporting.
- Source freshness issues in TimeQey or pay-period tables directly affect this view independently of modeled task success.
Validation for this path is source-system centric, not PAYROLLDB task centric.
Employee Attribute Enrichment Notes
NEW_VW_PAYROLL_EMPLOYEE_ATTRIBUTES uses TBLPAYROLLDATA_ALL as the payroll fact grain, then resolves employee context dynamically at query time from dimensional and lookup objects.
- Historical employee row as of pay period end and current row from DATASTORE.MART.DIM_EMPLOYEETS.
- City and geocoordinates from ENGAGEHR.GETWORKLOCATIONS and DATASTORE.MODELED.TBLWORKLOCATIONS_COORDS.
- Manager p2/p3 attribution through DATASTORE.MODELED.GET_VALUE and DATASTORE.MODELED.FIND_POSITION.
Operational Validation Checklist
- Confirm TASK_UPDATE_TIMEQEY_AGGREGATE completed successfully.
- Confirm TASK_UPDATEPAYROLLDATA_ALL completed successfully.
- Validate expected pay-period row presence in TBLTIMEQEYAGGRIGATE.
- Validate expected pay-period and geo row presence in TBLPAYROLLDATA_ALL.
- If only NEW_VW_TIMEQEY_MANUAL_DETAILS is impacted, inspect source-side TimeQey and pay-period freshness.
Scope note: country-specific payroll tasks and procedures exist in parallel but are not the direct feed path for the four NEW_ report views documented on this page.