First class support for date types in Airbyte

[sherifnada]

Tell us about the problem you're trying to solve

We currently map string-formatted dates e.g: ISO8601 as strings even when normalization is enabled in the destination. This makes it harder to leverage the data correctly in the destination when normalized. This issue aims to address that. The basic criteria for closing this issue is:

• In the types contained within AirbyteCatalog, support JsonSchema date/time types.
• In basic normalization, string-formatted date types are written into a column with the appropriate date/time type.
• Follow up issues have been created to make sources express the date/time formatting of string-represented date fields correctly.

This issue is more of an "epic" -- it will almost certainly require creating multiple child issues for each component of the solution.

┆Issue is synchronized with this Asana task by Unito

[cgardens]

@sherifnada we add the user stories on this. is the only thing we are trying to achieve here that when we run normalization it is surfaced with the correct type? I'm asking it in this way, because I suspect there's actually more that we want from this.

[jrhizor]

We currently do have several catalogs that are labelling date-time appropriately. Some don't, like Github: #1751

This should be a pretty high priority issue since it directly and negatively impacts the ability to play with data produced by normalization. Without a DBT-like step, all time-based analytics becomes impossible without writing some sort of a SQL "view" of the data. If someone is using Metabase/Looker/etc, they won't be able to create any time based graphs without this functionality.

An example of something that would require custom grouping queries do to the lack of this feature:
airbytehq/write-for-the-community#29

[ChristopheDuong]

If someone is using Metabase/Looker/etc, they won't be able to create any time based graphs without this functionality.

In metabase and looker, you can create "parameters" or "dimensions" (SQL Columns) specifying an SQL queries instead of just their name so you can actually say cast(my_data_column_in_string as timestamp) and it will convert it to a proper timestamp for use in the charts

• https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
• https://docs.looker.com/reference/field-params/sql

That's probably why we initially decided that support for date time types on the first iteration wasn't such a blocker for BI/analysis usage down the line...

But yes, it would be nice if more catalogs exposed the proper type and we correctly cast to it in normalization

[ChristopheDuong]

It seems like we don't handle integer type natively either and always end up casting them to float in the destination as they transit with number type in json primitive type:

airbyte/airbyte-db/src/main/java/io/airbyte/db/jdbc/JdbcUtils.java

Line 205 in 4c22b9b

case INTEGER -> JsonSchemaPrimitive.NUMBER;