SQL Data Cleansing Script for Construction Cost Code and Labor Records

Role: Lead Construction Analytics Engineer specializing in ERP job-costing data integrity.

Context

• Staging Timesheet Table: {{raw_timesheet_table}}
• Canonical Cost Code Table: {{master_cost_code_table}}
• Source Date Format Variations: {{date_format_variance}}
• Missing Worker ID Handling: {{null_handling_mode}}
• Max Labor Hours Threshold: {{error_flagging_threshold}}
• Target Validated Table: {{validated_table_name}}

Task

Generate an idempotent ANSI SQL transformation script to clean, validate, and standardize messy subcontractor timecard logs from {{raw_timesheet_table}}, cross-referencing CSI codes against {{master_cost_code_table}}, enforcing hours sanity checks using {{error_flagging_threshold}}, and populating {{validated_table_name}}.

Method

1. Create staging Common Table Expressions (CTEs) that cast and sanitize raw text columns from {{raw_timesheet_table}}.
2. Parse non-standard date entries matching patterns in {{date_format_variance}} into standard ISO 8601 date timestamps.
3. Normalize cost code strings by stripping non-alphanumeric noise and validating against {{master_cost_code_table}}.
4. Apply rule-based resolution for missing worker identifiers following the {{null_handling_mode}} policy.
5. Flag or clip anomalous work-shift entries exceeding {{error_flagging_threshold}} hours in a single 24-hour window.
6. Standardize trade classification codes, upper-casing craft names and trimming whitespace.
7. Insert clean records into {{validated_table_name}} while routing rejected records to an audit table with explicit rejection reason codes.

Constraints

• The SQL script MUST be deterministic and safe to re-run (idempotent) using transactions.
• The script MUST NOT delete unparseable rows; invalid rows must be inserted into an audit quarantine table.
• Use standard ANSI SQL compatible with modern data warehouses (PostgreSQL/Snowflake/BigQuery).
• Include explicit comments before each CTE explaining the transformation rule.

Output format

Provide the solution as a single structured SQL file containing:

1. Header comment detailing source tables, assumptions, and validation logic.
2. DDL statements ensuring {{validated_table_name}} and quarantine tables exist.
3. Main transformation query organized as readable CTEs ending with an INSERT INTO ... SELECT statement.
4. Post-execution verification queries that report row counts and error breakdown.

Self-review

• Check that all variables ({{raw_timesheet_table}}, {{master_cost_code_table}}, {{date_format_variance}}, {{null_handling_mode}}, {{error_flagging_threshold}}, {{validated_table_name}}) are integrated.
• Ensure timestamp conversions handle leap years and time zone shifts without throwing fatal query errors.
• Verify that foreign key mismatches with {{master_cost_code_table}} are caught gracefully.