How to copy all 1000 tables from source to target in ADF?

The most efficient way to copy many tables in ADF is a metadata-driven approach using a Lookup activity to fetch table names, followed by a ForEach activity that iterates and executes a Copy Data activity for each table.

Mechanics

The Lookup activity queries the source database's system catalog (e.g., INFORMATION_SCHEMA.TABLES for SQL Server, PostgreSQL, Snowflake, or sys.tables for SQL Server) to retrieve a list of table names. This list is then passed as input to a ForEach activity. Inside the ForEach loop, a Copy Data activity is configured. Both its source and sink datasets are parameterized to dynamically accept the current table name from the ForEach item. For example, the table name parameter in the dataset would be set to @item().TABLE_NAME. This pattern ensures reusability and scalability without creating individual copy activities for each table.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo';

Key Trade-offs & Best Practices

* Metadata-Driven Control: For more complex scenarios, instead of querying INFORMATION_SCHEMA, use a dedicated control table or JSON file. This allows for selective table inclusion/exclusion, custom source-to-target table mappings, or different copy configurations per table (e.g., specific column mappings for certain tables). * Concurrency: Set the batchCount property of the ForEach activity (typically 5-10) to control parallel execution. This balances performance with resource consumption, preventing throttling on source or sink systems. * Error Handling: Implement robust error handling within the ForEach loop (e.g., using on failure paths or try-catch patterns) to log and manage failures gracefully for individual tables without stopping the entire pipeline. This is critical for large-scale operations. * Logging: Ensure comprehensive logging for each table copy operation, capturing status, row counts, and any errors, which is vital for monitoring, auditing, and debugging. * Parameterization: Maximize the use of pipeline and dataset parameters to make the solution flexible and reusable across different environments or source/target connections.

In the interview, also mention: Discuss how you'd handle schema evolution, data type conversions, or implement change data capture (CDC) for production-grade solutions.