Data Processing, Organisation, Cleaning and Validation: formula sheet

Key formulas

Order of the data processing cycle

Collection → Preparation → Input → Processing → Output → Storage

Some books merge or rename stages. Keep this order and say what happens at each stage.

Batch processing rule

Group data → process at scheduled time → no immediate result

Suited to high-volume, non-urgent work such as payroll and bank statements.

Real-time processing rule

Data arrives → processed instantly → immediate action

Used where delay causes loss or danger, for example fraud alerts or process control.

Online processing rule

Transaction entered → processed at once → file updated

The user interacts with the system. A short delay is acceptable, unlike strict real-time.

Data hierarchy

Character → Field → Record → File → Database

Each level is built from the one before it. Reverse the order when a question asks you to break a database down.

Field vs record

Record = set of related fields about one entity

A field is one attribute (e.g. Invoice Date). A record is the full row for one invoice.

Primary key rule

Primary key = unique, non-repeating identifier for each record

Used to link and retrieve records. A foreign key in another file refers to a primary key.

Types of data by structure

Structured | Semi-structured | Unstructured

Structured: fixed rows and columns. Semi-structured: tags, e.g. JSON, XML. Unstructured: no format, e.g. images, emails.

Mean imputation

Replacement value = Σx ÷ n (of the available values)

Suits numeric data without extreme outliers. Use the median when outliers exist.

Outlier fences (IQR rule)

Lower fence = Q1 − 1.5 × IQR; Upper fence = Q3 + 1.5 × IQR; IQR = Q3 − Q1

A value outside the fences is flagged as a possible outlier. Flagged does not mean wrong.

Z-score

z = (x − mean) ÷ standard deviation

A common rule of thumb flags |z| greater than 3. It is a convention, not a law.

Completeness

Completeness % = (Records with required fields filled ÷ Total records) × 100

A simple measure of the completeness dimension.

Duplicate rate

Duplicate % = (Duplicate records ÷ Total records) × 100

Count only the extra copies, not the first occurrence.

Range check rule

Valid if minimum ≤ value ≤ maximum

Decide whether the limits are inclusive. Say so in your answer.

Completeness rate

Completeness % = (Records with the field filled ÷ Total records) × 100

Use it to report how much of a required field is missing.

Consistency check rule

Valid if related fields satisfy their logical condition, e.g. delivery date ≥ order date

Compares two or more fields in the same record.

Validation vs verification

Validation = is it reasonable and in the allowed form? Verification = does it match the source?

A value can pass validation yet fail verification.

Error rate

Error rate % = (Records failing a check ÷ Total records checked) × 100

Report it per check, not as one combined figure.

Min-max normalisation

x′ = (x − min) ÷ (max − min)

Gives values from 0 to 1. Sensitive to outliers because min and max change.

Standardisation (z-score)

z = (x − μ) ÷ σ

μ is the mean and σ the standard deviation. Result has mean 0 and standard deviation 1. Not limited to a fixed range.

ETL sequence

Extract → Transform → Load

Order matters: data is transformed before it is loaded into the target. In ELT, loading happens before transformation.

Data preparation flow

Collect → Integrate → Clean → Transform → Validate → Load/Use

A practical order for writing answers. Textbooks may group or name steps slightly differently.

Quick revision

• Data processing turns raw data into useful information through a sequence of stages.
• Know the order of the processing cycle stages and what each one does.
• Batch processing handles data in groups at intervals; real-time processing handles each transaction as it occurs.
• Data is organised from field to record to file to database.
• Structured data fits fixed rows and columns; unstructured data such as emails and images does not.
• Common quality problems include duplicates, missing values, inconsistent formats, outliers and incorrect entries.
• Cleaning corrects or removes errors in data that already exists.
• Validation applies rules at the point of entry to stop invalid data being accepted.
• Typical validation checks include range, type, format, mandatory field and consistency checks.
• Validation shows data is acceptable by the rules; it does not guarantee that the data is true.
• Transformation changes data into a suitable form, for example by standardising units or formats.
• Integration combines data from different sources into one consistent view for analysis.

Common mistakes

• Mixing up the order of stages, for example placing input before preparation. Fix: Remember that data is prepared and checked before it is entered, so errors do not enter the system.
• Treating online processing and real-time processing as the same. Fix: Real-time needs an immediate response within strict limits to affect an ongoing activity. Online processes each transaction as entered, with the user interacting.
• Calling a whole row a field. Fix: A column heading or single cell value is the field. The whole row about one entity is the record.
• Treating a file as the same as a database. Fix: A file holds similar records of one kind. A database holds several related files that are linked.
• Deleting every record with a missing value. Fix: Delete only when few records are affected and the loss does not bias results. Otherwise impute or collect the missing data.
• Removing all outliers automatically. Fix: Investigate first. A large genuine sale is valid data. Remove or correct only confirmed errors.
• Treating validation and verification as the same thing. Fix: Validation tests against rules. Verification tests against the source or intent. Write one line distinguishing them.
• Calling a blank cell a range or format error. Fix: A missing value is a completeness failure. Range and format checks apply only to values that are present.
• Treating normalisation and standardisation as the same thing. Fix: Normalisation maps to a fixed range such as 0 to 1 using min and max. Standardisation uses mean and standard deviation to give mean 0 and SD 1.
• Writing the ETL stages in the wrong order or mixing up Transform and Load. Fix: Remember the flow: take data out, fix it, put it in. Cleaning and conversion belong to Transform.

Exam tips

• In MCQs, the usual traps are stage order and the difference between batch and real-time. Check timing words such as 'immediately', 'periodically' and 'group'.
• For a 'compare' question, use fixed headings such as timing, speed, cost and use, and write one line under each for every method.
• Write one Indian business example per stage or method. It makes a short answer look complete.
• Do not spend time on rarely asked methods. Master the six stages and batch, real-time and online first.
• Learn the hierarchy in order and attach one business example to each level. Examiners often test it as an MCQ with options in a jumbled order.
• For the difference between structured and unstructured data, write at least three comparison points and one example for each.
• In case-based questions, identify the format first, not the business use. This avoids classification errors.
• Mention the primary key whenever the question talks about records, linking or avoiding duplication.