dbt Platform

I am currently working with dbt  and Snowflake  together. We use dbt  for data transformation purposes. We obtain the data and store the raw data directly into Snowflake , then perform all transformations using dbt to prepare the data for reporting purposes.

We use dbt's modular SQL models. In dbt, we do not use full refresh or full data refresh. We have incremental strategies in place that only compute or transform incremental data, which operates in a CDC architecture. This approach is very fast, and we use it on a daily basis. We have scheduled all our dbt models using Airflow  to run according to the scheduled time.

We use dbt's testing framework and the inbuilt functionality of dbt testing. For example, we use dbt's built-in tests to identify not null values and determine how many not null columns and values exist in each column. Beyond the built-in functionality, we have written custom SQL scripts to create external test cases on our models.

We ensure that incorrect or incomplete data does not go into the reporting layer because it can impact the business. We always perform dbt tests on our landing or raw data to ensure the correctness and completeness of the data before loading it into the final reporting layer. These reports are used by higher management, so we ensure that incorrect data is not published into the reporting layer for the Power BI reports.

We use dbt's documentation site generator. In dbt, we have YML file functionality, which can be used for creating documentation for each model. Whenever we make modifications to a model, we always update the YML file so we can track the history of how frequently we change the model. When new team members join, they can refer to this documentation to understand the data lineage and the data transformation strategy of the project.

dbt is very fast compared to the traditional tools. Previously, I worked on SSIS , which is provided by Microsoft, and data transformation took a considerable amount of time when dealing with large amounts of data. Since dbt works on the ELT architecture rather than the ETL architecture, it is much faster than traditional data transformation tools.

Previously, we were using SSIS  packages, which were very slow. Recently we migrated all our SSIS packages to dbt models. After the migration, we moved the data from SQL Server  to Snowflake. Previously, our data pipeline took around two days to load complete data when performing a full refresh. Since we migrated from SSIS to dbt model architecture, it takes around four hours only to complete a full refresh. The client is now happy because our downtime was drastically reduced when we perform a complete refresh of the data.

I am not very familiar with dbt's version control system.

I cannot identify any improvements in dbt because I am still exploring more functionality. I have been working with dbt for only three years, so I am exploring more functionalities and cannot see any limitations or improvement areas at this time.

In the past, I used the seed functionality, which is used to load raw files, individual files, or static files into the database. Going forward, if dbt can improve or implement more features on the seed side, that would be beneficial, especially when we have large files available that take time to load the data into Snowflake database.

I have been working with dbt for the last three years.

I have not experienced any crashes, performance issues, or anything regarding stability and reliability.

I find dbt very scalable.

The dbt support team is very responsive. Whenever we have any issues on the dbt side, we always reach out to them. We did not face any challenges in the initial setup. I would rate the technical support a nine out of ten.

Positive

Previously, we were using SSIS packages, which were very slow. Recently we migrated all our SSIS packages to dbt models. After the migration, we moved the data from SQL Server  to Snowflake. Previously, our data pipeline took around two days to load complete data when performing a full refresh. Since we migrated from SSIS to dbt model architecture, it takes around four hours only to complete a full refresh. The client is now happy because our downtime was drastically reduced when we perform a complete refresh of the data.

The main reason we decided to switch to dbt is performance. As mentioned earlier, every quarter we perform a full refresh, and that refresh took considerable time on SQL Server. Since we had to migrate because our data is very large and growing daily, we adopted dbt because Snowflake is very fast. In Snowflake, the storage layer and the computation layer are separate, which is not present in the SQL Server traditional database. That is why we moved from SQL Server to Snowflake and from SSIS to dbt.

We evaluated Databricks  as well, but ultimately the client wanted to adopt Snowflake and dbt technologies only.

We took help from Snowflake directly, the Snowflake company, for the Snowflake side. The dbt side is maintained or set up by our infrastructure team.

Since we migrated from SSIS to dbt model architecture, it takes around four hours only to complete a full refresh. The client is now happy because our downtime was drastically reduced when we perform a complete refresh of the data.

The pricing, setup cost, and licensing cost are managed by our infrastructure teams. As data engineers, we are not familiar with these details.

I need to check with my infrastructure team on whether we purchased dbt through the AWS Marketplace  or directly from the local vendor.

Since dbt has a license cost, if a company is small and does not have much budget, they can explore other tools because there are other tools that provide the same functionality at a lower cost. If an organization is small, they can explore other products as well.

I am currently working with Power BI, Tableau, Python, Databricks , Snowflake, and PySpark in the current project. I would rate my overall experience with dbt a nine out of ten.