ETL+ No-code Features

Many ETL+ users do extensive data warehousing deployment and maintenance without writing any code or knowing much about databases. However, when needed, ETL+ provides low-code / full-code capabilities to address complex data problems. At every release, our no-code capabilities continue to expand as we improve ETL+ and incorporate AI features.

The following are features that allow users to extract, transform, and load data without writing code:

Source Objects

Connecting to 430+ source systems.
Listing source systems' tables.
Listing source systems' tables' columns.
Listing source systems' tables' data types (when available).
Listing source systems' tables' PKs (when available).
Loading source metadata to ETL+ metadata (for remote and/or offline source mapping maintenance).
Re-loading sources' metadata to ETL+ metadata on demand.
Deleting mapped source systems from ETL+.
When deleting a source from ETL+, deleting its target objects.
When deleting a source from ETL+, deleting its target objects loaded to the data warehouse.
Allocating an ETL+ installation to run tasks from each source system.
Encrypting credentials to connect to sources.
Optionally, setting source credentials to be retrievable.
Setting source credentials to be non-retrievable.
Setting a custom SQL schema to load a source’s data into the data warehouse.
Changing a source's type (e.g.: from OData to MS SQL Server).
Additional no-code features per source type:
- Google Sheets
  - Security access setup.
  - Just requires the Spreadsheet ID to work.
  - Narrowing down an extraction to a specific Google Sheets tab.
- MariaDB
  - Retrieving available databases from a MariaDB instance.
- MySQL
  - Retrieving available databases from a MySQL instance.
- MS Access
  - Browsing to find the database file(s).
  - Configuring the access password.
- MS Excel
  - Browsing to find the database file(s).
  - Defining if the first row has column names.
  - Accepting Excel sources from a web address.
  - Defining if web extractions should be SSL-based or not.
- MS SQL Server
  - Supported authentication methods:
    - MS Entra MFA
    - MS Entra Password
    - MS Entra Integrated
    - MS Entra Service Principal
    - MS Entra Managed Identity
    - MS Entra Default
    - SQL Server Authentication
    - Windows Authentication
  - Retrieving available databases from a MS SQL instance.
- OData
  - Supported authentication methods:
    - None
    - NTLM
    - Basic
    - Digest
    - Negotiate
    - SharePointOnLine
  - Defining extraction page size.
  - Defining pause period between pages.
  - Defining if web extractions should be SSL-based or not.
- ODBC
  - Supporting 64 and 32 bit drivers.
  - Customizing left and right table name delimiters when mapping tables.
  - Customizing left and right table name delimiters when mapping table columns.
  - Optimized code for Google Big Query.
  - Optimized code for HubSpot.
  - Optimized code for Intacct.
  - Optimized code for MongoDB.
  - Optimized code for NetSuite.
  - Optimized code for Pervasive.
  - Optimized code for Providex.
  - Optimized code for QuickBooks Online.
  - Optimized code for Salesforce.com.
- Oracle
  - Defining protocol Default or TNS.
  - Retrieving available databases from an Oracle instance.
- PostgreSQL
  - Retrieving available databases from a PostgreSQL instance.
- SQLite
  - Browsing to find the folder storing the database file(s).
  - Setting a password when required.
- Text / CSV
  - Browsing to find the folder storing text file(s).
  - Defining if the first row has column names.
  - Defining the column delimiter.
  - Accepting text sources from a web address.
  - Defining if web extractions should be SSL-based or not.

Target Objects

WIP
When mapping new tables and/or columns, ETL+ creates the SQL scripts to create the data structures and extract the data into MS SQL - this includes tables, columns & data types, & PKs.
- Users don't have to worry/code the creation of tables, columns, PKs, and write data type conversions.
- In most cases, users don't even need to know column data types and their conversions.
- Internally, there are multiple CASE statements converting data types from different data feeds into MS SQL (for instance, converting string fields of unknown sizes into varchar max).
The ETL+ Design page allows users to change data types easily; and ETL+ writes T-SQL conversion expressions automatically.
- In the example below, the user changed a source's string(2000) to varchar(20) in the data warehouse, and ETL wrote the CAST statement.
- Also, the user informed ETL+ that the Last_invoicedate has invalid MS SQL values and ETL+ wrote TRY_CONVERT. These T-SQL expressions can be overwritten/customized by users, if needed.
Users can easily set up ETL+ Jobs to manage and coordinate the refresh of tables and running scripts such as Tableau data extract refreshes, Power BI semantic model refreshes, running Command Line scripts, downloading data warehouse tables to CSV, etc.
ETL+ Jobs can coordinate different Load types in different schedules. For instance, as you might know, Upsert features are amazingly efficient at syncing up new and modified rows, but they usually can't capture rows that have been deleted from source tables. For such use cases, one can easily run frequent Jobs with Upsert (like every 5 minutes) and less frequent ones with Load All or Replace (like every hour) - to sync up deleted rows.
There are four types of ETL+ no-code refresh processes: Load All (load all rows at every table refresh), Replace (drop rows based on a criteria such as DocDate >=1/1/25, and re-load all rows since then), Upsert (update modified rows, insert new ones), and Append (add rows to the table).
Things such as creating/modifying/deleting new tables and new columns, duplicating them, and managing SQL schemas don't require coding.
ETL will prevent more than one load happening on a given table at the same time.
ETL will run the load of tables on the right VM.
The following two are not no-code per se, but they help a ton in the BI work:
- ETL+ reads and stores source systems' metadata (tables, columns, column data types, and PKs). This enables users to maintain ETL+ modeling faster, anytime and anywhere, without having to connect to sources in real-time.
- With proper security configuration, users can model, preview, and load tables from behind firewall systems remotely. For instance, after the initial ETL setup, a user can do ETL work for a client with Intacct and an on-prem SQL database for inventory management without going behind their firewall.
- Extensive and friendly logging, debugging, and alerts to monitor, evaluate, and troubleshoot the ETL operation and issues.

Extract SQL Statement

WIP

Job

WIP