Overview
...
| Stage Number | Stage | Category | Summary | Environment | Iterations | Estimated Person Time | Estimated Computer Time |
|---|
| 1 | Import and parse reference dataset (Optional) | Parsing | This optional step in the Bulk Loader process is to cross check an address for a match in a reference data set. If a source address is found in the reference dataset the address makes it to the next step. If not found the address is put aside in an exclusion set for later review. | Python 3 PostgreSQL / pgAdmin | Once per Bulk Loader process | 1 hour | 10 minutes |
| 2 | Import, parse and filter source dataset | Parsing | Import the dataset destined for the EAS. Parse and filter the set. | Python 3 PostgreSQL / pgAdmin | Once per Bulk Loader process | 90 minutes | 15 minutes |
3 | Geocode and filter | Geocoding | Geocode the set and filter further based on geocoder score and status. | ArcMap | Once per Bulk Loader process | 1 hour | 5 minutes |
| 4 | Export full set (single batch) or subset (multiple batches) | Geocoding | For large datasets, create one of many subsets that will be run through the Bulk Loader in many batches. | ArcMap | One or more batches for each Bulk Loader process | 30 minutes per batch | 5 minutes per batch |
| 5 | Bulk Load batch (full set or subset) | Bulk Loading | Run each subset batch through the Bulk Loader. | EAS <environment>(+) PostgreSQL / pgAdmin | One or more batches for each Bulk Loader process | 1 hour per batch | 5 minutes per batch |
| 6 | Extract results | Bulk Loading | Extract and archive the list of addresses that were added to the EAS . Also archive the unique EAS 'change request id' associated with this batch. Also archive the addresses that were rejected by the Bulk Loader in this batch. | PostgreSQL / pgAdmin | One or more batches for each Bulk Loader process | 1 hour per batch | seconds |
...
Stage 1 -
Import and parse reference dataset (Optional) This optional stage is run once per Bulk Loader process. This stage can be skipped if the reference dataset is already available or if the optional 'filter by reference' step is skipped.
...
Stage 3 - Geocode and filter
-
Step 3.1 - Geocode source dataset
...
Stage 4 - Export full set (single batch) or subset (multiple batches)
| Note |
|---|
| title | A Note about Batches |
|---|
|
Stages 4, 5 and 6 can be run one time with the results from Stage 3, or they can be run in multiple batches of subsets. A major consideration of when to run the full set at once versus in batches is the number of records being Bulk Loaded. The size of each Bulk Loader operation affects the following aspects of the EAS: - The disk space consumed by the database server
- The EAS user interface section that lists addresses loaded in a given Bulk Loader operation
- The weekly email attachment listing new addresses added to the EAS
For medium-to-large datasets (input sets with over 1000 records) it is recommended to run the process on a development server and assess the implications of the operation. Where appropriate, perform the Bulk Loading process in batches over several days or weeks. The remaining steps will document one example iteration of a multi-batch process. |
...
Stage 5 - Run the Bulk Loader
-
Step 5.1 - Bulk Load shapefile
...
-
Step 6.1 - Archive exceptions
...
The number of base addresses found in the Stage 5 Analysis should be identical to the number of base addresses found in Step 6.3.
- The number of addresses found in the Stage 5 Analysis should be less than or equal to the number of addresses listed in Step 6.4. (The Bulk Loader does not provide enough information in the the
bulkloader.address_extract table to determine the exact number of new addresses added. But there is enough information to determine an upper limit.)
Stage 7 - Cleanup and Restoration
-
Step 7.1 - Database Cleanup
Connect to the database, <environment>_DB, and clear temporary records from the latest Bulk Loader batch.
| Code Block |
|---|
| language | sql |
|---|
| firstline | 1 |
|---|
| title | TRUNCATE |
|---|
| linenumbers | true |
|---|
|
TRUNCATE bulkloader.address_extract, bulkloader.blocks_nearest; |
| Code Block |
|---|
| language | sql |
|---|
| firstline | 1 |
|---|
| title | VACUUM |
|---|
| linenumbers | true |
|---|
|
VACUUM FULL ANALYZE bulkloader.address_extract; |
| Code Block |
|---|
| language | sql |
|---|
| firstline | 1 |
|---|
| title | VACUUM |
|---|
| linenumbers | true |
|---|
|
TRUNCATE bulkloader.address_extract, bulkloader.blocks_nearest; |
- On Failure Restore Database
- If the Bulk Loading Process ends in failure then follow these steps to restore from backup.
- Restore Services (Production Only)
- Turn on production-to-replication service
- Turn on downstream database propagation service(s)
- Enable front-end access to EAS
Notes
(
+) Substitute EAS
<environment> with one of the relevant environments:
SF_DEV, SF_QA, SF_PROD, SD_PROD.
...