Conversion Data Issues
This page lists all the problems with the data that were encountered during the data conversion process, and how the issue was resolved.
The problems are numbered, in the order in which they were encountered during the conversion.
Given a choice, earlier problems should be solved before later problems. This allows the later steps in the conversion to receive "correct" data, which help eliminate spurious problems, and aids the discovery of problems hidden by bad data.
Unsolved problems are marked with an *.
(#1) FOLLOW_MAP_TIME duplicate keys
Problem
The data dump says that the FOLLOW_MAP_TIME table has a primary key consisting of, in order, the columns: FMT_FOL_date, FMT_FOL_B_focal_AnimID, FMT_time.
But these columns contain duplicate values.
Bad Data
The duplicate values can be listed (from the raw schema) with:
This query no longer reports results because the data was changed in the original MS Access data.
SELECT *
FROM "FOLLOW_MAP_TIME"
JOIN (SELECT "FMT_FOL_date" AS the_date
, "FMT_FOL_B_focal_AnimID" AS the_animid
, "FMT_time" AS the_time
FROM "FOLLOW_MAP_TIME"
GROUP BY "FMT_FOL_date", "FMT_FOL_B_focal_AnimID", "FMT_time"
HAVING count(*) > 1
) AS fmt
ON ("FOLLOW_MAP_TIME"."FMT_FOL_date" = fmt.the_date
AND "FOLLOW_MAP_TIME"."FMT_FOL_B_focal_AnimID" = fmt.the_animid
AND "FOLLOW_MAP_TIME"."FMT_time" = fmt.the_time);
Solution
Fixed in MS Access 11/17/23 by ICG. Checked all against Tikis. When run in SokweDB, table does not exist
(#2) SUBADULT_ARRIVALS_LOG has a textual SA_first_tiki_date column
Problem
The SUBADULT_ARRIVALS_LOG table has a column that is supposed to contain a date, but instead contains the string "TEXTY".
Likely, the entire row is bad. The row contains:
SA_B_AnimID | SA_first_tiki_date | SA_notes
-------------+--------------------+----------
TEXTY | TEXTY | TEXTY
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select * from "SUBADULT_ARRIVALS_LOG" where "SA_first_tiki_date" = 'TEXTY';
Solution
Fixed in MS Access, 11/16/2023, ICG. Deleted row
(#3) BRECORD_NOTES contains rows where BREC_FOL_date has values that are not just a date
Problem
There are 4 rows in BRECORD_NOTES where the BREC_FOL_date column, supposedly a date, contains time values that are not '00:00:00'.
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select * from "BRECORD_NOTES" where "BREC_FOL_date"::TIME <> '00:00:00';
Solution
Fixed in MS Access, 11/16/2023, ICG Deleted time stamp
(#4) MATING_EVENT contains rows where M_FOL_date has values that are not just a date
Problem
The MATING_EVENT table contains 1 row where the time portion of M_FOL_date is not '00:00:00'.
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select * from "MATING_EVENT" where "M_FOL_date"::TIME WITHOUT TIME ZONE <> '00:00:00';
Solution
Fixed in MS Access, 11/16/2023, ICG Deleted time stamp
(#5) BIOGRAPHY.DepartdateError data discarded
Problem
The BIOGRAPHY.DepartdateError column contains data that was, after discussion, determined to be unusable.
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select * from raw."BIOGRAPHY" where "B_DepartdateError" <> 0;
Solution
Do not convert the data. There is no corresponding column in the new db design.
(#6) BIOGRAPHY.B_AnimID_num column contains the empty string
Problem
The BIOGRAPHY.B_AnimID_num column contains the empty string, instead of NULL.
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select * from raw."BIOGRAPHY" where "B_AnimID_num" = '';
Solution
Changed 11 empty string values to NULL in MS Access. ICG 12/6/2023
(#7) BIOGRAPHY.B_AnimID_num column is textual
Problem
The BIOGRAPHY.B_AnimID_num column contains has a data type of TEXT. The data values all begin with "CH".
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select "B_AnimID", "B_AnimID_num"
from raw."BIOGRAPHY"
where "B_AnimID_num" <> ''
AND "B_AnimID_num" IS DISTINCT FROM NULL
order by "B_AnimID";
Solution
Removed the "CH" prefix and made the column an integer in MS Access. ICG 12/6/2023
(#8) BIOGRAPHY.DadID_publication_info column contains NULL values
No longer a problem as NULLs are now allowed.
(Not) Problem
The BIOGRAPHY.DadID_publication_info column has a data type that allows NULL values.
SokweDB wants only text
Bad Data
In the raw schema run:
This query no longer reports results because the data was changed in the original MS Access data.
select * from raw."BIOGRAPHY" where "B_DadID" IS NULL order by "B_AnimID";
Solution
Changed the NULL to the empty string ("") in MS Access. ICG 12/6/2023
(#9) BIOGRAPHY.DadID column contains non-AnimID values
Problem
The BIOGRAPHY.DadID column has a non-AnimID values.
Bad Data
In the raw schema run:
select "B_AnimID", "B_DadID"
from raw."BIOGRAPHY"
where "B_DadID" is not null
and "B_DadID" <> ''
and not exists (select 1
from raw."BIOGRAPHY" as search
where search."B_AnimID" = raw."BIOGRAPHY"."B_DadID");
Solution
Create the DadIDPrelim column in a BIOGRAPHY_DATA table, and make a BIOGRAPHY view that combines Dad_ID and DadIDPrelim into DadID -- adding the '_prelim' suffix as expected.
(#10) BRECORD_NOTES contains rows where BREC_time has values that are not just a time
Problem
There are 56 rows in BRECORD_NOTES where the BREC_time column, supposedly a time, contains date values that are not '1899-12-30'.
Bad Data
In the raw schema run:
select * from raw."BRECORD_NOTES" where "BREC_time"::DATE <> '1899-12-30';
Solution
The data is fixed in the conversion process.
(#11) KAZ has b_dadid_publication_info, but b_dadid is NULL
Problem
KAZ has dad id publication info of 'Rudicell et al. 2010', but a NULL dadid.
Bad Data
In the clean schema run:
select *
from clean.biography
where b_dadid is NULL
and (b_dadid_publication_info <> ''
or b_dadid_publication_info is null);
Solution
KAZ has 2 potential dads. Introduce a DadIDStatus column, to replace the DadIDPrelim column and have a code that describes what's going on with KAZ.
Fixed in the MS Access data; KAZ was assigned the UNK individual as the dad. Problem will be marked resolved with the upload of the next MS Access database dump.
(#12) 9 BIOGRAPHY rows have BirthComm values that are not COMM_IDS.CommID values
Problem
There are 9 BIOGRAPHY rows with BirthComm values that are not valid COMM_IDS, their communities do not exist.
Bad Data
In the clean schema run:
select *
from easy.biography
where b_birthgroup is not NULL
and not exists (select 1
from easy.community_lookup
where community_lookup.cl_community_id
= biography.b_birthgroup);
Solution
Added KL and KL_KK to CommIds. KL = Kalande, KL_KK= Kasekela/kalande codes are created during conversion
Fixed in commit 5d85b83bfe1a.
(#13) COMM_MEMBS rows place individuals in a community, that is not their birth community, before their BIOGRAPHY.EntryDate
Problem
13 COMM_MEMBS rows place individuals into a community, that is not their birth community, before BIOGRAPHY says they entered the community.
update: 12 rows as of 2026-07-19
Bad Data
In the clean schema run:
select b.b_animid, b.b_birthgroup, b.b_entrydate, cm.cm_start_date
from clean.community_membership as cm
join clean.biography as b
on (b.b_animid = cm.cm_b_animid)
where b.b_birthgroup is distinct from cm.cm_cl_community_id
and cm.cm_start_date < b.b_entrydate
order by b.b_animid, cm.cm_start_date;
Solution
Talk to Karl about kk_P0 and kk_P1
(#14) COMM_MEMBS rows place individuals in a community after their BIOGRAPHY.EndDate
Problem
3 COMM_MEMBS rows place individuals into a community after BIOGRAPHY says they left the community.
Bad Data
In the clean schema run:
select b.b_animid, cm.cm_end_date
from clean.community_membership as cm
join clean.biography as b
on (b.b_animid = cm.cm_b_animid)
where cm.cm_end_date > b.b_departdate
order by b.b_animid, cm.cm_end_date;
Solution
ICG EVL fixed BH, HAI, TZB2 in Access. Need to talk to Karl about MG, RO, WD. Treat like KL chimps in Biography?
(#15) TT is placed in a community twice on the same day
Problem
An individual may not be in more than one community (or even twice in the same community) on any given day.
Bad Data
In the clean schema run:
select first.cm_b_animid as anim_id
, first.cm_start_date as first_start_date
, first.cm_end_date as first_end_date
, first.cm_cl_community_id as first_community_id
, first.cm_start_source as first_start_source
, first.cm_end_source as first_end_source
, second.cm_start_date as second_start_date
, second.cm_end_date as second_end_date
, second.cm_cl_community_id as second_community_id
, second.cm_start_source as second_start_source
, second.cm_end_source as second_end_source
from clean.community_membership as first
join clean.community_membership as second
on (first.cm_b_animid = second.cm_b_animid
and first.cm_start_date < second.cm_start_date)
where first.cm_end_date >= second.cm_start_date;
Solution
Fixed in MS Access 2/1/2024 Changed end date of KK_P1 membership to 8/14/2022
ICG
(#16) There are 31 rows in COMM_MEMB_LOG where MadeBy is NULL
Problem
There are 31 rows in COMM_MEMB_LOG where MadeBy is NULL, and the column does not allow NULL values.
Bad Data
In the clean schema run:
select * from clean.community_membership_update_log where made_by is null;
Solution
See problem #18. Fixed in commit 7a48fb2c4aad3b5bd4
(#17) There are 64 rows in COMM_MEMB_LOG where the chimp_id is not on BIOGRAPHY
Problem
There are 64 rows in COMM_MEMB_LOG where the chimp_id is not a BIOGRAPHY.AnimID.
Bad Data
In the clean schema run:
select date_of_update, chimp_id
from clean.community_membership_update_log as log
where not exists (select 1
from clean.biography
where biography.b_animid = log.chimp_id);
Solution
Make this a "soft" error. Fixed in commit 4c9626b304.
(#18) There are 31 rows in COMM_MEMB_LOG where the MadeBy is NULL
Problem
There are 31 rows in COMM_MEMB_LOG where MadeBy is NULL, and the column does not allow NULLs.
Bad Data
In the clean schema run:
select * from clean.community_membership_update_log where made_by is null;
Solution
Create an unknown person (UNK), and when the person is NULL, use the unkonwn person.
This allows us to make the unknown person "inactive", preventing them from being used in newly entered data. The alternative, allowing NULL MadeBy values, would allow new "bad data", and NULL values make querying harder.
Fixed in commit 7a48fb2c4aad3b5b.
(#19) There are 18 rows in BIOGRAPHY_LOG where the MadeBy is NULL
Problem
There are 18 rows in BIOGRAPHY_LOG where MadeBy is NULL, and the column does not allow NULLs.
Bad Data
In the clean schema run:
select * from clean.biography_update_log where made_by is null;
Solution
Create an unknown person (UNK), and when the person is NULL, use the unkonwn person. (See Problem #18)== * (#19) There are 18 rows in BIOGRAPHY_LOG where the MadeBy is NULL ==
Fixed in commit 8a528cc7d1a9fb.
update 2026-07-26:
The 18 problematic values are zero-length strings, not NULL, so they pass through the ELSE made_by branch and violate the foreign key as madeby=(). This solution was hardened with commit 6279986f5f77cab69d19733925f173eadca23b60.
(#20) There are 3 rows in BIOGRAPHY_LOG where the Rationale is NULL
Problem
There are 3 rows in BIOGRAPHY_LOG where Rationale is NULL. The column does not allow NULLs; normally the conversion program would convert NULL to the empty string. But the Rationale column requires there be (non-empty) textual data.
Bad Data
In the clean schema run:
select * from clean.biography_update_log where update_rationale is null;
Solution
ICG fixed in MS Access 2/8/2024. Updated rationale to 'routine update'.
(#21) There are 6 rows in BIOGRAPHY_LOG where the update_escription is NULL
Problem
There are 6 rows in BIOGRAPHY_LOG where Description is NULL. It appears that the description was put into the Rationale column, sometimes along with some rationale.
Bad Data
In the clean schema run:
select * from clean.biography_update_log where update_description is null;
Solution
ICG fixed in MS Access 2/8/2024, using information in update_rationale
(#22) There are 85 rows in BIOGRAPHY_LOG where the chimp_id is not on BIOGRAPHY
Problem
There are 85 rows in BIOGRAPHY_LOG where the chimp_id is not a BIOGRAPHY.AnimID.
Bad Data
In the clean schema run:
select *
from clean.biography_update_log as log
where not exists (select 1
from clean.biography
where biography.b_animid = log.chimp_id);
Solution
Make this a "soft" error. Fixed in commit c96555f9f326.
(#23) There are 65 rows in BIOGRAPHY_LOG where the MadeBy is not on PEOPLE
Problem
There are 65 rows in BIOGRAPHY_LOG where the MadeBy is not on PEOPLE, but there is MadeBy data.
These are "combination" ID errors, where multiple people are entered instead of a single people code.
Bad Data
In the clean schema run:
select *
from clean.biography_update_log as log
where not exists (select 1
from clean.people
where people.person = log.made_by)
and made_by is not null;
Solution
Fixed in MS Access by ICG 2/8/2024. Changed multiple IDs to the one who made the final change.
(#24) Follow starts are not first arrivals
Problem
There are 6,397 cases where the old FOLLOW table's columns "fol_time_begin" is not the first arrival time, fa_time_start, on FOLLOW_ARRIVAL.
The The Gombe Chimpanzee Database Handbook says that this should never happen, because the follow start is supposed to be the first arrival. Is there additional data in the old fol_time_begin, like the actual start the observers started working? If not, which data is correct, the one in FOLLOW or the one in FOLLOW_ARRIVAL?
Bad Data
WITH spans AS
(SELECT fa_fol_date, fa_fol_b_focal_animid
, MIN(fa_time_start) AS min_start
FROM clean.follow_arrival
WHERE fa_b_arr_animid = fa_fol_b_focal_animid
GROUP BY fa_fol_date, fa_fol_b_focal_animid)
SELECT spans.*, follow.fol_time_begin
FROM spans
JOIN clean.follow
ON (follow.fol_date = spans.fa_fol_date
AND follow.fol_b_animid = spans.fa_fol_b_focal_animid)
WHERE NOT EXISTS
(SELECT 1
FROM clean.follow
WHERE follow.fol_date = spans.fa_fol_date
AND follow.fol_b_animid = spans.fa_fol_b_focal_animid
AND spans.min_start = follow.fol_time_begin)
ORDER BY spans.fa_fol_date, spans.fa_fol_b_focal_animid;
Solution
This is data entry error, ignore the problem and ignore the data in the follow.fol_time_begin.
(#25) Follow ends are not last arrivals
Problem
There are 6,570 cases where the old FOLLOW table's columns "fol_time_end" is not the last arrival time, fa_time_end, on FOLLOW_ARRIVAL.
The The Gombe Chimpanzee Database Handbook says that this should never happen, because the follow end is supposed to be the first arrival/last departure. Is there additional data in the old fol_time_end, like the actual end-time the observers started working? If not, which data is correct, the one in FOLLOW or the one in FOLLOW_ARRIVAL?
Bad Data
WITH spans AS
(SELECT fa_fol_date, fa_fol_b_focal_animid
, MAX(fa_time_end) AS max_end
FROM clean.follow_arrival
WHERE fa_b_arr_animid = fa_fol_b_focal_animid
GROUP BY fa_fol_date, fa_fol_b_focal_animid)
SELECT spans.*, follow.fol_time_end
FROM spans
JOIN clean.follow
ON (follow.fol_date = spans.fa_fol_date
AND follow.fol_b_animid = spans.fa_fol_b_focal_animid)
WHERE NOT EXISTS
(SELECT 1
FROM clean.follow
WHERE follow.fol_date = spans.fa_fol_date
AND follow.fol_b_animid = spans.fa_fol_b_focal_animid
AND spans.max_end = follow.fol_time_end)
ORDER BY spans.fa_fol_date, spans.fa_fol_b_focal_animid;
Solution
This is data entry error, ignore the problem and ignore the data in the follow.fol_time_end.
(#26) Mismatch of start-in-nest on follow and follow_arrival
Problem
There are 1,848 3,369 follows where the follow arrival says the focal started in the nest but the follow does not, or vice-versa.
Bad Data
WITH spans AS
(SELECT fa_fol_date, fa_fol_b_focal_animid
, MIN(fa_time_start) AS min_start
FROM easy.follow_arrival
WHERE fa_b_arr_animid = fa_fol_b_focal_animid
GROUP BY fa_fol_date, fa_fol_b_focal_animid)
SELECT spans.*, follow.fol_time_begin, follow.fol_flag_begin_in_nest
, first_arrivals.fa_type_of_nesting
FROM easy.follow
JOIN spans
ON (follow.fol_date = spans.fa_fol_date
AND follow.fol_b_animid = spans.fa_fol_b_focal_animid)
JOIN easy.follow_arrival
AS first_arrivals
ON (first_arrivals.fa_fol_date = follow.fol_date
AND first_arrivals.fa_fol_b_focal_animid = follow.fol_b_animid
AND first_arrivals.fa_time_start = spans.min_start)
WHERE (((first_arrivals.fa_type_of_nesting = 1
OR first_arrivals.fa_type_of_nesting = 3)
AND follow.fol_flag_begin_in_nest = 0)
OR (first_arrivals.fa_type_of_nesting <> 1
AND first_arrivals.fa_type_of_nesting <> 3
AND follow.fol_flag_begin_in_nest = 1))
ORDER BY spans.fa_fol_date, spans.fa_fol_b_focal_animid;
Solution
Ignore. Default to data from Follow_Arrival
Remarks
When applying the solution, solving both problem #26 and #27, and updating follow_arrival so that it is the one source of truth used by the conversion, there are 297 follow_arrival rows updated.
This implies that it is primarily the follow table that does not mark the individual as being in a nest when they should be in a nest. ("Should be", according to the accepted solution.)
(#27) Mismatch of end-in-nest on follow and follow_arrival
Problem
There are 3,804 follows where the follow arrival says the focal ended in the nest but the follow does not, or vice-versa.
Bad Data
WITH spans AS
(SELECT fa_fol_date, fa_fol_b_focal_animid
, MAX(fa_time_end) AS max_end
FROM easy.follow_arrival
WHERE fa_b_arr_animid = fa_fol_b_focal_animid
GROUP BY fa_fol_date, fa_fol_b_focal_animid)
SELECT spans.*
, follow.fol_time_end, follow.fol_flag_end_in_nest
, last_arrivals.fa_type_of_nesting
FROM easy.follow
JOIN spans
ON (follow.fol_date = spans.fa_fol_date
AND follow.fol_b_animid = spans.fa_fol_b_focal_animid)
JOIN easy.follow_arrival
AS last_arrivals
ON (last_arrivals.fa_fol_date = follow.fol_date
AND last_arrivals.fa_fol_b_focal_animid = follow.fol_b_animid
AND last_arrivals.fa_time_end = spans.max_end)
WHERE (((last_arrivals.fa_type_of_nesting = 2
OR last_arrivals.fa_type_of_nesting = 3)
AND follow.fol_flag_end_in_nest = 0)
OR (last_arrivals.fa_type_of_nesting <> 2
AND last_arrivals.fa_type_of_nesting <> 3
AND follow.fol_flag_end_in_nest = 1))
ORDER BY spans.fa_fol_date, spans.fa_fol_b_focal_animid;
Solution
Ignore the problem. Default to Follow_Arrival
Remarks
See the remarks for problem #26.
(#28) The FOLLOW.FOL_distance_traveled column is not converted
Problem
The FOLLOW.FOL_distance_traveled column has no corresponding column in the new database design. The conversion process does not check that the value of this column is consistent with the other data in the database from which it is computed.
Solution
This is a computed column and does not need to be converted. The desired value is computed in the design of the new database.
The assumption is that the "raw" data from which this value is computed in the MS Access database is correct.
(#29) The FOLLOW.Brecord_notes column is not converted
Problem
The FOLLOW.Brecord_notes column has no corresponding column in the new database design.
Solution
This column is used for administrative purposes and does not need to be in the new database design.
(#30) Some follows have no community
Problem
There are 13 follows with no community.
Bad Data
select * from clean.follow where fol_cl_community_id is null order by fol_date, fol_b_animid;
solution
Solved
(#31) Some follows have an animid with trailing spaces
Problem
There are 9 39 follows with animids that don't exist, because they have trailing spaces. These are comprised of 9 23 distinct animids.
Bad Data
Cleaned up in clean schema, so query easy schema.
select rtrim(fol_b_animid) from easy.follow where rtrim(fol_b_animid) <> fol_b_animid order by fol_b_animid;
Solution
Remove the trailing spaces in the conversion process.
(#32) Some follows have an animid that is lower-case
Problem
There is 1 follows with an animids that don't exist, because it is lower-case.
Bad Data
Cleaned up in clean schema, so query easy schema.
select * from easy.follow where upper(fol_b_animid) <> fol_b_animid order by fol_b_animid;
Solution
Convert the animid to upper-case in the conversion process.
(#33) Some follows have an animid that does not exist, even after animid cleanup
Problem
There are 14 follows with animids that don't exist, even after cleanup that removes trailing spaces and forces upper-case. (Some of these may be due to prior errors.) These are comprised of 3 distinct animids.
Bad Data
-- The 14 follows with bad animids
select *
from clean.follow
where not exists
(select 1
from clean.biography
where biography.b_animid = upper(rtrim(follow.fol_b_animid)))
order by fol_date, fol_b_animid;
-- The 3 animids involved
select distinct follow.fol_b_animid
from clean.follow
where not exists
(select 1
from clean.biography
where biography.b_animid = upper(rtrim(follow.fol_b_animid)))
order by follow.fol_b_animid;
Solution
Solved
(#34) There are duplicate animid, date combinations on FOLLOW
Problem
There are 2 sets of duplicate animid, date combinations on the FOLLOW table, for a total of 4 rows.
Bad Data
-- The duplicate animid, date combinations select follow.fol_b_animid, follow.fol_date, count(*) from clean.follow group by follow.fol_b_animid, follow.fol_date having count(*) > 1 order by fol_b_animid, fol_date;
-- The duplicate rows
with dups as (
select follow.fol_b_animid, follow.fol_date
from clean.follow
group by follow.fol_b_animid, follow.fol_date
having count(*) > 1)
select *
from clean.follow
join dups on (follow.fol_b_animid = dups.fol_b_animid
and follow.fol_date = dups.fol_date)
order by follow.fol_b_animid, follow.fol_date;
Solution
Solved
(#35) There are follows done before a focal was under study
Problem
There are 5 follows that are done before their focal was under study, before the focal's EntryDate.
Bad Data
select follow.*, biography.b_entrydate
from clean.follow
join clean.biography on (biography.b_animid = follow.fol_b_animid)
where biography.b_entrydate > follow.fol_date
order by follow.fol_date, follow.fol_b_animid;
Solution
Solved
(#36) FOLLOW_OBSERVERS.Period is not checked against follow start or stop times
Problem
The conversion process uses the clean.follow columns of fol_am_observer_1, fol_am_observer_2, fol_pm_observer_1, and fol_pm_observer_2 to populate FOLLOW_OBSERVERS. (The *_observer_1 column going into FOLLOW_OBSERVERS.OBS_BRec and the *_observer_2 column going in OBS_Tiki.)
If both the *_observer_1 and the *_observer_2 columns are either NULL or the empty string (after space trimming), the no row is created for the respective time period.
There are no checks done to ensure that the time periods of the follow have any relation to the FOLLOW_OBSERVERS.Period value.
Solution
Ignore the problem. (If someone cares, add a query to the warning system.)
(#37) Some follows have no recorded observers
Problem
There are 56 follows with no recorded observers, but the system requires there be a FOLLOW_OBSERVERS record related to the follow.
See problem #36 for a description of the follow observer conversion process.
Bad data
select *
from clean.follow
where coalesce(btrim(fol_am_observer_1), '') = ''
and coalesce(btrim(fol_am_observer_2), '') = ''
and coalesce(btrim(fol_pm_observer_1), '') = ''
and coalesce(btrim(fol_pm_observer_2), '') = ''
order by fol_date, fol_b_animid;
Solution
Make the "NONE" (no observer) person the observers.
Create a "UNK" (unknown) time period in PERIODS, and make that the time period.
(#38) Some follows have only one observer, but the system wants two
Problem
There are follows with only one observer, but the system requires a FOLLOW_OBSERVERS record have a value in both OBS_BRec and OBS_Tiki.
See problem #36 for a description of the follow observer conversion process.
Solution
Create a "NONE" person, and make that person the observer when there's otherwise not a value.
(#39) In follow, there are observers that are 2 people
Problem
In the follow table, in columns fol_am_observer_1, fol_am_observer_2, fol_pm_observer_1, fol_pm_observer_2, there are 30 rows (28, really, because of n/a) that appear to represent 2 people.
These look like names, separated by the "/" character.
Bad data
WITH new_people AS (
SELECT BTRIM(follow.fol_am_observer_1) AS person
FROM clean.follow
WHERE follow.fol_am_observer_1 IS NOT NULL
GROUP BY BTRIM(follow.fol_am_observer_1)
UNION
SELECT BTRIM(follow.fol_am_observer_2) AS person
FROM clean.follow
WHERE follow.fol_am_observer_2 IS NOT NULL
GROUP BY BTRIM(follow.fol_am_observer_2)
UNION
SELECT BTRIM(follow.fol_pm_observer_1) AS person
FROM clean.follow
WHERE follow.fol_pm_observer_1 IS NOT NULL
GROUP BY BTRIM(follow.fol_pm_observer_1)
UNION
SELECT BTRIM(follow.fol_pm_observer_2) AS person
FROM clean.follow
WHERE follow.fol_pm_observer_2 IS NOT NULL
GROUP BY BTRIM(follow.fol_pm_observer_2)
)
, uniq_people AS (
SELECT new_people.person
FROM new_people
WHERE new_people.person <> ''
GROUP BY new_people.person
)
SELECT uniq_people.person
FROM uniq_people
JOIN uniq_people AS up
ON (LOWER(uniq_people.person) = LOWER(up.person))
WHERE uniq_people.person <> up.person
AND STRPOS(uniq_people.person, '/') <> 0
GROUP BY uniq_people.person
ORDER BY LOWER(uniq_people.person), uniq_people.person;
Solution
Ignore the problem. Mark all people containing a "/" character as not active, so they can't be used in the future.
(#40) In follow, there are observers that differ only by character case
Problem
In the follow table, in columns fol_am_observer_1, fol_am_observer_2, fol_pm_observer_1, fol_pm_observer_2, there are 511 names that differ only by character case. So, about half that in terms of unique names.
This is complicated to account for and exclude duplicates in the conversion process. So resolution of this is holding back additional conversion work.
Bad data
Note the use of the easy schema, instead of the
clean schema. This is because the observer data
in the clean schema has been case-normalized.
WITH new_people AS (
SELECT BTRIM(follow.fol_am_observer_1) AS person
FROM easy.follow
WHERE follow.fol_am_observer_1 IS NOT NULL
GROUP BY BTRIM(follow.fol_am_observer_1)
UNION
SELECT BTRIM(follow.fol_am_observer_2) AS person
FROM easy.follow
WHERE follow.fol_am_observer_2 IS NOT NULL
GROUP BY BTRIM(follow.fol_am_observer_2)
UNION
SELECT BTRIM(follow.fol_pm_observer_1) AS person
FROM easy.follow
WHERE follow.fol_pm_observer_1 IS NOT NULL
GROUP BY BTRIM(follow.fol_pm_observer_1)
UNION
SELECT BTRIM(follow.fol_pm_observer_2) AS person
FROM easy.follow
WHERE follow.fol_pm_observer_2 IS NOT NULL
GROUP BY BTRIM(follow.fol_pm_observer_2)
)
, uniq_people AS (
SELECT new_people.person
FROM new_people
WHERE new_people.person <> ''
GROUP BY new_people.person
)
SELECT uniq_people.person
FROM uniq_people
JOIN uniq_people AS up
ON (LOWER(uniq_people.person) = LOWER(up.person))
WHERE uniq_people.person <> up.person
GROUP BY uniq_people.person
ORDER BY LOWER(uniq_people.person), uniq_people.person;
Solution
Use the mixed case code when such exists. This involves changing the observer columns in the follow table. See the notes.
(#41)There are follow arrivals with NULL nesting information
Problem
In the follow_arrival table, there are 11 rows with a NULL fa_type_of_nesting.
Bad data
select * from clean.follow_arrival where fa_type_of_nesting IS NULL;
Solution
Solved
(#42) There are follow arrivals with NULL sexual cycle information
Problem
In the follow_arrival table, there are 222 rows with a NULL fa_type_of_cycle.
Bad data
select * from clean.follow_arrival where fa_type_of_cycle is null;
Solution
Make a MISS CYCLE_STATES value and use that for the arrivals with no cycle information.
Problem
In the follow_arrival table, there are 732 276 75 rows with a focal and a date that have no matching information on the follow table.
Bad data
SELECT *
FROM clean.follow_arrival
WHERE NOT EXISTS
(SELECT 1
FROM clean.follow
WHERE follow.fol_b_animid = follow_arrival.fa_fol_b_focal_animid
AND follow.fol_date = follow_arrival.fa_fol_date)
ORDER BY follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid;
Solution
5/5/2026 ICG FIXED MANY ROWS IN MS ACCESS
Most of the rest are hand-entered juvenile arrivals from Brec. Spot checks have Brec notes but no physical tiki Need to investigate further - brec swahili? Eventually the rest of the individuals in the Brec notes need to be entered by hand.
Should be solved by the WATCHES table
For now, allow these rows
Remarks on Solution
There is no follow to attach the arrival to. No WATCHES row. So there is no way to get the data in and simply write a warning.
Allowing these rows requires a re-design of the database and additional "hard" rules. Which is going to take time to program, and delay finishing. And, it will take additional time to rip-out the rules when you finally resolve the data issue.
What is needed is to make a new WATCHES.Type just to allow for arrivals with no related follow, and ensure that only arrivals without follows are attaching to this new Type.
The only "easy" alternative I can think of is:
Don't convert this data at this time. Convert it when we get around to fixing the data. In the mean time, anyone who wants to work with this data can get it from the "clean" schema, using the query provided in the problem #43 section, and figure out how to integrate it with whatever they are working on.
(#44) There are follow arrivals where the arriving chimp arrives before being under study
Problem
In the follow_arrival table, there are 217 49 rows where the fa_b_arr_animid, the arriving individual, has an entry date after the date of the follow.
Bad data
select follow_arrival.*, biography.b_entrydate, biography.b_sex
from clean.follow_arrival
join clean.biography
on (biography.b_animid = follow_arrival.fa_b_arr_animid)
where biography.b_entrydate > follow_arrival.fa_fol_date
order by biography.b_animid, follow_arrival.fa_fol_date;
Solution
Something is wrong with community entry date or biography
SN1 fixed in MS Access ICG 5/19/2026
Elo Confirmed CT and SG were with CA on 10/23/1980 from brec swahili
RESOLVED
(#45) The follow_arrival.fa_update column is not preserved
Problem
There is nowhere in the current design to store the values in the follow_arrival.fa_update column.
Solution
Add a temporal extension to Postgres to make the db into a temporal database to track change history and be able to see data as it existed at any point in time.
FOLLOWUP WITH KARL. Will re-review this should a temporal extension be out of budget, etc.
ASK KARL WHY THIS COLUMN CAN'T GO IN
There is now a ARRIVALS.Updated column. This provides a place to put the existing data. Going forward, a PG extension (see below) can be installed for more functionality.
Wouldn't you rather have a temporal database? (Simple description in the top paragraph here.) This would allow you to "time travel" and look at the database content at any point in time. And, it puts a "last changed" timestamp on every row, of every table you say to track temporally, automatically.
If so, install one of these extensions to PostgreSQL. (Or, as a separate project, I will improve one of them so that the people who don't need to time travel don't see any changes to the existing database at all. What I don't like about most of the extensions listed is that they add extra columns to existing tables, as well as adding extra "history" tables in places where they clutter up what users really need to see.)
(#46) There are follow_arrivals where non-females have a cycle code that is other than n/a
Problem
There are 169 168 follow_arrival rows, for non-female arriving individuals, that have a sexual cycle code (fa_type_of_cycle) that is not n/a.
Bad data
select follow_arrival.*, biography.b_sex
from clean.follow_arrival
join clean.biography
on (biography.b_animid = follow_arrival.fa_b_arr_animid)
where biography.b_sex <> 'F'
and follow_arrival.fa_type_of_cycle <> 'n/a'
order by follow_arrival.fa_fol_date
, follow_arrival.fa_b_arr_animid;
To summarize by sex and cycle code:
select biography.b_sex, follow_arrival.fa_type_of_cycle, count(*)
from clean.follow_arrival
join clean.biography
on (biography.b_animid = follow_arrival.fa_b_arr_animid)
where biography.b_sex <> 'F'
and follow_arrival.fa_type_of_cycle <> 'n/a'
group by biography.b_sex, follow_arrival.fa_type_of_cycle order by biography.b_sex
, follow_arrival.fa_type_of_cycle;
Solution
FIX THE FEW ACTUAL MALES THAT DON'T HAVE AN n/a
Change non-females (INCLUDING UNKNOWN SEX) who have a cycle code of 0 to a cycle code of n/a. IAN TO FIX IN ACCESS
IAN FIXED IN ACCESS 3/25/2026
* (#47) There are follow_arrivals where females that are too young have a cycle code of U
Problem
There are 16 follow_arrival rows, for female arriving individuals, that have a sexual cycle code (fa_type_of_cycle) of U but are less than 5 years of age.
Naturally, this number will change if the age limit is changed, but this is here as a placeholder.
Note: The test is against the birthdate, not the minimum possible birthdate.
Bad data
SELECT fa.*
, b.b_sex
, b.b_birthdate
, AGE(fa.fa_fol_date, b.b_birthdate) AS age_at_follow
, INTERVAL '5 years'
- AGE(fa.fa_fol_date, b.b_birthdate) AS younger_than_five_by
FROM clean.follow_arrival AS fa
JOIN clean.biography AS b
ON b.b_animid = fa.fa_b_arr_animid
WHERE b.b_sex = 'F'
AND fa.fa_type_of_cycle = 'U'
AND b.b_birthdate > fa.fa_fol_date - INTERVAL '5 years'
ORDER BY fa.fa_fol_date
, fa.fa_fol_b_focal_animid
, fa.fa_b_arr_animid;
Note: The original query used the wrong inequality symbol.
Solution
Changing the limit from 6 years of age to 5 reduced the number of outstanding problems to 16.
The rest will have to be fixed in the data. Alternately, we can adjust the hard limit, and set a soft limit of 5 years in the warning system with a note to change the hard limit back once the errors are resolved.
Resolution: Fixed in the data.
UPDATE 2026-07-23 it does not appear that these data were fixed, please see below for a summary of this issue and related Problem #49:
UPDATE 2026-08-14: fixed in Access by ICG
UPDATE 2026-08-19: improved but there are still 12 offending records
| problem | problem_description | age_years | current_cycle | youngest_exact_age | oldest_exact_age | offending_rows | problem_total |
|---|---|---|---|---|---|---|---|
| 47 | U assigned before age 5 | 1 | U | 1 year 4 mons 5 days | 1 year 4 mons 5 days | 1 | 21 |
| 47 | U assigned before age 5 | 2 | U | 2 years 11 mons 26 days | 2 years 11 mons 26 days | 1 | 21 |
| 47 | U assigned before age 5 | 3 | U | 3 years 3 mons 30 days | 3 years 11 mons 1 day | 10 | 21 |
| 47 | U assigned before age 5 | 4 | U | 4 years 5 mons 21 days | 4 years 9 mons 18 days | 9 | 21 |
| 49 | U assigned at age 15 or older | 15 | U | 15 years 1 mon 2 days | 15 years 10 mons 14 days | 16 | 38 |
| 49 | U assigned at age 15 or older | 16 | U | 16 years 19 days | 16 years 11 mons 6 days | 7 | 38 |
| 49 | U assigned at age 15 or older | 17 | U | 17 years 1 mon 19 days | 17 years 1 mon 19 days | 1 | 38 |
| 49 | U assigned at age 15 or older | 18 | U | 18 years 1 mon 18 days | 18 years 8 mons 16 days | 6 | 38 |
| 49 | U assigned at age 15 or older | 32 | U | 32 years 1 mon 21 days | 32 years 5 mons 14 days | 4 | 38 |
| 49 | U assigned at age 15 or older | 33 | U | 33 years 6 days | 33 years 6 days | 4 | 38 |
option matrix:
| Value | Meaning | Accepted under age 5? |
|---|---|---|
0 |
Not swollen | Yes |
MISS |
Missing data | Yes |
U |
Adolescent swelling | No |
0.25, 0.5, 0.75, 1 |
Increasing observed swelling | No—female must be at least 8 |
n/a |
Male/not applicable | No—prohibited for females |
* (#48) There are follow arrivals where the arriving chimp arrives after finishing being under study
Problem
In the follow_arrival table, there are 239 324 rows where the fa_b_arr_animid, the arriving individual, has a departure date before the date of the follow.
Bad data
select follow_arrival.*, biography.b_departdate, biography.b_sex
from clean.follow_arrival
join clean.biography
on (biography.b_animid = follow_arrival.fa_b_arr_animid)
where biography.b_departdate < follow_arrival.fa_fol_date
order by biography.b_animid, follow_arrival.fa_fol_date;
SOLUTION
IAN TO FIX IN ACCESS - CHECK DATES OF FOLLOWS AND ACCURACY OF IDS
* (#49) There are follow_arrivals where females that are too old have a cycle code of U
Problem
There are 127 98 86 48 follow_arrival rows, for female arriving individuals, that have a sexual cycle code (fa_type_of_cycle) of U but are more than 14 years of age.
(Actually, because the endpoint takes up the whole 14th year, this means at least 15 years of age.)
Naturally, this number will change if the age limit is changed, but this is here as a placeholder.
Note: The test is against the birthdate, not the maximum possible birthdate.
Bad data
Note: Original query used the wrong inequality.
SELECT fa.*
, b.b_sex
, b.b_birthdate
, AGE(fa.fa_fol_date, b.b_birthdate) AS age_at_follow
, AGE(fa.fa_fol_date, b.b_birthdate)
- INTERVAL '15 years' AS older_than_limit_by
FROM clean.follow_arrival AS fa
JOIN clean.biography AS b
ON b.b_animid = fa.fa_b_arr_animid
WHERE b.b_sex = 'F'
AND fa.fa_type_of_cycle = 'U'
AND b.b_birthdate
<= fa.fa_fol_date
- INTERVAL '1 year'
- INTERVAL '14 years'
ORDER BY fa.fa_fol_date
, fa.fa_fol_b_focal_animid
, fa.fa_b_arr_animid;
Solution
The number of problem rows was reduced after changing the limit to 14 years from 9 years.
The remaining problems will have to be adjusted in the data. Alternately, we can adjust the hard limit, and set a soft limit of 14 years in the warning system with a note to change the hard limit back once the errors are resolved.
5/7/2026: ICG checked through 2003. When there was a "?" for swelling, I entered '0'. If there was actually a 'U' on the tiki itself, I left "U" in the FA table. Note that a lot of these females are MGF, so the U might be legit.
5/19/2026: MGFs have a dummy birthdate which is why the age is being flagged. The rest are what the observer recorded, so let them in.
UPDATE 2026-07-23: There are 86 failing rows; many but not all are MGF (how to we treat MGF?). Please see below for a summary of this issue and related Problem #47:
8/14/2026 - ICG fixed in Access - all non-MGF entries changed to "0"
UPDATE 2026-08-14: Improved but there are still 48 offending records
note that this summary does not include MFG
| problem | problem_description | age_years | current_cycle | youngest_exact_age | oldest_exact_age | offending_rows | problem_total |
|---|---|---|---|---|---|---|---|
| 47 | U assigned before age 5 | 1 | U | 1 year 4 mons 5 days | 1 year 4 mons 5 days | 1 | 21 |
| 47 | U assigned before age 5 | 2 | U | 2 years 11 mons 26 days | 2 years 11 mons 26 days | 1 | 21 |
| 47 | U assigned before age 5 | 3 | U | 3 years 3 mons 30 days | 3 years 11 mons 1 day | 10 | 21 |
| 47 | U assigned before age 5 | 4 | U | 4 years 5 mons 21 days | 4 years 9 mons 18 days | 9 | 21 |
| 49 | U assigned at age 15 or older | 15 | U | 15 years 1 mon 2 days | 15 years 10 mons 14 days | 16 | 38 |
| 49 | U assigned at age 15 or older | 16 | U | 16 years 19 days | 16 years 11 mons 6 days | 7 | 38 |
| 49 | U assigned at age 15 or older | 17 | U | 17 years 1 mon 19 days | 17 years 1 mon 19 days | 1 | 38 |
| 49 | U assigned at age 15 or older | 18 | U | 18 years 1 mon 18 days | 18 years 8 mons 16 days | 6 | 38 |
| 49 | U assigned at age 15 or older | 32 | U | 32 years 1 mon 21 days | 32 years 5 mons 14 days | 4 | 38 |
| 49 | U assigned at age 15 or older | 33 | U | 33 years 6 days | 33 years 6 days | 4 | 38 |
(#50) There are follow_arrivals where females have a cycle code of n/a
Problem
There are 4,496 follow_arrival rows, for female arriving individuals, that have a sexual cycle code (fa_type_of_cycle) of n/a.
Bad data
select follow_arrival.*
, biography.b_sex
, biography.b_birthdate
from clean.follow_arrival
join clean.biography
on (biography.b_animid = follow_arrival.fa_b_arr_animid)
where biography.b_sex = 'F'
and follow_arrival.fa_type_of_cycle = 'n/a'
order by follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid;
Solution
Change the cycle code to MISS, for these rows. This is done in the clean schema.
This is an indication that future cleanup is required.
(#51) There are follow_arrivals with invalid fa_data_source values
Problem
There are 546 follow_arrival rows that have fa_data_source values that are not one of:
Tiki
Tiki_Mom
Tiki_ID
Brec.
Bad data
Query the easy schema, because the data has been cleaned in the clean schema.
select follow_arrival.*
from easy.follow_arrival
where follow_arrival.fa_data_source <> 'Tiki'
and follow_arrival.fa_data_source <> 'Tiki_Mom'
and follow_arrival.fa_data_source <> 'Tiki_ID'
and follow_arrival.fa_data_source <> 'Brec'
order by follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid;
-- Summarize with:
select follow_arrival.fa_data_source, count(*)
from easy.follow_arrival
where follow_arrival.fa_data_source <> 'Tiki'
and follow_arrival.fa_data_source <> 'Tiki_Mom'
and follow_arrival.fa_data_source <> 'Tiki_ID'
and follow_arrival.fa_data_source <> 'Brec'
group by follow_arrival.fa_data_source
order by follow_arrival.fa_data_source;
Solution
Change BREC and brec to Brec.
Change TikI to Tiki.
Add the other codes:
fa_data_source count Tiki_GM 202 Tiki_PM 165 Tiki_SS 22
Also: Tiki_Mom
(#52) There are follow_arrivals that are almost duplicates
Problem
This entry may end up being multiple problems.
There are follow_arrivals that are near duplicates.
When checking for duplicates on fa_fol_date, fa_fol_b_focal_animid, fa_b_arr_animid
and fa_seq_num, the rows are always unique.
But checking the combination of fa_fol_date, fa_fol_b_focal_animid, fa_b_arr_animid
and fa_time_start,
and fa_time_end yields 184 rows.
Leaving off fa_time_end and just checking the combination of fa_fol_date, fa_fol_b_focal_animid, fa_b_arr_animid
and fa_time_start yields 430 rows.
Why the duplicates?
This entry is a call for a definition of an ARRIVALS row, what does it mean to be a duplicate?
Bad data
-- no duplicates when looking at just sequence number
select fa_fol_date
, fa_fol_b_focal_animid
, fa_b_arr_animid
, fa_seq_num
from clean.follow_arrival
group by fa_fol_date
, fa_fol_b_focal_animid
, fa_b_arr_animid
, fa_seq_num
having count(*) > 1;
-- Checking against start and end time
with dups as
(select fa_fol_date
, fa_fol_b_focal_animid
, fa_b_arr_animid
, fa_time_start
, fa_time_end
from clean.follow_arrival
group by fa_fol_date
, fa_fol_b_focal_animid
, fa_b_arr_animid
, fa_time_start
, fa_time_end
having count(*) > 1)
select *
from clean.follow_arrival
where exists
(select 1
from dups
where follow_arrival.fa_fol_date = dups.fa_fol_date
and follow_arrival.fa_fol_b_focal_animid
= dups.fa_fol_b_focal_animid
and follow_arrival.fa_b_arr_animid = dups.fa_b_arr_animid
and follow_arrival.fa_time_start = dups.fa_time_start
and follow_arrival.fa_time_end = dups.fa_time_end)
order by follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid
, follow_arrival.fa_time_start
, follow_arrival.fa_time_end
, follow_arrival.fa_seq_num;
Solution
IAN FIXED IN ACCESS 8/24/2016
(#53) There are follow_arrivals where the arriving chimp does not exist
Problem
There are 688 84 78 rows, having 3 6 2 different fa_b_arr_animid values, where the fa_b_arr_animid value is not a biography.b_animid value.
Bad Data
-- The rows
select *
from clean.follow_arrival
where not exists
(select 1
from clean.biography
where biography.b_animid = follow_arrival.fa_b_arr_animid)
order by follow_arrival.fa_b_arr_animid
, follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid;
-- A summary of the bad animal ids
select follow_arrival.fa_b_arr_animid, count(*)
from clean.follow_arrival
where not exists
(select 1
from clean.biography
where biography.b_animid = follow_arrival.fa_b_arr_animid)
group by follow_arrival.fa_b_arr_animid
order by follow_arrival.fa_b_arr_animid;
Solution
ICG fixed in MS Access 10/2025. AMA-->AME, OBE-->POR
SRE: still need to address: UWE, GGl, FAC, FAR, GU, and Sl (as of the April 2026 dump)
FIXED UWE, GGl, FAC, FAR, GU, and Sl IN ACCESS 7/3/2026
UPDATE 2026-07-22: still need to resolve FAR (17 records) and FAC (61 records)
UPDATE 2026-08-18: ICG fixed in Access. FAR-->FAY, FAC-->FIC
(#54) There are follow_arrivals where females that are too young have a cycle state code that is not 0, U, or MISS
Problem
There are 26 42 follow_arrival rows, for female arriving individuals, that have a sexual cycle code indicating sexual swelling but are less than 8 years of age.
Note: The test is against the birthdate, not the minimum possible birthdate.
Bad data
select follow_arrival.*, biography.b_sex, biography.b_birthdate
from clean.follow_arrival
join clean.biography
on (biography.b_animid = follow_arrival.fa_b_arr_animid)
where biography.b_sex = 'F'
and follow_arrival.fa_type_of_cycle <> '0'
and follow_arrival.fa_type_of_cycle <> 'U'
and follow_arrival.fa_type_of_cycle <> 'MISS'
and biography.b_birthdate
> (follow_arrival.fa_fol_date
- '8 years'::INTERVAL
)
and follow_arrival.fa_b_arr_animid <> 'MGF'
and follow_arrival.fa_b_arr_animid <> 'MGF2'
and follow_arrival.fa_b_arr_animid <> 'MGF3'
order by follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid;
Solution
IAN TO FIX - CHANGE ALL TO "U"
That works. Would it be better to leave the original MS Access data untouched and have the conversion process make the change in the clean schema?
UPDATE 2026-07-23: The proposed solution has been applied to the clean schema (see commit 06c4178). However, please note that this does not solve Problem #47 and, in fact, forces the fa_type_of_cycle type of records associated with Problem #47 to 'U'.
(#55) There are community_membership rows that place an individual in a community before birth
Problem
There is one individual who is placed in a community, once, before birth.
Note: The test is against the birthdate, not the minimum possible birthdate.
Bad data
select b.b_animid, b.b_birthdate, cm.cm_start_date
from clean.community_membership as cm
join clean.biography as b
on (b.b_animid = cm.cm_b_animid)
where cm.cm_start_date < b.b_birthdate
order by b.b_animid, cm.cm_start_date;
Bad solution
ICG fixed FN community starte date in MS Access - oct 2025
(#56) There are follow_arrival focal animids with trailing spaces
Problem
There are 144 follow arrivals where the focal id has trailing spaces.
Bad data
select * from follow_arrival where fa_fol_b_focal_animid <> rtrim(fa_fol_b_focal_animid);
Solution
Remove the trailing spaces in table in the clean schema.
(#57) GROOM_BOUT duplicate keys
Problem
The data dump says that the GROOM_BOUT table has a primary key consisting of, in order, the columns: GRM_FOL_date, GRM_FOL_B_focal_AnimId, GRM_time_begin, GRM_B_partner_AnimId
But these columns contain duplicate values.
Bad Data
The duplicate values can be listed (from the raw schema) with:
SELECT *
FROM raw."GROOM_BOUT"
JOIN (SELECT "GRM_FOL_date" AS the_date
, "GRM_FOL_B_focal_AnimId" AS the_animid
, "GRM_time_begin" AS the_time
, "GRM_B_partner_AnimId" AS the_partner
FROM raw."GROOM_BOUT"
GROUP BY "GRM_FOL_date"
, "GRM_FOL_B_focal_AnimId"
, "GRM_time_begin"
, "GRM_B_partner_AnimId"
HAVING count(*) > 1
) AS gb
ON ("GROOM_BOUT"."GRM_FOL_date" = gb.the_date
AND "GROOM_BOUT"."GRM_FOL_B_focal_AnimId" = gb.the_animid
AND "GROOM_BOUT"."GRM_time_begin" = gb.the_time
AND "GROOM_BOUT"."GRM_B_partner_AnimId" = gb.the_partner);
SOLUTION
ICG FIXED IN ACCESS - ALL WERE LEGIT DUPLICATES. REMOVED. 8/18/2026
(#58) OTHER_SPECIES duplicate keys
Problem
The data dump says that the OTHER_SPECIES table has a primary key consisting of, in order, the columns: OS_FOL_date, OS_FOL_B_focal_AnimID, OS_time_begin
But these columns contain duplicate values.
Bad Data
The duplicate values can be listed (from the raw schema) with:
SELECT *
FROM raw."OTHER_SPECIES"
JOIN (SELECT "OS_FOL_date" AS the_date
, "OS_FOL_B_focal_AnimID" AS the_animid
, "OS_time_begin" AS the_time
FROM raw."OTHER_SPECIES"
GROUP BY "OS_FOL_date"
, "OS_FOL_B_focal_AnimID"
, "OS_time_begin"
HAVING count(*) > 1
) AS os
ON ("OTHER_SPECIES"."OS_FOL_date" = os.the_date
AND "OTHER_SPECIES"."OS_FOL_B_focal_AnimID" = os.the_animid
AND "OTHER_SPECIES"."OS_time_begin" = os.the_time);
Note
ICG fixed in Access 8/18/2026
(#59) Zero BIOGRAPHY.b_animid_num values
Problem
SokewDB requires that the animal ID number be greater than 0, or NULL, but some (what seem to be rows for babys) have a 0 value.
Bad Data
The 0 values can be listed (from the clean schema) with:
select * from clean.biography where b_animid_num = 0;
Solution
Change the 0 values to NULL.
This is a brute-force, but adequate, solution because it does not validate anything concerning the rows affected.
(#60) Invalid biography_update_log.made_by values
Problem
There is a biography_update_log.made_by value (SF/EVL) that is not a person. (Not on the PEOPLE table.)
Bad Data
The invalid values can be listed (from the clean schema) with:
SELECT *
FROM clean.biography_update_log
WHERE made_by IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM clean.people
WHERE people.person = biography_update_log.made_by);
SOLUTION
IAN CHANGED THE SINGLE SF/EVL ENTRY TO EVL IN ACCESS 3/25/2026
update: this fix was not reflected in the 2026-07-12 dump.
Codified in clean per commit b793345f4098fa012d559b8f2d01f06d5418126c.
-- Problem #60 BIOGRAPHY_UPDATE_LOG.MadeBy contains the invalid combination -- person code SF/EVL. The source-data resolution is to use EVL. UPDATE biography_update_log SET made_by = 'EVL' WHERE made_by = 'SF/EVL';
* (#61) Invalid follow date/focals pairs in follow_arrival
Problem
There are follow_arrival.fa_fol_b_animid and follow_arrival.fa_fol_date value combinations that do not exist in follow.
Bad Data
The invalid values can be listed (from the clean schema) with:
SELECT *
FROM clean.follow_arrival
WHERE NOT EXISTS
(SELECT 1
FROM clean.follow
WHERE follow.fol_date = follow_arrival.fa_fol_date
AND follow.fol_b_animid
= follow_arrival.fa_fol_b_focal_animid)
ORDER BY follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid
, follow_arrival.fa_time_start
, follow_arrival.fa_time_end;
SOLUTION
These are all cases when a juvenile arrival has been extracted from b record notes, but there seems to be no tiki. Do we add WATCHES for these?
(#62) There are follow_arrival rows with arriving animids with trailing spaces
Problem
There are 39 follow arrivals where the arriving animid has trailing spaces.
Bad data
select * from easy.follow_arrival where fa_b_arr_animid <> rtrim(fa_b_arr_animid);
Solution
Remove the trailing spaces in table in the clean schema. Addressed 9c114ac.
(#63) There are follow_arrival rows with NULL fa_data_source values
Problem
There are 5,020 follow arrivals where the fa_data_source is NULL.
Bad data
SELECT *
FROM easy.follow_arrival
WHERE fa_data_source IS NULL
ORDER BY follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid
, follow_arrival.fa_time_start
, follow_arrival.fa_time_end;
Solution
KARL TO FIX
Create a none value in ARRIVAL_SOURCES, and use that value instead of NULL in follow_arrival table in the clean schema. Addressed 8c05232.
(#64) There are follow_arrival rows with NULL fa_type_of_certainty values
Problem
There is 1 follow arrivals where the fa_type_of_certainty is NULL.
Bad data
SELECT *
FROM easy.follow_arrival
WHERE fa_type_of_certainty IS NULL
ORDER BY follow_arrival.fa_fol_date
, follow_arrival.fa_fol_b_focal_animid
, follow_arrival.fa_b_arr_animid
, follow_arrival.fa_time_start
, follow_arrival.fa_time_end;
Solution
IAN fixed in Access 3/25/2026
(#65) There are AGGRESSION_EVENT rows that have "YES" or a space as a ae_bad_observeration_flag value
Problem
There are 3,079 rows where the ae_bad_observation_flag is a space, and 1 row where it is YES.
Bad data
SELECT COALESCE(ae.ae_bad_observation_flag, 'NULL'), count(*) FROM easy.aggression_event AS ae GROUP BY ae.ae_bad_observation_flag;
Solution
KARL: Treat spaces (along with NULL) as FALSE, and YES (along with X() as TRUE.
Stevan: Change the data in the clean schema to contain only boolean values, based on the above.
addressed as part of the solution to problem #74 (commit efa75af037a5e22b2b43675b435f472193a6672f)
(#66) There are AGGRESSION_EVENT rows that have a space as a ae_decided_flag value
Problem
There are 2,041 rows where the ae_decided_flag is a space.
Bad data
SELECT COALESCE(ae.ae_decided_flag, 'NULL'), count(*) FROM easy.aggression_event AS ae GROUP BY ae.ae_decided_flag;
Solution
KARL: Treat spaces (along with NULL) as FALSE.
Stevan: Fix in the clean schema.
addressed as part of the solution to problem #74 (commit efa75af037a5e22b2b43675b435f472193a6672f)
(#67) There are AGGRESSION_EVENT rows that have a space or an x as a ae_multiple_aggressor_flag value
Problem
There are 2,397 rows where the ae_multiple_aggressor_flag is a space and 2 rows where the value is an x.
Bad data
SELECT COALESCE(ae.ae_multiple_aggressor_flag, 'NULL'), count(*) FROM easy.aggression_event AS ae GROUP BY ae.ae_multiple_aggressor_flag;
Solution
Treat spaces (along with NULL) as FALSE, and x (along with X) as TRUE.
addressed as part of the solution to problem #74 (commit efa75af037a5e22b2b43675b435f472193a6672f)
(#68) There are AGGRESSION_EVENT rows that have a space as a ae_multiple_recipient_flag value
Problem
There are 2,528 rows where the ae_multiple_recipient_flag is a space.
Bad data
SELECT COALESCE(ae.ae_multiple_recipient_flag, 'NULL'), count(*) FROM easy.aggression_event AS ae GROUP BY ae.ae_multiple_recipient_flag;
Solution
Treat spaces (along with NULL) as FALSE.
addressed as part of the solution to problem #74 (commit efa75af037a5e22b2b43675b435f472193a6672f)
(#69) There are duplicate year*community records in AGGRESSION_EVENT_LOG
Problem
There are 11 duplicate pairs of year*community records in AGGRESSION_EVENT_LOG. There can be, at most, one row per-community, per-year. The duplicates each have a `b_rec_english` value of `ALL` or `F-F (ALL); F-M (ALL); M-M (ALL)`.
Bad data
SELECT *
FROM clean.aggression_event_log
JOIN (
SELECT
year,
community
FROM
clean.aggression_event_log
GROUP BY
year,
community
HAVING count(*) > 1
) AS dups ON (
dups.year = aggression_event_log.year
AND dups.community = aggression_event_log.community
)
order by
aggression_event_log.year,
aggression_event_log.community ;
Solution
IAN fixed 8/18/2026
(#70) There are AGGRESSION_EVENT rows that do not have a matching FOLLOW for the same focal ID and date.
Problem
There are 1494 records where clean.aggression_event does not have a matching row in clean.follow for the same focal ID and date.
Bad data
WITH problem_rows AS (
SELECT
ae.*,
EXISTS (
SELECT 1
FROM clean.follow f_id
WHERE f_id.fol_b_animid = ae.ae_fol_b_focal_id
) AS focal_id_exists_in_follow
FROM clean.aggression_event ae
WHERE NOT EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
)
SELECT
pr.ae_date,
pr.ae_time,
pr.ae_fol_b_focal_id,
pr.ae_b_aggressor_id,
pr.ae_b_recipient_id,
pr.ae_source,
pr.ae_full_description,
CASE
WHEN pr.focal_id_exists_in_follow THEN 'missing_follow_on_same_date'
ELSE 'focal_id_not_found_in_follow'
END AS issue_type
FROM problem_rows pr
ORDER BY
pr.ae_date,
pr.ae_fol_b_focal_id,
pr.ae_time;
Resolution
Resolved by commit `1cc9e694a1f6a53c396d9074f77d5d279018721b`.
A matching FOLLOW row is not required for an aggression event. WATCHES represents the observation context independently of FOLLOW. A B-record WATCHES row may represent either a follow or an ad-hoc observation such as an aggression.
During conversion, `load_aggressions.m4` looks for an existing B-record WATCHES row having the aggression event's focal individual and date. If no such row exists, the loader creates one using the aggression event's focal, community, and date. The aggression's EVENTS row is then related to that WATCHES row.
The original diagnostic query will continue to identify aggression records without matching FOLLOW rows. These rows are not necessarily bad data and are not expected to disappear.
* (#71) There are AGGRESSION_EVENT animal ids that are not reflected in among FOLLOW animal ids.
Problem
There are 16 unique(!) aggression_event.ae_fol_b_focal_id records that are not reflected among follow.fol_b_animid values.
Processing note
All of the problematic records are reflected in the rows excluded as part of Problem #70. As such, there is not a separate exclusion clause in the conversion process for these data. As a corollary, addressing all issues in Problem #70 would address all Problem #71 infractions well.
Problem #70 concerns aggression rows without a matching follow for the same focal ID and date. Canonical master solves that relationship problem by creating a B-record WATCHES row when no suitable watch exists. An aggression therefore does not inherently require a FOLLOW.
Problem #71 is a stricter subset: its focal IDs never occur among follow focal IDs. More importantly, a read-only check of the local data found that the documented 16 IDs are also absent from clean.biography, including values such as DL DUK, GA GGL, GROUP, Males, and stranger.
WATCHES.AnimID → BIOGRAPHY_DATA.AnimID
Consequently:
- If a #71 focal ID is a valid biography ID, the #70 WATCHES solution handles it. - If it is not a valid biography ID, creating the WATCHES row fails its foreign key. - The documented 16 IDs appear to be in the latter category and therefore require a separate source-data decision or clean-stage mapping.
There is also a branch-specific complication: the temporary branch loader still has the old #70 WHERE EXISTS filter. It excludes all rows lacking a matching follow, including every #71 row. Thus the successful mung conversion does not demonstrate that these rows were converted. Canonical master does not have that filter and would expose invalid #71 focal IDs when attempting to create their WATCHES rows.
Therefore, the Problem #71 processing note is too broad. The #70 solution removes the requirement for a corresponding follow, but it does not resolve invalid or composite focal animal IDs. Problem #71 should remain a separate unresolved data-quality problem, and mung’s stale #70 exclusion should eventually be removed when testing the full solution.
Bad data
SELECT
ae_fol_b_focal_id,
COUNT(*) AS row_count
FROM clean.aggression_event
WHERE ae_fol_b_focal_id NOT IN (
SELECT DISTINCT fol_b_animid
FROM clean.follow
WHERE fol_b_animid IS NOT NULL
)
GROUP BY ae_fol_b_focal_id
ORDER BY
row_count DESC,
ae_fol_b_focal_id
;
SOLUTION
IAN FIXED IN ACCESS 8/25/2026
(#72) There are AGGRESSION_EVENT recipient certainty flags other than `Y` or `N` (required).
Problem
There are 28,322 AGGRESSION_EVENT rows that have a ae_recipient_certainty_flag other than `Y` or `N` as required.
Bad data
Full record
SELECT
ae.*,
COALESCE(ae_recipient_certainty_flag, '<NULL>') AS raw_flag
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND UPPER(BTRIM(COALESCE(ae.ae_recipient_certainty_flag, ''))) NOT IN ('N', 'Y') ;
Summary of non-compliant values
SELECT
COALESCE(ae_recipient_certainty_flag, '<NULL>') AS raw_flag,
COUNT(*) AS row_count
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND UPPER(BTRIM(COALESCE(ae.ae_recipient_certainty_flag, ''))) NOT IN ('N', 'Y')
GROUP BY COALESCE(ae_recipient_certainty_flag, '<NULL>')
ORDER BY row_count DESC, raw_flag;
Solution
Ian or Elizabeth to please confirm.
- sokwe conversions:
- ' ' to '0'
- 'N' to '0'
- 'NO' to '0'
- 'X' to '1'
- 'Y%' to '1'
ICG AND ELO CONFIRM 7/3/2026
KARL
Stevan: The usual. Fix in the clean schema.
addressed as part of the solution to problem #74 (commit efa75af037a5e22b2b43675b435f472193a6672f)
(#73) There are AGGRESSION_EVENT aggression_event.ae_b_recipient_id values that are NULL.
Problem
There are 5,820 AGGRESSION_EVENT rows for which the aggression_event.ae_b_recipient_id is NULL.
Bad data
SELECT
ae.ae_date,
ae.ae_time,
ae.ae_fol_b_focal_id,
ae.ae_b_aggressor_id,
ae.ae_b_recipient_id,
ae.ae_recipient_certainty_flag,
ae.ae_full_description,
ae.ae_source,
ae.ae_comments
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND ae.ae_b_recipient_id IS NULL
ORDER BY ae.ae_date, ae.ae_fol_b_focal_id, ae.ae_time;
SOLUTION
CAN THESE ALL BE NULL? IF NOT, THEN WE NEED TO DO SOMETHING ABOUT INSTANCES WHEN 'GROUP' IS THE TARGET.
Karl's comment
The usual solution is to have the unknown individual, UNK, be the target. If NULL has some other special meaning, other than we don't know at whom the aggression was directed, we could make up another "special" BIOGRAPHY_DATA row, similar to the UNK, MGF, MGM, etc. rows.
Yes, use UNK.
(#74) There are AGGRESSION_EVENT event flags (multiple columns) that have values other than X or NULL.
Problem
Among AGGRESSION_EVENT flags (ae_bad_observation_flag, ae_bristle_flag, ae_chase_flag, ae_contact_flag, ae_contact_flag, ae_decided_flag, ae_display_flag, ae_multiple_aggressor_flag, ae_multiple_recipient_flag, ae_vocal_flag, ae_vocal_flag) there are 2,864 rows that have a value other than `X` or NULL (required) for one or more of the flags.
Bad data
Full record
SELECT
ae.ae_date,
ae.ae_time,
ae.ae_fol_b_focal_id,
ae.ae_b_aggressor_id,
ae.ae_b_recipient_id,
ae.ae_decided_flag,
ae.ae_multiple_aggressor_flag,
ae.ae_multiple_recipient_flag,
ae.ae_bad_observation_flag,
ae.ae_bristle_flag,
ae.ae_display_flag,
ae.ae_chase_flag,
ae.ae_contact_flag,
ae.ae_vocal_flag,
ae.ae_source,
ae.ae_comments
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND ae.ae_b_recipient_id IS NOT NULL
AND (
(ae.ae_decided_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_decided_flag)) <> 'X')
OR (ae.ae_multiple_aggressor_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_multiple_aggressor_flag)) <> 'X')
OR (ae.ae_multiple_recipient_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_multiple_recipient_flag)) <> 'X')
OR (ae.ae_bad_observation_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_bad_observation_flag)) <> 'X')
OR (ae.ae_bristle_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_bristle_flag)) <> 'X')
OR (ae.ae_display_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_display_flag)) <> 'X')
OR (ae.ae_chase_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_chase_flag)) <> 'X')
OR (ae.ae_contact_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_contact_flag)) <> 'X')
OR (ae.ae_vocal_flag IS NOT NULL AND UPPER(BTRIM(ae.ae_vocal_flag)) <> 'X')
)
ORDER BY ae.ae_date, ae.ae_fol_b_focal_id, ae.ae_time;
Summary of non-compliant values
WITH base AS (
SELECT ae.*
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND ae.ae_b_recipient_id IS NOT NULL
),
flag_values AS (
SELECT 'ae_decided_flag' AS flag_name, COALESCE(ae_decided_flag, '<NULL>') AS raw_value FROM base
UNION ALL
SELECT 'ae_multiple_aggressor_flag', COALESCE(ae_multiple_aggressor_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_multiple_recipient_flag', COALESCE(ae_multiple_recipient_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_bad_observation_flag', COALESCE(ae_bad_observation_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_bristle_flag', COALESCE(ae_bristle_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_display_flag', COALESCE(ae_display_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_chase_flag', COALESCE(ae_chase_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_contact_flag', COALESCE(ae_contact_flag, '<NULL>') FROM base
UNION ALL
SELECT 'ae_vocal_flag', COALESCE(ae_vocal_flag, '<NULL>') FROM base
)
SELECT
flag_name,
raw_value,
COUNT(*) AS row_count
FROM flag_values
WHERE raw_value <> '<NULL>'
AND UPPER(BTRIM(raw_value)) <> 'X'
GROUP BY flag_name, raw_value
ORDER BY flag_name, row_count DESC, raw_value;
Solution
Ian or Elizabeth to please confirm.
- clean schema conversions (mimic patterns in Access):
- `?` to ' '
- `Y%` to `X`
- `X%` to `X`
- sokwe conversions:
- ' ' to '0'
- 'X' to '1'
ICG AND ELO CONFIRM 7/3/26
addressed with commit efa75af037a5e22b2b43675b435f472193a6672f note that the solution to problem #74 was an umbrella fix that also addressed #65-68, and #72
* (#75) There are AGGRESSION_EVENT records where ae_extracted_by values are not in the people table.
Problem
There are 28,513 AGGRESSION_EVENT records where ae_extracted_by does not match a person in the PEOPLE table.
Bad data
Full record
WITH scoped AS (
SELECT ae.*
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND ae.ae_b_recipient_id IS NOT NULL
)
SELECT
s.ae_date,
s.ae_time,
s.ae_fol_b_focal_id,
s.ae_b_aggressor_id,
s.ae_b_recipient_id,
s.ae_extracted_by,
s.ae_source,
s.ae_full_description,
s.ae_comments
FROM scoped s
WHERE NOT EXISTS (
SELECT 1
FROM people p
WHERE p.person = BTRIM(COALESCE(s.ae_extracted_by, ''))
OR LOWER(p.name) = LOWER(BTRIM(COALESCE(s.ae_extracted_by, '')))
)
ORDER BY s.ae_date, s.ae_fol_b_focal_id, s.ae_time;
Summary of non-compliant values
WITH scoped AS (
SELECT ae.*
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND ae.ae_b_recipient_id IS NOT NULL
)
SELECT
BTRIM(COALESCE(ae_extracted_by, '')) AS raw_extractedby,
COUNT(*) AS row_count
FROM scoped s
WHERE NOT EXISTS (
SELECT 1
FROM people p
WHERE p.person = BTRIM(COALESCE(s.ae_extracted_by, ''))
OR LOWER(p.name) = LOWER(BTRIM(COALESCE(s.ae_extracted_by, '')))
)
GROUP BY BTRIM(COALESCE(ae_extracted_by, ''))
ORDER BY row_count DESC, raw_extractedby;
SOLUTION
ALLOW FOR NOW. NOT SURE IF/HOW THE PEOPLE TABLE IS POPULATED.
Karl's comment
We populate the PEOPLE however we like. We just make things up.
We can add all these values to the table as different people.
However, unless you set the PEOPLE.Active to FALSE
you run the risk of future data entry using a bunch of odd or outdated people.
(#76) There are AGGRESSION_EVENT rows where the Actor/Actee are not in the biography table.
Problem
There are 10,866 combined (but see following!) rows where the ae_b_aggressor_id and/or ae_b_recipient_id is not in the BIOGRAPHY table.
Notes:
- The row count is inflated by a CROSS LATERAL JOIN, which yields the number of combined, pivoted records for both ae_b_aggressor_id and ae_b_recipient, not the actual number of confounding AGGRESSION_EVENT rows.
- The queries currently exclude ae_b_recipient_id values that are NULL, which seems a related but separte problem; the number of rows jumps to 10,357 if ae_b_recipient_id = NULL are included.
- How are we treating ae_b_recipient_id values such as `group`, `males`, `females`, etc.?
- Whitespace is considered in the incongruence such that, for example, `VIN ` (with a space) does not match `VIN` in biography.
Bad data
Full record
SELECT
ae.ae_date,
ae.ae_time,
ae.ae_fol_b_focal_id,
v.role_name,
v.participant,
ae.ae_b_aggressor_id,
ae.ae_b_recipient_id,
ae.ae_source,
ae.ae_full_description,
ae.ae_comments
FROM clean.aggression_event ae
CROSS JOIN LATERAL (
VALUES
('Actor'::text, ae.ae_b_aggressor_id),
('Actee'::text, ae.ae_b_recipient_id)
) AS v(role_name, participant)
WHERE v.participant IS NOT NULL
AND v.participant <> ''
AND NOT EXISTS (
SELECT 1
FROM clean.biography b
WHERE b.b_animid = v.participant
)
ORDER BY ae.ae_date, ae.ae_fol_b_focal_id, ae.ae_time, v.role_name;
Summary of non-compliant values
trimmed_match denotes a match between Actor/Actee and biography if whitespace is trimmed.
SELECT
v.role_name,
v.participant AS raw_participant,
b_trimmed.b_animid AS trimmed_match,
COUNT(*) AS row_count
FROM clean.aggression_event ae
CROSS JOIN LATERAL (
VALUES
('Actor'::text, ae.ae_b_aggressor_id),
('Actee'::text, ae.ae_b_recipient_id)
) AS v(role_name, participant)
LEFT JOIN clean.biography b_trimmed
ON BTRIM(v.participant) = BTRIM(b_trimmed.b_animid)
WHERE v.participant IS NOT NULL
AND v.participant <> ''
AND NOT EXISTS (
SELECT 1
FROM clean.biography b
WHERE b.b_animid = v.participant
)
GROUP BY v.role_name, v.participant, b_trimmed.b_animid
ORDER BY row_count DESC, v.role_name, v.participant;
SOLUTION
ICG fixed in Access 7/7/2026. replaced "group", "males", etc with UNK. Fixed bad IDs. Updated comments field to reflect original entry terms.
Karl comment
You can't just "allow them". You can make rows in BIOGRAPHY_DATA that have AnimID values that correspond to the values that appear. It's hard to say whether or not these rows would have to be "special". Probably not, because anybody can aggress against anybody. But if you're going to make a whole bunch of "dummy" individuals (138 of them, to be exact), it might be prudent to add a flag to BIOGRAPHY_DATA that says whether or not the row corresponds to a specific chimpanzee. (That flag could even be used to filter the dummy individuals out of the BIOGRAPHY view.)
(#77) There are AGGRESSION_EVENT rows where ae_recipient_behavior is NULL or an empty string.
Problem
There are 9,102 records where ae_recipient_behavior is NULL or an empty string.
Bad data
SELECT * FROM clean.aggression_event WHERE ae_recipient_behavior IS NULL OR ae_recipient_behavior = '' ;
SOLUTION
ALLOW FOR NOW
Karl's comments
Change the NULL values to the empty string in the clean schema.
Addressed with commit a84462b47c8b2100caa02958623817981179d068.
(#78) There are AGGRESSION_EVENT rows where ae_full_description is NULL or an empty string.
Problem
There are 4,718 records where ae_full_description is NULL or an empty string.
Bad data
SELECT * FROM clean.aggression_event WHERE ae_full_description IS NULL OR ae_full_description = '' ;
SOLUTION
ALLOW FOR NOW.
Karl's comments
Change the NULL values to the empty string in the clean schema. Addressed with commit a84462b47c8b2100caa02958623817981179d068.
(#79) There are AGGRESSION_EVENT participants outside their valid study participation window in the biography data.
Problem
There are 46 6 records where an Actor and/or Actee are in the AGGRESSION_EVENT table but outside their valid study participation window in the biography data. Note that the query strips white space when comparing the ids of aggression participant ids and biography animid ids given that the focus here is on checking for events outside of defined date ranges rather than matching ids and assuming that issues concerning white space will be resolved (also in the migration work-around). The query further winnows aggression events for which there is a valid follow record (not in the migration work-around).
Bad data
WITH candidate_agg AS (
SELECT
ae.ae_date,
ae.ae_time,
ae.ae_fol_b_focal_id,
BTRIM(COALESCE(ae.ae_b_aggressor_id, '')) AS actor_id,
BTRIM(COALESCE(ae.ae_b_recipient_id, '')) AS actee_id,
ae.ae_source,
ae.ae_full_description
FROM clean.aggression_event ae
WHERE EXISTS (
SELECT 1
FROM clean.follow f
WHERE f.fol_b_animid = ae.ae_fol_b_focal_id
AND f.fol_date = ae.ae_date
)
AND ae.ae_b_recipient_id IS NOT NULL
AND EXISTS (
SELECT 1
FROM clean.biography b
WHERE b.b_animid = BTRIM(COALESCE(ae.ae_b_aggressor_id, ''))
)
AND EXISTS (
SELECT 1
FROM clean.biography b
WHERE b.b_animid = BTRIM(COALESCE(ae.ae_b_recipient_id, ''))
)
),
participants AS (
SELECT
c.ae_date,
c.ae_time,
c.ae_fol_b_focal_id,
'Actor' AS role,
c.actor_id AS participant,
c.ae_source,
c.ae_full_description
FROM candidate_agg c
UNION ALL
SELECT
c.ae_date,
c.ae_time,
c.ae_fol_b_focal_id,
'Actee' AS role,
c.actee_id AS participant,
c.ae_source,
c.ae_full_description
FROM candidate_agg c
)
SELECT
p.ae_date,
p.ae_time,
p.ae_fol_b_focal_id,
p.role,
p.participant,
b.b_entrydate,
b.b_departdate,
CASE
WHEN p.ae_date < b.b_entrydate THEN 'before_entry'
WHEN p.ae_date > b.b_departdate THEN 'after_departure'
ELSE 'ok'
END AS violation_type,
p.ae_source,
p.ae_full_description
FROM participants p
JOIN clean.biography b
ON b.b_animid = p.participant
WHERE p.ae_date < b.b_entrydate
OR p.ae_date > b.b_departdate
ORDER BY p.ae_date, p.ae_fol_b_focal_id, p.ae_time, p.role, p.participant;
SOLUTION
ICG FIXED IN ACCESS 7/6/2026
Karl comment
There is no good way to "allow for now".
We could change the under study dates (EntryDate, DepartDate) of the affected individuals "for now". That would "solve" the problem.
Or just don't convert the data and let the users get the info from the clean schema and integrate it into their processing.
Update 2026-07-26: row count is much improved but there are six records that violate this constraint.
(#80) There are AGGRESSION_EVENT rows where ae_aggressor_behavior is NULL or an empty string.
Problem
There are 143 records where ae_aggressor_behavior is NULL or an empty string.
Bad data
SELECT * FROM clean.aggression_event WHERE ae_aggressor_behavior IS NULL OR BTRIM(ae_aggressor_behavior) = '' ORDER BY ae_date, ae_fol_b_focal_id, ae_time;
SOLUTION
ALLOW FOR NOW. EVENTUALLY A VOLUNTEER CAN POPULATE USING THE FULL DESCRIPTION FIELD.
Change the NULL values to the empty string in the clean schema.
Addressed with commit a84462b47c8b2100caa02958623817981179d068.
(#81) There are AGGRESSION_EVENT rows where ae_time is NULL or outside the allowable window ('04:00:00', '20:00:00').
Problem
There are 112 AGGRESSION_EVENT rows where ae_time is NULL or outside the allowable window ('04:00:00', '20:00:00').
Bad data
SELECT
ae_date,
ae_time,
ae_fol_b_focal_id,
ae_b_aggressor_id,
ae_b_recipient_id,
ae_source,
ae_full_description,
CASE
WHEN ae_time IS NULL THEN 'null_time'
WHEN ae_time < '04:00:00'::time THEN 'below_min'
WHEN ae_time > '20:00:00'::time THEN 'above_max'
END AS time_issue
FROM clean.aggression_event
WHERE ae_time IS NULL
OR ae_time < '04:00:00'::time
OR ae_time > '20:00:00'::time
ORDER BY ae_date, ae_fol_b_focal_id, ae_time;
SOLUTION
MOST ARE NULL VALUES BECAUSE THEY WERE 'BOOK EXTRACTS'. ASSIGN A 'DUMMY' TIME?
ICG FIXED THE FEW THAT WERE NOT BETWEEN 4:00 AND 20:00 IN ACCESS 7/6/2026
Karl's comment
Allowing means changing the "too early" and "too late" limits for all times, appearing anywhere in the db. This is easy, but opens the door for other data errors.
The change to the time limits cannnot (easily) be restricted to just the table that is converted. Well.... It can. But the change will not be reflected in the documentation. (And it'll be completely annoying because a change will have to be re-made each time the conversion program is re-run, which is a lot because we have a lot of errors to resolve.)
Changing the limits back is less than easy. A separate SQL statement needs to be run for each time column that appears in the database. Probably better is to dump the database content, rebuild the database tables, etc., and then re-load the database content. If any time values are out of bounds, the re-load of the database content will fail.
Executive Summary: Anything's possible but you're coloring outside the lines here.
resolution
The schema already has a deliberate non-NULL representation for an unknown aggression time: 00:00, defined as sdb_no_time.
The design is explicit:
- EVENTS.Start and EVENTS.Stop remain NOT NULL. - Normal event times must be between 04:00 and 20:00. - Aggressions alone may use 00:00 to mean “no time was recorded.” - Both start and stop must be 00:00 together.
Existing implementation Commit 37bb5493b7571084e6d57aa45c5440dcb0db357e Karl O. Pinc 2026-07-15
(#82) There are AGGRESSION_EVENT rows where severity (ae_fight_category) is not an allowable value.
Problem
There are 2,041 AGGRESSION_EVENT rows where severity (ae_fight_category) is not an allowable value [('unrated', '0', '1', '2', '?')]. Note that all non-compliant values are NULL or some variation of white space.
Bad data
Full record
SELECT
ae_date,
ae_time,
ae_fol_b_focal_id,
ae_b_aggressor_id,
ae_b_recipient_id,
ae_fight_category,
BTRIM(COALESCE(ae_fight_category, '')) AS normalized_fight_category,
ae_source,
ae_full_description,
ae_comments
FROM clean.aggression_event
WHERE ae_fight_category IS NOT NULL
AND (
BTRIM(ae_fight_category) = ''
OR BTRIM(ae_fight_category) NOT IN ('unrated', '0', '1', '2', '?')
)
ORDER BY ae_date, ae_fol_b_focal_id, ae_time;
Summary of non-compliant values
SELECT
BTRIM(COALESCE(ae_fight_category, '')) AS offending_value,
COUNT(*) AS row_count
FROM clean.aggression_event
WHERE ae_fight_category IS NOT NULL
AND (
BTRIM(ae_fight_category) = ''
OR BTRIM(ae_fight_category) NOT IN ('unrated', '0', '1', '2', '?')
)
GROUP BY BTRIM(COALESCE(ae_fight_category, ''))
ORDER BY row_count DESC, offending_value;
Fine-grain assessment of non-compliant values
Note the white space problem.
select distinct '"' || ae_fight_category || '"' from clean.aggression_event ;
SOLUTION
ALLOW FOR NOW - MOST DATA ENTERERS DIDN'T FILL THIS COLUMN IN
Karl's comment
There seems to be only a problem because NULL values exist. The other non-conformant data seems to be gone from MS Access. So make a fight category code that is "no data" and clean up the data in the clean schema.
NULL means unrated
resolution
Added `NODATA` to `AGG_SEVERITIES` for aggression records where no fight-category data were recorded. This is distinct from `unrated`, which means the aggression was not evaluated for severity.
During construction of the `clean` schema, `ae_fight_category` values are trimmed. `NULL`, empty-string, and whitespace-only values are converted to `NODATA`. The aggression loader then copies the cleaned value directly into `AGGRESSIONS.Severity`, allowing unexpected nonblank codes to remain visible as conversion errors.
Implemented in commit `d911ef2` (`Resolve Problem #82 with NODATA severity`). A complete conversion using the updated code succeeded.
* (#83) There are AGGRESSION_EVENT rows where the Actor and Actee have the same animal ID.
Problem
There are 1,832 363 AGGRESSION_EVENT rows where `ae_b_aggressor_id` and `ae_b_recipient_id` share the same animal id.
Bad data
WITH potential_role_dupes AS (
SELECT
ae.ae_date,
ae.ae_time,
ae.ae_fol_b_focal_id,
ae.ae_b_aggressor_id,
ae.ae_b_recipient_id,
ae.ae_source,
ae.ae_full_description,
ae.ae_comments,
ae.dup
FROM clean.aggression_event ae
WHERE ae.ae_b_recipient_id IS NOT NULL
AND ae.ae_b_aggressor_id IS NOT NULL
)
SELECT
ae_date,
ae_time,
ae_fol_b_focal_id,
ae_b_aggressor_id,
ae_b_recipient_id,
ae_source,
ae_full_description,
ae_comments,
dup
FROM potential_role_dupes
WHERE ae_b_aggressor_id = ae_b_recipient_id
ORDER BY ae_date, ae_fol_b_focal_id, ae_time;
Processing note
This data problem generates the following error but note that, in practice, this error could be generated for other reasons as well.
./load_chunks.sh load_aggressions.m4 clean.aggression_event
psql:<stdin>:394: ERROR: duplicate key value violates unique constraint "On ROLES, Participant + EID must be unique"
DETAIL: Key (participant, eid)=(FD, 759513) already exists.
CONTEXT: SQL statement "INSERT INTO roles (
eid
, role
, participant)
VALUES (
CURRVAL('events_eid_seq')
, 'Actee'
, this_ae.ae_b_recipient_id)"
PL/pgSQL function inline_code_block line 186 at SQL statement
make: *** [Makefile:373: load_aggressions] Error 3
SOLUTION
ALLOW FOR NOW. EVENTUALLY A VOLUNTEER CAN CHECK AND FIX
Karl's comment
If we allow, we allow for all event types, not just aggressions. Groomings, matings, etc would all allow "self-dealing". I'd rather not.
How about not converting this data and letting people who want it get it from the clean schema?
do not convert these rows, fix them later
(#84) Some follows have a community with trailing spaces
Problem
There are 3 follows where the fol_cl_community_id has trailing spaces.
Bad Data
Cleaned in clean schema, so query easy schema.
select '"' || fol_cl_community_id || '"' AS fol_cl_community_id_untrimmed, * FROM easy.follow WHERE RTRIM(fol_cl_community_id) <> fol_cl_community_id;
SOLUTION
KARL TO FIX
Fix by trimming spaces in the clean schema.
* (#85) There are numerous instances of local food names that translate to multiple scientific food names.
Problem
There are 47 records documenting instances where a local food name (fl_local_food_name) is associated with more than one food scientific food name (fl_sci_food_name).
Bad Data
WITH duplicates AS (
SELECT
fl_sci_food_name,
COUNT(*) AS count
FROM clean.food_lookup
GROUP BY fl_sci_food_name
HAVING COUNT(*) > 1
)
SELECT
fl_local_food_name,
duplicates.fl_sci_food_name
FROM clean.food_lookup
JOIN duplicates ON clean.food_lookup.fl_sci_food_name = duplicates.fl_sci_food_name
ORDER BY fl_sci_food_name;
Notes
But see problem #87. This problem is only relevant if codes.food_names.description is derived from fl_sci_food_name (i.e., not fl_sci_food_name_gen); else, refer to #87.
SOLUTION
ALLOW FOR NOW, BUT NEEDS TO BE DISCUSSED AS A PI GROUP
9/3/2026 - ICG: Actually this returns cases where a scientific food name is associated with more than one local food name. That is, There can be multiple words for the same latin name.
Karl's comment
To allow, do some hackery when constructing the support tables. Make non-unique values unique by adding extra "duplicate #1 --" sort of text into the value of the column. Either "by hand" in the conversion code or by way of some algorythm.
(#86) There are numerous instances in the food_part_lookup that that conflate name, initial, and/or the english translation.
Problem
There are numerous instances in the food_part_lookup that that conflate name, initial, and/or the english translation. These issues are detailed below. Please note that this problem concerns the food_part_lookup table specifically.
Related, we need to clarify how we are treating the initials of food parts. Currently, initials are added to the codes.food_parts table with the corresponding food part name but with a trailing `-initial` so as not to violate the codes.food_parts.description unique constraint. So, both the food part name and food part initials are included in food events. This is probably not a good approach. Better would be to map the initial to the corresponding food part name and use only the full name (but then you do lose some reference to the original data). Ian needs to please consider.
Bad Data
query
WITH source_rows AS (
SELECT
ROW_NUMBER() OVER () AS src_ord,
COALESCE((
SELECT ARRAY_AGG(tok)
FROM (
SELECT BTRIM(x) AS tok
FROM REGEXP_SPLIT_TO_TABLE(COALESCE(fpl_local_food_part, ''), E'[;:,]') AS x
WHERE BTRIM(x) <> ''
) s
), ARRAY[]::text[]) AS local_arr,
COALESCE((
SELECT ARRAY_AGG(tok)
FROM (
SELECT BTRIM(x) AS tok
FROM REGEXP_SPLIT_TO_TABLE(COALESCE(fpl_food_part_initials, ''), E'[;:,]') AS x
WHERE BTRIM(x) <> ''
) s
), ARRAY[]::text[]) AS initials_arr,
COALESCE((
SELECT ARRAY_AGG(tok)
FROM (
SELECT BTRIM(x) AS tok
FROM REGEXP_SPLIT_TO_TABLE(COALESCE(fpl_english_food_part, ''), E'[;:,]') AS x
WHERE BTRIM(x) <> ''
) s
), ARRAY[]::text[]) AS english_arr
FROM clean.food_part_lookup
),
expanded AS (
SELECT
s.src_ord,
gs.idx,
COALESCE(s.local_arr[gs.idx], '') AS fpl_local_food_part,
COALESCE(s.initials_arr[gs.idx], '') AS fpl_food_part_initials,
COALESCE(s.english_arr[gs.idx], '') AS fpl_english_food_part
FROM source_rows s
CROSS JOIN LATERAL GENERATE_SERIES(
1,
GREATEST(
CARDINALITY(s.local_arr),
CARDINALITY(s.initials_arr),
CARDINALITY(s.english_arr)
)
) AS gs(idx)
),
deduped AS (
SELECT
e.*,
ROW_NUMBER() OVER (
PARTITION BY
e.fpl_local_food_part,
e.fpl_food_part_initials,
e.fpl_english_food_part
ORDER BY e.src_ord, e.idx
) AS rn
FROM expanded e
)
SELECT
fpl_local_food_part,
fpl_food_part_initials,
fpl_english_food_part
FROM deduped
WHERE rn = 1
ORDER BY src_ord, idx
fpl_local_food_part,
fpl_food_part_initials,
fpl_english_food_part;
summary
| fpl_local_food_part | fpl_food_part_initials | fpl_english_food_part |
|---|---|---|
| CHIPUKIZA | C | SHOOTS |
| MAJANI | J | LEAVES |
| MBEGU | MB | SEEDS |
| WADUDU | W | INSECTS |
| MABUA | B | PITH |
| MAGOMA | G | BARK |
| MATUNDA | T | FRUIT |
| MAUA | M | FLOWERS |
| UTOMVI | U | SAP |
| WADUDU WENGINE | D | INSECTS |
| UTOMVU | U | SAP |
| MCHWA | W | TERMITES |
| MIFUPA | NA | BONES |
| MITI | NA | TREE |
| MIZIZI | NA | ROOTS |
| NA | NA | NOT APPLICABLE |
| NONE | NA | None |
| NYAMA | N | MEAT |
| SIAFU | S | INSECTS |
| UNRECORDED | NA | UNRECORDED |
| WADUDU | D | INSECTS |
notes
- there are two initials (`W`, `D`) for `WADUDU` ~ `INSECTS`
- need to clarify `WADUDU WENGINE`, which also shares an initial (`D`) and english translation (`INSECTS`) as `WADUDU`
- the initial `W` is associated with both `WADUDU` and `MCHWA`
- different spellings for `SAP`: `UTOMVU` and `UTOMVI`
- `INSECTS` associated with `WADUDU`, `WADUDU WENGINE`, and `SIAFU`
- how should we treat `NA`, `NONE`, and `UNRECORDED`
SOLUTION
9/4/2026 Ian fixed mifupa, miti and mizizi in FOOD_BOUT by changing to UNRECORDED. DELETED FROM FOOD_PART_LOOKUP Consolidated insects to 'dudu' changed all "NA" to "None" Kept unrecorded fixed spellings of utomvi and chipukizi
I made all associated changes in FOOD_BOUT, choosing to use names rather than initials
* (#87) There are numerous duplicate fl_sci_food_name_gen values in food_lookup.
Problem
There are 42 instances where fl_sci_food_name_gen values are associated with more than one fl_local_food_name value. This problem assumes that we are using the fl_sci_food_name_gen value for food_lookup.description (i.e., instead of fl_sci_food_name). If instead, food_lookup.description should reflect fl_sci_food_name then this particular problem is moot and can be ignored (but other problems will certainly arise with the switch to fl_sci_food_name).
Bad Data
WITH ranked AS (
SELECT
fl_local_food_name,
fl_sci_food_name,
fl_sci_food_name_gen,
COUNT(*) OVER (
PARTITION BY BTRIM(fl_sci_food_name_gen)
) AS sci_gen_count
FROM clean.food_lookup
WHERE fl_sci_food_name_gen IS NOT NULL
AND BTRIM(fl_sci_food_name_gen) <> ''
)
SELECT
fl_local_food_name,
fl_sci_food_name,
fl_sci_food_name_gen,
sci_gen_count
FROM ranked
WHERE sci_gen_count > 1
ORDER BY fl_sci_food_name_gen, fl_local_food_name, fl_sci_food_name;
Notes
But see problem #85. This problem is only relevant if codes.food_names.description is derived from fl_sci_food_name_gen (i.e., not fl_sci_food_name); else, refer to #85.
SOLUTION
ALLOW FOR NOW, DISCUSS AS A PI GROUP
* (#88) There are food_bout rows for which there is not a corresponding food name.
Problem
There are 2,114 clean.food_bout rows for which there is not a matching codes.food_names.name.
Bad data
Full record
Exact-match diagnostic: food_bout food names missing from codes.food_names
WITH food_rows AS (
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
1 AS seq,
fb_fl_local_food_name AS foodname
FROM clean.food_bout
-- WHERE fb_fl_local_food_name IS NOT NULL
UNION ALL
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
2 AS seq,
fb_local_food_name2 AS foodname
FROM clean.food_bout
-- WHERE NULLIF(fb_local_food_name2, '') IS NOT NULL
)
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
seq,
foodname
FROM food_rows
WHERE foodname NOT IN (
SELECT name
FROM codes.food_names
)
ORDER BY
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
seq,
foodname;
Summary of non-compliant values
Use this to list only the distinct food names present in clean.food_bout but missing from codes.food_names.
WITH food_rows AS (
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
1 AS seq,
fb_fl_local_food_name AS raw_foodname,
BTRIM(fb_fl_local_food_name) AS trimmed_foodname
FROM clean.food_bout
-- WHERE fb_fl_local_food_name IS NOT NULL
UNION ALL
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
2 AS seq,
fb_local_food_name2 AS raw_foodname,
BTRIM(fb_local_food_name2) AS trimmed_foodname
FROM clean.food_bout
-- WHERE NULLIF(fb_local_food_name2, '') IS NOT NULL
)
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
seq,
raw_foodname,
trimmed_foodname
FROM food_rows
WHERE raw_foodname NOT IN (
SELECT name
FROM codes.food_names
)
AND trimmed_foodname IN (
SELECT name
FROM codes.food_names
)
ORDER BY
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
seq,
raw_foodname;
white-space specific mismatches
Whitespace-only diagnostic: rows excluded only because of whitespace differences This finds values whose trimmed form exists in codes.food_names, but the raw value does not.
WITH food_rows AS (
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
1 AS seq,
fb_fl_local_food_name AS raw_foodname,
BTRIM(fb_fl_local_food_name) AS trimmed_foodname
FROM clean.food_bout
-- WHERE fb_fl_local_food_name IS NOT NULL
UNION ALL
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
2 AS seq,
fb_local_food_name2 AS raw_foodname,
BTRIM(fb_local_food_name2) AS trimmed_foodname
FROM clean.food_bout
-- WHERE NULLIF(fb_local_food_name2, '') IS NOT NULL
)
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
seq,
raw_foodname,
trimmed_foodname
FROM food_rows
WHERE raw_foodname NOT IN (
SELECT name
FROM codes.food_names
)
AND trimmed_foodname IN (
SELECT name
FROM codes.food_names
)
ORDER BY
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
seq,
raw_foodname;
SOLUTION
ALLOW FOR NOW. NEED TO CHECK WHETHER LEGIT NEW FOODS OR JUST POOR HANDWRITING, SPELLING, ETC
Karl's comments
Ok. Allowing means adding a bunch of odd values, that could continue to be used in the future until the data is cleaned and the "extra" codes removed.
* (#89) There is a food_bout row that has both a compound fb_fpl_local_food_part value and where fb_fpl_local_food_part2 is not null.
Problem
| FB_FPL_local_food_part | FB_FPL_local_food_part2 |
|---|---|
| MATUNDA; CHIPUKIZA | MATUNDA; CHIPUKIZA |
SOLUTION
SHOULD BE ALLOWED
* (#90) There are food_bout rows for which the fb_fpl_local_food_part or fb_fpl_local_food_part2 does not have a matching value in codes.food_parts.
Problem
There are 229 records for which a food_bout row does not have a match between for which the fb_fpl_local_food_part or fb_fpl_local_food_part2 and codes.food_parts. Note, however, that the record count is somewhat ambiguous since we are deailing with multiple parts (1 and 2), and multiple rows per event.
Bad data
Full record
diagnostic: fb_fpl_local_food_part or fb_fpl_local_food_part2 does not have a matching value in codes.food_parts
WITH prepared AS (
SELECT
fb.*,
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
) AS part1_norm,
UPPER(
NULLIF(
BTRIM(
split_part(
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
),
';',
1
)
),
''
)
) AS seq1_part,
CASE
WHEN regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
) LIKE '%;%'
THEN UPPER(
NULLIF(
BTRIM(
split_part(
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
),
';',
2
)
),
''
)
)
ELSE UPPER(NULLIF(BTRIM(fb.fb_fpl_local_food_part2), ''))
END AS seq2_part
FROM clean.food_bout fb
)
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
fb_fpl_local_food_part,
fb_fpl_local_food_part2,
seq1_part,
seq2_part,
CASE
WHEN seq1_part NOT IN (SELECT part FROM codes.food_parts)
THEN 'seq1_part missing from codes.food_parts'
WHEN seq2_part IS NOT NULL
AND seq2_part NOT IN (SELECT part FROM codes.food_parts)
THEN 'seq2_part missing from codes.food_parts'
END AS exclusion_reason
FROM prepared
WHERE seq1_part NOT IN (SELECT part FROM codes.food_parts)
OR (
seq2_part IS NOT NULL
AND seq2_part NOT IN (SELECT part FROM codes.food_parts)
)
ORDER BY
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time;
Summary of non-compliant values
WITH prepared AS (
SELECT
UPPER(
NULLIF(
BTRIM(
split_part(
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
),
';',
1
)
),
''
)
) AS seq1_part,
CASE
WHEN regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
) LIKE '%;%'
THEN UPPER(
NULLIF(
BTRIM(
split_part(
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
),
';',
2
)
),
''
)
)
ELSE UPPER(NULLIF(BTRIM(fb.fb_fpl_local_food_part2), ''))
END AS seq2_part
FROM clean.food_bout fb
)
SELECT missing_part, COUNT(*) AS occurrences
FROM (
SELECT seq1_part AS missing_part
FROM prepared
WHERE seq1_part NOT IN (SELECT part FROM codes.food_parts)
UNION ALL
SELECT seq2_part AS missing_part
FROM prepared
WHERE seq2_part IS NOT NULL
AND seq2_part NOT IN (SELECT part FROM codes.food_parts)
) x
GROUP BY missing_part
ORDER BY missing_part;
SOLUTION
ALLOW FOR NOW. NEED TO HAND CHECK AND DECIDE ON PROTOCOL
Karl's comments
Stevan can chime in here, but I think this means adding more illegitimate "legitimate" codes.
* (#91) There are food_bout rows that have fb_begin_feed_time > fb_end_feed_time.
Problem
There are 43 clean.food_bout rows that have fb_begin_feed_time > fb_end_feed_time.
Bad data
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
fb_begin_feed_time - fb_end_feed_time AS inverted_by,
fb_fpl_local_food_part,
fb_fl_local_food_name
FROM clean.food_bout
WHERE fb_begin_feed_time IS NOT NULL
AND fb_end_feed_time IS NOT NULL
AND fb_begin_feed_time > fb_end_feed_time
ORDER BY fb_fol_date, fb_fol_b_focal_animid, fb_begin_feed_time;
SOLUTION
IAN/VOLUNTEER TO FIX
* (#92) There are food_bout rows that do not have a corresponding follow.
Problem
clean.food_bout references follows by (fb_fol_b_focal_animid, fb_fol_date), but not all rows (n=455) match sokwedb.follows exactly.
Bad data
Includes both:
- Normalization-only matches: match after TRIM+UPPER (raw focal text quality issue).
- True misses: no matching follow even after normalization (actual source data gap).
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
fb_begin_feed_time - fb_end_feed_time AS inverted_by,
fb_fpl_local_food_part,
fb_fl_local_food_name
FROM clean.food_bout
WHERE fb_begin_feed_time IS NOT NULL
AND fb_end_feed_time IS NOT NULL
AND fb_begin_feed_time > fb_end_feed_time
ORDER BY fb_fol_date, fb_fol_b_focal_animid, fb_begin_feed_time;
SOLUTION
NEED TO LOOK AT THE SOURCE OF THE PROBLEM B/C NO-ONE ENTERS FEEDING FROM B-REC NOTES.
Karl's comments
I think it makes sense to normalize the data in the clean schema, upper casing and removing spaces. And then deal with what's left.
* (#93) Need to address how we treat behaviours for the second value of food parts AND how they are derived.
Problem
1. In load_food_events.sql, we are loading into table food_events, where each row requires both:
- foodpart (NOT NULL + FK)
- foodname (NOT NULL + FK)
...but for seq=2, these cases behave differently:
- part2 exists, name2 missing: insert NODATA to name2
- part2 missing, name2 exists: insert NODATA to part2
- both missing: no seq=2 row.
- both exist: normal seq=2 row.
This approach required adding NODATA to codes.food_names and codes.food_parts.
Require approval for this approach or an altnernative.
2. Multiple food parts can be dervied in one of two ways:
- there are values for both fb_fpl_local_food_part and fb_fpl_local_food_part2, or
- there are multiple food parts in fb_fpl_local_food_part separated by a `;` (usually) or, rarely, `:`
Do we need to document or log if fb_fpl_local_food_part2 is derived from a compound fb_fpl_local_food_part value?
Bad data
count of values that meet these conditions
WITH prepared AS (
SELECT
fb.*,
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
) AS part1_norm,
UPPER(
NULLIF(
BTRIM(
split_part(
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
),
';',
2
)
),
''
)
) AS seq2_from_part1,
UPPER(NULLIF(BTRIM(fb.fb_fpl_local_food_part2), '')) AS seq2_from_part2,
NULLIF(BTRIM(fb.fb_local_food_name2), '') AS seq2_name
FROM clean.food_bout fb
),
effective AS (
SELECT
*,
CASE
WHEN part1_norm LIKE '%;%' THEN seq2_from_part1
ELSE seq2_from_part2
END AS seq2_part
FROM prepared
),
seq2_rows AS (
SELECT
COALESCE(seq2_part, 'NODATA') AS final_seq2_part,
COALESCE(seq2_name, 'NODATA') AS final_seq2_name
FROM effective
WHERE seq2_part IS NOT NULL
OR seq2_name IS NOT NULL
)
SELECT
CASE
WHEN final_seq2_part = 'NODATA' AND final_seq2_name = 'NODATA' THEN 'both_nodata'
WHEN final_seq2_part = 'NODATA' THEN 'part_nodata_only'
WHEN final_seq2_name = 'NODATA' THEN 'name_nodata_only'
ELSE 'no_nodata'
END AS seq2_case,
COUNT(*) AS rows
FROM seq2_rows
GROUP BY 1
ORDER BY 1;
full record
WITH prepared AS (
SELECT
fb.*,
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
) AS part1_norm,
UPPER(
NULLIF(
BTRIM(
split_part(
regexp_replace(
COALESCE(fb.fb_fpl_local_food_part, ''),
E'\\s*[:;]\\s*',
';',
'g'
),
';',
2
)
),
''
)
) AS seq2_from_part1,
UPPER(NULLIF(BTRIM(fb.fb_fpl_local_food_part2), '')) AS seq2_from_part2,
NULLIF(BTRIM(fb.fb_local_food_name2), '') AS seq2_name
FROM clean.food_bout fb
),
effective AS (
SELECT
*,
CASE
WHEN part1_norm LIKE '%;%' THEN seq2_from_part1
ELSE seq2_from_part2
END AS seq2_part
FROM prepared
)
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_fpl_local_food_part,
fb_fpl_local_food_part2,
fb_fl_local_food_name,
fb_local_food_name2,
COALESCE(seq2_part, 'NODATA') AS final_seq2_part,
COALESCE(seq2_name, 'NODATA') AS final_seq2_name
FROM effective
WHERE seq2_part IS NOT NULL
OR seq2_name IS NOT NULL
ORDER BY
fb_fol_date,
fb_fol_b_focal_animid,
fb_fl_local_food_name;
SOLUTION
GET STEVAN TO EXPLAIN TO ICG AND ELO
* (#94) There are food_bout rows where the start or end of the observation is outside of allowable bounds.
Problem
There are 11 record where the food_bout is is outside of allowable bounds.
Bad data
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time,
CASE
WHEN fb_begin_feed_time IS NULL THEN 'BEGIN_NULL'
WHEN fb_begin_feed_time < '04:00:00'::time THEN 'BEGIN_BEFORE_MIN'
WHEN fb_begin_feed_time > '20:00:00'::time THEN 'BEGIN_AFTER_MAX'
ELSE 'BEGIN_OK'
END AS begin_status,
CASE
WHEN fb_end_feed_time IS NULL THEN 'END_NULL'
WHEN fb_end_feed_time < '04:00:00'::time THEN 'END_BEFORE_MIN'
WHEN fb_end_feed_time > '20:00:00'::time THEN 'END_AFTER_MAX'
ELSE 'END_OK'
END AS end_status,
COALESCE(
LEAST(GREATEST(fb_begin_feed_time, '04:00:00'::time), '20:00:00'::time),
'04:00:00'::time
) AS clamped_begin,
COALESCE(
LEAST(GREATEST(fb_end_feed_time, '04:00:00'::time), '20:00:00'::time),
'04:00:00'::time
) AS clamped_end
FROM clean.food_bout
WHERE fb_begin_feed_time IS NULL
OR fb_end_feed_time IS NULL
OR fb_begin_feed_time < '04:00:00'::time
OR fb_begin_feed_time > '20:00:00'::time
OR fb_end_feed_time < '04:00:00'::time
OR fb_end_feed_time > '20:00:00'::time
ORDER BY fb_fol_date, fb_fol_b_focal_animid, fb_begin_feed_time;
SOLUTION
ALLOW FOR NOW. IAN TO FIX
Karl's comment
See remarks on problem #81.
(#95) There is at least one food_bout rows where the start or stop time has a seconds value.
Problem
There is one food_bout row where the start or stop time has a seconds value, which violates our time-check constraints.
Bad data
SELECT
fb_fol_date,
fb_fol_b_focal_animid,
fb_begin_feed_time,
fb_end_feed_time
FROM clean.food_bout
WHERE EXTRACT(SECOND FROM fb_begin_feed_time) <> 0
OR EXTRACT(SECOND FROM fb_end_feed_time) <> 0
ORDER BY fb_fol_date, fb_fol_b_focal_animid, fb_begin_feed_time;
SOLUTION
IAN (HOPEFULLY) FIXED IN ACCESS 7/3
Karl's comment
Stevan, this is supposed to be fixed in the transition from the raw schema to the tidy schema. At least this is where the date and time intervals are normalized.
Maybe it got missed, or just needed bringing up as an issue. No real point in fixing it in MS Access. Doing so feels like losing information, and possibly being inconsistent in the way time intervals are normalized.
Update 2026-07-22 SRE cannot reproduce this error, e.g., see below querying the tidy schema, which returns zero records; calling this resolved.
SELECT
"FB_FOL_date",
"FB_FOL_B_focal_AnimID",
"FB_begin_feed_time",
"FB_end_feed_time"
FROM tidy."FOOD_BOUT"
WHERE EXTRACT(SECOND FROM "FB_begin_feed_time") <> 0
OR EXTRACT(SECOND FROM "FB_end_feed_time") <> 0
ORDER BY "FB_FOL_date", "FB_FOL_B_focal_AnimID", "FB_begin_feed_time";
(#96) There are GROOMINGS rows that have direction values that do not match to mappable values.
Problem
There are 26 GROOMINGS rows that have have a direction other than 'G', 'R', 'M', 'U', which are the only values that map to an allowable direction.
Bad data
Summary of non-compliant values
SELECT
COALESCE(grm_direction, '<NULL>') AS raw_direction,
quote_literal(grm_direction) AS quoted_direction,
UPPER(COALESCE(grm_direction, '')) AS upper_no_trim,
LENGTH(COALESCE(grm_direction, '')) AS raw_length,
COUNT(*) AS row_count
FROM clean.groom_bout
WHERE UPPER(COALESCE(grm_direction, '')) NOT IN ('G', 'R', 'M', 'U')
GROUP BY
COALESCE(grm_direction, '<NULL>'),
quote_literal(grm_direction),
UPPER(COALESCE(grm_direction, '')),
LENGTH(COALESCE(grm_direction, ''))
ORDER BY row_count DESC, raw_direction;
Full record
SELECT
grm_fol_date,
grm_fol_b_focal_animid,
grm_b_partner_animid,
grm_direction,
quote_literal(grm_direction) AS quoted_direction,
UPPER(COALESCE(grm_direction, '')) AS upper_no_trim,
LENGTH(COALESCE(grm_direction, '')) AS raw_length,
(
COALESCE(grm_direction, '') ~ '^[[:space:]]'
OR COALESCE(grm_direction, '') ~ '[[:space:]]$'
) AS has_edge_space,
grm_problems,
grm_comments,
grm_extracted_by
FROM clean.groom_bout
WHERE UPPER(COALESCE(grm_direction, '')) NOT IN ('G', 'R', 'M', 'U')
ORDER BY grm_fol_date, grm_fol_b_focal_animid, grm_b_partner_animid;
SOLUTION
ICG FIXED IN ACCESS 8/18/2026
(#97) There are GROOMINGS rows that have certainty values that do not match to mappable values.
Problem
There are 303 GROOMINGS rows that have have a direction other than 'Y' or 'N', which are the only values that map to an allowable certainty (Y -> 1; N -> 0). Only values of 0 and 1 are allowed in events.certainty.
Bad data
Summary of non-compliant values
SELECT
COALESCE(grm_time_certainty, '<NULL>') AS raw_certainty,
quote_literal(grm_time_certainty) AS quoted_certainty,
UPPER(COALESCE(grm_time_certainty, '')) AS upper_no_trim,
LENGTH(COALESCE(grm_time_certainty, '')) AS raw_length,
COUNT(*) AS row_count
FROM clean.groom_bout
WHERE UPPER(COALESCE(grm_time_certainty, '')) NOT IN ('Y', 'N')
GROUP BY
COALESCE(grm_time_certainty, '<NULL>'),
quote_literal(grm_time_certainty),
UPPER(COALESCE(grm_time_certainty, '')),
LENGTH(COALESCE(grm_time_certainty, ''))
ORDER BY row_count DESC, raw_certainty;
Full record
SELECT
grm_fol_date,
grm_fol_b_focal_animid,
grm_b_partner_animid,
grm_time_certainty,
quote_literal(grm_time_certainty) AS quoted_certainty,
UPPER(COALESCE(grm_time_certainty, '')) AS upper_no_trim,
LENGTH(COALESCE(grm_time_certainty, '')) AS raw_length,
(
COALESCE(grm_time_certainty, '') ~ '^[[:space:]]'
OR COALESCE(grm_time_certainty, '') ~ '[[:space:]]$'
) AS has_edge_space,
grm_problems,
grm_comments,
grm_extracted_by
FROM clean.groom_bout
WHERE UPPER(COALESCE(grm_time_certainty, '')) NOT IN ('Y', 'N')
ORDER BY grm_fol_date, grm_fol_b_focal_animid, grm_b_partner_animid;
SOLUTION
ICG FIXED IN ACCESS 8/18/2026
(#98) --- empty placeholder to restore count
(#99) There are GROOMINGS rows for which the extractedBy values is empty.
Problem
There are 10,710 GROOMINGS rows for which the extractedBy values is empty. This value cannot be NULL.
Bad data
SELECT
grm_fol_date,
grm_fol_b_focal_animid,
grm_b_partner_animid,
grm_direction,
grm_time_certainty,
grm_extracted_by,
quote_literal(grm_extracted_by) AS quoted_extracted_by,
LENGTH(COALESCE(grm_extracted_by, '')) AS raw_length,
grm_problems,
grm_comments
FROM clean.groom_bout
WHERE NULLIF(BTRIM(grm_extracted_by), '') IS NULL
ORDER BY grm_fol_date, grm_fol_b_focal_animid, grm_b_partner_animid;
SOLUTION
ICG CHANGED ALL NULLS TO 'UNK' IN ACCESS. 8/18/2026
* (#100) There are GROOMINGS rows that have extractedby values that do not match to mappable people values.
Problem
There are 14,771 GROOMINGS rows that have extractedby values that do not match to mappable codes.people values.
Bad data
Summary of non-compliant values
SELECT
BTRIM(gb.grm_extracted_by) AS normalized_extracted_by,
COUNT(*) AS row_count
FROM clean.groom_bout gb
WHERE NULLIF(BTRIM(gb.grm_extracted_by), '') IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM codes.people p
WHERE p.person = BTRIM(gb.grm_extracted_by)
)
GROUP BY BTRIM(gb.grm_extracted_by)
ORDER BY row_count DESC, normalized_extracted_by;
Full record
SELECT
gb.grm_fol_date,
gb.grm_fol_b_focal_animid,
gb.grm_b_partner_animid,
gb.grm_extracted_by,
BTRIM(gb.grm_extracted_by) AS normalized_extracted_by,
gb.grm_direction,
gb.grm_time_certainty,
gb.grm_problems,
gb.grm_comments
FROM clean.groom_bout gb
WHERE NULLIF(BTRIM(gb.grm_extracted_by), '') IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM codes.people p
WHERE p.person = BTRIM(gb.grm_extracted_by)
)
ORDER BY gb.grm_fol_date, gb.grm_fol_b_focal_animid, gb.grm_b_partner_animid;
* (#101) There are AGGRESSION_EVENT rows that have NULL focals, a NULL ae_fol_b_focal_id value
Problem
There are 144 aggression_event rows that have NULL for the focal.
Bad data
SELECT * FROM easy.aggression_event WHERE ae_fol_b_focal_id IS NULL;
Solution
Change these values to the unknown individual, UNK.
This means that these rows are converted to WATCHES rows with a Type of B, and UNK as the focal.
The data needs to be reviewed to see if this is appropriate. See also problem #102.
* (#102) There are AGGRESSION_EVENT rows that have NULL times, a NULL ae_time value
Problem
There are 107 aggression_event rows that have NULL for the time. Note that all of these rows also have NULL for the focal, problem #101.
Bad data
SELECT * FROM easy.aggression_event WHERE ae_time IS NULL;
Solution
Change these values to the unknown time, midnight, 00:00.
These need to be reviewed to ensure the change is appropriate.
Meeting discussions indicate the many, ideally all, of these rows are the result of perusal of a book's content, where aggressions were mentioned but these aggressions did not otherwise appear in the data.
(#103) There are BIOGRAPHY rows that have empty birthgroup values.
Problem
There are 64 BIOGRAPHY rows that have empty strings for birthgroup; NULL values are allowed but not empty strings. Some of the offending records are addressed also in Problem #13.
Bad data
note: (temporary) solution applied to clean so query tidy
SELECT "B_AnimID",
"B_AnimName",
quote_nullable("B_BirthGroup") AS birthgroup,
length("B_BirthGroup") AS character_length,
octet_length("B_BirthGroup") AS byte_length,
"B_BGCertainty"
FROM tidy."BIOGRAPHY"
WHERE "B_BirthGroup" IS NOT NULL
AND BTRIM("B_BirthGroup") = ''
ORDER BY "B_AnimID";
Solution
Propose converting empty strings to NULL in the clean schema, PI to confirm.
UPDATE biography SET b_birthgroup = NULLIF(BTRIM(b_birthgroup), '') WHERE b_birthgroup IS DISTINCT FROM NULLIF(BTRIM(b_birthgroup), '');
Addressed with commit f2ddb85cd22d3e5233d049d92ae8cb361a538114
(#104) There are BIOGRAPHY rows that have empty momid values.
Problem
There are 89 BIOGRAPHY rows that have empty strings for momid; NULL values are allowed but not empty strings.
Bad data
note: (temporary) solution applied to clean so query tidy
SELECT "B_AnimID",
"B_AnimName",
quote_nullable("B_MomID") AS momid,
length("B_MomID") AS character_length,
octet_length("B_MomID") AS byte_length,
"B_Birthdate"
FROM tidy."BIOGRAPHY"
WHERE "B_MomID" IS NOT NULL
AND BTRIM("B_MomID") = ''
ORDER BY "B_AnimID";
Solution
Propose converting empty strings to NULL in the clean schema, PI to confirm.
UPDATE biography SET b_momid = NULLIF(BTRIM(b_momid), '') WHERE b_momid IS DISTINCT FROM NULLIF(BTRIM(b_momid), '');
Addressed with commit 5e0e787825e847eac1cc93375e26ea1d3f715d3e
(#105) There is a comm member log record that is missing a description.
Problem
There is a single comm member log records where the description is missing.
Bad data
note: querying tidy
SELECT date_of_update,
chimp_id,
quote_nullable(update_description) AS update_description,
length(update_description) AS description_length,
update_rationale,
"made by"
FROM tidy."COMMUNITY_MEMBERSHIP_UPDATE_LOG"
WHERE update_description IS NULL
OR BTRIM(update_description) = ''
ORDER BY date_of_update,
chimp_id,
update_rationale,
"made by";
solution
Fixed in Access by ICG - duplicated update_rationale
* (#106) There are GROOM_SCANS records whose extracted-by person is not in PEOPLE.
Problem
There are 44,679 GROOM_SCANS records where the GS_extracted_by value is not a PEOPLE.Person value. All GROOM_SCANS records are affected. There are four distinct offending values: KASEN, KAREN MCLELLAN, Anika Richter, and Janelle Carmichael.
Bad data
note: querying clean, the schema read by the production loader
SELECT gs.gs_date,
gs.gs_fol_b_focal_animid,
gs.gs_time,
gs.gs_b_chimp1_animid,
gs.gs_b_chimp2_animid,
quote_nullable(gs.gs_extracted_by) AS gs_extracted_by,
length(gs.gs_extracted_by) AS extracted_by_length
FROM clean.groom_scans AS gs
WHERE NOT EXISTS (
SELECT 1
FROM codes.people AS people
WHERE people.person = gs.gs_extracted_by)
ORDER BY gs.gs_date,
gs.gs_fol_b_focal_animid,
gs.gs_time,
gs.gs_b_chimp1_animid,
gs.gs_b_chimp2_animid;
* (#107) There are GROOM_SCAN_AREC records having the same chimpanzee as both grooming participants.
Problem
There are 1,784 GROOM_SCAN_AREC records where Chimp_1 and Chimp_2 are the same individual. The records span 210 dates and 39 distinct chimpanzees, and all have a Direction value of M. The production loader converts Direction M into two Mutual ROLES rows. These records cannot be loaded because a participant may occur only once in an event and the attendance groom-scan rules require two different participants. The project investigators must determine whether these records represent self-grooming or erroneous participant data and how they should be represented in the production database.
Bad data
note: querying clean, the schema read by the production loader
SELECT gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
gs.direction
FROM clean.groom_scan_arec AS gs
WHERE gs.chimp_1 = gs.chimp_2
ORDER BY gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2;
* (#108) There are GROOM_SCAN_AREC participants that are not in BIOGRAPHY_DATA.
Problem
There are 1,124 GROOM_SCAN_AREC records having at least one participant that is not a BIOGRAPHY_DATA.AnimID value. There are 81 distinct missing participant values. The missing value occurs in Chimp_1 in 112 records and in Chimp_2 in 1,016 records; four records have missing values in both columns. None of the 81 values are present in clean.BIOGRAPHY, from which BIOGRAPHY_DATA is loaded. The production loader cannot create the related ROLES rows until the project investigators determine whether these values identify individuals that should be added to BIOGRAPHY, corrected to existing AnimID values, or otherwise represented.
Bad data
note: querying clean, the schema read by the production loader, and sokwedb.BIOGRAPHY_DATA, the referenced production table
SELECT gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
gs.direction,
bad_participant.source_column,
quote_nullable(bad_participant.animid) AS missing_animid,
length(bad_participant.animid) AS animid_length
FROM clean.groom_scan_arec AS gs
CROSS JOIN LATERAL (
VALUES ('Chimp_1', gs.chimp_1),
('Chimp_2', gs.chimp_2)
) AS bad_participant(source_column, animid)
WHERE NOT EXISTS (
SELECT 1
FROM sokwedb.biography_data AS bio
WHERE bio.animid = bad_participant.animid)
ORDER BY gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
bad_participant.source_column;
SOLUTION
HAVE A STUDENT CHECK AGAINST PAPER
* (#109) There are GROOM_SCAN_AREC records whose dates have no ATTENDANCE records from which to obtain a community.
Problem
There are 5,111 GROOM_SCAN_AREC records on 134 dates for which there is no ATTENDANCE record on the same date. GROOM_SCAN_AREC does not contain a community value, and the production loader obtains the WATCHES.CommID value from ATTENDANCE.A_CL_Community_ID by date. Consequently, the loader cannot create the required WATCHES row for these scans without assuming a community. After the temporary exclusions for Problems #107 and #108, 4,786 otherwise-loadable records on 133 dates are affected. The project investigators must determine the appropriate community for these scan dates.
Bad data
note: querying clean, the schema read by the production loader
SELECT gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
gs.direction
FROM clean.groom_scan_arec AS gs
WHERE NOT EXISTS (
SELECT 1
FROM clean.attendance AS attendance
WHERE attendance.a_date = gs.date)
ORDER BY gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2;
* (#110) There are GROOM_SCAN_AREC records involving individuals after their departure from the study.
Problem
There are 2,090 GROOM_SCAN_AREC records where at least one participant's scan date is after the individual's BIOGRAPHY_DATA.DepartDate. The records involve 18 distinct individuals on 177 dates. The offending individual occurs in Chimp_1 in 1,928 records and in Chimp_2 in 162 records; no record has both participants after their departure dates. After the temporary exclusions for Problems #107, #108, and #109, 1,725 otherwise-loadable records on 147 dates are affected. The production schema requires event participants to be under study on the WATCHES.Date, so these records cannot be loaded until the project investigators determine whether the scan participants or biography departure dates should be corrected.
The trigger error originally reported PT's EntryDate, 1970-09-11, as its DepartDate due to an error in the trigger's diagnostic query. PT's actual BIOGRAPHY_DATA.DepartDate is 1973-04-17, which is before the first affected PT scan on 1973-05-25. The constraint comparison itself used the correct DepartDate and therefore rejected the row correctly.
Bad data
note: querying clean, the schema read by the production loader, and sokwedb.BIOGRAPHY_DATA, the referenced production table
SELECT gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
gs.direction,
bad_participant.source_column,
bad_participant.animid,
bio.departdate,
gs.date - bio.departdate AS days_after_departure
FROM clean.groom_scan_arec AS gs
CROSS JOIN LATERAL (
VALUES ('Chimp_1', gs.chimp_1),
('Chimp_2', gs.chimp_2)
) AS bad_participant(source_column, animid)
JOIN sokwedb.biography_data AS bio
ON bio.animid = bad_participant.animid
WHERE gs.date > bio.departdate
ORDER BY gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
bad_participant.source_column;
* (#111) There are GROOM_SCAN_AREC records involving individuals before their entry into the study.
Problem
There are 130 GROOM_SCAN_AREC records where at least one participant's scan date is before the individual's BIOGRAPHY_DATA.EntryDate. The records involve 10 distinct individuals on 21 dates. The offending individual occurs in Chimp_1 in 37 records and in Chimp_2 in 93 records; no record has both participants before their entry dates. After the temporary exclusions for Problems #107 through #110, 30 otherwise-loadable records on 11 dates are affected. The production schema requires event participants to be under study on the WATCHES.Date, so these records cannot be loaded until the project investigators determine whether the scan participants or biography entry dates should be corrected.
Bad data
note: querying clean, the schema read by the production loader, and sokwedb.BIOGRAPHY_DATA, the referenced production table
SELECT gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
gs.direction,
bad_participant.source_column,
bad_participant.animid,
bio.entrydate,
bio.entrydate - gs.date AS days_before_entry
FROM clean.groom_scan_arec AS gs
CROSS JOIN LATERAL (
VALUES ('Chimp_1', gs.chimp_1),
('Chimp_2', gs.chimp_2)
) AS bad_participant(source_column, animid)
JOIN sokwedb.biography_data AS bio
ON bio.animid = bad_participant.animid
WHERE gs.date < bio.entrydate
ORDER BY gs.date,
gs.time,
gs.chimp_1,
gs.chimp_2,
bad_participant.source_column;
* (#112) GROOM_BOUT uses U as an unknown initiator or terminator animal ID
Problem
Some GROOM_BOUT rows contain U in GRM_B_initiator_AnimId or GRM_B_terminator_AnimId. Although U is a valid value for GRM_direction, it is not a valid animal ID.
The production GROOMINGS.Initiator and GROOMINGS.Terminator columns reference participant rows in ROLES. During conversion, the loader creates roles for the focal and partner animals and then looks up the role corresponding to the recorded initiator or terminator. A value of U cannot match either participant, causing the loader to fail with query returned no rows.
This problem became visible after the Problem #99 and Problem #100 exclusions were removed. Those exclusions had prevented many affected grooming bouts from reaching the initiator and terminator lookup.
Bad data
The following query identifies every occurrence of U in the initiator and terminator animal-ID fields:
SELECT gb.grm_fol_date,
gb.grm_fol_b_focal_animid,
gb.grm_time_begin,
gb.grm_b_partner_animid,
gb.grm_direction,
invalid_participant.source_column,
invalid_participant.animid AS offending_animid,
gb.grm_extracted_by,
gb.grm_problems,
gb.grm_comments
FROM clean.groom_bout AS gb
CROSS JOIN LATERAL (
VALUES ('GRM_B_initiator_AnimId', gb.grm_b_initiator_animid),
('GRM_B_terminator_AnimId', gb.grm_b_terminator_animid)
) AS invalid_participant(source_column, animid)
WHERE BTRIM(invalid_participant.animid) = 'U'
ORDER BY gb.grm_fol_date,
gb.grm_fol_b_focal_animid,
gb.grm_time_begin,
gb.grm_b_partner_animid,
invalid_participant.source_column;
Solution
proposed
During construction of the clean schema, convert U in groom_bout.grm_b_initiator_animid and groom_bout.grm_b_terminator_animid to SQL NULL. Do not change groom_bout.grm_direction when it contains U, because that is a valid direction code representing unknown grooming direction.
The grooming loader already treats a NULL initiator or terminator as unknown and stores NULL in the corresponding production column without attempting to find a participant role.
Other nonempty initiator or terminator animal IDs that do not identify either the focal or partner animal should remain conversion errors and be reviewed separately rather than being converted automatically to NULL.
ICG or ELO to please confirm.
(#113) The Access BIOGRAPHY.NONE row lacks values required by BIOGRAPHY_DATA
Problem
The A-record groom-scan conversion represents feeding-station scans as A-record WATCHES. These scans have no focal individual, so the conversion uses the special AnimID NONE for the related WATCHES row rather than incorrectly identifying the focal individual as unknown with UNK. The production BIOGRAPHY_DATA table must therefore contain a NONE row.
The conversion originally created this row in clean.BIOGRAPHY. After the row was added to the Access BIOGRAPHY table, retaining that INSERT caused the clean-schema build to fail with a duplicate BIOGRAPHY primary key for NONE. Removing the INSERT exposed a second problem: the Access row contains NULL in fields that are required by BIOGRAPHY_DATA, beginning with BCCertainty, so it cannot be loaded directly into the production table.
Bad data
The affected row is the single Access BIOGRAPHY record whose B_AnimID is NONE. A diagnostic query is unnecessary because this is an intentionally defined sentinel record rather than an unidentified set of source-data rows.
Solution
Keep Access authoritative for the existence of the NONE BIOGRAPHY row. During construction of the clean schema, update only that row with the previously established no-focal placeholder values required by BIOGRAPHY_DATA. Do not insert a second row, modify the restored Access data in raw, or apply the correction directly to sokwedb.
This preserves the decision that A-record groom scans use NONE when no focal individual exists while allowing the Access-supplied sentinel record to satisfy the production biography constraints.
(#114) BRECORD_NOTES times are not uniformly compatible with EVENTS
Problem
The Access BRECORD_NOTES.BREC_time column is stored as a timestamp and is converted to a PostgreSQL TIME value in the tidy schema. Problem #10 corrected records whose timestamp had the wrong date component, but converting the datatype preserves seconds. The production EVENTS.Start and EVENTS.Stop columns record times to the minute and reject nonzero seconds.
Some B-record notes also use midnight to indicate that no time was recorded. The production schema previously reserved the sdb_no_time midnight sentinel for aggression events, so it rejected otherwise-loadable B-record notes with no recorded time.
In the local conversion data examined on 2026-08-20, 22 rows contained nonzero seconds, 46 rows contained midnight, and no rows had a SQL NULL time. Forty-five of the midnight rows had a matching follow-derived B-record watch.
Bad data
SELECT brec_fol_date,
brec_fol_b_focal_animid,
brec_time,
extract(second FROM brec_time) AS seconds
FROM clean.brecord_notes
WHERE brec_time IS NULL
OR brec_time = '00:00'::TIME
OR extract(second FROM brec_time) <> 0
ORDER BY brec_fol_date,
brec_fol_b_focal_animid,
brec_time;
Solution
Truncate nonzero seconds from BREC_time in tidy_cleanups.sql before the column is converted to TIME. Do not round the values. Convert a SQL NULL time to sdb_no_time at the production load boundary and preserve an existing midnight value as that sentinel. Permit both aggression and B-record-note EVENTS rows to use sdb_no_time, with both Start and Stop set to the sentinel.
* (#115) BRECORD_NOTES rows lack a matching follow-derived WATCHES row
Problem
A production BRECORD_NOTES row belongs to an EVENTS row, which in turn must belong to a WATCHES row. The ordinary B-record watches are created from clean.follow. Some B-record notes have no follow with the same focal individual and date. It is not yet known which records truly lack a follow and which contain an incorrect focal or date.
Creating provisional watches would require guessing the focal, community, or date and would make the affected records difficult to identify for later review. In the local conversion data examined on 2026-08-20, 35,517 of 603,977 B-record-note rows lacked a matching follow.
Bad data
SELECT brecord_notes.*
FROM clean.brecord_notes
WHERE NOT EXISTS (
SELECT 1
FROM clean.follow
WHERE follow.fol_b_animid =
brecord_notes.brec_fol_b_focal_animid
AND follow.fol_date = brecord_notes.brec_fol_date)
ORDER BY brec_fol_date,
brec_fol_b_focal_animid,
brec_time;
Solution
Temporarily exclude these rows from the production conversion. Retain them unchanged in clean.brecord_notes so that the focal and date can be reviewed and corrected in Access. The loader must reuse only an existing type-B WATCHES row having the same focal and date; it must not create a watch for an unmatched B-record note.
* (#116) BRECORD_NOTES contains times outside the EVENTS limits
Problem
Except for the sdb_no_time sentinel, production event times must be between sdb_min_event_start (04:00) and sdb_max_event_stop (20:00), inclusive. Some B-record-note times are outside those limits and their correct values must be determined from the source records.
In the local conversion data examined on 2026-08-20, 751 rows had an out-of-range time other than midnight. Of those, 711 had a matching follow-derived watch and would otherwise be loadable.
Bad data
SELECT brecord_notes.*
FROM clean.brecord_notes
WHERE brec_time IS NOT NULL
AND brec_time <> '00:00'::TIME
AND (brec_time < '04:00'::TIME
OR brec_time > '20:00'::TIME)
ORDER BY brec_fol_date,
brec_fol_b_focal_animid,
brec_time;
Solution
Temporarily exclude the offending rows from the production conversion while retaining them in clean.brecord_notes. Correct the source times in Access after reviewing the original records. Do not clamp the values or weaken the production time constraints.
(#117) BRECORD_NOTES text values do not satisfy production constraints
Problem
All production BRECORD_NOTES text columns are NOT NULL. Observation must also be trimmed of leading and trailing spaces. The remaining text columns permit the empty string to represent absent text but reject values consisting only of whitespace.
The Access source uses SQL NULL for absent text and contains some whitespace-only values. In the local conversion data examined on 2026-08-20, the rows otherwise eligible for conversion included 41 NULL observations and 14,349 untrimmed observations. The optional columns contained between 15,080 and 566,253 NULL values. Four comments, three Voc values, and three VocID values consisted only of whitespace.
Bad data
SELECT brec_fol_date,
brec_fol_b_focal_animid,
brec_time,
field_name,
quote_nullable(field_value) AS field_value
FROM clean.brecord_notes
CROSS JOIN LATERAL (
VALUES ('Observation', brec_observation),
('Comments', brec_comments),
('Observer', brec_observer),
('Translator', brec_translator),
('TranscribedBy', brec_transcribed_by),
('Voc', brec_voc),
('VocID', brec_vocid),
('GroomingAggression', brec_grooming_aggressionflag),
('Duplicate', brec_duplicate_flag)
) AS source_text(field_name, field_value)
WHERE field_value IS NULL
OR (field_value <> '' AND btrim(field_value) = '')
OR (field_name = 'Observation'
AND field_value <> btrim(field_value))
ORDER BY brec_fol_date,
brec_fol_b_focal_animid,
brec_time,
field_name;
Solution
Normalize only what the production constraints require at the final load boundary. Convert a NULL or whitespace-only value to the empty string. Trim Observation because its production column requires it, but preserve leading and trailing spaces in other nonempty source text. Keep the unchanged source-like values available in clean.brecord_notes.
(#118) PANTGRUNT_EVENT rows have no recorded time
Problem
Some PANTGRUNT_EVENT rows have NULL in pg_time. EVENTS.Start and EVENTS.Stop are not nullable, but the absence of a recorded pantgrunt time is meaningful and must not cause the entire row to be discarded. In the local conversion data examined on 2026-08-21, 684 rows had no recorded time.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE pg_time IS NULL
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_b_actor_id,
pg_b_recipient_id;
Solution
At the production load boundary, convert a NULL pg_time to the sdb_no_time value, midnight, and use that value for both EVENTS.Start and EVENTS.Stop. Extend the EVENTS constraints so pantgrunt events, like aggression events, may use sdb_no_time. Retain NULL in clean.pantgrunt_event so the clean schema continues to distinguish missing source data from an actual recorded time.
(#119) PANTGRUNT_EVENT times may contain seconds
Problem
EVENTS times are stored to the minute and reject nonzero seconds. The Access pg_time value is initially represented as a timestamp, and merely converting it to a PostgreSQL time value does not remove seconds. The local clean data examined on 2026-08-21 contained no remaining nonzero seconds, but the pantgrunt workflow previously had no explicit normalization guaranteeing that result.
Bad data
SELECT pg_date,
pg_fol_b_foc_id,
pg_time,
extract(second FROM pg_time) AS seconds
FROM clean.pantgrunt_event
WHERE extract(second FROM pg_time) <> 0
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time;
Solution
During construction of the tidy schema, truncate pg_time to the minute before tidy.sql converts the column to TIME. Do not round the value. The pantgrunt sanity check rejects the conversion if a value containing seconds nevertheless reaches the clean schema.
* (#120) PANTGRUNT_EVENT times fall outside the permitted event window
Problem
Except for sdb_no_time, production event times must be between 04:00 and 20:00, inclusive. In the local conversion data examined on 2026-08-21, 35 non-NULL pantgrunt times were outside this interval. Their correct values cannot be inferred during conversion.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE pg_time IS NOT NULL
AND (pg_time < '04:00'::TIME
OR pg_time > '20:00'::TIME)
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time,
pg_b_actor_id,
pg_b_recipient_id;
Solution
Temporarily exclude these rows from the production conversion and report their count in the pantgrunt sanity check. Retain the rows unchanged in clean.pantgrunt_event. The project investigators must review and correct the source times in Access; the conversion must not clamp them or weaken the production time constraints.
* (#121) PANTGRUNT_EVENT rows name the same actor and recipient
Problem
A production pantgrunt is a dyadic event involving two different participants. Some source rows identify the same animal as both pg_b_actor_id and pg_b_recipient_id. In the local conversion data examined on 2026-08-21, 11 rows had the same trimmed actor and recipient value.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE BTRIM(pg_b_actor_id) = BTRIM(pg_b_recipient_id)
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time,
pg_b_actor_id;
Solution
Temporarily exclude these rows and report their count in the pantgrunt sanity check. The project investigators must determine the correct actor or recipient and correct the source records in Access.
(#122) PANTGRUNT_EVENT rows have no data source
Problem
PANTGRUNTS.Source is required and must reference PG_SOURCES, but some source pantgrunt rows have a NULL, empty, or whitespace-only pg_data_source. In the local conversion data examined on 2026-08-21, 109 rows had no data source.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE NULLIF(BTRIM(pg_data_source), '') IS NULL
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time,
pg_b_actor_id,
pg_b_recipient_id;
Solution
During construction of the clean schema, normalize source codes by trimming and converting them to uppercase. Convert a missing source to NODATA. Populate PG_SOURCES from the distinct normalized source values and give NODATA the description "No pantgrunt data source was recorded". Load NODATA into PANTGRUNTS.Source for affected rows.
(#123) PANTGRUNT_EVENT rows have no extractor
Problem
PANTGRUNTS.EnteredBy is required and must reference an active PEOPLE row, but some source rows have a NULL, empty, or whitespace-only pg_extracted_by value. In the local conversion data examined on 2026-08-21, 2,223 rows had no recorded extractor.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE NULLIF(BTRIM(pg_extracted_by), '') IS NULL
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time,
pg_b_actor_id,
pg_b_recipient_id;
Solution
During construction of the clean schema, change a NULL, empty, or whitespace-only pg_extracted_by value to UNK. The established UNK row is copied from clean.people to codes.people as an active person and is loaded into PANTGRUNTS.EnteredBy for affected eligible rows.
(#124) PANTGRUNT_EVENT extractor values are missing from PEOPLE
Problem
Many nonempty pg_extracted_by values do not exactly match a clean.people person value. The local conversion data examined on 2026-08-21 had 26,334 rows without an exact PEOPLE match when missing values were included, and 24,111 nonmissing rows without a match after trimming. Some extractor names also occur with case variants, such as Anne and ANNE, while codes.people requires case-insensitive uniqueness.
Bad data
SELECT BTRIM(pg.pg_extracted_by) AS extracted_by,
COUNT(*) AS row_count,
ARRAY_AGG(DISTINCT pg.pg_extracted_by ORDER BY pg.pg_extracted_by) AS source_spellings
FROM clean.pantgrunt_event AS pg
WHERE NULLIF(BTRIM(pg.pg_extracted_by), '') IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM clean.people AS p
WHERE p.person = BTRIM(pg.pg_extracted_by))
GROUP BY BTRIM(pg.pg_extracted_by)
ORDER BY row_count DESC,
extracted_by;
Solution
During construction of the clean schema, trim nonempty pantgrunt extractor values and add one deterministic spelling of each case-insensitive value to clean.people when no case-insensitive PEOPLE match already exists. Prefer an existing PEOPLE spelling, and otherwise prefer a mixed-case source spelling over an all-uppercase or all-lowercase spelling. Rewrite pg_extracted_by to the exact spelling stored in clean.people. The ordinary support-table loader then copies these rows to codes.people with active set to true, which is required because new PANTGRUNTS rows may reference only active people.
(#125) PANTGRUNT_EVENT two-sided flags use multiple encodings
Problem
The pg_two_sided_flag determines whether the two participants receive directed Actor and Actee roles or two Mutual roles. The Access data use NULL, N, Y, X, lowercase x, and potentially questionable yes/no forms rather than a single canonical encoding.
Bad data
SELECT pg_two_sided_flag,
quote_nullable(pg_two_sided_flag) AS quoted_value,
COUNT(*) AS row_count
FROM clean.pantgrunt_event
GROUP BY pg_two_sided_flag
ORDER BY row_count DESC,
pg_two_sided_flag;
Solution
Normalize NULL, the empty string, N, N?, and ? to N in the clean schema. Normalize Y, Y?, X, and lowercase x to Y. During loading, Y creates two Mutual roles; N creates an Actor role for pg_b_actor_id and an Actee role for pg_b_recipient_id. The sanity check stops conversion if an unrecognized value remains.
(#126) PANTGRUNT_EVENT multiple-participant flags use noncanonical encodings
Problem
PANTGRUNTS.MultiActors and PANTGRUNTS.MultiRecipients are required Boolean values, while the corresponding Access fields are nullable text flags. The local conversion data include a lowercase x in pg_multiple_recipient_flag, and future dumps may include questionable yes/no forms.
Bad data
SELECT flag_name,
flag_value,
COUNT(*) AS row_count
FROM clean.pantgrunt_event
CROSS JOIN LATERAL (
VALUES ('pg_multiple_actor_flag', pg_multiple_actor_flag),
('pg_multiple_recipient_flag', pg_multiple_recipient_flag)
) AS flags(flag_name, flag_value)
GROUP BY flag_name,
flag_value
ORDER BY flag_name,
row_count DESC,
flag_value;
Solution
Normalize NULL, the empty string, N, N?, and ? to the empty string in the clean schema and load them as false. Normalize Y, Y?, X, and lowercase x to X and load them as true. The sanity check stops conversion if an unrecognized value remains.
* (#127) PANTGRUNT_EVENT participants are missing from BIOGRAPHY_DATA
Problem
Every pantgrunt actor and recipient must be represented by a BIOGRAPHY_DATA row before a corresponding ROLES row can be created. In the local conversion data examined on 2026-08-21, 165 rows had an actor missing from BIOGRAPHY_DATA and 609 rows had a recipient missing from BIOGRAPHY_DATA. A row can be counted in both categories.
Bad data
SELECT pg.*,
participant.source_column,
participant.animid
FROM clean.pantgrunt_event AS pg
CROSS JOIN LATERAL (
VALUES ('pg_b_actor_id', pg.pg_b_actor_id),
('pg_b_recipient_id', pg.pg_b_recipient_id)
) AS participant(source_column, animid)
WHERE NOT EXISTS (
SELECT 1
FROM clean.biography AS b
WHERE b.b_animid = BTRIM(participant.animid))
ORDER BY pg.pg_date,
pg.pg_fol_b_foc_id,
pg.pg_time,
participant.source_column,
participant.animid;
Solution
Temporarily exclude any row whose trimmed actor or recipient does not have a matching BIOGRAPHY_DATA animal. Report actor and recipient counts separately in the sanity check. The project investigators must identify the intended individuals and correct the source records in Access.
(#128) PANTGRUNT_EVENT focal/date keys have no corresponding follow
Problem
A pantgrunt event must relate to a WATCHES row. The normal B-record watches are created from clean.follow, but many pantgrunt focal/date keys have no matching follow. The local conversion data examined on 2026-08-21 contained 2,443 normalized focal/date keys without a B-record watch before other pantgrunt exclusions were applied.
Bad data
SELECT COALESCE(NULLIF(BTRIM(pg.pg_fol_b_foc_id), ''), 'NONE') AS watch_animid,
pg.pg_date,
COUNT(*) AS pantgrunt_rows
FROM clean.pantgrunt_event AS pg
WHERE NOT EXISTS (
SELECT 1
FROM clean.follow AS f
WHERE f.fol_b_animid = BTRIM(pg.pg_fol_b_foc_id)
AND f.fol_date = pg.pg_date)
GROUP BY COALESCE(NULLIF(BTRIM(pg.pg_fol_b_foc_id), ''), 'NONE'),
pg.pg_date
ORDER BY pg.pg_date,
watch_animid;
Solution
Prefer and reuse an existing B-record WATCHES row having the same focal and date. If no such watch exists for an otherwise eligible pantgrunt, reuse an existing Other watch or create a WATCHES row with Type Other. Use the pantgrunt community for a newly created watch. This avoids falsely representing an ad-hoc observation as a follow and avoids the missing-arrival warnings associated with B-record watches. The source association can be corrected later without discarding the converted pantgrunt.
(#129) PANTGRUNT_EVENT rows have no focal animal ID
Problem
Some pantgrunt records are ad-hoc observations and contain a NULL, empty, or whitespace-only pg_fol_b_foc_id. A WATCHES row requires an animal ID, but assigning the unknown individual UNK would incorrectly imply that a focal existed and merely could not be identified. In the local conversion data examined on 2026-08-21, 5,136 rows had no focal value.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE NULLIF(BTRIM(pg_fol_b_foc_id), '') IS NULL
ORDER BY pg_date,
pg_time,
pg_b_actor_id,
pg_b_recipient_id;
Solution
For WATCHES resolution only, normalize an absent pantgrunt focal to the established no-focal AnimID NONE. Create or reuse an Other watch for NONE and the pantgrunt date. Leave the original blank focal value unchanged in clean.pantgrunt_event.
* (#130) PANTGRUNT_EVENT focal/date keys contain conflicting communities
Status: Partially resolved; unresolved for twelve no-focal/date keys. The current loader exclusion is broader than necessary.
Problem
Every pantgrunt is related through EVENTS to one supporting WATCHES row. WATCHES records one CommID, and only one watch of a given Type may exist for a particular Date and AnimID. PANTGRUNTS also records its own CommID. This duplication is intentional: PANTGRUNTS.CommID preserves the community recorded with the pantgrunt in Access, while WATCHES.CommID records the community of the supporting follow or watch period. The two values are permitted to disagree, although the production database reports the disagreement as a warning.
The local conversion data examined on 2026-08-21 contain 15 normalized focal/date keys with both KK and MT pantgrunt communities. These keys cover 104 pantgrunt rows. They fall into two materially different categories:
- Three named-focal keys, covering 48 pantgrunt rows, already have a corresponding follow. The follow supplies an unambiguous B-record WATCHES row whose community is KK. These rows can be converted without choosing or discarding a pantgrunt community: reuse the existing watch and preserve each source value in PANTGRUNTS.CommID. Pantgrunts recorded as MT will appropriately produce community-mismatch warnings.
- Twelve keys, covering 56 pantgrunt rows, have no focal animal ID and no corresponding follow. The conversion normalizes the absent focal to NONE for watch resolution. Each date contains both KK and MT pantgrunts, but the conversion can create only one Other watch for the combination of NONE and that date. There is no source-supported way to decide whether that WATCHES row should contain KK or MT.
The community conflict therefore does not itself prevent a PANTGRUNTS row from storing its source community. The unresolved problem is selecting the canonical WATCHES.CommID when a supporting watch must be created and there is no follow or focal context from which to determine it.
Bad data
The following query shows every conflicting key, any community supplied by a corresponding follow, the pantgrunt communities, and the number of affected pantgrunt rows:
WITH conflict_keys AS (
SELECT COALESCE(NULLIF(BTRIM(pg_fol_b_foc_id), ''), 'NONE') AS watch_animid,
pg_date
FROM clean.pantgrunt_event
GROUP BY COALESCE(NULLIF(BTRIM(pg_fol_b_foc_id), ''), 'NONE'),
pg_date
HAVING COUNT(DISTINCT BTRIM(pg_cl_community_id)) > 1
)
SELECT conflict_keys.watch_animid,
conflict_keys.pg_date,
follow.fol_cl_community_id AS follow_community,
ARRAY_AGG(DISTINCT BTRIM(pantgrunt_event.pg_cl_community_id)
ORDER BY BTRIM(pantgrunt_event.pg_cl_community_id)) AS pantgrunt_communities,
COUNT(*) AS pantgrunt_rows
FROM conflict_keys
JOIN clean.pantgrunt_event
ON COALESCE(NULLIF(BTRIM(pantgrunt_event.pg_fol_b_foc_id), ''), 'NONE') = conflict_keys.watch_animid
AND pantgrunt_event.pg_date = conflict_keys.pg_date
LEFT JOIN clean.follow
ON BTRIM(follow.fol_b_animid) = conflict_keys.watch_animid
AND follow.fol_date = conflict_keys.pg_date
GROUP BY conflict_keys.watch_animid,
conflict_keys.pg_date,
follow.fol_cl_community_id
ORDER BY conflict_keys.pg_date,
conflict_keys.watch_animid;
Examples with named focals
| Focal | Date | Follow community | Pantgrunt communities | Pantgrunt rows |
|---|---|---|---|---|
| FD | 1996-09-08 | KK | KK and MT | 11 |
| ZS | 2013-11-22 | KK | KK and MT | 6 |
| DL | 2013-11-29 | KK | KK and MT | 31 |
For example, the FD records can all use the existing B-record watch for FD on 1996-09-08. That watch remains associated with KK. Each pantgrunt retains either KK or MT in PANTGRUNTS.CommID. The MT records disagree with the watch community, but this is a documented and intentionally preserved source inconsistency rather than a conversion failure.
Examples without a focal
| Watch AnimID used by conversion | Date | Pantgrunt communities | Pantgrunt rows |
|---|---|---|---|
| NONE | 1986-01-11 | KK and MT | 8 |
| NONE | 1986-09-10 | KK and MT | 16 |
| NONE | 1986-10-30 | KK and MT | 3 |
| NONE | 1987-09-26 | KK and MT | 7 |
| NONE | 1992-11-21 | KK and MT | 3 |
| NONE | 1993-12-25 | KK and MT | 2 |
| NONE | 1994-01-19 | KK and MT | 3 |
| NONE | 1994-05-26 | KK and MT | 2 |
| NONE | 1994-07-07 | KK and MT | 2 |
| NONE | 1994-08-01 | KK and MT | 3 |
| NONE | 1994-08-08 | KK and MT | 4 |
| NONE | 1994-10-07 | KK and MT | 3 |
For example, the eight records on 1986-01-11 require a supporting Other watch with AnimID NONE. The WATCHES uniqueness rule permits only one such Other watch on that date. Assigning KK would be unsupported for the MT observations, while assigning MT would be unsupported for the KK observations. Creating two Other watches distinguished only by community is not permitted by the current WATCHES key.
Solution
Refine the conversion so that the three named-focal keys reuse their existing B-record watches and all 48 pantgrunt rows are loaded. Preserve the source community in PANTGRUNTS.CommID and accept the documented warning when it differs from the B-record watch community.
Continue to exclude only the 56 pantgrunt rows belonging to the twelve no-focal/date keys. Report those twelve keys in the sanity check. The investigators must determine whether each date represents one observation period with incorrect community coding, two distinct observation periods that the current WATCHES key cannot represent, or records with incorrect dates or focal values.
Until that refinement is implemented, the current loader conservatively excludes all 15 conflicting keys and therefore excludes 48 otherwise convertible named-focal rows in addition to the 56 genuinely ambiguous no-focal rows.
* (#131) PANTGRUNT_EVENT observer values have no lossless production destination
Status: Unresolved; intentionally retained only in the clean schema.
Problem
The pg_observer field is populated only with JF in the local conversion data examined on 2026-08-21, affecting 1,696 rows across 221 normalized watch keys. PANTGRUNTS has no observer column. FOLLOW_OBSERVERS describes observers assigned to follow periods, so creating FOLLOW_OBSERVERS rows from pantgrunt records would require deciding whether pg_observer represents the same concept and period.
Bad data
SELECT pg_observer,
COUNT(*) AS pantgrunt_rows,
COUNT(DISTINCT (COALESCE(NULLIF(BTRIM(pg_fol_b_foc_id), ''), 'NONE'), pg_date)) AS watch_keys
FROM clean.pantgrunt_event
WHERE pg_observer IS NOT NULL
GROUP BY pg_observer
ORDER BY pg_observer;
Solution
Do not convert pg_observer into FOLLOW_OBSERVERS or another production table without an investigator-approved semantic mapping. Retain the values in clean.pantgrunt_event for later review. The pantgrunt loader explicitly documents that this field is omitted.
* (#132) PANTGRUNT_EVENT participants occur outside their biography intervals
Problem
ROLES requires an event participant's date to fall between BIOGRAPHY_DATA.EntryDate and BIOGRAPHY_DATA.DepartDate, inclusive. Some pantgrunt actors or recipients have a biography row but occur before entry or after departure. In the local conversion data examined on 2026-08-21, 792 rows involved at least one participant outside this interval.
Bad data
SELECT pg.*,
participant.source_column,
participant.animid,
b.b_entrydate,
b.b_departdate,
CASE
WHEN pg.pg_date < b.b_entrydate THEN 'BEFORE ENTRY'
WHEN pg.pg_date > b.b_departdate THEN 'AFTER DEPARTURE'
END AS date_problem
FROM clean.pantgrunt_event AS pg
CROSS JOIN LATERAL (
VALUES ('pg_b_actor_id', pg.pg_b_actor_id),
('pg_b_recipient_id', pg.pg_b_recipient_id)
) AS participant(source_column, animid)
JOIN clean.biography AS b
ON b.b_animid = BTRIM(participant.animid)
WHERE pg.pg_date NOT BETWEEN b.b_entrydate AND b.b_departdate
ORDER BY pg.pg_date,
pg.pg_fol_b_foc_id,
pg.pg_time,
participant.source_column,
participant.animid;
Solution
Temporarily exclude affected rows and report their count in the pantgrunt sanity check. The project investigators must determine whether the pantgrunt date, participant identity, or biography interval should be corrected in Access. Do not weaken the ROLES temporal constraints.
* (#133) PANTGRUNT_EVENT contains invalid nonempty focal animal IDs
Problem
An Other WATCHES row created for a pantgrunt requires a valid BIOGRAPHY_DATA animal ID. Some pg_fol_b_foc_id values are nonempty but do not match a biography animal and therefore cannot be treated as the no-focal value NONE. In the local conversion data examined on 2026-08-21, 70 rows contained such focal values.
Bad data
SELECT pg.*
FROM clean.pantgrunt_event AS pg
WHERE NULLIF(BTRIM(pg.pg_fol_b_foc_id), '') IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM clean.biography AS b
WHERE b.b_animid = BTRIM(pg.pg_fol_b_foc_id))
ORDER BY pg.pg_date,
pg.pg_fol_b_foc_id,
pg.pg_time,
pg.pg_b_actor_id,
pg.pg_b_recipient_id;
Solution
Temporarily exclude these rows and report their count in the sanity check. The project investigators must identify whether each value is a malformed focal ID, descriptive text, or evidence that no focal existed, then correct Access accordingly. Do not automatically convert a nonempty value to NONE or UNK.
(#134) PANTGRUNT_EVENT notes may be NULL
Problem
EVENTS.Notes is required but permits the empty string. The Access pantgrunt table uses NULL for absent notes. In the local conversion data examined on 2026-08-21, 7,339 rows had NULL pg_notes and no rows contained whitespace-only notes.
Bad data
SELECT *
FROM clean.pantgrunt_event
WHERE pg_notes IS NULL
OR (pg_notes <> '' AND BTRIM(pg_notes) = '')
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time,
pg_b_actor_id,
pg_b_recipient_id;
Solution
At the production load boundary, convert a NULL or whitespace-only pg_notes value to the empty string and otherwise preserve the source text unchanged. Retain the original value in clean.pantgrunt_event.
* (#135) PANTGRUNT_EVENT year values have no lossless production destination
Problem
The production pantgrunt model obtains the event date through WATCHES and has no separate year column. Although pg_year appears redundant with pg_date, the local conversion data examined on 2026-08-21 contain 178 rows where pg_year differs from the year extracted from pg_date. Discarding the field as derived data would therefore lose a recorded discrepancy.
Bad data
records
SELECT *
FROM clean.pantgrunt_event
WHERE pg_year IS NULL
OR pg_year IS DISTINCT FROM extract(year FROM pg_date)::INTEGER
ORDER BY pg_date,
pg_fol_b_foc_id,
pg_time,
pg_b_actor_id,
pg_b_recipient_id;
summary
SELECT pg_year,
extract(year FROM pg_date)::INTEGER AS date_year,
pg_year - extract(year FROM pg_date)::INTEGER AS year_difference,
count(*) AS row_count
FROM clean.pantgrunt_event
WHERE pg_year IS DISTINCT FROM
extract(year FROM pg_date)::INTEGER
GROUP BY pg_year,
extract(year FROM pg_date)::INTEGER
ORDER BY pg_year,
date_year;
Solution
Do not load pg_year into another production column or silently replace pg_date. Retain pg_year in clean.pantgrunt_event for investigator review. The pantgrunt loader explicitly documents that the field is omitted pending a project decision about the 178 discrepancies.