DuckDB is particularly useful when working with large datasets that do not fit into memory, or when you require SQL capabilities for complex queries. You would use DuckDB over Pandas, for example, when analyzing a 200GB dataset stored in a CSV file that you need to query efficiently without loading the whole dataset into memory.
Pandas is preferred when you need to perform complex data manipulations or analyses without dealing with database complexities. It is most useful when working with datasets that fit comfortably in memory and when you require advanced data handling features. For example, if you're conducting exploratory data analysis on a small CSV file with intricate data cleaning steps, Pandas provides convenient tools to accomplish this efficiently.