Empower supports publishing datasets to SFTP servers via CSV, TSV, or parquet file formats automatically when the underlying data is updated.

Prerequisites

1. An SFTP server and its credentials.
2. Data in your Empower delta lake to publish.
3. Advanced Options is toggled on for your account.

Steps

Add the SFTP Server as a Data Source in your Empower Environment

1. Navigate to your Empower deployment's Data Sources tab in the UI.
2. Click + to add a new data source, navigate to SFTP or use the search bar.
3. Fill in the required fields:
4. Username
5. Password
6. Host
7. Port (defaults to 22)
8. Click save and connect to save the new SFTP data source.

🚧

Connection Test Errors

You may notice an error message pop up with SFTP source types, even when the connection has saved. This is expected behavior, as Empower cannot currently test SFTP connections. Ensure that your server has an open port and you have entered all field information correctly. We will be enabling SFTP testing at a later Empower release.

Configure DatabaseToStepCommand

1. Navigate to the Advanced Options tab on the left side of the screen.
2. Add a new record to the DatabaseToStepCommand table.
3. Use the SFTP server in the Database ID field.
4. Configure this record to be a PUBLISH phase.
5. Select a new load group number if you want this to run independently OR add to an existing loadgroup to have it run with the rest of that load group.
6. Write 1 for the Execution Order field if this is the only command to be executed; otherwise configure to be in whatever order you wish.
7. You can leave the Source Schema Suffix and Target Schema Suffix fields as they are defaulted.
8. You can leave the Item Name to Execute field set to None.

Configure Publish Entity Table

1. Identify the entities (tables) you want to publish to the target SFTP Server.
2. Create a new record for each table you wish to publish in the Publish Entity Table.
3. Ensure each new Publish Entity entry includes at least the following columns:
   1. Load Group: matches DatabaseToStepCommand entry’s loadgroup.
   2. Target Entity: this could be name of the entity or desired path (month/date/Target_entity_name) as it will be written to a path in the SFTP server.
   3. Source Catalog: the source table’s Unity Catalog.
   4. Source Schema: the source table’s schema.
   5. Source Entity Name: the source table’s name.
   6. Source Entity Filter: this adds an optional where condition parameter to the (ex: account_type=”Debit”).
      1. Keep in mind that this filter must be valid SQL (everything that could be part of a SQL WHERE clause, just without the preceding WHERE in the statement).
      2. For proper behavior, make sure to use fields that are contained within the table you are specifying.
   7. Target ID: The DatabaseListID of the target SFTP server (this should be the same as the DatabaseToStepCommand’s DatabaseID).
   8. Is Active: only active entities are published, must be set to true for this entity to be enabled.
      

Configure Entity Options Table

1. For each of the entities specified in the Publish Entity Table, create an entry in the Entity Options table (using the same entity ID) to define the type of file (name = "file_type").
2. You wish to publish to SFTP. You may select between CSV, TSV, and parquet formats.