Selection set 1c - Conversions - new development and restoration features are not loaded to the proper target table

[leeensminger]

This selection set contains two CON_PURPOSE values: Conversion ("Part 1" of selection set 1C, if you're following along in my original ETL Excel), and New Development ("Part 2"). Part 1 is intended to be loaded to the target.BMPS table AND target.BMPPOI. Part 2 is intended to be loaded to the target.RestBMP table.

Summary Currently, the system appears to be finding Part 1 + Part 2, and loading all of them to target.RestBMP. Some, but not all, of Part 2 is loaded to target.BMPs. The system appears to be loading Part 2 to the BMPPOI feature class correctly.

I've modified the SDE data so that all of selection set 1C has SW_TRACKING reporting year 2017; all others are 2020 (or null). So effectively the ETL should be only be processing conversions as of this post.

Details The total selection set is 158 SW_TRACKING records. This includes: Part 1 (where SW_TRACKING.CON_PURPOSE = Conversion) - 80 records Part 2 (where SW_TRACKING.CON_PURPOSE = New Development) - 78 records

The expected output is that:

• The 80 Part 1 records are loaded into target.RestBMP (to be clear - data issues are causing a load fail, so they actually end up in append_failure_restbmp)
• The 78 Part 2 records are loaded to BMP table
• The 78 Part 2 records are also loaded to BMPPOI feature class (again, this appears correct).

Currently:

• The target RestBMP (looking at append_failure_RESTBMP_p_to_RESTBMP) output is holding 158 records. This represents all records, but should only be the 80 Conversion records.
• The target BMP table (looking at append_failure_BMP_p_to_BMP) holds 43 records. This should be holding the 78 New Development records. I'm not sure why those 43 specifically were pushed to target BMP table, and what the logic is that caused this. Either way, it should be 78 records.
• The 78 Part 2 records are being loaded to the BMPPOI feature class, appears to be correct (actual count on the output for me is 77, but this is correct I think based upon the conversion of a conversion discussed below). So again no issues here I don't think.

[leeensminger]

@gerrykelly Update - I have prepended text in SW_TRACKING.ADTNL_COMMENTS as "Selection set 1C ----". This way the reporting year can change for other testing as needed. Searching for this text will yield you the appropriate 158 records.

ADTNL_COMMENTS LIKE 'Selection set 1C ----%'

[leeensminger]

@gerrykelly Also note that SW_TRACKING.BMP_ID {2C881615-9A59-4D26-8CA6-243C8AE67FEF} is a conversion of a conversion - this is a viable workflow from the County's perspective, but I don't want us to get tripped up by this - so I modified the prepended ADTNL_COMMENT as: "Exclude from set 1C, this is a conversion of a conversion"

So this would be expected to drop the target.BMP output by 2 records - but let's handle this in a separate issue once you get this one close.

[gerrykelly]

@leeensminger This should be basically resolved.

[leeensminger]

@gerrykelly None of the RestBMP features appear to have made it to the output. I have an Append Failure RestBMP feature class, but it is empty.

[gerrykelly]

@leeensminger I just ran the GP for RY 2023 and my RestBMP FC has 88 features that include 40 for "Selection set 1C Part 1****"

[leeensminger]

@gerrykelly What am I missing?? When I run for RY 2015 - which is the year that contains all of the selection set 1C (its also the lowest year), I get no RestBMP feature class, and the appendfailure rest bmp feature class is empty.

[gerrykelly]

@leeensminger I was checking the behind the scenes working GDB, not the final output. So the records were getting correctly processed but then RestBMP was not copied to the final FGDB bc of a quirk in how arcpy handles featureclasses in featuredatasets. This should be fixed now, (but we need to keep an eye out that the fix has not messed up anything else in the final output)

[leeensminger]

Appears resolved