Hi all, Objective: I have data from multiple sour...
# replication-troubleshooting
s
Hi all, Objective: I have data from multiple source accounts and need to sync them to a single destination (with extra column for account_id) using airbyte? (Source: square—multiple accounts; Destination: postgres) Approaches: 1. Edit the
source-connector
to add account_id in response body and schema. - If I go with this approach I'll have to create source myself and cannot utilize existing one. 2. Load data to different postgres schema, per account. And, combine data from all postgres schema into a global one. - How should I approach the combination of data? - Will I be able to run normalization on the combined data (using airbyte's normalization module)? Question: What is the recommended approach to achieve this? Is there some easy to use tools developed already? Is there a better approach to handle this scenario? What I have found... • https://docs.airbyte.com/understanding-airbyte/namespaces#--custom-format • https://airbytehq.slack.com/archives/C019WEENQRM/p1633512549383200?thread_ts=1633498797.374500&cid=C019WEENQRM
using prefixes (or custom namespace) to dispatch your source data per account in the destination. and then, using a custom SQL operations at the end of the sync to union all dispatched tables back into a single table but with an added column denoting which account it is coming from
2️⃣ 1
a
@Sabbiu Shah you can also sync the data to same destination with prefix to identify it's tenant
s
@Akilesh V So, this will still create multiple tables, right? If I am syncing categories, it will create
<prefix>_categories
tables. After that I will have to write a code that combines and puts data into a single table. And, then run the normalization? How can I handle the combination and normalization part?
a
@Sabbiu Shah simple solution is if you are using incremental update you can write view to combine these table
Copy code
SELECT col1, col2, col3, 'tenant_id' as source
FROM table1
UNION
SELECT colA, colB, colC, 'tenant_id' as source
FROM table2;
but my recommended would be write custom dbt
s
Hi @Akilesh V, thanks for your response. I have written series of operations that we might need to perform. This is what we need to do right?
Copy code
-- initially, I have tables for two customers "cus_1" and "cus_2"

select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_1' as source from _airbyte_raw_cus1_categories
union
select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_2' as source from _airbyte_raw_cus2_categories;


select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_1' as source from _airbyte_raw_cus1_locations
union
select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_2' as source from _airbyte_raw_cus2_locations;


--- create combined categories table
create table _airbyte_raw_categories
(
    _airbyte_ab_id      varchar not null
        constraint _airbyte_raw_categories_pkey
            primary key,
    _airbyte_data       jsonb,
    _airbyte_emitted_at timestamp with time zone default CURRENT_TIMESTAMP
);

alter table _airbyte_raw_categories
    owner to postgres;

--- create combined locations table
create table _airbyte_raw_locations
(
    _airbyte_ab_id      varchar not null
        constraint _airbyte_raw_locations_pkey
            primary key,
    _airbyte_data       jsonb,
    _airbyte_emitted_at timestamp with time zone default CURRENT_TIMESTAMP
);

alter table _airbyte_raw_locations
    owner to postgres;


--- source and _airbyte_ab_id to new table
create table _airbyte_source
(
    _airbyte_ab_id      varchar not null
        constraint _airbyte_source_pkey
            primary key,
    source      varchar
);

alter table _airbyte_source owner to postgres;

--- lets link source and airbyte_id for future reference
-- think about
-- 1. copy only incremental data (using _airbyte_emitted_at)
-- 2. handle on conflict (copy conflicted result to new table??)
INSERT INTO _airbyte_source
SELECT _airbyte_ab_id, source from (
    select _airbyte_ab_id, 'cus_1' as source from _airbyte_raw_cus1_categories
    union
    select _airbyte_ab_id, 'cus_2' as source from _airbyte_raw_cus2_categories
    union
    select _airbyte_ab_id, 'cus_1' as source from _airbyte_raw_cus1_locations
    union
    select _airbyte_ab_id, 'cus_2' as source from _airbyte_raw_cus2_locations
                                       ) as idsourcemapping;


select * from _airbyte_source;

-- lets merge airbyte raw categories
INSERT INTO _airbyte_raw_categories
SELECT * from (
    select * from _airbyte_raw_cus1_categories -- add where clause for incremental update
    union
    select * from _airbyte_raw_cus2_categories
                                       ) as allcategories;

select * from _airbyte_raw_categories;

-- lets merge airbyte raw locations
INSERT INTO _airbyte_raw_locations
SELECT * FROM (
    select * from _airbyte_raw_cus1_locations -- add where clause for incremental update
    union
    select * from _airbyte_raw_cus2_locations
                                       ) as alllocations;

select * from _airbyte_raw_locations;



-- then, use normalization module for the combined tables
n
Hey, Sabbiu! Yes, you can sync multiple sources to one destination. Airbyte offers a feature called "namespace configuration" that allows you to replicate multiple sources into the same destination while avoiding conflicts on tables being overwritten by syncs. You can either use a specific namespace for each connection or use prefix to stream names. For more information, please refer to the Airbyte Namespaces documentation.