Why optimization reduces warehouse costs in dbt - See It in Action

Why optimization reduces warehouse costs

📖 Scenario: You work as a data analyst for a company that uses a cloud data warehouse. The company wants to reduce costs by optimizing how much data they process in their queries.Cloud warehouses charge based on the amount of data scanned. By filtering data early and selecting only needed columns, you can reduce the data scanned and save money.

🎯 Goal: Build a simple dbt model that selects only necessary columns and filters data to reduce the amount of data scanned, demonstrating how optimization reduces warehouse costs.

📋 What You'll Learn

Create a source table with sales data

Add a configuration variable for minimum sales amount

Write a dbt model that filters sales above the minimum amount and selects only needed columns

Display the final filtered data

💡 Why This Matters

🌍 Real World

Companies use cloud data warehouses that charge based on data scanned. Optimizing queries by filtering and selecting columns reduces costs.

💼 Career

Data analysts and engineers must write efficient queries to control cloud costs and improve performance.

1

Create the sales data source

Create a dictionary called sales_data with these exact entries: {'order_id': [101, 102, 103, 104], 'customer': ['Alice', 'Bob', 'Charlie', 'Diana'], 'amount': [250, 80, 120, 300]}

dbt

# Create the sales_data dictionary with order_id, customer, and amount lists
# Your code here

Hint

Use a dictionary with keys 'order_id', 'customer', and 'amount'. Each key should map to a list of values.

2

Add a minimum sales amount filter

Create a variable called min_amount and set it to 100 to filter sales above this amount.

dbt

sales_data = {'order_id': [101, 102, 103, 104], 'customer': ['Alice', 'Bob', 'Charlie', 'Diana'], 'amount': [250, 80, 120, 300]}
# Create min_amount variable and set it to 100
# Your code here

Hint

Just create a variable named min_amount and assign it the value 100.

3

Filter sales and select columns in dbt model

Write a dbt model SQL string called optimized_query that selects order_id and amount from sales_data where amount > min_amount.

dbt

sales_data = {'order_id': [101, 102, 103, 104], 'customer': ['Alice', 'Bob', 'Charlie', 'Diana'], 'amount': [250, 80, 120, 300]}
min_amount = 100
# Write optimized_query string with SQL to select order_id and amount where amount > min_amount
# Your code here

Hint

Use a multi-line string with f"""...""" and write a SQL SELECT statement filtering by amount > min_amount.

4

Display the optimized query

Write print(optimized_query) to display the final optimized SQL query.

dbt

sales_data = {'order_id': [101, 102, 103, 104], 'customer': ['Alice', 'Bob', 'Charlie', 'Diana'], 'amount': [250, 80, 120, 300]}
min_amount = 100
optimized_query = f"""
SELECT order_id, amount
FROM sales_data
WHERE amount > {min_amount}
"""
# Print the optimized_query
# Your code here

Hint

Use print(optimized_query) to show the SQL query.

Practice

1. Why does optimizing SQL models in dbt help reduce warehouse costs?

easy

A. Because it reduces the amount of data scanned and processed

B. Because it increases the number of queries run

C. Because it stores more data for future use

D. Because it makes the warehouse hardware faster

Solution

1. Step 1: Understand data scanning impact on cost

   Warehouse costs often depend on how much data is scanned during queries.

2. Step 2: Connect optimization to data scanned

   Optimizing SQL models reduces unnecessary data scanned, lowering query time and cost.

3. Final Answer:

   Because it reduces the amount of data scanned and processed -> Option A

4. Quick Check:

   Less data scanned = lower cost [OK]

Hint: Less data scanned means lower cost [OK]

Common Mistakes:

• Thinking more queries reduce cost
• Assuming storing more data saves money
• Believing hardware speed affects cost directly

2. Which of the following is the correct way to write a dbt model that selects only necessary columns to optimize cost?

easy

A. SELECT * FROM raw_data

B. SELECT id, name FROM raw_data

C. SELECT COUNT(*) FROM raw_data

D. SELECT id, name, age, address, phone FROM raw_data

Solution

1. Step 1: Identify selecting necessary columns

   Choosing only needed columns reduces data scanned and processed.

2. Step 2: Compare options for column selection

   SELECT id, name FROM raw_data selects only 'id' and 'name', fewer columns than options selecting all columns or more columns.

3. Final Answer:

   SELECT id, name FROM raw_data -> Option B

4. Quick Check:

   Select only needed columns [OK]

Hint: Select only needed columns, not * [OK]

Common Mistakes:

• Using SELECT * which scans all columns
• Selecting unnecessary columns increases cost
• Confusing COUNT(*) with column selection

3. Given this dbt model SQL code:

SELECT user_id, SUM(amount) AS total
FROM sales
WHERE sale_date > '2024-01-01'
GROUP BY user_id

What is the main reason this query helps reduce warehouse costs compared to selecting all sales data without filtering?

medium

A. It runs the query multiple times to optimize

B. It increases the number of columns scanned

C. It stores the data permanently in the warehouse

D. It reduces the number of rows processed by filtering by date

Solution

1. Step 1: Analyze the WHERE clause effect

   The WHERE clause filters sales after 2024-01-01, reducing rows processed.

2. Step 2: Understand impact on warehouse cost

   Fewer rows scanned means less compute and storage cost during query execution.

3. Final Answer:

   It reduces the number of rows processed by filtering by date -> Option D

4. Quick Check:

   Filtering rows reduces cost [OK]

Hint: Filter rows early to reduce data scanned [OK]

Common Mistakes:

• Thinking more columns scanned reduces cost
• Confusing storage with query cost
• Assuming multiple runs optimize cost

4. This dbt model code is intended to optimize cost by filtering data, but it has an error:

SELECT user_id, amount
FROM sales
WHERE sale_date = '2024-01-01'

What is the issue that could cause higher warehouse costs?

medium

A. Using '=' instead of '>=' filters only one day, missing other data

B. Missing GROUP BY clause causes syntax error

C. Selecting user_id and amount scans all columns

D. No error, this is optimized

Solution

1. Step 1: Check the WHERE clause condition

   Using '=' filters only sales on exactly 2024-01-01, missing other recent data.

2. Step 2: Understand cost impact of missing filter range

   Missing broader date range means more queries or processing later, increasing cost.

3. Final Answer:

   Using '=' instead of '>=' filters only one day, missing other data -> Option A

4. Quick Check:

   Use correct date filters to optimize cost [OK]

Hint: Use range filters (>=) not exact (=) for dates [OK]

Common Mistakes:

• Thinking missing GROUP BY causes error here
• Assuming selecting two columns scans all columns
• Believing no error exists

5. You want to optimize a dbt model to reduce warehouse costs by minimizing data scanned. Which approach is best?

hard

A. Run the model multiple times to cache results

B. Select all columns and join all tables to get complete data

C. Select only needed columns, filter rows early, and avoid unnecessary joins

D. Store all raw data in the model without filtering

Solution

1. Step 1: Identify best practices to reduce data scanned

   Selecting only needed columns and filtering rows early reduces data scanned.

2. Step 2: Avoid unnecessary joins to reduce complexity and data size

   Unneeded joins increase data scanned and processing time, raising costs.

3. Final Answer:

   Select only needed columns, filter rows early, and avoid unnecessary joins -> Option C

4. Quick Check:

   Minimize data scanned by filtering and selecting [OK]

Hint: Filter early, select less, avoid extra joins [OK]

Common Mistakes:

• Selecting all columns wastes resources
• Joining all tables increases cost
• Relying on multiple runs instead of optimization