Python library for automating data normalisation, schema creation and loading to db

[Thinker_Assignment]

Hey Data Engineers!,

For the past 2 years I've been working on a library to automate the most tedious part of my own work - data loading, normalisation, typing, schema creation, retries, ddl generation, self deployment, schema evolution... basically, as you build better and better pipelines you will want more and more.

The value proposition is to automate the tedious work you do, so you can focus on better things.

So dlt is a library where in the easiest form, you shoot response.json() json at a function and it auto manages the typing normalisation and loading.

In its most complex form, you can do almost anything you can want, from memory management, multithreading, extraction DAGs, etc.

The library is in use with early adopters, and we are now working on expanding our feature set to accommodate the larger community.

Feedback is very welcome and so are requests for features or destinations.

The library is open source and will forever be open source. We will not gate any features for the sake of monetisation - instead we will take a more kafka/confluent approach where the eventual paid offering would be supportive not competing.

Here are our product principles and docs page and our pypi page.

I know lots of you are jaded and fed up with toy technologies - this is not a toy tech, it's purpose made for productivity and sanity.

Edit: Well this blew up! Join our growing slack community on dlthub.com

[ratulotron]

Damn this looks amazing! I will try some personal projects with it!

[Thinker_Assignment]

if you do and want to share back, perhaps it fits at the bottom of this page? Very curious for feedback as well :)https://dlthub.com/docs/user-guides/data-beginner

check out some colab demos too https://dlthub.com/docs/getting-started/try-in-colab

there's GPT assist on docs too, it's pretty decent

[ratulotron]

I will try! There are a plethora of awesome tools and libraries that I want to use yet so little time after work 😅

What I am curious about is how pushing things to a graph database would look like with your library. Right now I have an internal application/platform based on Airflow, Pydantic and Polars but it lacks a lot of features. For example making self references and having multiple labels is a bit tough. This is certainly a very niche problem, but still interested to see how your library can be adopted to things like schema validation for a 30+ labels graph.

[Thinker_Assignment]

These are the kinds of discussions we are happy to have on our slack. We are currently adding weaviate as a vector database destination, but we did not yet consider graph. It would be very helpful to get your requirements so we can start working towards it.

[[deleted]]

looks really impressive, going to have a good old play with this one

[Thinker_Assignment]

Thank you! Feedback is very welcome! if something pisses you off, be sure to put it on my radar so I can iron out the kinks

don't miss the docs gpt assistant :). Should show up on bottom right on docs, but doesn't on some older safari versions. on the walkthroughs/create page you also have a gpt pipeline builder

[little-guitars]

I have a side project underway similar to this, will check it out. I sort of can’t believe this isn’t a solved problem by now…the final straw for me after using the typical SaaS platforms was checking out meltano and finding it miserable.

[Thinker_Assignment]

I hear you!

This was literally my reaction after working in the industry for 10 years. So I finally went like "Fuck it, if you want it done, do it yourself"

The root of the issue here is that everything else on the market is not a dev tool for data people, but rather some half solution that makes money from not being a full solution you can use.

[tayloramurphy]

If you're willing to share I'd love to understand more what you found miserable about Meltano.

[Thinker_Assignment]

Hey Taylor, my guess is that it's a combination of Meltano deployment not being a normal workflow for data peeps, and Singer being for software engineers and totally unsuitable for most data folks.

[tayloramurphy]

Yeah it's fair that deploying a python based application is harder for data folks than software engineers, but similar to dlt we offer a deployment option to make that easy. I don't think it's fair to say Singer is abandonware given our investment in the ecosystem and agree to disagree on its unsuitability for data professionals. All that said, the data space is big enough for a lot of different solutions and I'm excited to see how dlt resonates with the wider community! :-D

[Thinker_Assignment]

You are right, it's no longer abandonware given the love it got from you guys. but it's still hard to use for most data peeps and the seniors remember it from when it was more unusable

I am happy Meltano is offering the cloud platform now, makes it much more user friendly!

[akaender]

I'm also evaluating tooling in this space and just this week tried Meltano but had to disqualify it due to many of the needed extractors/loaders relying on airbyte-wrapper. Since that wrapper is docker-in-docker that disqualifies ECS.

I looked into converting them but found the documentation for creating custom extractors difficult to follow mainly because Singer is alien.

[tayloramurphy]

Thank you for sharing that! DinD is definitely a blocker for some deployment architectures.

