Sources and extracts

You can then begin setting up extracts by clicking on the source and clicking Add extract. A new prompt will appear that requires you to fill in fields detailing which endpoint or table to extract data from.

Click on the extract to view or edit its properties.

Extract properties:

• Extract name
  • Used for organization. We recommend naming it based on the data you're extracting. For example: "User events"
• Description
  • For reference purposes. For example: "Daily extract of all MongoDB user events"
• Status
  • Keep this as "active" when first setting up an extract. If you have a scheduled extract that you don't want to run, you can switch this to "pause".
  • Pausing will not stop any currently running extracts.

Load method:

The load method determines how data is loaded into the destination table.

• Drop, create, load
  • Deletes the destination table, recreates it, then loads the new data.
• Truncate, load
  • Removes old data from the table, then adds new data. The column names and table schema persist.
• Append
  • Adds new data to the bottom of the destination table without deleting old data. If incremental is supported, it will execute automatically when the load method is set to append.

Extract type:

• This can be either Full or Incremental.
• A Full extract type extracts all available data from the connector and loads it into the destination table based on the load method.
• Incremental extracts only the data you need, avoiding duplicate data already in your destination table.
  • This typically works by checking the destination table for the most recent row of data, defined by the incremental key (usually an updated_at field). The equivalent SQL command is:
  SQL

  SELECT MAX(updated_at) FROM <destination_table>;

  • Once the extract has this date, it only requests data updated since that date.
  • The incremental key for each connector is documented in the connector-specific docs.

Destination table:

• This is the table name that will be filled with extracted data. It can be a pre-existing table or a new table. You must also define the schema for the data.
• An example is mongodb_raw.mongodb_user_events_raw, which references a table called mongodb_user_events_raw in the mongodb_raw schema. If the table doesn't exist, it will be created when the extract first runs. If the table exists, data will be added to it. Note: if the load method is not "Drop, create, load", the table definition (columns) must match the extract target data exactly, or an error will occur.
  • The column order must also be the same to avoid data type mismatches and data appearing in the wrong columns.

We recommend following this naming convention for destination tables: <source_name>_raw.<source_name>_<collection_name>_raw.

For example, data from the transactions collection in MongoDB will land in the mongodb_raw schema with the table name mongodb_transactions_raw, resulting in mongodb_raw.mongodb_transactions_raw.

Once you've set everything up, click SAVE to save the extract. It will now appear under the source in the Your Sources and Extracts screen.

From here, you can schedule the extract by clicking the clock icon on the far right, run it manually by clicking the play button, edit it by clicking the pencil icon, or delete it by clicking the bin icon.

Scheduling extracts is important for your pipeline, as it determines when data is extracted and loaded into the data warehouse for the transform layer to process.

Additional functionality

Details: Provides a high-level overview of the extract, including its description, current schedule settings, and the timestamp of the last successful run.

Schedule: A dedicated section for managing the frequency and timing of the extract's execution.

Notifications: Configure and manage alert preferences for the extract, such as success or failure notifications.

Logs: Displays a filtered table of the extract's execution history, including status (Success, Info, Failure) and detailed system messages.

Table details: Shows technical metadata for the resulting data table, such as the full table name, creation date, row count, and a schema overview listing columns and their data types. This tab also provides quick access to the SQL Console and SQL Generator.

Archive Sources (UAT)

The Archiving feature provides a secure way to manage your workspace by moving inactive data sources into a protected, read-only state. This keeps your active source list organised without permanently losing historical configurations or data.

To archive a source:

1. Navigate to Sources
2. Click the '...' menu
3. Select Archive source

To restore a source:

1. Click the '...' menu next to Sources and extracts

2. Open the View Archive section.

   

3. Find the source you want to reactivate.

4. Click Restore selected sources.

The source will return to your active list with all its previous configurations intact.