Week 15/34: Data Transformation with dbt for Data Engineering Interviews
Understanding dbt's role in data engineering and use cases
Data transformation helps unify and integrate data, making it simpler to view and analyse. Without transformation, it's impossible to have a clear and consistent view of data across an organisation. Bill Inmon, known as the father of data warehousing, emphasised that transformation is key to integration.
James Densmore, in his book Data Pipelines Pocket Reference, describes transformation as the vital "T" in ETL and ELT processes.
By transforming raw data, we turn it into useful, actionable information. When data engineers follow best practices and collaborate effectively, they produce high-quality data that supports decision-making and analysis. Transformation forms the foundation of the entire data lifecycle.
If you would like to learn more about data transformation, check our previous post:
During interviews, data engineers are typically expected to know at least one data transformation tool. The level of expertise required depends on the company's size. In smaller companies, a data engineer might manage the whole data engineering process, including ingestion, transformation, and serving. In larger organisations, these responsibilities are usually divided among different specialists.
For example, if your focus is primarily on data transformation, you might come across roles such as Analytics Engineer. Even Data Analysts may use transformation tools as part of their workflow.
In this post, we'll discuss one of the most popular transformation tools among data professionals: dbt (data build tool). Widely used by organisations of all sizes, from startups to large enterprises.
Note: There are several alternatives to dbt worth exploring, such as Dataform and SQLMesh.
What We'll Cover:
What is dbt?
Why is dbt so popular?
Scenario-based interview questions
We assume you're familiar with dbt. However, if you're new, don’t worry! dbt Fundamentals course is an excellent starting point, and you'll earn a badge for your CV or LinkedIn. If you're already comfortable with SQL, learning dbt will be much easier.
If you want to explore more about dbt and its various features and capabilities, we recommend checking out our dbt-series posts:
Post 3 (In Progress): Exploring dbt analyses, snapshots, and incremental models for managing historical and large-scale data transformations.
Post 4: Understanding the dbt semantic layer and MetricFlow.
In most cases, certification for this tool is not required. However, some companies may prefer or even mandate it. If you're interested in getting certified, you have two options:
Note: If you're planning to get certified, make sure to learn dbt and go through its documentation. The exam can be challenging without proper study and practice.
For the previous posts of this interview series, check here: [Data Engineering Interview Preparation Series]1
What is dbt?
Before dbt, data professionals usually created data pipelines in Python using parameterised SQL statements. This approach had several problems. Pipelines lacked standardisation, and data quality checks had to be built separately. There was no built-in data lineage to easily track transformations from raw data to reports, making debugging and auditing difficult. Documentation was also often missing or inconsistent, causing maintenance issues.
To address these issues, dbt (data build tool) was developed as an open-source transformation tool that focuses exclusively on the Transformation step of data pipelines in a modular way. It has gained popularity in Analytics Engineering and Data Engineering for its ability to bridge the gap between raw data storage and analytical insights.
With dbt, data teams can:
Write modular SQL transformations that are easy to maintain.
Run built-in tests to ensure data quality.
Automatically generate clear documentation.
Use version control for better team collaboration.
Schedule workflows effectively.
dbt comes in two versions: dbt Core and dbt Cloud.
dbt Core is the open-source command-line version that allows users to define, test, and document transformations using SQL and Jinja. It provides flexibility but requires manual setup for scheduling, orchestration, and infrastructure management.
dbt Cloud is a managed service that offers a web-based UI, automated scheduling, logging, and integrations with various data platforms. It simplifies collaboration and deployment, making it easier for teams to manage transformations without worrying about infrastructure.
While dbt Core is ideal for those who prefer full control over their environment, dbt Cloud is beneficial for teams looking for a streamlined and scalable solution with built-in automation and governance features.