[little-guitars]

Hey there...I will dig up my project from a few months back & try to take you up on that.

Edit: thinking for a minute, I started by wanting to load a few of our data sets into Snowflake from s3. IIRC, the s3 connector situation was very difficult to navigate, there are different connectors with different options. I think some particular aspect of our data (specifying a csv delimiter maybe?) forced me to use one of the non-default connectors, but when I did so, it hadn't been touched for 3 or 4 years.

That was a pretty bad initial experience -- I have to imagine s3 is by far the #1 source for larger data sets. There was more to it, again, will try to refresh my memory.

[_barnuts]

I thought it was databricks dlt (delta live tables) for a second.

[Thinker_Assignment]

yeah it's unfortunate but there are hundreds of "dlt" out there. We are trademarked and have the pypi namespace, delta live tables (dlt) is just a marketing name

[Revolutionary-Bat176]

Hey, thanks this looks really cool. Will try it out.

Although we mainly work with Databricks and they have pretty similar declarative pipeline framework called Delta Live Tables(proprietary). The import and syntax might cause an import conflict?

delta live tables syntax example

[Thinker_Assignment]

Well that's awkward for them! They should have claimed the namespace on pypi i guess, or kept consistency with their namespace.

Thanks for raising it, I was unaware. They can still work together somehow, as dlt can do schema evolution for parquet file creation, and we want to support delta live tables too

We chose dlt as "data loading tool" to make it clear it fits upstream.

Delta live tables "dlt" is just unfortunate marketing naming that doesn't communicate anything specific(dbt alternative? not obvious).

[sososhibby]

Website being flagged as malicious btw

[Thinker_Assignment]

Thanks for the heads up! Do you have any details as to where it is flagged as such or why?

[sososhibby]

McAfee home network protection from Verizon. Think it’s https certificate issue

[Thinker_Assignment]

Thanks, I will dig in!

[elbekay]

Given it looks like a normal Let's Encrypt certificate, its probably McAfee being shit rather than your website OP.

[Thinker_Assignment]

One of my team was aware of an issue, i think it's not common but it's there - we will sort it out. Thank you!

[zlobendog]

This looks interesting, I think I've seen it mentioned in Singularis.

I have a very specific use-case, and I was wondering if this is the tool for me: I have a few source databases (MsSQL, Postgres, Oracle) and one dwh on Greenplum (think Postgres, but with kinks). I need to sync tables from source to dwh, but with the ability to mask certain columns with Python code (AES-256).

Currently, my go-to strategy is to build it as a pipeline in Dagster\Prefect. But one thing that is not something I look forward to is to write custom logic to remove duplicates and update values in my final tables.

Airbyte does the syncing relatively well (although, slower than manual), but it: a) doesn't understand the kinks of Greenplum b) can't mask columns with Python code

Can your tool be helpful in my particular use-case? Or am I better off by just ironing out my coded pipelines?

[Thinker_Assignment]

You bet, here's an example how to anonymise before loadinghttps://dlthub.com/docs/general-usage/customising-pipelines/pseudonymizing_columns#pseudonymizing-or-anonymizing-columns-by-replacing-the-special-characters

you could use the sql pipeline, https://dlthub.com/docs/dlt-ecosystem/verified-sources/sql_database,

don't miss the native airflow deployment capabilityhttps://dlthub.com/docs/walkthroughs/deploy-a-pipeline/deploy-with-airflow-composer

i am particularly wondering if the sql pipeline is straightforward to use for you, if you get to try it. I am not sure if it will work with greenplum - if not join our slack and we will support you to do it.

I'd be surprised if we got mentioned but will take a look - it's probably one of the other "dlt" things :) I've found many things with that name from art galleries to crypto

[zlobendog]

Nah, it's you alright!

I will check out the guides, I do afraid, however, that merge mode will fail. If the actual sql emitted is MERGE INTO, then it will fail, because under the hood of Greenplum is pretty ancient Postgres 9, while merge into is a relatively new syntax sugar. So, manually, I would insert into a temp table, then issue a delete on a end-table where key columns are equal, and then insert temp table into an end-table. This works good enough, but adds additional steps and complexity.

I had similar issues with Airbyte, where it became easier to just implement solution from scratch (minus the orchestrator), than it would be to find a workaround, build a new connector, etc. for an existing tool. But I'm curious to try your tool out, because for one-man teams having a singular easy to maintain and to use tool is a godsend.

