37 Days: Review of on-site amounts before SDS constituent cleanup before threshold determinations

Or, do your conversions pass the “giggle test”?

BTW, I didn’t make it clear, but the way we process this information is to put the “raw'“ data into a “temporary’ table, then move it out as the conversion progresses through to pounds. When the temporary table is empty you are done. If you find mistaken transactions that you aren’t going to convert move them into a different Errors/Exceptions table with Reason (and Reporting Year so you can compare from year to year.

One Definition of a Data geek.

Getting ready and data conversion to Pounds:

1. Backup the temporary table before you start processing, which makes it easy to roll back if you need to. Zipping it with YYYY_MMdd_HHMM (Year-2 digit month-2 digit day-24hour-minutes) at the beginning of the zipped filename will list them in chronological order. I would usually append something at the end of the filename to tell me what this backup had in it

2. We created a file of Run Numbers with the Source System that we incremented for each run. (Did I forget to mention that you will want Source System for each type of data you bring in—which purchasing system it was from, “Manual” for data where you just create a transaction based on knowledge)? We would track start and end times so you could track metrics, as well as records processed (we could conduct a monthly run of purchases up into HAP reporting in 15 minutes for one client). Create that if you will be tracking run numbers.

3. Depending on the source of the data, we would run an electronic import to put the data into the temporary table. Any data for which you get an error message, go find the data, fix it, then re-import (you see the reason for the backing up so you can roll back. Examples of “bad” data we have encountered in the past: SDS numbers for a transaction with one or more slashes / between multiple different SDS numbers indicated that the transaction was a kit (copy into 2 transactions, each with one of the two SDS numbers or create a consolidated SDS with all the constituents and replace with that SDS number); sometimes dates import as numbers (Julian dates) instead of dates; delete any blank rowsCconduct a quick review the raw data. We did this by chronological date, in the order it was received in the export table, and ran a sum and checked that the total number of containers received was believable

4. Check for duplicates. In some cases they may be real (you may have received the same number of drums a second time on the same day), in some cases they are an anomaly. With 55 gallon drums, these can really have an impact

5. We had a DoNotProcess field that we could set the value to Yes if for some reason we needed to leave this transaction behind for now

6. From the data, create the unique values of SDS/Part Number, Ctnr Description not Part Number related, and SDS/Part Number/Ctnr Description tables. These are the conversion factors you will need to create. Edit the conversion factors into these tables as appropriate

7. Edit Specific Gravity values into any SDS’s that will need it for volumetric conversion to pounds.

8. Easy to say but a lot of steps, convert to pounds. If you would like the details of these steps in the order we found it to be the least number of steps and the least number of data items needed to be entered, reach out.

The above is a summary of what we’ve covered in the last couple of days. On to cleanup steps. Today we’ll do it at the macro level for transactions and pounds, then we’ll go into SDS constituent cleanups. We’ll ge to threshold determinations by the end of the week, I promise!

QA/QC of the Conversion to Pounds Activity:

(Note that if you a consultant doing this for a client you will need to involve them in this step, because you probably won’t have the intuitive “that’s not right” meter that your client will have. We have had conversion factors that the client provided be wrong by thousands of pounds because of simple misunderstandings easily fixed)

1. Sort with the largest amounts showing first and scan the highest values. This is where you will spot significant conversion factor errors that made your numbers too large.

2. Sort ascending with the smallest numbers first. Displaying 3 decimal places on this report will show true zeroes, which may be missing conversion factors, as distinct from very small values (we have had users tracking 10gm vials)

   1. Make sure that any zeroes are valid. If they are because conversion factors are missing, fix and reprocess

   2. There shouldn’t be any negative numbers unless that is how your source system is accounting for mistakes.

      1. Even then, you need to confirm whether it was a Return to Vendor, which was on your property and so needs to be considered for threshold determinations,

      2. or whether it was never received, in which case leave the negative numbers so they will subtract.

3. Sort by SDS Number and review the intervals between receipts. Again, this is a reality check. For the big numbers, confirm with the owners of that data that the information makes sense.

4. Sum by SDS Number. Do the same check sorting descending by largest amount and increasing by lowest amounts to see if the data makes sense

Community outreach: Does the process correspond to the process you use? If not, what differences have worked for you?

#SARA312 #SARATierII #SARATier2 #EPCRA #EGLE #March1EPAReportDeadline #ThresholdDeterminations

Michigan EGLE links from first and second webinars of 2022 SARA 312 virtual training courses:

I’m keeping these links for awhile because they are so useful. I’ll post the session 3 recording info when it becomes available. You can sign on and listen even if you didn’t sign up for the webinar series.

RECORDING LINK FOR SESSION 2:  Wed Jan 19, 2022.

Really good example on batteries. This is the “in the weeds” how to conduct your threshold determinations episode: https://attendee.gotowebinar.com/recording/4027906237723673347

I talked to Mike Young, one of the presenters, right after the first webinar. He said that it’s not unusual for them to take phone calls from people from other states (with the warning to confirm that your state doesn’t have differences in their requirements compared to Michigan).

RECORDING LINK FOR SESSION 1:  

https://attendee.gotowebinar.com/recording/7957790593170499843

PRESENTATION:  (I wasn’t able to attach a copy of the presentation, email the link below and they will send it to you)

 SARA TITLE III – TIER II REPORTING WEBSITE:  http://www.michigan.gov/sara

 MICHIGAN FACILITIES’ GUIDE TO SARA TITLE III:  https://www.michigan.gov/documents/deq/deq-oea-saraguidebook_509720_7.pdf

 CONTACT INFORMATION:

• Mike Young and Adam Pohl

• EGLE-SARA@michigan.gov

• 517-284-7272

Michigan EGLE SARA 312 virtual training courses for Jan 2022