dsp-job-data-migration — Post-migration validation¶

Checklist and queries to confirm that migration via dsp-job-data-migration completed successfully and that the destination is consistent.

When to validate¶

Moment	Goal
After each job (L1, L2, L3, area of interest, layers, kpi-job)	Isolate problems by level
After the full sequence (including `kpiCalculationJob`)	Confirm base ready for the DSP and Home KPIs
Before enabling GeoServer/API	Avoid publishing incomplete data

Quick checklist¶

1. Spring Batch status¶

Connect to the destination database (spring.datasource.target / dsp-db), schema data_migration:

psql -h localhost -p 6666 -U postgres -d dsp_db
SET search_path TO data_migration;

Latest runs¶

SELECT i.job_name,
       e.status,
       e.exit_code,
       e.start_time,
       e.end_time
FROM batch_job_execution e
JOIN batch_job_instance i
  ON e.job_instance_id = i.job_instance_id
ORDER BY e.job_execution_id DESC
LIMIT 20;

The most recent row should be COMPLETED. If you see FAILED, use the steps query below and application logs to find the cause.

Steps for the latest run of a job¶

SELECT se.step_name,
       se.status,
       se.read_count,
       se.write_count,
       se.skip_count,
       se.exit_message
FROM batch_step_execution se
JOIN batch_job_execution je
  ON se.job_execution_id = je.job_execution_id
JOIN batch_job_instance ji
  ON je.job_instance_id = ji.job_instance_id
WHERE ji.job_name = 'adminUnitLevel1GeoserverJob'
ORDER BY se.step_execution_id DESC
LIMIT 20;

Job names

Use the bean name (adminUnitLevel1GeoserverJob, etc.), not the kebab-case execution-jobs flag.

Incremental watermark¶

SELECT sync_key,
       source_table,
       watermark_last_event_at,
       last_success_at,
       last_orphan_check_at,
       last_job_execution_id
FROM data_migration.BATCH_JOB_EXECUTION_SYNC_STATE
ORDER BY sync_key;

Reading	Meaning
`watermark_last_event_at` null	No `COMPLETED` load with delta yet
`last_orphan_check_at` old (> 24 h)	Next run should sweep orphans
Job `COMPLETED` but watermark unchanged	No temporal delta (SKIP or orphans only)

To reprocess a job from scratch, delete the row for the corresponding sync-key (and destination data if needed).

2. Source × destination counts¶

Validate both destinations after each job. Adapt schema/table to your YAML. In the official DSP contract:

dsp-db (operational — no full geom)¶

SELECT COUNT(*) AS dsp_db_count
FROM dsp.territory_level_1
WHERE boundary_box IS NOT NULL;

geoserver-db (full geometry)¶

SELECT COUNT(*) AS geoserver_count
FROM dsp.territory_level_1
WHERE geom IS NOT NULL;

The first load should reflect the source filtered by where-clause and creation-date-column IS NOT NULL. Later loads only add records new or updated after the watermark; orphans are removed in the periodic scan.

3. Key integrity and orphans¶

PK on destination¶

SELECT tc.constraint_type, kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.table_schema = kcu.table_schema
WHERE tc.table_schema = 'dsp'
  AND tc.table_name = 'territory_level_1'
  AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE');

IDs and FKs in the official destination are VARCHAR (territory_level_*: varchar(64); AOI/layers: varchar(255) on id).

4. Geometries¶

geoserver-db — full geom¶

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalidas,
  COUNT(*) FILTER (WHERE ST_SRID(geom) <> 4326) AS srid_diferente,
  COUNT(*) FILTER (WHERE geom IS NULL) AS nulas
FROM dsp.territory_level_1;

Replace 4326 with the srid value from the corresponding YAML block. AOI and layers also use geom.

dsp-db — bbox and centroid¶

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE NOT ST_IsValid(boundary_box)) AS bbox_invalidas,
  COUNT(*) FILTER (WHERE ST_SRID(boundary_box) <> 4326) AS bbox_srid_diferente,
  COUNT(*) FILTER (WHERE NOT ST_IsValid(centroid_coordinates)) AS centroid_invalidos,
  COUNT(*) FILTER (WHERE ST_SRID(centroid_coordinates) <> 4326) AS centroid_srid_diferente
