> ## Documentation Index > Fetch the complete documentation index at: https://docs.peliqan.io/llms.txt > Use this file to discover all available pages before exploring further. # Sync data between parent & sub accounts > Sync tables between parent and sub accounts in Peliqan using a Python data app with interactive selection, state persistence, and scheduled execution. You can sync datasets from a parent account to a sub-account or vice versa. This can be used to share datasets with customers. This approach can also be used as a permission model to share data with teams, where each team uses their own sub-account. ## Sync app This script supports syncing tables in both directions: from **subaccount → parent** and from **parent → subaccount**. It can be used for one-time data migrations or recurring scheduled syncs, ensuring that records in subaccounts and parent accounts stay consistent and up to date. ### Steps 1. **Pick sync direction** * Choose whether data should flow from **subaccount → parent** or **parent → subaccount**. This determines the source and target of your sync. 2. **Select source account, schema, and tables** * Pick the account where the data currently resides. * Select the database schema and tables you want to sync. 3. **Select target account and schema** * Choose the account where the data should be copied to. * Select the target schema to ensure the data lands in the correct location. 4. **Run the sync immediately or save for scheduled sync** * Trigger an instant sync to move data now. * Or save the configuration to run automatically later (daily, weekly, etc.). 5. **Verify that records are synced and linked correctly** * Check that all selected tables are picked successfully. * Ensure that records in the target account are correctly linked to their parent records. ```python theme={null} import json from datetime import datetime, timezone # --- Utility Functions --- def fetch_subaccounts(pq): current_time = datetime.now(timezone.utc) all_subaccounts = [] page = 1 while True: result = pq.list_subaccounts(page=page) if isinstance(result, str): result = json.loads(result) all_subaccounts.extend(result.get("data", [])) if not result.get("next"): break page += 1 return [ {"id": item["id"], "name": item.get("name", "Unnamed Subaccount"), "owner_id": item.get("owner", {}).get("id")} for item in all_subaccounts if not item.get("expires_on") or datetime.fromisoformat(item["expires_on"].replace("Z", "+00:00")) >= current_time ] def fetch_schemas(instance): schemas = [] databases = instance.list_databases() for db in databases: for schema in db.get("schemas", []): schemas.append({"id": schema.get("id"), "name": schema.get("name", "Unnamed Schema")}) return schemas, databases def fetch_tables(databases, schema_id): tables = [] for db in databases: for table in db.get("tables", []): if table.get("schema_id") == schema_id: tables.append({"id": table.get("id"), "name": table.get("name", "Unnamed Table")}) return tables def get_primary_key(table_id, databases): for db in databases: for table in db.get("tables", []): if table.get("id") == table_id: return table.get("primary_field_name") return None def load_state(direction_key): full_state = pq.get_state() or {} return full_state.get(direction_key, {}) def save_state(direction_key, data): full_state = pq.get_state() or {} full_state[direction_key] = data pq.set_state(full_state) def sync_sub2parent_from_state(subaccount_id, sub_schema_name, selected_table_names, target_schema_name, target_db_name): subaccount_instance = pq.get_subaccount_instance(account_id=subaccount_id) schemas, databases = fetch_schemas(subaccount_instance) selected_schema = next((s for s in schemas if s["name"] == sub_schema_name), None) if not selected_schema: return tables = fetch_tables(databases, selected_schema["id"]) selected_tables = [t for t in tables if t["name"] in selected_table_names] pk_map = {t["name"]: get_primary_key(t["id"], databases) for t in selected_tables} dw_name = f'dw_{subaccount_id}' sub_account_dbconn = subaccount_instance.dbconnect(dw_name) LIMIT = 500 for table in selected_tables: table_name = table["name"] pk = pk_map[table_name] offset = 0 all_rows = [] while True: query = f'SELECT * FROM "{sub_schema_name}"."{table_name}" LIMIT {LIMIT} OFFSET {offset}' rows = sub_account_dbconn.fetch(dw_name, query=query) if not rows: break all_rows.extend(rows) offset += LIMIT if not all_rows: continue dbconn = pq.dbconnect(pq.DW_NAME) dbconn.write(target_schema_name, table_name, records=all_rows, pk=pk) def materialize_parent2sub_from_state(subaccount_id, subaccount_owner_id, subaccount_schema, source_db_name, source_schema_name, selected_table_names): parent_databases = pq.list_databases() selected_source_db = next((db for db in parent_databases if db["server"]["friendly_name"] == source_db_name), None) source_schemas = selected_source_db.get("schemas", []) if selected_source_db else [] selected_source_schema = next((s for s in source_schemas if s["name"] == source_schema_name), None) parent_tables = [] if selected_source_schema: for db in parent_databases: if db["server"]["friendly_name"] == source_db_name: for table in db.get("tables", []): if table.get("schema_id") == selected_source_schema["id"]: parent_tables.append(table) selected_parent_tables = [t for t in parent_tables if t["name"] in selected_table_names] source_tables = [(t["id"], t["name"]) for t in selected_parent_tables] pq.subaccount_materialize( connection_name=f'dw_{subaccount_id}', database_name=f'dw_{subaccount_id}', subaccount_id=subaccount_owner_id, table_source_list=source_tables, target_schema=subaccount_schema ) if RUN_CONTEXT == "interactive": # --- Interactive mode --- sync_direction = st.radio( "Choose sync direction", ("sub2parent", "parent2sub"), horizontal=True ) stored_state = load_state("sub_to_parent" if sync_direction == "sub2parent" else "parent_to_sub") subaccounts = fetch_subaccounts(pq) if not subaccounts: st.warning("No subaccounts found.") st.stop() if sync_direction == "sub2parent": st.title("🔁 Sync Subaccount Table(s) to Parent Account") col1, col2, col3 = st.columns(3) with col1: subaccount_names = [s["name"] for s in subaccounts] selected_sub_name = st.selectbox("Subaccount", subaccount_names, index=None) selected_sub = next((s for s in subaccounts if s["name"] == selected_sub_name), None) schemas, databases = [], [] if selected_sub: subaccount_instance = pq.get_subaccount_instance(account_id=selected_sub["id"]) schemas, databases = fetch_schemas(subaccount_instance) with col2: sub_schema_names = [s["name"] for s in schemas] if schemas else [] selected_sub_schema_name = st.selectbox("Schema (Subaccount)", sub_schema_names, index=None) selected_schema = next((s for s in schemas if s["name"] == selected_sub_schema_name), None) with col3: tables = fetch_tables(databases, selected_schema["id"]) if selected_schema else [] table_names = [t["name"] for t in tables] selected_table_names = st.multiselect("Table(s) (Subaccount)", table_names, default=None) st.subheader("📢 Select Target in Parent Account") col4, col5 = st.columns(2) parent_databases = pq.list_databases() with col4: target_db_options = [db["server"]["friendly_name"] for db in parent_databases if "server" in db] selected_target_db_name = st.selectbox("Target Database (Parent)", target_db_options, index=None) selected_target_db = next((db for db in parent_databases if db["server"]["friendly_name"] == selected_target_db_name), None) with col5: target_schema_list = selected_target_db.get("schemas", []) if selected_target_db else [] target_schema_names = [s["name"] for s in target_schema_list] selected_target_schema_name = st.selectbox("Target Schema (Parent)", target_schema_names, index=None) if st.button("💾 Save selections for scheduled sync"): selection_state = { "subaccount_id": selected_sub["id"] if selected_sub else None, "subaccount_schema": selected_schema["name"] if selected_schema else None, "selected_tables": selected_table_names, "target_db_name": selected_target_db_name, "target_schema_name": get.selected_target_schema_name, } set_state("sub_to_parent", selection_state) st.success("Selections saved for scheduled Subaccount→Parent sync!") if st.button("🚀 Sync Table(s) to Parent"): if not (selected_sub and selected_schema and selected_table_names and selected_target_schema_name and selected_target_db_name): st.error("Please select all fields.") else: sync_sub2parent_from_state( subaccount_id=selected_sub["id"], sub_schema_name=selected_schema["name"], selected_table_names=selected_table_names, target_schema_name=selected_target_schema_name, target_db_name=selected_target_db_name ) else: st.title("🔁 Sync Parent Account Table(s) to Subaccount") parent_databases = pq.list_databases() col1, col2, col3 = st.columns(3) with col1: source_db_options = [db["server"]["friendly_name"] for db in parent_databases if "server" in db] selected_source_db_name = st.selectbox("Source Database (Parent)", source_db_options, index=None) selected_source_db = next((db for db in parent_databases if db["server"]["friendly_name"] == selected_source_db_name), None) with col2: source_schema_list = selected_source_db.get("schemas", []) if selected_source_db else [] source_schema_names = [s["name"] for s in source_schema_list] selected_source_schema_name = st.selectbox("Source Schema (Parent)", source_schema_names, index=None) selected_source_schema = next((s for s in source_schema_list if s["name"] == selected_source_schema_name), None) with col3: parent_tables = [] if selected_source_schema: for db in parent_databases: if db["server"]["friendly_name"] == selected_source_db_name: for table in db.get("tables", []): if table.get("schema_id") == selected_source_schema["id"]: parent_tables.append(table) parent_table_names = [t["name"] for t in parent_tables] selected_parent_table_names = st.multiselect("Table(s) to Materialize", parent_table_names, default=None) st.subheader("📦 Select Target Subaccount & Schema") col4, col5 = st.columns(2) with col4: subaccount_names = [s["name"] for s in subaccounts] selected_sub_name = st.selectbox("Target Subaccount", subaccount_names, index=None) selected_sub = next((s for s in subaccounts if s["name"] == selected_sub_name), None) with col5: if selected_sub: subaccount_instance = pq.get_subaccount_instance(account_id=selected_sub["id"]) schemas, databases = fetch_schemas(subaccount_instance) schema_names = [s["name"] for s in schemas] selected_sub_schema_name = st.selectbox("Target Schema (Subaccount)", schema_names, index=None) else: selected_sub_schema_name = st.selectbox("Target Schema (Subaccount)", [], index=None) if st.button("💾 Save selections for scheduled materialization"): selection_state = { "source_db_name": selected_source_db_name, "source_schema_name": selected_source_schema_name, "selected_tables": selected_parent_table_names, "subaccount_id": selected_sub["id"] if selected_sub else None, "subaccount_schema": selected_sub_schema_name, } set_state("parent_to_sub", selection_state) st.success("Selections saved for scheduled Parent→Subaccount materialization!") if st.button("🚀 Sync Table(s) to Subaccount"): if not (selected_sub and selected_sub_schema_name and selected_parent_table_names): st.error("Please select all required fields.") else: materialize_parent2sub_from_state( subaccount_id=selected_sub["id"], subaccount_owner_id=selected_sub["owner_id"], subaccount_schema=selected_sub_schema_name, source_db_name=selected_source_db_name, source_schema_name=selected_source_schema_name, selected_table_names=selected_parent_table_names ) elif RUN_CONTEXT == "scheduled": # --- Subaccount → Parent --- if load_state("sub_to_parent"): print(f"Starting Subaccount → Parent sync for {load_state('sub_to_parent').get('subaccount_id')}") state = load_state("sub_to_parent") print(state) sync_sub2parent_from_state( subaccount_id=state.get("subaccount_id"), sub_schema_name=state.get("subaccount_schema"), selected_table_names=state.get("selected_tables"), target_schema_name=state.get("target_schema_name"), target_db_name=state.get("target_db_name") ) print("✅ Subaccount → Parent sync complete.") # --- Parent → Subaccount --- if load_state("parent_to_sub"): print(f"Starting Parent → Subaccount materialization for {load_state('parent_to_sub').get('subaccount_id')}") state = load_state("parent_to_sub") # state = pq.get_state() or {} subaccounts = fetch_subaccounts(pq) selected_sub = next((s for s in subaccounts if s["id"] == state["subaccount_id"]), None) if selected_sub: materialize_parent2sub_from_state( subaccount_id=state.get("subaccount_id"), subaccount_owner_id=selected_sub.get("owner_id") if selected_sub else None, subaccount_schema=state.get("subaccount_schema"), source_db_name=state.get("source_db_name"), source_schema_name=state.get("source_schema_name"), selected_table_names=state.get("selected_tables") ) print("✅ Parent → Subaccount materialization complete.") else: print("❌ Subaccount not found.") ``` This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.