[source-redshift] DATE and TIMESTAMP columns discovered as plain String (missing JSON Schema format hints)

[ffranzin]

Environment

• Connector: source-redshift
• Stack: Legacy JDBC CDK (AbstractJdbcSource / JdbcSourceOperations)

Current Behavior

Given a Redshift table:

CREATE TABLE invoices (
    invoice_date DATE,
    due_date DATE,
    created_at TIMESTAMP,
    amount NUMERIC,
    invoice_id INTEGER, 
    ....
);

Schema discovery produces:

Column	Discovered Airbyte type
invoice_date	String
due_date	String
created_at	String
amount	Number
invoice_id	Integer

Catalog JSON Schema for the date/timestamp columns is effectively {"type":"string"} with no format (date / date-time).

Downstream, destinations that honor semantic types (e.g. destination-postgres) therefore create VARCHAR columns instead of DATE / TIMESTAMP.

Redshift metadata itself is correct (DATE / TIMESTAMP). The destination mapping is also correct when given format-aware schema types. The loss happens during source discovery.

Expected Behavior

Discovery should emit semantic Airbyte temporal types, e.g.:

Column	Expected type
invoice_date	Date (JsonSchemaType.STRING_DATE, format=date)
due_date	Date
created_at	Timestamp without timezone (STRING_TIMESTAMP_WITHOUT_TIMEZONE, format=date-time)

So destinations can create native temporal columns.

Root Cause

RedshiftSourceOperations extends JdbcSourceOperations but does not override getAirbyteType.

The legacy JDBC CDK default maps temporal JDBC types to plain string:

// airbyte-cdk/.../JdbcSourceOperations.kt
JDBCType.DATE -> JsonSchemaType.STRING
JDBCType.TIME -> JsonSchemaType.STRING
JDBCType.TIMESTAMP -> JsonSchemaType.STRING

Discovery path:

RedshiftSource → AbstractJdbcSource / AbstractDbSource.discover
  → discoverInternal (JDBC metadata → JDBCType on CommonField)  // still correct
  → DbSourceDiscoverUtil.convertTableInfosToAirbyteCatalog
  → sourceOperations.getAirbyteType(JDBCType)  // ★ semantic info lost here
  → AirbyteCatalog

RedshiftSourceOperations only customizes value serialization (putTimestamp, copyToJsonField, etc.), not catalog type mapping. Git history under source-redshift has no prior getAirbyteType / STRING_DATE mapping — this appears to be an omission.

Comparison with other connectors

Connector	Temporal discover mapping
ClickHouse	Overrides getAirbyteType → STRING_DATE / STRING_TIMESTAMP_* (fixed in #72484 / #72483)
DB2 / SingleStore	Same override pattern
MySQL / Postgres	Bulk CDK FieldType mappers → DATE / TIMESTAMP_*
Redshift	Inherits CDK default → plain STRING

Note: changing the CDK default was briefly tried historically and reverted (#7859 / #7969), so a connector-local override is the established fix pattern.

[octavia-bot]

AI Triage Starting...

Thank you for opening this issue! I'm an AI assistant that will do an initial review and may:

• Ask clarifying questions if needed
• Suggest relevant documentation or workarounds
• Flag this for internal visibility

Note: Community issues are reviewed on a best-effort basis and do not have a guaranteed response time. For additional help:

• Join the Airbyte Community Slack to connect with other users and community members
• If you're an Airbyte Cloud customer, you can open a support ticket or contact your account representative, referencing this issue URL

View playbook

• 🔗 Session URL: https://app.devin.ai/sessions/2cbf9a2a78734ef39cfff82e3d0524ae

✅ Devin AI session created successfully!

[airbyte-support-bot]

Hi Flavio Franzin (@ffranzin), thank you for the detailed report — the reproduction, the discovery path walkthrough, and the comparison table are all very helpful.

We've escalated this to our team for investigation: airbytehq/oncall#13267. Note that community-reported issues are reviewed on a best-effort basis and we can't commit to a timeline.

One thing worth flagging for whoever picks this up (including you, if you do open a PR): changing the discovered type for existing columns alters the catalog that users already have, so it may need breaking-change handling and a version bump strategy rather than a straight patch. It would be good to note that in the PR description so reviewers can weigh it.

If you'd like to contribute the change, the connector contribution guide and the source-redshift docs are the best starting points. If you attach any logs to this issue later, please mask or remove any sensitive information (API keys, passwords, tokens, connection strings) first.

---

Need more help? Join Airbyte Community Slack for peer support, or if you're a Cloud customer, open a support ticket referencing this URL.