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

🔥 Pitfall Guide (5 Rules)

1. 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.

2. Watch for scope and naming conflicts: Column names referenced inside macros that conflict with outer query columns can cause unexpected results. Use tbl. prefixes or AS aliases inside macros to stay safe.

3. Macros can’t directly reference Python variables: If you need dynamic values from Python inside macros, use conn.execute with string formatting, but be aware of SQL injection risks. Parameterized queries are safer.

4. 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.

5. 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.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。