Data Cleaning Pipeline Sankey Diagram: Template
Show how many records from each source pass validation, are fixed automatically or are rejected, and why. Replace the counts with your own import or ETL numbers.
A data cleaning Sankey follows records from their sources through validation to the warehouse, and breaks the rejected ones down by reason.
10,000 records arrive from three sources. 8,300 pass, 1,000 are fixed automatically and 700 are rejected. The rejected ones are then sorted by why they failed.
The picture makes an import report readable: not just a rejection rate, but which source produces the rejects and which rule catches them.
How to read this diagram
- 9,300 of 10,000 records (93%) are loaded: 8,300 clean and 1,000 fixed.
- Web forms and the spreadsheet import each reject 10% of their records, against 5% for the CRM.
- Missing email is the largest reason for rejection, 300 of 700.
- 1,000 records needed an automatic fix, which is worth checking for what the fix changes.
The data behind it
Invented figures for illustration. Not data about any real business.
| From | To | Amount (records) |
|---|---|---|
| CRM export | Passed validation | 5200 |
| CRM export | Fixed automatically | 500 |
| CRM export | Rejected | 300 |
| Web forms | Passed validation | 2300 |
| Web forms | Fixed automatically | 400 |
| Web forms | Rejected | 300 |
| Spreadsheet import | Passed validation | 800 |
| Spreadsheet import | Fixed automatically | 100 |
| Spreadsheet import | Rejected | 100 |
| Passed validation | Loaded to warehouse | 8300 |
| Fixed automatically | Loaded to warehouse | 1000 |
| Rejected | Missing email | 300 |
| Rejected | Invalid format | 250 |
| Rejected | Duplicate | 150 |
The same data as CSV
source,target,value
CRM export,Passed validation,5200
CRM export,Fixed automatically,500
CRM export,Rejected,300
Web forms,Passed validation,2300
Web forms,Fixed automatically,400
Web forms,Rejected,300
Spreadsheet import,Passed validation,800
Spreadsheet import,Fixed automatically,100
Spreadsheet import,Rejected,100
Passed validation,Loaded to warehouse,8300
Fixed automatically,Loaded to warehouse,1000
Rejected,Missing email,300
Rejected,Invalid format,250
Rejected,Duplicate,150How it works
Take counts from your validation log
Records in per source and the result for each.
Group rejection reasons
Use the rule names your pipeline reports, plus Other.
Enter the flows
Source to result, result to loaded or to a reason.
Check the balance
Rejected should equal the sum of its reasons.
Frequently asked questions
Can this show which rows were rejected?
No. A Sankey shows counts only. Keep the rejected rows themselves in a table.
What if a record fails two rules?
Count it once, under the first rule it failed or the most important, and say which on the chart.
Edit this pipeline Sankey
Open the example as your own chart and enter your record counts.