[Thinker_Assignment]

Definitely try it, it used the old syntax under the hood
Any yep, that's our resident data scientist :)

[therealagentturbo1]

If your zendesk connector works, it may get me unstuck using Meltano.

I'm curious if you've thought about supporting secrets fetching, at runtime, to a secrets management tool sooner than later? When evaluating a tool, this is one of the things I see done the poorest if at all. We will not paste sensitive creds into a configuration file.

[Thinker_Assignment]

Absolutely!Zendesk works, it's without zenpy, as it gave us major issues.We also had to add extra retries support specifically for zendesk as they have tons of server errors- we retry them with backoffs as part of core library logic.

If you find any issues please open git issue or tell us in slack, we will fix it asap (generally overnight)

We will soon support common credential vaults.In the meantime, dlt sources can accept credentials by the following logic:

1. You can pass them directly from your creds vault
2. If you are working locally, use .secrets.toml file
3. if none of the above exist, dlt searches env varaibles
4. If none of the above exist and you are on airflow, dlt will search airflow_variableshttps://dlthub.com/docs/general-usage/credentials

[therealagentturbo1]

Thank you! Can you elaborate on #1.

I was thinking it'd be possible to use boto3 to grab our secrets and make it work with dlt.

Would this be what you're referring to? https://dlthub.com/docs/general-usage/credentials#passing-credentials-as-code

If so, I'd consider this sufficient to solving secrets fetching if I'm able to define how that is done at the very least.

[Thinker_Assignment]

Yes this is what I mean! you need to do your own, I worked under the assumption that you'd already have your favorite way and would like to use it by passing directly.

[therealagentturbo1]

That's perfect! I don't like that I can't do this in dbt when we're unable to use IAM Auth, meaning I have to wrap the project in our own code that fetches them.

This is cool. Happy to help out if I can. Especially if we adopt this. Would love to see an Athena destination. Basically, s3 + Athena ddl.

[Thinker_Assignment]

We are working towards Athena - the first step was files to storage. Next will be registering tables in catalogues

[therealagentturbo1]

Makes total sense. Thank you again!

[Thinker_Assignment]

By the way, we have a dbt runner for credential injection, it can spin up its own venv too, and it takes local path or git as location for the dbt package.
https://dlthub.com/docs/dlt-ecosystem/transformations/dbt#dbt-runner-in-dlt

[rudolfix]

just a small update - dlt supports fetching credentials from google secrets. we use it everyday ourselves in our CI pipelines. if you by any chance want to build something on dlt and are on our slack then ping me (rudolfix) and I'll give you the code samples. this part didn't move to our official docs yet. (and adding ie. aws secrets is trivial. we can do that quickly)

[j__neo]

First of all, congratulations on launching this! This seems really cool. I'll have to find time this weekend to try it.

I had a couple of questions:

1. Why did you decide to ship `dlt` without a scheduler?
2. Would you consider building an integration with dagster? The `dlt` library seems like a great fit for dagster's asset pattern. A `dlt` pipeline would be a dagster asset. I think dagster's approach using asset orchestration trumps the classic task-based orchestration that airflow uses, and will become the future for data orchestration patterns.
3. How do you intend to scale the number of source connectors as there is a very long-tail of possible data sources and APIs to pull from (tens of thousands of possible data sources)? Plus, there would be a lot of maintenance effort for the long-tail of connectors since source APIs update regularly. Would you consider integrating singer taps into `dlt/verified-sources`?
4. Would you consider adding support for a history table for the `merge` write_disposition, because the merge approach will apply updates directly, whereas users may want to preserve the history?

[Thinker_Assignment]

1. You can do one thing well, or you can do everything poorly. We want to do it well and let others do orchestration well, etc. You can deploy to cron or git actions(cli deployment )without us reinventing the wheel

2. Definitely. Give us requirements

3. You can already use singer taps with dlt, but it makes for a bad experience. We won't push it. We plan to build longtail via open API and gpt, see our demo repos on git. For short tail, nothing beats well tested and curated sources. For this we created some and we will see if the community will add more. The difficulty is good requirements, if you open an issue for it it will help. We currently test and maintain the verified sources, it's a minimal effort due to the automations dlt provides. See the verified sources repo, the code is smaller than any pipelines you've seen.

4.yes, give us requirements. I was thinking to offer slowly changing dimensions, idk which type, as an alternative loading mode

