Checking end-to-end data integrity from collection to visualisation
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. What does end-to-end integrity checking involve?
2. You collected 250 survey responses. After cleaning, the spreadsheet holds 250 records. What does this record count check show?
3. Raw data has a sales total of $12,400, but the pivot table total is $11,900. What should you do?
4. Which check is an example of tracing a value to the chart?
5. Why should you document your integrity checks?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1=COUNTA(A2:A9)=COUNTA(Clean!A2:A9) | ACompares the number of records in the source with the number in the cleaned data to check none were lost or added | |
| 2=SUM(B2:B9)-SUM(Summary!B2:B9) | BStage: Cleaned | Check: count | Expected: 8 | Actual: 8 | |
| 3Tracing a value to the chart: pick one value in the source data and find the same value in the chart's data table | CThe record counts do not match, so 5 records are missing and the check has failed. It should not be marked as passed. | |
| 4Documenting the checks: recording each stage, what was checked, the expected result and the actual result | D=XLOOKUP("Mia",A2:A9,B2:B9) | |
| 5Only check the total on the final chart and skip the earlier stages | ESubtracts the summary total from the source total; a result of 0 shows the total is unchanged between stages | |
| 6Source has 120 records, cleaned data has 115 records, so mark it as passed | FEnd-to-end checking must happen at every stage. Checking only the end cannot show where an error was introduced. |
C
Definitions
End-to-end integrity checking
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
=______(Raw!A:A)=COUNTA(Clean!A:A)
2.
=IF(___(Data!C:C)=SUM(Viz!B:B),"OK","Fix")
3.
=_______(A2,Data!A:A,Data!C:C)
4.
log.______("Totals match: 5210 = 5210")
5.
if len(raw) __ len(chart_data):
print("Records lost")
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
records_in == 120
B
records_out = 120
C
if records_in == records_out:
D
print('Counts match')
Question 2
A
raw = [12, 15, 9, 20]
B
clean = [12, 15, 9, 20]
C
print(len(raw) == len(raw))
Question 3
A
raw = [12, 15, 9, 20]
B
chart = [12, 15, 9, 20]
C
print(sum(raw) == len(chart))
Question 4
A
raw = [40, 25, 35]
B
total_raw = len(raw)
C
chart_total = 100
D
print(total_raw == chart_total)
Question 5
A
data = {'Mon': 40, 'Tue': 55}
B
chart = 55
C
print(data['Mon'] == chart)
Answer Key
Checking end-to-end data integrity from collection to visualisation
Section A: Quick Questions
Q1
A — Following the data from collection to the final chart and confirming it is unchanged and correct at each stage
Q2
A — No records were lost or added between collection and cleaning
Q3
B — Investigate the stage where the $500 difference appeared and fix it
Q4
A — Finding the Term 2 bar value in the chart and matching it to the source cell it came from
Q5
B — It gives evidence of what was checked, the result and any fixes made at each stage
Section B: Match the Pairs
Q1
=COUNTA(A2:A9)=COUNTA(Clean!A2:A9) → Compares the number of records in the source with the number in the cleaned data to check none were lost or added
Q2
=SUM(B2:B9)-SUM(Summary!B2:B9) → Subtracts the summary total from the source total; a result of 0 shows the total is unchanged between stages
Q3
Tracing a value to the chart: pick one value in the source data and find the same value in the chart's data table → =XLOOKUP("Mia",A2:A9,B2:B9)
Q4
Documenting the checks: recording each stage, what was checked, the expected result and the actual result → Stage: Cleaned | Check: count | Expected: 8 | Actual: 8
Q5
Only check the total on the final chart and skip the earlier stages → End-to-end checking must happen at every stage. Checking only the end cannot show where an error was introduced.
Q6
Source has 120 records, cleaned data has 115 records, so mark it as passed → The record counts do not match, so 5 records are missing and the check has failed. It should not be marked as passed.
Section C: Definitions
Q1
End-to-end integrity checking — Following data through every stage, from collection to chart, and confirming at each stage that it is unchanged and correct, for example by matching record counts and totals.Section D: Fill in the Blanks
Q1
=COUNTA(Raw!A:A)=COUNTA(Clean!A:A)Q2
=IF(SUM(Data!C:C)=SUM(Viz!B:B),"OK","Fix")Q3
=XLOOKUP(A2,Data!A:A,Data!C:C)Q4
log.append("Totals match: 5210 = 5210")Q5
if len(raw) != len(chart_data):
print("Records lost")Section E: Spot the Error
Q1
A — The line uses == (comparison) instead of = (assignment), so records_in is never stored. It should be records_in = 120.
Q2
C — The record count of raw is compared with itself, so the check is always True. It should compare len(raw) with len(clean).
Q3
C — This compares a total with a record count, which are different measures. It should compare sum(raw) with sum(chart).
Q4
B — len() gives the number of records (3), not the total of the values. It should be sum(raw) so the total can be checked against the chart.
Q5
C — The chart bar shows the Tuesday value (55), so the source value traced must be data['Tue'], not data['Mon'].