Lakeflow connect Native connectors (tik, meta ads, Google Ads) - one table per account

[GabeMatch]

We want to leverage these connectors to pull in marketing spend data. But the docs seem to say that the destination must be unique based on accounts. For Tik, we have a hundred accounts... each account will have a destination table for each object.  So like this... 

•  ads_account1, campaign_account1
•  ads_account2, campaign_account2

Total tables is accounts * number of objects. 100 accounts and assuming 6 tables means 600 tables! 

Is there a better solution? Would be great to ingest into only one table... all ads for an accounts feed into one Ads table.

Managed ingestion connectors don't support duplicate destination table names in the same schema

also, forum says:

> The message subject contains t*i*k*t*o*k, which is not permitted in this community. Please remove this content before sending your post.

makes it hard to make a post about its connector!

[Rjdudley]

I share your pain, I am sitting on 710 landing tables for this reason (of our services is templated website hosting and this is all Google Tag data).  I tried all kinds of tricks in the UI to try and land data into a single landing table but it wouldn't let me.  The best I could do to speed the process up is edit the YAML definition in a text editor and change the identifiers manually.  From here I combine all the landed data into a single bronze table which I then use for further processing.  Genie Code helped me create the landing>>bronze pipeline using a control table to list the source (landing) table and the target bronze table.  The control table looks a little funny because there are hundreds of different landing tables in the source column but only one target table for all rows.  Once this step runs it's the usual bronze-silver-gold pattern.

[aliyasingh]

I completely understand the frustration here managing that many individual landing tables feels like the exact kind of toil a modern data platform should eliminate. However, as confirmed in the thread, Databricks managed ingestion connectors currently do not support pointing multiple accounts to the exact same destination table name in the same schema. Since you cannot bypass the connector's requirement to land the data into separate tables, the standard Databricks workaround is to accept the multiple landing tables, isolate them, and unify them dynamically downstream. Here is the community-tested architecture to handle this efficiently:

1. Automate the Ingestion Setup

Do not use the Databricks UI to click through and set up 100 different account connections. Instead, define these connections as code. You can quickly generate the underlying YAML definitions for these jobs by editing the identifiers manually in a text editor or using a simple Python script to loop through your account IDs.

2. Isolate the "Mess" in a Landing Schema

Do not clutter your primary Bronze or Silver schemas with these 600 tables. Create a dedicated landing schema (e.g., marketing_raw_landing). Let Lakeflow Connect dump all 100 ad accounts and their respective objects safely into this quarantined zone.

3. Dynamically Unify the Data (The Control Table Pattern)

Instead of writing a massive SQL query with 600 UNION ALL statements, use a metadata-driven approach to dynamically merge these tables into your single, master Bronze table.

• Create a Control Table: Build a configuration table that lists all the source landing tables in one column and your single target Bronze table in another.

• Loop and Read: In a PySpark notebook or Delta Live Tables (DLT) pipeline, write a loop that reads this control table.

• Inject Lineage: As your script iterates through each landing table (e.g., ads_account1), dynamically inject a literal column like account_id so you know exactly which account the row originated from.

• Union and Write: Append each dataframe together using df.unionByName(..., allowMissingColumns=True) and write the final output to your unified master table.

By implementing this control table pattern, whenever a new marketing account is added, you simply add its name to the control table, and your downstream pipeline will automatically pick it up and merge it on the next run.

[Rjdudley]

There is also a forum post which demonstrates code to handle a large number of tables dyamically: Solved: Automate Lakeflow connect to ingest 300 tables not... - Databricks Community - 158058

[balajij8]

You can follow below

• Accept the landing tables in Bronze layer - Create a dedicated schema  like marketing_landing or meta_landing. Allow the managed ingestion pipeline to create all the destination tables here (ads_account1, ads_account2, etc.). Keep it as append-only raw tables managed by the connectors
• Build consolidation layer in Silver layer - You can create a Lake flow Spark Declarative Pipeline (SDP) that reads from all landing tables and combines them into consolidated tables. You can use STREAMING TABLE for real-time consolidation as new data arrives. Add account_id column in the table to track source account & do DQ checks. You will get 6 unified tables (ads, campaigns, ad_groups, etc.) ready for gold processing.

Create pipelines that are decoupled by domain, managed via multi target declarations within configuration and also driven by immutable, versioned artifacts stored securely within Unity Catalog Volumes.

Bundle Structure & Domain Isolation

• Decoupled Bundles - You can group configurations into small focused bundles aligned to specific data products or business domains or connectors

• Shared Lifecycles - Ensure that a single bundle contains only the resources (jobs, pipelines, dashboards) that share a unified deployment lifecycle and ownership domain boundary.

• Target Definitions - You can maintain all target definitions (dev, uat, prod) within a single yml per bundle to guarantee environmental structural parity. More details here

You can refer to the link given by @Rjdudley for the approach on handling bundles

[prafullkatiyar]

You can name a destination table when you create or edit your managed ingestion pipeline using Declarative Automation Bundles, notebooks, or the Databricks CLI, have a look at below doc. This should help.  
Name a destination table | Databricks on AWS