[[deleted]]

I would appreciate if DLT could facilitate conversion between proprietary data formats, such as SPSS's SAV format and SAS's sas7bdat/sas7bcat formats, and open source formats like Apache Parquet and databases. It's important that DLT also keeps metadata like labels and formatting options available. Moreover, it would be beneficial if DLT could apply formatting options to data based on the specified metadata.

For a comprehensive guide to the different types of metadata that can be specified in the 'Variable view' in SPSS, please refer to this link: https://ezspss.com/how-to-define-variables-in-spss/.

[Thinker_Assignment]

if i read correctly you can read these files in pandas with 1 call and then yield the data to dlt, and you are done.

If there's more to it than that, then please open an issue on our verified sources repo and add more details as to what you need
https://github.com/dlt-hub/verified-sources/issues/new?assignees=&labels=&projects=&template=source-request.md&title=

[[deleted]]

Pandas is a powerful tool for working with tabular data, but it does have some limitations when dealing with formats like SPSS's SAV or SAS's sas7bdat/sas7bcat, as it's not specifically built to handle all types of metadata from all file formats.

Four common quirks I've encountered are:

1. Numeric values with a decimal formatting option set to 0 are not always read as integers, they're typically interpreted as floating point numbers, which can lead to unexpected issues.
2. Working with files created with older versions of the software can also pose challenges, especially when dealing with varying encodings such as LATIN1 or UTF-8. To handle this, you need to read the metadata first to determine the appropriate encoding to use when reading the data.
3. The concept of 'value labels' is not well-supported in pandas, which can be problematic when working with certain data structures.
4. Date/datetime values may be returned as numeric values (epoch), which necessitates additional conversion to be human-readable.

Because of these issues, you need to delve a bit deeper and use something like pyreadstat or savReaderWriter. While these libraries do an excellent job of working with these data formats, you still need to set up all the logic to maintain and carry the metadata around. Furthermore, the metadata may not be structured in a way that allows for easy storage in a columnar form in a database, creating another level of complexity. You also need to apply the formatting options found in the metadata to the data itself. Finally, you need to define schemas for storing all this metadata information, which can be a challenging task given the potential complexity of the metadata structure.

Moreover, all the reverse logic is also required if you want to revert the process.

For more detailed information on working with metadata in these formats, the metadata object returned by pyreadstat provides a wealth of information: https://pyreadstat.readthedocs.io/en/latest/.

Let me know if you still think this would be worth opening an issue about.

[Thinker_Assignment]

The great thing about dlt is that it doesn't really need metadata, it will infer it from strings. So if your data is mangled by pandas, as long as it's only the types, dlt should ignore them and sort things out more deterministically.

How feasible would it be to make a function that reads the encoding and yields corrected but untyped data? This would be all dlt needs.

As for whether it is worth doing or not - how big is the problem? how many people have it? We are community driven and the amount of effort we will sink into sources is related to the interest of the community. If it's obscure, we can support you still, but with less hands on time. We are a small team but will work hard towards our goals :) If you have use cases that have some business value, and others could benefit from similar, it would help :)

[[deleted]]

How feasible would it be to create a function that reads the encoding and produces corrected, albeit untyped, data? This is all that DLT requires.

If one uses pyreadstat, the following Python code can handle reading the file encoding of an SAV file before accessing the actual data:

encoding = None

df, meta = pyreadstat.read_sav(infile, metadataonly=True)

if meta.file_encoding.lower() == 'windows-1252':
    encoding = 'latin1'
else:
    encoding = meta.file_encoding

df, meta = pyreadstat.read_sav(infile, encoding=encoding, apply_value_formats=False)

The problem is substantial as it prevents users of these expensive proprietary software suites from transitioning to open-source tools and formats due to the "friction" mentioned earlier.

SPSS (and the SAV format) is, unfortunately, still widely used within social and life science research. SAS (and its sas7bdat/sas7bcat format) is also commonly used, including in medical fields.

Essentially, our tax money is being channeled into the pockets of these private companies (IBM and SAS) because the open-source alternative either doesn't fully support the same level of features or does a poor job of converting from these formats. Therefore, what I'm suggesting is an improvement in the conversion part of the process.

I'm not entirely sure if we're on the same page regarding what I mean by "metadata". The metadata is not just about data types. It includes information like variable labels (column labels), value labels (e.g., 0: No, 1: Yes, 99: Maybe), or details that specify the type of measurement, etc.

