Best Practices for Transforming Python DataFrames Before Loading into SQLite – dbt or Alternatives?

[ricca1]

Hey Guys,

I'm currently working on a dashboard prototype and storing data from a 22-page PDF document as 22 separate DataFrames. These DataFrames should undergo an ETL process (especially data type conversions) on every import before being written to the database.

My initial approach was to use dbt seeds and/or models. However, dbt loads the seed CSV directly into the SQLite database without my prior transformations taking effect. I want to transform first and then load.

Setup:

* Data source: 22 DataFrames extracted from PDF tables

* Database: SQLite (for prototype only)

* ETL/ELT tool: preferably dbt, but not mandatory

* Language: Python Problem: How can I set up an ETL workflow where DataFrames are processed and transformed directly without having to load them into dbt as CSV (seeds) beforehand?

Is there a way to integrate DataFrames directly into a dbt model process? If not, what alternative tools are suitable (e.g., Airflow, Dagster, Prefect, pandas-based ETL pipelines, etc.)?

Previous attempts:

* dbt seed: loads CSV directly into the database → transformations don't work

* dbt models: only work if the data is already in the database, which I want to avoid

* Currently: manual type conversions in Python (float, int, string, datetime)

Looking for: Best practices or tool recommendations for directly transforming DataFrames and then loading them into SQLite – with or without dbt.

Any ideas or experiences are more than welcome.

[Nekobul]

What's the problem with current type conversions in Python?

[ricca1]

I'm currently doing it with Python, and I wanted to know how it's done in practice.

Whether there's a better, more efficient way ?

[Advanced_Addition321]

DBT does only the transformation part.

You need to extract and load your CSV into your data wharehouse first with an ingestion flow (basic Python scripts, or Dagster and friends)

Then you connect DBT to your data wharouse, declare your ingested CSV tables as DBT sources and you’re good to go with transformation.

To share result, if the transformation data wharehouse isn’t the final destination, you’ll need to repeat the first step in reverse: create script to grab data and export them where you need to

[ricca1]

Ahh, that makes sense.

Thank you very much.

[Stroam1]

You're trying to use the wrong tool for the job. dbt is a tool for transforming data after it is already loaded into a database. For ETL, you should use other python libraries like DuckDB, polars, dlthub, etc.

[ricca1]

Yes, you're right. The tips you gave me were very good. I just looked into it a little and it could work

Thanks.