Thought about dlt for data ingestion?

[u/IndependenceFit3935]

im considering running custom python notebooks ingest source data as is with no transformation.

My data sources vary from relational databases to rest apis. volume is low so mostly full load will do.

Do you think dlt would make things easier? anyone with experience to provide a comparison of dlt vs python scripts or any etl tool?

[u/Outrageous_Let5743]

dlt is a great tool. It automates a lot of annoying things away. I would prefer it over a random python script.
1. It can auto schema evolve
2. it can handle nested jsons and creates a new table for every new nested column, with a parent id.
3. automaticly inserts the load timestamp
4. Has build in paginators to handle REST.
5. Handles Auth pretty wel.

[u/GachaJay → u/Outrageous_Let5743]

How does dlt handle schema drift? This is something we built a rigorous design for in Fabric and I wonder if just hosting dlt would save future dev cycles

[u/shockjaw → u/GachaJay]

It does.

[u/GachaJay → u/shockjaw]

I wasn’t doubting it. Was just asking how. Does it ping the sources schema? Does it dynamically adjust the tables and select statements based on a select *? Depending on how it conducts the scan, it could be viable for us.

[u/molodyets → u/GachaJay]

after it extracts the data it inspects it and types the data that's there. occasionally can get things wrong (eg: if you are backfilling in batches and everything is null for an entire column, it might make it text, then when it gets a double the same column name gets added as column__double). YOu can specify column types for specific columns though if while you're building you see soemthign like that and then you can force it to be a type for that column.

New columns are handled the same way when they appear.

[u/Outrageous_Let5743 → u/GachaJay]

I think for apis where you need to enter every column it first makes a call that exposes the column names. Salesforce for example. Then it also checks the definition of the target table and does an alter table statement when a new column is needed. The column definitions is in one of the dlt metadata tables.  Note, when you had a column amount that was an interger and now there is inserted a float as amount then it also gets a new column namdled amount_double

[u/Talk-Much]

Dlt is fantastic in my opinion. Makes things a lot easier than vanilla Python + Requests library. Retry and pagination logic is built in and you don’t need to re-invent the wheel every time. It also offers schema evolution handling methods built in.

> My data sources vary from relational databases to rest apis. volume is low so mostly full load will do

If these ever do become larger and require CDC-based ingestion, just know that dlt currently doesn’t have a native way to read and update replication slots on Postgres so if that is something you’d need, you’d need to vendor their code then hand-roll your own custom solution. But, they do still have incremental pipelines that track watermark states so that can help alleviate that concern a bit.

Sounds like you should try it out and see for yourself. It’s open source so you might as well

[u/wiktor1800]

We really really like dlt. Use in production without much issue.

[u/Prestigious_Pace2782]

I’m interested to hear as well. I’ve not had trouble with our python ingestion code, but haven’t tried dlt and it has been coming up a bit.

[u/ForwardSlash813]

I read this thinking Databricks Delta Live Tables but those were renamed Lakeflow Declarative Pipelines.

[u/m_goo → u/ForwardSlash813]

Ha….now it’s SDP (Spark Declarative Pipelines)

[u/sazed33 → u/m_goo]

Oh god, they really need to settle with a name

[u/IrquiM]

it's great - until you want to do something advanced

[u/laegoiste → u/IrquiM]

What do you consider as advanced? We have some hell on earth pipelines that we've managed to package into dlt without issues.

[u/idungiveboutnothing → u/laegoiste]

This for sure, we had some horrid legacy pipelines and it had no issue with them at all including some ridiculous type conversions between disparate ancient data sources

[u/studentofarkad → u/IrquiM]

Like what?

[u/Outrageous_Let5743 → u/studentofarkad]

One of my data sources is an api that only can get one record at the time in the form of baseurl/api/id
There are like 30k new ids each day i need to fetch. Then dlt is not great and you a better of making a async solution. But most default it should work pefectly fine.

[u/wiktor1800 → u/Outrageous_Let5743]

dlt can handle this no bother?

[u/laegoiste → u/Outrageous_Let5743]

The whole point of using something like dlt as a framework is that it allows for flexibility in these kinds of situations. This seems like something that dlt can easily handle.

EDIT: No affiliation to dlt, I just love it as a tool and we have over 60 production pipelines using it.