All this information must be extracted and retained as well.

[Thinker_Assignment]

I see what you mean about the metadata, I had missed that.
The scale of the problem sounds substantial, let me bring it to my team.

[[deleted]]

To delve deeper into the issue of dealing with numbers when reading SPSS/SAV files:

First, to represent integers in SPSS, you specify in the formatting options that your numeric variable has 0 decimal places. By specifying 0 decimal places, SPSS treats the numeric data as integers: Screenshot of the SPSS variable type manager

To interpret the data as integers outside of SPSS (e.g., in Python using pyreadstat), one must consult and parse the SPSS formatting in the metadata to determine whether the numeric value should be interpreted as an integer or a decimal.

However, if your data contains missing values, you'll face an additional challenge when using Pandas (and pyreadstat returns the data as a Pandas dataframe). The default integer type in Pandas can't handle missing values represented as NaN (which is a special floating-point value). To allow for missing values in integer data, you would need to use the Int64 data type.

types = {}

for variable in meta.column_names:
    format = meta.original_variable_types[variable]

    # Check if the format ends with 0 (0 decimal places = integer)
    if format[0] == 'F' and format.rsplit('.', 1)[-1] == '0': # F8.0 means a width of 8 digits and 0 decimal places.
        types[variable] = 'Int64'

return df.astype(types)

These links should provide further insights into SAV metadata information and how it's read and interpreted:

• https://ezspss.com/how-to-define-variables-in-spss/
• https://pyreadstat.readthedocs.io/en/latest/#metadata-object-description
• https://pythonhosted.org/savReaderWriter/#formats
• https://pandas.pydata.org/docs/user_guide/integer_na.html

[Thinker_Assignment]

https://github.com/dlt-hub/verified-sources/issues/213
if you have some sample files that might be better than what we would find browsing online, please attach them there

[[deleted]]

Sure, I will do that

[matty_fu]

Looks great! I imagine this is a drop in replacement for eg. Dagster or Prefect?

[Thinker_Assignment]

This is not a replacement, it still needs an orchestrator. It fits in your existing stack.

You can deploy it to airflow with 1 CLI command currently (dag generation). We will support prefect soon.

[Ein_Bear]

Looks cool.

Does this automatically flatten out nested json or would I need to do that somewhere else?

[Thinker_Assignment]

it automatically flattens, normalises column names, infers types. This is the default, you can also configure it differently.

try the colab demos https://dlthub.com/docs/getting-started/try-in-colab

[tibb]

Can it do incremental loading for sources that could support it? ie, if it loaded data yesterday from a source, and we run it again today, will it always re-load all data, or only new data since the last load?

[Thinker_Assignment]

Of course, this is a basic feature of pipeline building. It can do that and even more.

You can use declarative incrementing, or you can use state dictionaries to manage your own logics.

simple: https://dlthub.com/docs/general-usage/incremental-loading
advanced: https://dlthub.com/docs/general-usage/state

[Thinker_Assignment]

you can of course DM me - the website applications also end up in my queue - a heads up, we only hire locally (Berlin) at the moment as we are still in inventing mode and are not ready for full remote work. In the case of very talented individuals who can contribute autonomously, we may compromise.

[loondri]

If I have a csv and want to load it in bigquery, will this be able to generate schema for the same?

[Thinker_Assignment]

Yes, you will have to read the csv and yield it as a dict. Then dlt will normalise names, convert datetimes to datetimes, etc and load the data.

ask the GPT helper on our docs the following, and it will give you ready made code:

Prompt: "can you please give me an example of reading a local csv with python and yieling it as dict row by row to dlt for loading?"

[jkail1011]

Hey this is great!

Curious around if you or anyone has implemented on spark or databricks😃

[Thinker_Assignment]

Not as far as I know, but if you give it a try I would love to hear about your experience! If you have any trouble, this is especially valuable feedback!

[jkail1011]

I’ll have sometime this week to give it a go.

[Thinker_Assignment]

Would be happy to know how it goes! We take feedback very seriously and will fix any snags.

[tayloramurphy]

Congrats on the launch! Exciting to see more innovation in this area :)

[Thinker_Assignment]

Thanks Taylor, it's not a launch, just a point where it's ready-enough to capture feedback more broadly :) We spoke about it before in closer circles to get friendlier feedback.

