Module 7: Engineering Data, Spreadsheets, and Structured Analysis

Learning Objectives

  • Design data-first AI workflows that preserve schema, units, identifiers, lineage, and source revisions.
  • Use structured outputs and deterministic transformations for repeatable spreadsheet and tabular analysis.
  • Audit formulas, joins, filters, missing values, outliers, and derived fields without overwriting authoritative source data.
  • Build machine-checkable validation rules and reconciliation totals before trusting generated charts or summaries.
  • Produce editable engineering artifacts while preserving reproducibility and a clear change record.

Data Provenance

Data provenance is the documented origin, transformation history, units, timestamp, ownership, and identifier of a data item so that an engineering result can be traced back to its source.

Data Lineage

Data lineage is the end-to-end record of how source data moves through validation, cleaning, transformation, calculation, aggregation, visualization, and final reporting.

Common Civil Engineering Data Sources

AI-supported data work may involve:

  • materials laboratory results;
  • survey coordinates and field measurements;
  • rainfall, streamflow, and water-quality records;
  • traffic counts and crash datasets;
  • construction progress and quantity records;
  • project schedules and cost reports;
  • sensor and monitoring data;
  • inspection observations;
  • asset inventories;
  • geotechnical logs and test summaries.

The first task is understanding schema, provenance, units, timestamps, identifiers, missingness, and quality before performing higher-level analysis.

Reproducible Engineering Data Workflow

A traceable path from raw engineering data to reviewed analysis.

The sequence keeps the source dataset immutable while transformations and checks occur on controlled copies.

Reproducible Engineering Data WorkflowA traceable path from raw engineering data to reviewed analysis.. Identify source data and provenance → Validate schema, units, IDs, and missing fields; Validate schema, units, IDs, and missing fields → Create controlled working copy; Create controlled working copy → Transform, calculate, and summarize; Transform, calculate, and summarize → Checks and reconciliation pass?; Checks and reconciliation pass? — Yes → Release reviewed table or analysis; Checks and reconciliation pass? — No → Log exceptions and correct logic; Log exceptions and correct logic → Create controlled working copy

Identify source data and provenance → Validate schema, units, IDs, and missing fields; Validate schema, units, IDs, and missing fields → Create controlled working copy; Create controlled working copy → Transform, calculate, and summarize; Transform, calculate, and summarize → Checks and reconciliation pass?; Checks and reconciliation pass? — Yes → Release reviewed table or analysis; Checks and reconciliation pass? — No → Log exceptions and correct logic; Log exceptions and correct logic → Create controlled working copy

  • Identify source data and provenance: terminator
  • Validate schema, units, IDs, and missing fields: process
  • Create controlled working copy: data
  • Transform, calculate, and summarize: process
  • Checks and reconciliation pass?: decision
  • Log exceptions and correct logic: document
  • Release reviewed table or analysis: terminator

Spreadsheet Audit Prompt Pattern

Audit this workbook as an engineering calculation artifact.
1. Identify inputs, formulas, linked sheets, control totals, and outputs.
2. Detect inconsistent units, hard-coded constants, broken references, pattern breaks, and hidden assumptions.
3. Recompute a sample of critical rows independently.
4. Flag outliers, duplicates, and missing values without deleting or replacing them.
5. Distinguish data-quality issues from legitimate engineering extremes.
6. Produce an audit table with cell/range or row ID, issue, evidence, consequence, and recommended check.
Engineering Data Lineage

A compact chain of custody for spreadsheet and tabular analysis.

Data lineage records the path from original records to the result so later reviewers can reproduce the analysis.

Source record
Validated import
Controlled transformation
Calculated output
Reviewed deliverable

Outliers Need Engineering Interpretation

A statistical outlier may represent:

  • an instrument or transcription error;
  • an unusual but valid site condition;
  • a change in material batch or construction process;
  • a storm, traffic event, or operational anomaly;
  • a unit mismatch;
  • a true failure or degradation signal.

AI should help locate unusual values, but a competent reviewer must decide whether exclusion, correction, retention, or investigation is justified.

Common Civil Engineering Data Sources

Typical data families that benefit from structured validation.

Different engineering datasets carry different assumptions, units, and quality risks; provenance should travel with each source.

Survey and geometry
Laboratory tests
Traffic counts
Hydrologic records
Schedules and quantities
Inspection and sensor data

Preserve Formulas, Source IDs, and Raw Data

When AI creates or edits a spreadsheet or generates a script, formulas and transformations should remain visible and traceable. Do not overwrite the raw dataset. Preserve stable row/sample/feature identifiers and document units, constants, source ranges, transformation rules, and assumptions.

Do Not Trust a Polished Workbook Without Sampling It

A spreadsheet can look complete while containing wrong cell references, copied formulas with shifted ranges, mixed units, circular logic, silently overwritten formulas, hidden hard-coded constants, or mismatched chart ranges. Independently recompute representative rows and all high-consequence outputs.

Reproducible Data Workflow

A defensible workflow follows:

  1. preserve raw data;
  2. create a working copy or derived dataset;
  3. validate schema and identifiers;
  4. record cleaning and transformation rules;
  5. standardize units and formats;
  6. identify missing and anomalous values;
  7. perform analysis with explicit formulas or code;
  8. generate charts from traceable ranges or dataframes;
  9. retain transformed data and scripts/formulas;
  10. independently verify key results and control totals.
Outlier Versus Engineering Anomaly

Statistical rarity and engineering invalidity are different judgments.

A statistical outlier is not automatically an error; engineering context determines whether it is a valid extreme observation or a bad record.

May be valid
  • Rare but physically plausible
  • Traceable extreme event
  • Consistent with field context
Needs correction or escalation
  • Impossible unit or sign
  • Duplicate or broken identifier
  • Sensor or transcription failure
  • Contradicts verified source
Data Audit Improvement Loop

Engineering data workflows improve through repeated validation and exception review.

Reproducibility requires retaining both transformation logic and the exceptions discovered during checking.

Reproducible analysis
Validate inputs
Transform
Reconcile results
Review exceptions
Update rules and rerun
↺ repeat when checks fail

Worked Use Cases and Reusable Prompts

The companion Worked Examples contains prompts for CSV audits, formula-pattern review, control-total reconciliation, outlier screening, reproducible chart generation, schema conversion, validation scripts, and engineering data-lineage records.

Schema First, Analysis Second

Before asking an agent to analyze engineering data, define the expected schema: field names, data types, units, allowed nulls, identifiers, time zone, coordinate reference where applicable, and valid ranges. Schema validation should occur before interpretation. This prevents a fluent analysis from masking malformed inputs.

Preserve a Transformation Ledger

Every material data change should be reconstructable. Record the input revision, operation, affected rows or fields, validation result, and output revision. For spreadsheets, preserve formula lineage and distinguish imported values, formulas, AI-generated values, and manually approved overrides.

Use Reconciliation Tests as Release Gates

Examples include row counts before and after joins, quantity or cost totals, unit-balance checks, duplicate-key counts, missing-value counts, control totals, and expected extrema. Run these deterministic tests after AI-assisted transformations and before charts, dashboards, or engineering conclusions are released.

Key Takeaways
  • Good AI-assisted spreadsheet work begins with provenance, lineage, schema, and data quality.
  • Statistical outliers require engineering interpretation rather than automatic removal.
  • Formula transparency, stable identifiers, control totals, and sample recomputation are essential controls.
  • Preserve raw data and document every transformation.
  • Spreadsheet appearance is not evidence of correctness.

References