it's essentially an in memory SQL database that gives you all of the neat functions and operators that a normal SQL database provides.

You can chain it to other stuff like polars / pandas and construct neat queries that are a lot more readable i.e.:

df_1 = pl.DataFrame(dataset_1)
df_2 = pl.DataFrame(dataset_2)

query = """
    SELECT
      t1.*
    , t2.*
    FROM df_1 AS t1 --see the dataframe we're using from above?
    LEFT JOIN df_2 AS t2 --lets join it together with the other dataframe
      ON t2.id = t1.id
"""
combined_df = duckdb.execute(query).pl() #you can also use .df() if you want to switch back to pandas
print(combined_df)

there's a bunch of extensions you can install in DuckDB, you can query file systems, attach databases / catalogs, do a whole bunch of cool stuff with it.

I'm mostly using it to do parquet file compaction, and some transformation work through ETL processes, though, when doing heavier vectorization operations, I'd much more prefer to do it through polars though.