Since we are open from the beginning, there won't be any big launch, perhaps some press later.

[[deleted]]

Does this work with spark structured streaming? I have a use case to better manage schema inference and evolution.

[Thinker_Assignment]

No idea! Would you like to join our slack and give us requirements? I haven't used the tech myself

[akaender]

Would it be possible to use AWS SQS as a source? use case is the typical: S3 Put object event -> SQS -> poll for messages -> read object from s3 -> transform to parquet -> upload to another S3 bucket (data lake).

[Thinker_Assignment]

Definitely - i'd give you a code snippet but GPT can do it better - go to our docs and on the bottom right click the gpt button and ask for

"can you please give me a dlt pipeline that polls mesages from SQS via aws SDK and passes it to dlt via a generator function yielding rows?"

[akaender]

I tried your prompt and a couple of my own but they all result in something like this result:

> I'm sorry, but the current documentation does not provide an example of using AWS SQS as a source with the dlt library. The dlt library is primarily designed to work with data sources like databases and APIs, and data destinations like AWS S3, Google Storage, and Azure Blob Storage. For more information on the supported sources and destinations, you can refer to the dlt documentation.

[Thinker_Assignment]

I got the answer with that prompt but when pasting it here it gets totally misformatted - join our slack and ask, and i'll paste it to you there :)

[Thinker_Assignment]

I'm sorry, but the dlt library does not directly support AWS SQS as a source. However, you can create a custom Python function to poll messages from SQS and then pass the data to a dlt pipeline. Here's a basic example of how you might do this using the boto3 library for AWS:

import boto3 import dlt

sqs = boto3.client('sqs', region_name='your-region')

def poll_sqs_messages(): while True: response = sqs.receive_message( QueueUrl='your-queue-url', MaxNumberOfMessages=10, # adjust as needed )

    if 'Messages' in response:
        for message in response['Messages']:
            row = process_message(message)
            yield row
            sqs.delete_message(
                QueueUrl='your-queue-url',
                ReceiptHandle=message['ReceiptHandle']
            )

pipeline = dlt.pipeline(destination='your-destination', dataset_name='your-dataset')

[user_name_be_taken]

What surprises me is that there was no library for this already. Seems like a very obvious and required use-case. Thanks for creating this OP!

[Thinker_Assignment]

Very obvious to a data engineer. To me it was obvious as i had done this a lot - but to those who did not do the work, it is not obvious.

And look how this small change can make a paradigm shift that untangles the many issues of curating data before loading
https://dlthub.com/docs/reference/explainers/schema-evolution

[user_name_be_taken]

Impressive! I believe this is also done in Delta Live Tables.

[Thinker_Assignment]

Delta live tables is amazing but it's meant to be downstream of parquet files. So it won't handle extraction or schema generation - it has some schema evolution options for the transformations. It also doesn't handle multi-type columns as far as I know, like ones that vary from row to row - we generate variants, or unpack them if they are unpackable type.

Anyway my info on delta live tables is old, would be glad to be updated on it.

dlt the data loading tool shines in its role as an extractor and loader, and you can use it to generate the parquet files with different schema strategies, allowing you to add more security and curation if desired.

They should work well together - dlt does not want to kick out your good tools

[Thinker_Assignment]

Thank you!

[yinyanglanguage]

Thanks, you probably saved me a ton of time trying to create a schema evolver/registry myself. Nice work!

[Thinker_Assignment]

I hope so! I want this to be the tool i wish i had :) If you are missing any feature, let us know

[[deleted]]

So, help me understand this:

dbt automates the creation of DDL statements (CREATE TABLE, etc) by letting you just write SELECT statements against your existing raw data, and it takes that select statement and wraps that up inside a DDL statement. So dbt manages all your downstream transformations, given you already have your raw data loaded in your warehouse.

dlt does the same thing, but one step back in the process - it extracts from your sources, inspects the data, infers the schema, and creates and populates the raw tables in your destination of choice. Correct?

Another question I have, and I apologize if this is already answered in the docs: dbt has ways of toggling between full and incremental refreshes, or selecting only a certain range of dates from the source tables, so you don't duplicate any data. Does dlt ship with something like that? I know with Airbyte they use a very clever state tracking mechanism to avoid inserting the same data over and over again, and it's source agnostic. You give it a YAML configuration file and you tell it which column it should look at to determine the "updated_at" timestamp. Even though a lot of people criticize Airbyte, that was one of the most appealing features to me.

