Interval Data Upload (SFTP)

Some programs (e.g. device-level DSGS, CPS, & Connected Solutions programs) require interval data to be uploaded so that Leap can calculate baselines and performance, and submit this data to program administrators for revenue payments.

Check out the Interval Data Upload Guide in the Leap Knowledge Center for details on the required CSV format and schema and how to upload files through the Partner Portal. In addition to the Partner Portal, Leap offers an SFTP server that can be used for this purpose. Details on connecting to this SFTP server are included below.

Automating interval data upload through an SFTP client library can be very beneficial for operations and data accuracy as this data needs to be provided ongoing throughout the year or program season. Uploading data more frequently (e.g. daily) is recommended as this allows for quicker insights into event performance and when applicable quicker resolution of issues that may be negatively impacting performance.

Recommended automation steps

1. Use an SFTP client library to transfer files
   1. See SFTP details in remainder of this guide
2. Call the Interval data upload statuses endpoint with the file_path query parameter to check the status of each file
   1. Be sure to use the full path and .csv extension (e.g. /interval-data/filename.csv)
3. If FAILED status, use last_job_id to query the data validation errors endpoint
   1. Log failure reasons and alert required team in order to make necessary corrections to data
   2. Review data validations performed

Connecting to SFTP server

📘

SFTP connection details

Hostname: sftp.leap.energy or sftp.staging.leap.energy
Username: your Leap Partner Account ID (find it at the top of the page here)
Password: one of your Leap API keys (manage them here for production and here for staging)

🚧

File management

You can list and upload files on the SFTP server, but not delete them. This is because Leap immediately triggers some automated processing workflows as soon as you've uploaded the file. If you really need to delete a file, e.g. because it contains some sensitive data, please contact your Partner Success Manager.

Selecting an SFTP client

You can use any SFTP client to connect to the server. Here's a list of clients that Leap has tested:

FileZilla

• Open the Site Manager in FileZilla to add an account.
• Use the connection details as described above:

• Click Connect.
• When connected, you can interact with the files and folders. You can drag files into the interval-data folder and list the ones you've already uploaded.

Linux/macOS sftp command

You can connect using Linux's or macOS's built-in sftp command. Simply run sftp [email protected] to connect. Once connected, you can use basic file operations like ls for listing a directory.

$ sftp [email protected]
============ LEAP SFTP SERVER ============
You can use this server to upload things
like meter interval data to Leap. Use
your Leap partner account ID as the username, and one
of your production or staging API keys as the password. If you
have any questions, please reach out to
your Leap Account Manager.
==========================================
[email protected]'s password: 
Connected to sftp.staging.leap.energy.
sftp> ls
interval-data  
sftp> ls interval-data
interval-data/interval_data_template (3).csv  

Validating the host key

Most clients (like FileZilla) will ask you to trust the host key. That looks something like this:

Here are Leap's host key fingerprints, so you can validate that you're indeed connecting to the right server:

Writing to the SFTP server programmatically

There are many client libraries out there for communicating with SFTP servers. Here are some examples for common programming languages. If you work with a programming language that's not listed here, let us know, and we'll be happy to provide an example.

Python

For Python, we'll be using the well-maintained Paramiko library. Here's how you can upload a file either from your filesystem of from a text string:

import io
import paramiko

# create ssh client
ssh_client = paramiko.SSHClient()

# remote server credentials
host = "sftp.staging.leap.energy"
# the username is your Leap Partner Account ID. You can find it at https://partner.staging.leap.energy/account
username = "YOUR_LEAP_PARTNER_ID_HERE"
# one of your Leap API keys (staging key for this example!). Manage your API keys here: https://partner.staging.leap.energy/account?settings=apiKeys
password = "YOUR_LEAP_API_KEY_HERE"

print("Connecting to SFTP server...")

ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh_client.connect(hostname=host,username=username,password=password)

# create an SFTP client object
ftp = ssh_client.open_sftp()

print("Uploading file to SFTP server...")

"""
OPTION 1: uploading a file from the filesystem
"""

# upload a file to the server (note that it has to be in the `interval-data` folder)
# ftp.put("interval-data.csv","interval-data/dummy.csv")

"""
OPTION 2: uploading a file from a string
"""

data_string = """meter_id,interval_start_time_utc,interval_end_time_utc,energy_net_kwh,energy_consumed_kwh,energy_generated_kwh
5be80922-2345-4ed8-ae70-e7d8114cd2a3,2024-04-01T11:00:00Z,2024-04-01T11:15:00Z,,5.01,0"""
fl = io.StringIO(data_string)

# upload a file to the server (note that it has to be in the `interval-data` folder)
ftp.putfo(fl, "interval-data/dummy.csv")

print("Successfully uploaded file to SFTP server!")

# close the connection
ftp.close()
ssh_client.close()

How files are processed

You can upload interval data files to the root directory in /interval-data. As soon as a file is uploaded, Leap's systems immediately get notified and will start processing it. Most files get processed within a minute, depending on the size of your file and the load on our systems.

🚧

Processing results on SFTP server only

You can upload CSV interval files through both Leap's SFTP server and Partner Portal. They share the same storage backend, so you'll also find files you uploaded through the Partner Portal on the SFTP server.

Note that at this time, you'll only find processing results on the SFTP server through the accepted and failed folders. Leap is working on an API endpoint that will allow you to poll the status of your file uploads. This is expected to be released in Q3 of 2024.

When processing is done, the file will be moved into one of these two folders on the SFTP server:

• accepted- The CSV file passed basic validation checks (see Data validations section below) and Leap accepted the file.
  • Note: while there is no additional action required from your side at this point, it might take up to a day for Leap to fully process the interval data and, if applicable, calculate baselines and performance. You can use the Analyze API or Analyze Tab to see the results.

• failed - The CSV file contains one or more rows with invalid data and the file was rejected.
  • Note: Leap looks for all data validation errors within the file and then stops processing the file entirely, meaning that you'll have to fix the issues and re-upload the entire file.
  • In the failed folder, you will find a subfolder with the filename (e.g. example-interval-data-upload.csv) which contains three files:
    1. [RANDOM_ID].errors.csv which contains per-row, per-column information of what exactly is wrong. E.g. Could not find column X, X is not a valid positive number, X is not a valid time, etc.
    2. [RANDOM_ID].feedback contains high-level information about the file processing, e.g. Parsed 14 lines in 6700 milliseconds, found 14 errors on 14 lines
    3. example-interval-data-upload.csv which is the original file that you uploaded

Data validations performed

If a file doesn't meet these criteria below, you'll find the exact reason why it failed in the failed folder on the SFTP server, as mentioned above