Use for ETL (Extract Transform Load)?

[markhalonen]

Our use-case is to generate a single-tenant DuckDB database from a multi-tenant Postgres database so the DuckDB can be downloaded by the user for reporting functionality.

So we rebuild the entire DuckDB database and populate it from Postgres.

Our first iteration was in SQLite and we just did homegrown sql strings, which now has to be completely re-written since we're moving to DuckDB.

So I am wondering, is this project a fit? It seems like the potential upsides are:

• Having a hardcore model of the SQL expressions instead of Strings gives way to long-term easier maintenance and refactoring
• Easier to write tests etc, doesn't rely on a sql engine to catch syntax issues

Downsides:

• Have to learn how to do it in this library
• We don't have Python in our stack, not a huge deal to add a service.

Maybe a "hello world" example that I would want to run would be

import sqlglot

# print(schema)

tables = []
tables.append(sqlglot.parse_one('create table user(id int)', read="duckdb"))

# Create the table
# duckdb.execute(user_table.sql())

# Do the insert from Postgres
for t of tables:
    insert_statement = sqlglot.parse_one("insert into user<SMARTLY GET COLUMN NAMES HERE> SELECT <SMARTLY GET COLUMN NAMES HERE> FROM postgres_scan('', 'steelhead', t.name) where domain_id=76;", read="duckdb")
    # duckdb.execute(insert_statement.sql())

I don't know if this is worth the effort, as this library seems more aimed towards translating/formatting SQL.

Thanks for considering! 🙏

[tobymao]

SQLGlot is used by many companies to programatically build cross-platform SQL. so i think it's an appropriate tool for the job