ODBC

This page contains the setup guide and reference information for the ODBC source.

The ODBC source is a generic database connector for systems that expose data through an ODBC driver. If Daspire has a dedicated source for your database, use the dedicated source first. Dedicated sources usually provide better type handling, CDC support, and database-specific setup options.

Features

Feature	Supported?
Full Refresh Sync	Yes
Incremental Sync - Append	Yes, with a user-selected cursor column
Relative date lookback	Yes, through Daspire stream policy payloads
CDC	No
Delete replication	No
Namespaces	Yes, based on the ODBC schema name
Custom SQL	Yes, with optional per-stream cursor fields

Included drivers

The standard ODBC image includes:

• unixODBC
• FreeTDS
• PostgreSQL ODBC
• MariaDB/MySQL ODBC
• SQLite ODBC

Proprietary drivers such as Microsoft SQL Server, Oracle, SAP, and NetSuite ODBC drivers are not bundled. To use those drivers, build a custom connector image that extends yuanrui2014/source-odbc and installs the driver plus the required odbcinst.ini or DSN configuration.

Setup guide

1. Make sure the database is reachable

The machine running Daspire must be able to reach the target system through the ODBC driver. For private databases, configure the network path before creating the source.

2. Configure the ODBC connection

You can connect in either of two ways:

• DSN: reference a DSN already configured inside the connector image.
• Connection string: provide a full ODBC connection string.

Example SQLite connection string:

DRIVER={SQLite3};Database=/data/example.db

Example PostgreSQL connection string:

DRIVER={PostgreSQL Unicode};Server=postgres.example.com;Port=5432;Database=analytics

If Username or Password is set in Daspire, it is appended as UID and PWD unless those attributes already exist in the connection string.

3. Limit schemas or tables when needed

The connector discovers all non-system tables and views by default. Use Schemas and Tables to restrict discovery. Table allowlist values can be unqualified names such as orders or schema-qualified names such as public.orders.

4. Configure custom SQL streams

For systems with very large catalogs, such as NetSuite2.com through SuiteAnalytics Connect, use Custom SQL mode to define explicit streams instead of discovering every table.

Each SQL stream accepts one read-only SELECT or WITH query. Add Cursor Field when the query returns a stable cursor column and the stream should support incremental sync.

{
  "source_mode": "custom_sql",
  "sql_streams": [
    {
      "name": "netsuite_transactions",
      "namespace": "NetSuite2",
      "query": "SELECT id, tranid, lastmodifieddate FROM transaction",
      "cursor_field": "lastmodifieddate",
      "primary_key": ["id"]
    }
  ]
}

When a custom SQL stream is configured for incremental sync, Daspire wraps the SQL as a subquery, filters on the saved cursor value, and orders by the cursor field:

SELECT * FROM (<custom query>) daspire_custom_sql_stream
WHERE cursor_field > last_state
ORDER BY cursor_field ASC

Leave Cursor Field empty for full-refresh-only SQL streams.

5. Configure incremental syncs

ODBC has no generic CDC mechanism. For incremental syncs, choose a cursor column in the catalog, such as updated_at or a monotonic numeric id. The connector reads rows with:

WHERE cursor > last_state
ORDER BY cursor ASC

If no state exists and Initial Cursor Value is empty, the first incremental run reads the full table ordered by the cursor and then stores the latest cursor value.

6. Configure a rolling relative lookback

For validation pipelines or sources with late-arriving updates, Daspire can re-read a rolling date/cursor window without custom SQL. Add daspireStreamPolicy to the stream configuration in the pipeline catalog.

For a validation table that should contain only the recent window, use full refresh plus overwrite:

{
  "config": {
    "syncMode": "full_refresh",
    "destinationSyncMode": "overwrite",
    "daspireStreamPolicy": {
      "relativeLookbackDays": 30,
      "cursorField": "updated_at"
    }
  }
}

For an ongoing incremental pipeline, use a reliable primary key and a dedupe-capable destination mode:

{
  "config": {
    "syncMode": "incremental",
    "cursorField": ["updated_at"],
    "destinationSyncMode": "append_dedup",
    "primaryKey": [["id"]],
    "daspireStreamPolicy": {
      "relativeLookbackDays": 30,
      "cursorField": "updated_at"
    }
  }
}

This policy reads rows where the configured date/cursor field is greater than or equal to today - relativeLookbackDays on every run. Avoid plain append with a rolling lookback unless the destination table has its own downstream dedupe logic, because the same recent records are intentionally re-read each run.

Identifier quoting

Different ODBC drivers expect different identifier quoting rules. The Identifier Quote Style setting controls generated SQL:

Option	Example
none	public.orders
double_quote	"public"."orders"
backtick	`public`.`orders`
bracket	[public].[orders]

Use the quote style that matches your driver.

Data type mapping

ODBC Type	Daspire Type
Character, wide character, GUID, unknown	string
Integer, smallint, bigint, tinyint	integer
Numeric, decimal, float, real, double	number
Bit, boolean	boolean
Date	string, date
Time	string, time
Timestamp, datetime	string, date-time
Binary, varbinary, long varbinary	base64 string

If a driver reports a type that is not recognized, Daspire treats the value as a nullable string.

Limitations

• CDC and delete capture are not supported.
• Multi-column cursor fields are not supported.
• Custom SQL and joins are not supported.
• Driver installation and DSN file management are not configured in the UI.
• Driver-specific metadata behavior can vary; use schema and table allowlists for large or unusual catalogs.