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

Need a 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

Need a 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

Need a 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

Need a hint?

Use print(optimized_query) to show the SQL query.