Mapping multiple source streams into a single destination table on Postgres

[varunbpatil]

Tell us about the problem you're trying to solve

I have a 3rd party API from which I read industrial sensor data (time-series data) of the form (timestamp, value) for each of the close to 1000 sensors. I'm planning to write a custom HTTP source for this. The source's discover method returns one stream per sensor so that I can filter which sensors I want to sync in the Airbyte UI. So, I have close to 1000 source streams. On the Postgres destination (using TimescaleDB), I obviously don't want to create 1000 tables corresponding to 1000 streams. Instead, I want all streams to be merged into a single table with the format (stream_name/sensor name, timestamp, value).

Describe the solution you’d like

Ability to merge multiple source streams into a single destination table by adding the stream name as an additional column without having to write a custom destination in Airbyte, especially when all the streams have the same schema.

Describe the alternative you’ve considered or used

Custom pipelines.

┆Issue is synchronized with this Asana task by Unito

[jonniedarko]

@varunbpatil did you find a work around for this, I too would like to include the source as a colum in my tables

[albertlieyingadrian]

Interested in hearing the answers for this as well! Have you found a work around? @varunbpatil

[grishick]

@misteryeo this is potentially a feature for the future of normalization/transformation

[walker-philips]

I'd like to add another situation that would benefit from this:
Multiple source systems handling similar datasets, but varying primary keys among them. A separate/new source system is created that maps PK to PK of all source systems rather than trying to save/track PKs within each source system.

The PK mapping source can be merged with any of the other sources to allow correct importing into the desired destination (which is also a source). This way, systems can be agnostic of the unique identifiers of other systems, but still be able to send/receive data.