How to validate large batch ingestion in AEP

Validation steps

1. Batch metadata — record counts and failures per batch in monitoring.
2. Count query — total rows in the dataset vs the source.
3. Distinct keys — confirm unique IDs match expectations.
4. Incremental checks — validate each batch as it lands.
5. Historical comparison — previous total plus new load equals current total.

Example Query Service checks

SELECT COUNT(*) FROM my_dataset;
SELECT COUNT(DISTINCT customer_id) FROM my_dataset;
SELECT customer_id, COUNT(*) c FROM my_dataset GROUP BY customer_id HAVING COUNT(*) > 1;

Clean across files before loading

When data is split across many CSV files, deduplication must happen across all files, not just within each one. Use an ELT pipeline (for example Spark or a cloud ETL service) to trim, standardise and deduplicate, then load clean files.

Do you need an ETL tool?

Decide in discovery: source variety, transformation complexity, volume and frequency, performance needs, existing tooling, budget and timeline. Simple feeds may only need scripts; complex, high-volume pipelines justify a dedicated tool.