Bridger Snowflake Integration
The Bridger Snowflake integration allows us to pull data via the Snowflake API, process it and import to Aershed.
Getting setup
The Bridger documentation can be found in our SharePoint here. The main reference is the data dictionary.
For testing we have been using the EQT data. To view the data in the Snowflake console you can navigate to here. Within this console you can navigate to different tables and run queries, this is useful as the data dictionary is not entirely accurate and there are some differences with the actual tables.
You will need a Snowflake user and password. Since these are user specific they must be set in your local .env file and are not available via the datalab's shared Bitwarden account. The .env file for each environment should look like|
SNOWFLAKE_USER="username"
SNOWFLAKE_PASSWORD="password"The datalab tools application contains a client for working with the Snowflake Python API. The client is defined in bridger_snowflake/client.py and follows the interface defined by IBridgerSnowflakeClient which can be injected as a dependency into any datalab tools program definition. This client handles the query construction and execution, returning the required data frames.
Currently only one Bridger Snowflake account is supported
Only the EQT account is setup with account name BRIDGERPHOTONICS-EQT_READER_V2 hard coded in the client implementation. Once we have another account online this will be generalised to pull the account name from the configuration file, and a system will need to be developed to handle different credentials for each account. This may include using scoped .env files or some other system to handle the different details. This has not been done yet to avoid generalising too early and in the wrong way, it will be clear once we have multiple accounts how exactly this should be done.
Survey filtering
Since some customers may have a large repository of Snowflake data it is important to avoid massive downloads of data and long running queries. The simplest way to ensure automated jobs only pull in what they should is to filter by survey name. This also keeps processing inline with how we have historically managed Bridger data. Even as a fully automated check and pull of data, requiring a manual update to configuration to explicitly request a new survey or list of surveys is a good way to ensure there are no unexpected imports. This can be revised in future if needs change, but for now we want to avoid the possibility of pulling in data we are not expecting, or pulling in far too much historical data which we already have.
To find out what surveys are available we can use the bridger_snowflake.data_deliveries program, which at this stage (since we are using on the EQT store) requires no configuration inputs except for an output directory. This program will generate a CSV file listing all available surveys and their JOB_NUMBER. This JOB_NUMBER is what we must use when specifying the survey we want to download and process. Some job numbers may have multiple DATA_DELIVERY_DESCRIPTION values, such as AMI0018_EQT - Sector 1 Processed Report and AMI0018_EQT - Sector 2 Processed Report; we do not currently support filtering by delivery description, but this may useful to see the various segments that make up a given job number at the time of extraction.
Overlap with existing Bridger data
The work to integrate the Snowflake pipeline has involved consistency checking with legacy assets. However, we have found some key differences and these have been raised with the vendor - these include variations in detected timestamp, emission rate and some records missing.
The ability to robustly identify duplicates between Snowflake data and legacy data was out of scope of the original integration work. Although this would be possible, due to various inconsistencies it is not a trivial task. New Snowflake records also use a different method for constructing the record-id so simply deduplicating on this field would not be possible either.
Data download and processing
The primary purpose of this set of programs is to download and process Bridger data into a format that can be passed to our existing Bridger importers. The following asset types are supported, with the corresponding program listed alongside them.
- Sites
bridger_snowflake.sites - Emissions
bridger_snowflake.emissions - Site non-detects
bridger_snowflake.site_non_detects - Aerial Photography
bridger_snowflake.aerial_images
All programs take survey as a configuration parameter. The emissions program takes download_images as a parameter which defaults to True.
Non-detects dependence on sites import
The non-detects process requires that we have a site in Aershed that intersects with all the facility/swath data. Swaths are associated with facilities in Snowflake, so if we have imported all the facilities using the bridger_snowflake.sites program then the non-detects program will run. If it detects there are sites missing, then it will raise a warning and not complete. The next time the application runs these sites should have been fully imported, and it will run successfully.
This is the only dependency in our automation. It would be possible to solve this by putting an await on the sites import, but since import jobs can occasionally get stuck for multiple hours, at this point I have decided to allow the tasks to complete and pick up the changes in the next cycle which should be no more than 24 hours later. This decision also considers the fact that non-detections are less urgent than emissions - there is no impact to emission imports which will always proceed as soon as new data has been identified.
Automation
The automation runs as a cron job on the datalab. The current strategy is run the full set of programs for a single survey. The following configuration is an example file for a full set of Bridger data.
{% set folder = "${TIMESTAMP}" %}
{% set owner = "jt806" %}
config:
owner: {{ owner | upper }}
survey: AMI0018N_EQT
base_output_dir: "${PROCESSING_DIR}/jt806/bridger/automation/{{ folder }}"
pipeline:
- task: bridger_snowflake.aerial_images
- task: bridger_snowflake.sites
- task: bridger_snowflake.emissions
- task: bridger_snowflake.site_non_detects
- task: infrastructure.process_update
- task: bridger_snowflake.non_detect_overlap_check
exceptions:
duplicates:
- 708e4a32-fc50-464f-904a-ec74336d07e1
- ae88a025-5a99-4bcf-8ecd-fc7eb69020ad
- task: importers.generic
base_input_dir: "${PROCESSING_DIR}/{{ owner }}/bridger/automation/{{ folder }}"
allow_updates: TrueThe details of the infrastructure are omitted, see the full examples in the configuration folder.
Checks
This is the first example of fully automated Bridger imports into the Aershed platform. Every new delivery format has potential risks so as part of this work I have considered what could go wrong and implemented checks where possible. One of the primary differences from the legacy format is the static versus dynamic nature of the data source. The legacy assets were at least static when they were given to us, and the assumption is that they were self contained and complete. Now we have a data source that is undergoing multiple updates and the nature and scope of these is not clear.
One possible issue is the generation of a non-detect before emission data arrives. While we could mitigate this by introducing a delay and running non-detects later (like we have done with Insight-M), this is not an ideal strategy. To list a few reasons, it requires a level of manual intervention to execute the task at some point, it creates risk that the task is forgotten entirely and it creates a delay between data delivery and complete representation of that in Aershed. Finally, even waiting a long time does not completely avoid the possibility that the provider revises data at a later date and creates a conflicting emission where we have a non-detect; the cleanest and simplest solution is to allow non-detect updates and archiving via an importer. Regardless of the different mechanisms to work around this risk while this integration is in a testing phase we will assume that each LDAR report update is self consistent and complete, and import everything as it is presented to us.
At the end of each automation task we run a self-consistency check on the current state of the imported data. Since this check is run immediately after imports start, it may not pick up issues from the most recent data. Since the job runs regularly, it will run eventually. The check looks for duplicate non-detects and non-detects that overlap with emission records. This will give us the opportunity to investigate these and flag the problematic non-detects for manual deletion, and investigate the cause.