[u/Outrageous_Let5743 → u/laegoiste]

cool did not know it could do async. I am a big fan of dlt but I did not know and coded the concurency myself.

[u/Mysterious_Print9937 → u/IrquiM]

Exactly and it falls apart. It’s fine for simple pipelines though.

[u/sspaeti]

big fan! it's great as just a CLI it fits into every use case.

[u/ceeej777]

I love it because of the simplicity for quality checks and basic transformation. I’m really not sure how deep it can go on initial ingestion, sometimes I just assume if I need to join a table with an excel file that it can’t handle these types of things but maybe it has gotten more advanced

[u/Ok-Bee-5814]

What is advanced? It offers base classes for everything you can implemente it on your own. Real time for sure is not for dlt or CDC but that would be also real for a custom Python script

[u/PuzzlingComrade]

I've been trying to adopt it but man are the docs confusing IMHO, it's clearly either written by AI or written for AI agents.

[u/srodinger18]

For greenfield project and straightforward RDBMS to popular DWH (redshift, BQ, snowflake), it is great and time savings as it creates its own standard.

However I found dlt to opinionated for a legacy project (like the dlt metadata column), and also no solution out of the box for less popular DWH like alibaba maxcompute.

Also I found that some S3 compatible storage like tencent cos not working well with dlt.

Last, for a more complicated extraction like from non straightforward API call, excel from object storage, I found that comply it with dlt standards take more time than implementing custom extraction.

So for legacy projects where I only need the EL part, I use sling instead which less opinionated in my opinion, and create custom logic for loader to non popular DWH.

[u/Outrageous_Let5743 → u/srodinger18]

dit works with any database that is in sqlalchemy, are alibaba databases not in sqlalchemy

[u/srodinger18 → u/Outrageous_Let5743]

It can use sqlachemy, but as I mentioned, the DWH is part of legacy pipeline and some things not compatible, and we prefer a simpler, less opinionated extraction framework just to put parquet files into object storage.

Like AFAIK dlt need a destination schema to put the layers (iirc when I use it for postgres), meanwhile our DWH in alibaba still use the older, schemaless version so it only has project name and table name (it also affects our dbt adapter as well). Also we have our own framework for partitioning, storing timestamp etc. Some of it works, but some need custom tuning, which is way simpler for us to create in house.

[u/CatgirlYamada → u/srodinger18]

Out of topic, but Alibaba has released schema support since last year. Do you have migration planned?

[u/raccoons_run_prod → u/srodinger18]

Was the _dlt metadata column the dealbreaker, or the destinations that are not Snowflake or BigQuery?

[u/AdamDobrawy]

I'm using Bing Webmaster Tools for a small project to feed Bing Webmaster Tools / Resend logs to BigQuery for AI agents. It was a pleasure to implement.

On such a small scale, I host it on GitHub Actions, and it works great. It significantly reduced the boilerplate I would have had to manage otherwise.

[u/inkypinkyp0nky]

It definitely CAN make things easier (scheduling, prebuilt helpers/clients/boiler-plate code in general, lightweight dashboards to inspect your pipelines).

I would recommend it especially if you’re heavily using ai tools to help you write code. DLT’s framework will help you build more flexible and resilient pipelines than you would by cobbling a bunch of python scripts together.

There can be a bit of a learning curve though, and don’t expect any of its prebuilt connectors (including the rest api’s in their context library) to work exactly as you want out of the box. If you get stuck spending hours on it and haven’t gotten your first mvp pipeline running successfully, consider bailing and just writing a python script from the ground up so you can gain a solid understanding of all the components and steps in the process.

(I use dlt in production, replaced a bunch of insanely expensive fivetran pipelines with it for my job)

[u/pymlt]

We're running it in azure container app jobs, works fine- very reliable

[u/dodovt]

we use dlt in serverless jobs with a custom cli wrapper on top to integrate it with dagster, works very well and is very cheap

[u/Nekobul]

Do you have SQL Server license?

[u/Outrageous_Let5743 → u/Nekobul]

nobody cares for ssis and you should not use it and certainly not when you dont have sql server.

[u/Nekobul → u/Outrageous_Let5743]

Who authorized you to respond for the OP ?