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.