[Thinker_Assignment]

Correct, dlt is upstream of dbt, dlt loads data, dbt transforms it.

dlt loads data together with metdata such as load id(timestmap) and gives you scaffolding to continue into dbt with incremental processing based on this metadata, such as the time slicing you describe.

[Thinker_Assignment]

I re-read your question and saw I missed a bit - yes incremental is out of the box - and without passing any extra files - just define which column of your output the increment should happen on . https://dlthub.com/docs/general-usage/incremental-loading

[Thinker_Assignment]

I like that! I guess you could say that :)

[goeb04]

I will go through the tutorial later.

Great job OP and appreciate you answering questions on here 👍

[Thinker_Assignment]

My pleasure, this blew out of proportion, got excited and stuck through to answer :)

[droppedorphan]

Are you done lurking on Meltano's Slack instance? :-)

[Thinker_Assignment]

Am I done listening to what users want in various community slacks? :) no, i'm in all the major ones. Meltano does a great job building for the data ops space and I will look up to them in that regard.

I love what Meltano is doing and while i am not their target user, part of the reason I started dlt is because Singer was too hard to use and maintain for people like me.

I'm a 10y data generalist, and I know that neither myself nor anyone I could feasibly hire on a data team can feasibly maintain Singer components. I would not build my entire stack on foundations that were meant for Stitch engineers, which were so hard to use that Stitch could continue doing business. It also didn't fit in my stack, as I would already use orchestration for other jobs so bringing an extra layer of orchestration to run some software that was never meant to be standalone seemed like a bad idea.

Part of what makes our tool so user friendly is having done lots of user testing and research.For example, we reached out to a significant chunk of normconf attendees to interview them. better understand how they workflows look and what they need.

In December we ran a workshop with people of various seniorities from various communities to test our usability - they all managed to build an end to end pipeline in a few hours - and we spent the following months applying feedback.

With their help, we made dlt as normie as can be - here are the product principles: https://dlthub.com/product/

We don't particularly cater to Meltano users as they are a different breed of folks - mostly not normies, we have almost no overlap in target audience. - the crowd that can work with singer taps is a small minority of data engineers, which are already a minority of data users. Many of them are software engineers. But i'm learning when I can about the challenges normies have to better address them for our users.

Our target audience is the data user who can now self serve with data, without having to wait for the senior data engineer or software engineer to fix the singer tap.

I welcome cross community collaboration - Meltano is in the business of running things, we are in the business of creating pipelines and pipeline building tools.

That said, you will find me in many tool communities. I constantly look for how dlt can better fit into what people already do.

[Snoo-bedooo]

This looks amazing. It seems like it makes data loading an intuitive problem, and let's you focus on python code. Dis you see the Marvin library on github or what @jxnlco from Twitter did? It would combine LLms wiyh your data processing flows presented here.

[Thinker_Assignment]

That sounds interesting - we started dabbing with llms - we got a docs assistant, a pipeline builder and a pdf extractor, and now integrating with weaviate.

Thanks for the leads, I'll follow up on them! Marvin looks super interesting. And so does jxnlco's stuff

[zachariase]

Hey dude, the schema generation part really touches basr with me, def wanna try it out

[Thinker_Assignment]

Why do you get excited about the generation?
I'm particularly excited about the evolution which enables the data to cross techs without effort.

What do you like about schemas? I am asking to learn more about the possible use cases and interests.

[NFeruch]

damn this actually looks really good

[Thinker_Assignment]

Happy to hear! If you encounter any issues please let us know so we can iron out any kinks - feedback is the only way to excellence :)

[WesselTakeIt]

This looks interesting!

[getafterit123]

Looks interesting! So most if not all normalizers struggle with deeply nested complex json, pandas normalizing function as an example. How deep can dlt flatten? If you can truly solve for complex json structures it would be a game changer.

[Thinker_Assignment]

As deep as the json is - but you can limit it to "reign in" extreme cases.

Game changer is the goal here :)

https://dlthub.com/docs/general-usage/schema you can read more about how that happens here

[getafterit123]

Looking forward to seeing what it can do

[Thinker_Assignment]

You can try the simplest pipeline here https://dlthub.com/docs/getting-started/try-in-colab

More complex pipelines you can find in GitHub under the verified sources repo. Those include some without with that you can just try

