What are data connectors?

Data connectors are software components that automatically extract data, sometimes periodically, from one or more upstream data sources and land that data in another database. Data connectors are common in most business intelligence, analytics and data science applications and frameworks.

As business transactions have increasingly shifted online, most organizations collect vast amounts of data. This information resides in multiple locations, including traditional databases, file systems, key-value stores, object stores and streaming data sources.

How data connectors work

A data connector sits between a source system and a destination, handling the mechanics of moving data so that analysts and engineers don't have to write custom integration code for every system. A typical connector:

1. Authenticates to the source using stored credentials, API keys, or tokens
2. Reads the source's structure, such as its tables, objects, or files
3. Extracts the selected data, applying any filters or parameters
4. Delivers the data to a destination such as a data warehouse, data lake, or analytics platform
5. Repeats on a schedule or on demand, picking up new and changed records

Once data lands, it can be mapped and modeled for analysis — see What is data mapping? — or transformed as part of an ETL process.

Types of data sources

While organizations once stored most of their information in on-premises databases, with the rise of cloud computing, information can be spread far and wide. Today, an organization's data may reside in cloud object stores, software-as-a-service (SaaS) platforms such as NetSuite or Salesforce, cloud-resident data warehouses, and even tools such as Google Sheets. In many organizations data can also reside in hundreds or thousands of spreadsheets, which are typically siloed and disparate sources. The more varied the information sources, the richer the analytics and decision quality, so it is imperative that organizations be able to connect to data wherever it resides.

Common connector categories include:

• Relational databases — such as Oracle, SQL Server, MySQL, and PostgreSQL
• Enterprise applications — ERP, CRM, HR, and supply chain systems such as Oracle E-Business Suite
• SaaS applications — accessed through vendor APIs
• Cloud storage and data lakes — object stores holding files in formats such as CSV, JSON, or Parquet
• Cloud data warehouses — used as either a source or a destination
• Files and spreadsheets — uploaded or synced from shared drives
• Streaming sources — event and message platforms that deliver continuous data

Data connectors do more than just transfer data

To be effective, data connectors need to do more than just access and transfer data from remote data sources. Ideally, a data loading service should handle details such as managing credentials and endpoints, applying filters, and accommodating parameters specific to each datastore to securely and transparently ingest data. Data connectors also need to handle full or incremental loads either on demand or according to preset schedules. Another important capability is support for "upserts" — updating records when they already exist and inserting new records when they do not.

Data connectors are frequently highly optimized, supporting features such as parallel loads, encryption at ingest, webhook callbacks and mechanisms to access data sources via proxies across firewalls. Without these capabilities, data connectors would be of less value in real-world applications.

Full vs. incremental loads

A full load copies the entire contents of a source table each time it runs. It is simple and guarantees a complete copy, but it can be slow and resource-intensive for large tables. An incremental load copies only the records that were added or changed since the last run, usually identified by a timestamp or change log. Incremental loads keep data fresher with far less processing. A common pattern is to run a full load once and incremental loads thereafter.

Benefits of data connectors

• Faster access to data — new sources can be connected without building custom code
• Fresher analytics — scheduled and incremental loads keep data current
• Less engineering effort — connectors handle authentication, paging, and error handling
• Broader coverage — data from many systems can be combined for a more complete picture

Incorta data connectors

Incorta's Direct Data Platform™ makes it easy to connect to any data source in the enterprise. With Incorta, organizations can realize a modern, durable data hub for multiple analytic and data science applications. With over 200 data connectors and additional connectors being added all the time, Incorta can access data wherever it resides. Analysts and data scientists benefit from timely, high-performance access to all data sources for better, faster decisions and improved organizational agility.