📄 Data Load and Operational Runbook

Data Load and Operational Runbook

Purpose

This document describes the process for loading CSV files into the Crop Data Warehouse.

Load Sequence

The load process consists of three phases:

  1. Load raw tables

  2. Transform data into staging tables

  3. Load reporting tables

Phase 1 – Load Raw Tables

Import source CSV files into:

  • stage.Job_Raw

  • stage.JobActivity_Raw

  • stage.JobActivityMaterial_Raw

Recommended methods:

  • SQL Server Integration Services (SSIS)

  • SQL Bulk Insert

  • Azure Data Factory

  • Third-party ETL tools

Validation Checks

After import:

Verify record counts.

Example:

SELECT COUNT(*) FROM stage.Job_Raw

Confirm:

  • File row count matches table row count

  • No import failures occurred

Phase 2 – Build Staging Tables

Execute staging procedures.

Job

EXEC CrpRpt.usp_Stage_Job_From_Raw

Job Activity

EXEC CrpRpt.usp_Stage_JobActivity_From_Raw

Job Activity Material

EXEC CrpRpt.usp_Stage_JobActivityMaterial_From_Raw

Validation

Confirm records exist in:

  • CrpRpt.Stage_Job

  • CrpRpt.Stage_JobActivity

  • CrpRpt.Stage_JobActivityMaterial

Review any conversion or validation errors.

Phase 3 – Load Reporting Tables

Execute SCD2 load procedures.

Job

EXEC CrpRpt.usp_Load_Job_SCD2

Job Activity

EXEC CrpRpt.usp_Load_JobActivity_SCD2

Job Activity Material

EXEC CrpRpt.usp_Load_JobActivityMaterial_SCD2

SCD2 Processing

The reporting layer maintains historical records.

When a source record changes:

  • Existing record is expired

  • New version is inserted

  • Historical version remains available

Typical columns include:

  • FromDate

  • ToDate

  • IsCurrent

  • RowHash

Operational Monitoring

The following checks should be completed after every load.

Row Counts

Compare:

  • Source file counts

  • Raw table counts

  • Stage table counts

  • Reporting table counts

Error Review

Investigate:

  • Data conversion failures

  • Missing business keys

  • Duplicate records

  • Invalid dates

Historical Verification

Verify:

  • Current records marked correctly

  • Historical records retained

  • No duplicate current records exist

Recovery Procedure

If a load fails:

  1. Correct source data.

  2. Reload raw tables.

  3. Re-run staging procedures.

  4. Re-run SCD2 load procedures.

  5. Revalidate record counts.

Recommended Load Frequency

The solution supports:

  • Daily loads

  • Weekly loads

  • Monthly loads

  • On-demand loads

The frequency should align with source system data availability and reporting requirements.