FROM dsp.territory_level_1;

Check	Acceptance criteria
`invalidas`	0 (or known list handled separately)
`srid_diferente`	0 relative to YAML `srid`
`nulas`	Consistent with business rules

5. Hierarchy between levels¶

SELECT c.id, c.parent_id
FROM dsp.territory_level_2 c
LEFT JOIN dsp.territory_level_1 p
  ON p.id = c.parent_id
WHERE p.id IS NULL
LIMIT 50;

Repeat the pattern for level-3 → level-2. AOI: territory_level_3_id must exist in dsp.territory_level_3.

6. Parallelization and performance¶

Signal	Interpretation
`write_count` much lower than expected	Geometry filters / writer skips / watermark delta only
Many connection errors	`thread-pool-size` larger than Hikari pool
Slow job with `thread-pool-size: 1`	Expected on large tables
Immediate `SKIP`	No temporal delta — confirm source actually changed after watermark

7. KPI job (`kpiCalculationJob`)¶

Confirm the latest run of the kpiCalculationJob bean (do not confuse it with the kebab-case flag kpi-job):

SELECT i.job_name, e.status, e.exit_code, e.start_time, e.end_time
FROM data_migration.batch_job_execution e
JOIN data_migration.batch_job_instance i ON e.job_instance_id = i.job_instance_id
WHERE i.job_name = 'kpiCalculationJob'
ORDER BY e.job_execution_id DESC
LIMIT 5;

AOI area (dsp-db)¶

SELECT COUNT(*) AS total,
       COUNT(area) AS com_area,
       COUNT(*) FILTER (WHERE area IS NOT NULL AND area > 0) AS area_positiva
FROM dsp.area_of_interest;

Compare with valid geometries on geo-target:

SELECT COUNT(*) AS com_geom
FROM dsp.area_of_interest
WHERE geom IS NOT NULL AND ST_IsValid(geom);

Theme measures (`kpi_measure`)¶

SELECT kpi_name, COUNT(*) AS registros, SUM(value) AS soma
FROM dsp.kpi_measure
GROUP BY kpi_name
ORDER BY kpi_name;

With theme-count: 0, expect zero rows after truncate. With themes enabled, each kpi_name must match the YAML layer-name and card.layer in installation-config.json.

8. GeoServer¶

Check	How
Layer exists	GeoServer UI/REST with same `layer-name` as YAML
Store points to geoserver-db	Check JDBC/PostGIS datastore → `dsp-geoserver-db` (not `dsp-db`)
WMS preview	Bounding box consistent with geoserver-db SQL sample

Common failure signals¶

Symptom	Likely cause	Fix
`batch_job_instance does not exist`	Missing BATCH schema	Run `01_spring_batch_schema.sql`
`ON CONFLICT` error	No PK/unique on destination	Create constraint
Destination count = 0	SKIP job, flags false, wrong JDBC, or watermark already advanced	Review `execution-jobs`, URLs, and `SYNC_STATE`
Null geometries	Wrong geom column mapping	Review `geometry-column` / `column-mapping` (destination: `geom`)
Broken FK between levels	Wrong order or incomplete L1	Re-run L1 → L2 → L3
App starts and exits “ok” with no data	No flag `true`	Enable at least one job
Incremental “lagging”	Null or missing `updated-at-column`	Fill column or reset `sync-key`
Empty or missing theme cards on Home	`kpi-job` did not run, `theme-count` does not match configured layers, or backend still has a cached config	Confirm `kpiCalculationJob` `COMPLETED`; align `kpis.themes[].layer-name` with migrated layers; restart `dsp-backend` after `./config.sh`
AOI area zero or null	Missing/invalid geometry on geo-target, or KPI job did not run	Check `geom` on `dsp.area_of_interest` in geoserver-db; run `kpi-job`

Job overview: Overview.