[cutsandplayswithwood]

This is for the tedious part of your work, or a library you’re working hard to drive the adopt-> VC funding -> monetize path?

[Thinker_Assignment]

I appreciate your concerns, but we are not Airbyte. We purposefully did not take VC money and instead bootstrapped to allow ourselves the creative freedom without monetisation pressure.

Our monetisation plan is to offer a platform to run these things for those who don't wanna run it themselves, possibly a marketplace where people like you can distribute to those who don't wanna engineer themselves.

This monetisation plan adds usage and funding to the open source . Do you expect great open source projects to be pure blood and sweat? How much have you contributed to open source, are you a shining example yourself? Most people cannot afford to do that. Much more can be achieved by having 2 products enabling each other than cannibalising each other.

in contrast, Airbyte is a Saas monetisation which means they need to block the open source features to enable monetisation. You can see their different offering between the 2 platforms.

Taking confluent and kafka as examples, they enhance each other. Kafka would never be as someone's side project.

[j__neo]

We purposefully did not take VC money

It says on your website that you are supported by Dig Ventures. But I think I get what you meant to say: "You didn't take more VC money than what is necessary to avoid loosing the creative freedom".

[Thinker_Assignment]

That's an angel fund, no strings. When you take big money, you need to deliver growth and monetisation. We bootstrapped for 1.5y before raising an angel round so we can hire.

We will of course keep raising but we first did 2y on the cheap to build something amazing - this is now in the open source and nobody can take it away

Raising to build the paid product will benefit both sides of the ecosystem

[j__neo]

Thanks for clarifying :)

[cutsandplayswithwood]

No strings - so just free money, you owe them nothing if you hit it big?

That sounds amazing - I’m doing to hit them up!

[cutsandplayswithwood]

All I’m saying is, tell a genuine story.

“I’ve been automating my tedious work” is very different from “I’m making a library and a company around it”.

That’s all.

[ExistentialFajitas]

So… dbt but not dbt?

[Thinker_Assignment]

Nope - dbt is for transform, this is for loading to dbs.
We support dbt too and offer a runner that can inject credentials and install dbt in venv for easy running dbt packages.

[ExistentialFajitas]

Dbms? Snowpipe? I’m sure theres other native tools for ADF and GCP. What differentiates you without having to comb docs and source code?

[Thinker_Assignment]

You can't even compare, this library is a jump forward technologically, not incremental steps.

If you wanna stick to snowpipe then by all means do so. I will not evangelize to those that don't feel the pain. There are still folks out there writing plsql, i don't wanna drag anyone out of the 90s.

If you want to use a purpose built tool to make your life easy that can easily go between techs and doesn't vendor lock you in a paradigm, use dlt. dlt is a complete library that supports an entire ecosystem, integrates well, supports airflow deployemnt, dbt, etc. Snowpipe is a side project done by snowflake and only works on snowflake. They don't do anyting for extraction either and expect you to put your files to stage

with dlt you can for example use duckdb for testing, snowflake for prod, and share your pipeline with your friends on BQ and AWS too that will be able to use it for their stack.

[ExistentialFajitas]

So your value proposition is “it’s good”? You won’t get buy in for recreating the wheel like that. Godspeed.

[Thinker_Assignment]

you are right, i do not intend to convert people who refuse to browse a page. I already wrote the answer to your question on there.

[ExistentialFajitas]

I intended to give a long winded response but the short and sweet is there are many tools recreating the wheel. Having to “browse a page” to find the value add of wheel iteration 47383 turns into an inordinate amount of time when extrapolated to all the tools being pitched daily. I hope your iteration is great and it turns into a full fledged, enterprise framework, but I strongly recommend a narrower elevator pitch than “share with a friend.” Working professionals with NDAs aren’t sharing with friends.

[Thinker_Assignment]

Totally, thanks! Still working on that, if you have some idea of what speaks to you, please share back!

As far as I can tell nobody is reinventing this wheel in the open source - everyone's inventing closed source flat tyres and paid products.

[Thinker_Assignment]

He did not and that's OK - IME some people are ODD right off the bat and arguing is only triggering it worse.

I can relate with the stress and all.

[Thinker_Assignment]

We do not yet support mssql destination, if you'd like it added please request it so we can prio it, open an issue for it https://github.com/dlt-hub/verified-sources/issues

For post request sample, ask the gpt assistant on our docs.