DuckDB Macros Guide: Reuse SQL Logic Like Functions, 10x Productivity
Advanced Techniques | For: Data analysts and engineers who write repetitive SQL
The Pain: Are You Still Copy-Pasting 20-Line SQL Queries?
Let me ask you something: do you often take a 20-line SQL block from an old script, change the table name, and paste it into a new query? Then one day the business team says “we need to adjust the calculation logic,” and you’re left modifying 15 copy-pasted instances, questioning your life choices.
You’re not alone. In the SQL world, we’ve been reinventing the wheel forever. DuckDB’s Macros feature is here to end this “copy-paste-modify” nightmare. It lets you encapsulate complex SQL logic into a function, then call it just like any built-in function.
Today, we skip the theory and dive straight into 6 practical scenarios showing how macros transform you from a “SQL porter” to a “SQL architect.”
Scenario 1: Unified Metric Definitions — Goodbye “Different GMV Numbers”
The Pain: The operations team and finance team define “GMV” differently (tax included? refunds excluded?), causing daily reconciliation disputes. You need to enforce a unified calculation logic across all reports.
The Code: Define a macro to calculate “effective GMV” that automatically filters out test orders and refunds.
import duckdb
# Create sample data
conn = duckdb.connect()
conn.execute("""
CREATE TABLE orders AS
SELECT * FROM (VALUES
(1, 'A', 100.0, 0, '2023-10-01'),
(2, 'B', 200.0, 1, '2023-10-01'), -- test order
(3, 'A', 300.0, 0, '2023-10-02'),
(4, 'C', 400.0, 1, '2023-10-02'), -- refund
(5, 'B', 500.0, 0, '2023-10-03')
) AS t(order_id, seller, amount, is_refund, order_date)
""")
# Define macro: calculate effective GMV
conn.execute("""
CREATE OR REPLACE MACRO effective_gmv(amount, is_refund) AS
CASE
WHEN is_refund = 0 THEN amount
ELSE 0
END;
""")
# Use macro for daily aggregation
result = conn.execute("""
SELECT
order_date,
SUM(effective_gmv(amount, is_refund)) AS daily_gmv
FROM orders
GROUP BY order_date
ORDER BY order_date
""").fetchdf()
print(result)
Output:
order_date daily_gmv
0 2023-10-01 100.0
1 2023-10-02 300.0
2 2023-10-03 500.0
💡 Key Insight: The macro locks in business logic at definition time. Anyone writing queries just calls effective_gmv(), and the calculation is always correct. When the definition changes, update the macro once, and all callers auto-update.
Scenario 2: Parameterized Table Names — Dynamic Partitioned Table Handling
The Pain: Your data is partitioned by day with table names like sales_20231001. Running daily reports requires manual SQL string concatenation, which is error-prone and prevents query optimization.
The Code: DuckDB macros support table names as parameters using the TABLE keyword.
import duckdb
conn = duckdb.connect()
# Create two simulated daily partition tables
for date in ['20231001', '20231002']:
conn.execute(f"""
CREATE TABLE sales_{date} AS
SELECT * FROM (VALUES
('Product_A', 100 + {date[-2:]}),
('Product_B', 200 + {date[-2:]})
) AS t(product, revenue)
""")
# Define macro: accept table name as parameter
conn.execute("""
CREATE OR REPLACE MACRO get_daily_sales(tbl TABLE) AS TABLE
SELECT product, SUM(revenue) AS total_revenue
FROM tbl
GROUP BY product;
""")
# Dynamically query different date tables
for date in ['20231001', '20231002']:
result = conn.execute(f"""
SELECT * FROM get_daily_sales(sales_{date})
ORDER BY product
""").fetchdf()
print(f"--- {date} ---")
print(result)
Output:
--- 20231001 ---
product total_revenue
0 Product_A 101
1 Product_B 201
--- 20231002 ---
product total_revenue
0 Product_A 102
1 Product_B 202
💡 Key Insight: Macros with TABLE parameters let you pass entire tables as input and return processed result sets. This turns macros into true “functional” query blocks.
Scenario 3: Nested Macros — Building Complex Data Cleaning Pipelines
The Pain: Data cleaning involves many steps: trimming whitespace, standardizing date formats, normalizing status codes. Writing nested CASE WHEN and REGEXP_REPLACE every time is ugly and hard to maintain.
The Code: Macros can call other macros, enabling modular cleaning.
import duckdb
conn = duckdb.connect()
conn.execute("""
CREATE TABLE raw_data AS
SELECT * FROM (VALUES
(' Active ', '2023/10/01', 'NY'),
('inactive', '10-02-2023', 'ca'),
('PENDING', '2023.10.03', 'TX')
) AS t(status, date_str, state)
""")
# Macro 1: Standardize status codes
conn.execute("""
CREATE OR REPLACE MACRO clean_status(s) AS
CASE
WHEN LOWER(TRIM(s)) IN ('active', 'act') THEN 'ACTIVE'
WHEN LOWER(TRIM(s)) IN ('inactive', 'inact') THEN 'INACTIVE'
ELSE UPPER(TRIM(s))
END;
""")
# Macro 2: Standardize dates (compatible with multiple separators)
conn.execute("""
CREATE OR REPLACE MACRO clean_date(d) AS
strptime(REGEXP_REPLACE(d, '[./]', '-'), '%Y-%m-%d');
""")
# Execute cleaning
result = conn.execute("""
SELECT
clean_status(status) AS clean_status,
clean_date(date_str) AS clean_date,
UPPER(state) AS state
FROM raw_data
""").fetchdf()
print(result)
Output:
clean_status clean_date state
0 ACTIVE 2023-10-01 NY
1 INACTIVE 2023-10-02 CA
2 PENDING 2023-10-03 TX
💡 Key Insight: Nested macros break complex logic into independently testable units. Test clean_status() alone, confirm it works, then compose the cleaning pipeline.
Scenario 4: Macros + JSON — One-Line Complex Parsing
The Pain: Your data has a JSON array field containing multiple user events. Filtering, transforming, and aggregating arrays requires verbose UNNEST + FILTER combinations every time.
The Code: Encapsulate JSON array processing logic in a macro.
import duckdb
conn = duckdb.connect()
conn.execute("""
CREATE TABLE user_events AS
SELECT * FROM (VALUES
(1, '[{"type":"view","value":5},{"type":"click","value":3}]'),
(2, '[{"type":"click","value":2},{"type":"purchase","value":100}]'),
(3, '[{"type":"view","value":1}]')
) AS t(user_id, events_json)
""")
# Define macro: sum values for a specific event type
conn.execute("""
CREATE OR REPLACE MACRO sum_event_value(events_json, event_type) AS
(
SELECT COALESCE(SUM(e.value), 0)
FROM json_each(events_json) AS je
CROSS JOIN LATERAL (
SELECT json_extract_string(je.value, '$.type') AS type,
json_extract_int(je.value, '$.value') AS value
) AS e
WHERE e.type = event_type
);
""")
# Query each user's total click value
result = conn.execute("""
SELECT
user_id,
sum_event_value(events_json, 'click') AS total_click_value,
sum_event_value(events_json, 'view') AS total_view_value
FROM user_events
ORDER BY user_id
""").fetchdf()
print(result)
Output:
user_id total_click_value total_view_value
0 1 3 5
1 2 2 0
2 3 0 1
💡 Key Insight: Macro parameters can be any expression, including strings. This black-boxes complex JSON parsing, making your main query extremely clean and readable.
Scenario 5: Multi-Column Macros — One-Shot Derived Feature Generation
The Pain: In feature engineering, you often need to compute multiple derived fields from originals (like RFM analysis: Recency, Frequency, Monetary). Writing multiple CASE WHEN blocks repeating the same column references is tedious.
The Code: Macros can return a table structure (multiple columns) via the TABLE keyword.
import duckdb
conn = duckdb.connect()
conn.execute("""
CREATE TABLE customers AS
SELECT * FROM (VALUES
(1, 5, 300.0),
(2, 10, 1500.0),
(3, 2, 80.0)
) AS t(cust_id, order_count, total_spent)
""")
# Define macro: return customer tier and frequency label (multiple columns)
conn.execute("""
CREATE OR REPLACE MACRO customer_segment(count, spent) AS TABLE
SELECT
CASE
WHEN spent >= 1000 THEN 'VIP'
WHEN spent >= 100 THEN 'Standard'
ELSE 'New'
END AS tier,
CASE
WHEN count >= 10 THEN 'Frequent'
ELSE 'Occasional'
END AS frequency_label
WHERE 1=1;
""")
# Use macro and expand multiple columns
result = conn.execute("""
SELECT
cust_id,
cs.tier,
cs.frequency_label
FROM customers,
LATERAL customer_segment(order_count, total_spent) AS cs
ORDER BY cust_id
""").fetchdf()
print(result)
Output:
cust_id tier frequency_label
0 1 Standard Occasional
1 2 VIP Frequent
2 3 New Occasional
💡 Key Insight: TABLE-returning macros can be referenced like relational tables via JOIN or LATERAL. Feature engineering code becomes modular — adding new features only requires extending the macro.
Scenario 6: Macros as Code Generators — Batch Weekly Report SQL
The Pain: Every Monday you generate a report with 20 different dimensional metrics. Manual coding risks missing metrics or formula errors, and the format is always monotonous.
The Code: Use macros + Python to dynamically generate and execute SQL.
import duckdb
conn = duckdb.connect()
conn.execute("""
CREATE TABLE sales AS
SELECT * FROM (VALUES
('2023-10-01', 'North', 'Electronics', 1000.0),
('2023-10-01', 'South', 'Clothing', 500.0),
('2023-10-02', 'North', 'Electronics', 1500.0),
('2023-10-02', 'South', 'Clothing', 800.0)
) AS t(sale_date, region, category, amount)
""")
# Define macro: calculate percentage of total
conn.execute("""
CREATE OR REPLACE MACRO pct_of_total(part, total) AS
CASE
WHEN total > 0 THEN ROUND(100.0 * part / total, 2)
ELSE 0
END;
""")
# Dynamically generate multi-dimensional stats SQL
dimensions = ['region', 'category']
base_query = """
SELECT
'{dim}' AS dimension_type,
{dim} AS dimension_value,
SUM(amount) AS total_amount,
pct_of_total(SUM(amount), (SELECT SUM(amount) FROM sales)) AS pct_total
FROM sales
GROUP BY {dim}
"""
# Concatenate all dimension queries and execute
all_queries = " UNION ALL ".join([
base_query.format(dim=d) for d in dimensions
])
result = conn.execute(all_queries).fetchdf()
print(result)
Output:
dimension_type dimension_value total_amount pct_total
0 region North 2500.0 65.79
1 region South 1300.0 34.21
2 category Electronics 2500.0 65.79
3 category Clothing 1300.0 34.21
💡 Key Insight: Macros serve as a “public function library.” Use Python loops to dynamically generate SQL while macros ensure consistent calculation logic across all queries.
Comparison Table: Macros vs Traditional Approaches
| Scenario | Traditional Approach | Macro Approach | Advantage |
|---|---|---|---|
| Unified metrics | Repeat CASE WHEN everywhere | Define once, call globally | Consistent definitions, single-point updates |
| Dynamic table names | SQL string concatenation | TABLE parameter macro | Type-safe, query optimizer aware |
| Data cleaning | Nested CASE WHEN + REGEXP | Composable small macros | Testable, reusable |
| JSON parsing | Verbose UNNEST + LATERAL each time | Encapsulated macro | Clean main queries |
| Feature engineering | Repetitive CASE WHEN for multiple cols | TABLE macro returns multiple cols | Modular extensibility |
| Batch reports | Manually copy 20 queries | Macro + loop generation | Zero omissions, zero errors |
🔥 Pitfall Guide (5 Rules)
Macros are not performance optimizations: Macros expand into underlying SQL at parse time — they don’t speed up queries. They improve development efficiency and maintainability. For performance, use indexes or materialized views.
Watch for scope and naming conflicts: Column names referenced inside macros that conflict with outer query columns can cause unexpected results. Use
tbl.prefixes orASaliases inside macros to stay safe.Macros can’t directly reference Python variables: If you need dynamic values from Python inside macros, use
conn.executewith string formatting, but be aware of SQL injection risks. Parameterized queries are safer.Don’t over-nest macros: More than 3 levels of macro nesting makes debugging difficult. When macro logic gets too complex, split it into simpler macros or handle it in Python instead.
Macros don’t support window functions as parameters: Passing window functions like
ROW_NUMBER()directly to macros may error. Compute results in a subquery first, then pass to the macro.
🎯 Core Philosophy
Macros embody “queries as code” — elevating repeated SQL patterns to first-class citizens.
- Consistency: Define business logic once in macros, reuse globally. No more “everyone calculates differently.”
- Testability: Each macro is an independent unit you can verify separately before combining.
- Composability: Macros nest, accept table parameters, return multiple columns — building powerful SQL function libraries.
When you catch yourself copying the same SQL block for the third time, stop. Encapsulate it in a macro. Your codebase will thank you, your teammates will thank you, and future you especially will thank you.
💰 Monetization Advice
After mastering DuckDB Macros, here’s how to turn this skill into income:
Enterprise Consulting: Help companies unify data definitions and build macro libraries. Rate: ¥5,000-15,000/project. A mid-size enterprise typically has 10-30 core metrics needing standardization.
Data Product Templates: Package commonly used macros (GMV calculation, user segmentation, RFM analysis) into reusable templates. Sell on Gumroad or Afdian. Price: ¥99-299/set.
Training Courses: Create a “DuckDB Macros Advanced” course series for Udemy or NetEase Cloud Classroom. Estimated 500-2,000 buyers, revenue ¥5,000-50,000.
SaaS Tool: Build a “metric management platform” based on DuckDB macros, letting business users configure macro parameters via UI and auto-generate reports. Subscription: ¥99-499/month.
Freelance Platforms: Take DuckDB macro library and data pipeline projects on Upwork or Programmer House. Hourly rate: ¥200-500.
Key Tip: The value of macros isn’t the technology itself — it’s the pain points it solves: consistency and maintainability. When pitching to clients, emphasize “define once, enforce everywhere” and “update once, propagate everywhere.” These resonate far more than any performance metric.
📖 Full tutorial at duckdblab.org
💡 More DuckDB实战 tips → duckdblab.org