Is it possible to have a master (parent) and detail (substream) which just looks up the details from the parent stream?

Yes, it is possible to have a master (parent) stream and a detail (substream) that looks up details from the parent stream in Airbyte. This is often referred to as nested streams or parent-child relationships in Airbyte's terminology. Airbyte provides a mechanism called

HttpSubStream

which is designed specifically for this purpose. Here's how it works: 1. You define a parent stream that fetches the main data (e.g., a list of IDs or primary records). 2. You then create a child stream (substream) that inherits from

HttpSubStream

and uses the data from the parent stream to make subsequent API calls for detailed information. Here's a basic example of how you might structure this:

python class ParentStream(HttpStream):     # ... parent stream implementation ...      @property     def use_cache(self) -> bool:         return True  # Enable caching for efficiency  class ChildStream(HttpSubStream):     # ... child stream implementation ...

The

ChildStream

can then use the data from the

ParentStream

to construct its API requests, typically in its

path

or

stream_slices

method. This approach is particularly useful when you need to make API calls for detailed information based on IDs or other data obtained from a parent stream. It allows you to efficiently fetch related data without unnecessary API calls. For more detailed information and implementation examples, you can refer to the following resources: 1. [Airbyte documentation on Nested Streams & Caching](https://docs.airbyte.com/connector-development/cdk-python/http-streams#nested-streams--caching) 2. [GitHub discussion on multi-stage streams](https://github.com/airbytehq/airbyte/discussions/46711) Remember that the exact implementation details may vary depending on your specific API and data structure, but the general concept of using

HttpSubStream

for parent-child relationships is well-supported in Airbyte.

I mean with the low code connector

To set up a master-detail substream (also known as a nested stream or child stream) using the low-code connector framework in Airbyte, you can use the

SubstreamPartitionRouter

. This feature allows you to create streams that depend on other streams. Here's how you can implement this: 1. In your connector's YAML file, you'll need to define both the parent (master) stream and the child (detail) stream. 2. For the child stream, you'll use the

SubstreamPartitionRouter

in the

retriever

section. This allows the child stream to use data from the parent stream. Here's a basic example of how the YAML structure might look:

yaml streams:   - name: parent_stream     # ... other parent stream configurations ...    - name: child_stream     retriever:       type: SubstreamPartitionRouter       parent_stream_configs:         - stream: "#/streams/parent_stream"           parent_key: id           partition_field: parent_id     # ... other child stream configurations ...

In this setup, the

child_stream

uses the

SubstreamPartitionRouter

to fetch data based on the results of the

parent_stream

. The

parent_key

specifies which field from the parent stream to use, and the

partition_field

indicates how this key is represented in the child stream's API calls. It's important to note that the exact implementation details may vary depending on your specific API and requirements. The Connector Builder UI can be a helpful tool for iterating on and debugging your connector configuration [Connector Builder UI](https://docs.airbyte.com/connector-development/connector-builder-ui/overview). For more detailed information on implementing nested streams, you can refer to the Airbyte documentation on [partition routers and nested streams](https://docs.airbyte.com/connector-development/config-based/understanding-the-yaml-file/partition-router#nested-streams).