<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by amaury dumoulin on Medium]]></title>
        <description><![CDATA[Stories by amaury dumoulin on Medium]]></description>
        <link>https://medium.com/@adumoulin?source=rss-63b084a2b9ed------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*3YZ4awyOJhh_fxNtDI33IQ.png</url>
            <title>Stories by amaury dumoulin on Medium</title>
            <link>https://medium.com/@adumoulin?source=rss-63b084a2b9ed------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 02:38:54 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@adumoulin/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[What if … you had to use parameters in SQL queries]]></title>
            <link>https://medium.com/castor-app/what-if-you-had-to-use-parameters-in-sql-queries-29abd9cf4b2?source=rss-63b084a2b9ed------2</link>
            <guid isPermaLink="false">https://medium.com/p/29abd9cf4b2</guid>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[amaury dumoulin]]></dc:creator>
            <pubDate>Wed, 05 May 2021 08:28:24 GMT</pubDate>
            <atom:updated>2021-05-12T07:05:32.392Z</atom:updated>
            <content:encoded><![CDATA[<h3>What if … you had to use parameters in SQL queries</h3><h4>TLDR: SQL parameters can be a mess, quickly. Introducing tricks for SQL, DBT, pandas, Airflow, Metabase</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/0*ee4gPSWSqXjeE3MO.png" /><figcaption>SQL query with parameters</figcaption></figure><p>SQL is a powerful language 🤖⠀fueling analytics, product, and operations. It directly impacts decision-making and eventually revenue.</p><p>Maintaining an analytics or feature store pipeline involves a lot of SQL and parameters. We give a useful tip on how to serve those parameters in a smooth manner to cut down on headaches 🤕⠀and errors.</p><h3>Hell game of literals in BI queries</h3><p>As I was designing and reviewing BI queries at <a href="https://qonto.com/">Qonto</a> (a french fintech) I had to integrate constants:</p><ul><li><strong>revenue</strong> contractual commission on certain transactions 💰</li><li><strong>compliance</strong> thresholds for certain batch reporting</li><li><strong>marketing</strong> segmentation parameters (e.g. number of employees)</li><li><strong>top management dashboard</strong> temporal differences to account to different moving averages</li></ul><h3>Use Case</h3><h4>Need</h4><p>Let’s pick the following example where the <em>marketing team</em> 👩🏽‍💻⠀wants reporting based upon a medium bracket customer segment. We agreed with stakeholders on the following definition:</p><blockquote><em>The medium bracket should hold clients bearing between 10 and 30 valid transactions through the last 20 days</em></blockquote><p>We want a base table that stores the client id belonging to that bracket and summarizes the number of transactions as well as the total amount of those transactions 💳</p><h4>First approach</h4><p>The base query would group transactions by customer_id over the period of time and then filter upon the threshold aforementioned.</p><ol><li>Select all valid transactions within the timeframe ⏰</li><li>Group them by customer_id and aggregate count of transactions</li><li>Filter lines where the count is within the rang 🚦</li></ol><p>We can use a <a href="https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression">CTE</a> (common table expression) for the first two steps and produce the following</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0c2f0a03758a5e9674e54b584e1745ef/href">https://medium.com/media/0c2f0a03758a5e9674e54b584e1745ef/href</a></iframe><h4>Introducing tricks</h4><p>One nice improvement over scattering constants all over lengthy SQL files is to group all constants and parameters in a dedicated <a href="https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression">CTE</a> (common table expression) 🏗</p><p>Another one is to use WHERE TRUE<strong><em> </em></strong>with AND for each filter which allows easy editing</p><p>Here is what the query could look like in a Snowflake dialect</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6574be2bc00defac30389a42eaf9a006/href">https://medium.com/media/6574be2bc00defac30389a42eaf9a006/href</a></iframe><p>This example has a minimal footprint. In real-life base tables, you could face 10–20 parameters and 50+ output columns.</p><p>The upsides are manyfold:</p><ul><li>DRY (don’t repeat yourself): declare once use multiple times if needed 🙉</li><li>Easy maintenance: fewer errors and easier update for anyone</li><li>Easy to understand: parameters should come ahead</li></ul><h3>Real-world usage</h3><p>Let’s explore how to use this trick with the most common use cases</p><ul><li><strong>science</strong> pandas 👨🏻‍🔬</li><li><strong>engineering</strong> airflow and dbt</li><li><strong>visualization</strong> Metabase 📈</li></ul><p>We use python 3.9 syntax throughout those use cases (including new dictionary update)</p><h4>pandas</h4><p>Most database connection engine allows binding parameters:</p><ul><li>Object Relationship Mapping tools — <a href="https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping">ORM</a> (think <a href="https://www.sqlalchemy.org/">sqlalchemy</a>)</li><li>SQL tools (think <a href="https://dbeaver.io/">DBeaver</a>)</li></ul><p>The syntax are defined in <a href="https://www.python.org/dev/peps/pep-0249/#paramstyle">PEP249</a> and are driver dependent: for psycopg2 use the %(parameter)s syntax</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/75de402f452db06175be1109b2ba6bec/href">https://medium.com/media/75de402f452db06175be1109b2ba6bec/href</a></iframe><h4>DBT</h4><p>You can mix it with configuration, references, and variables in customer_transaction.sql</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/713d3f39b995defd782cb53410610945/href">https://medium.com/media/713d3f39b995defd782cb53410610945/href</a></iframe><p>Set variables in dbt_project.yml configuration file as such:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c9c81a24ba4a2525635306be16375629/href">https://medium.com/media/c9c81a24ba4a2525635306be16375629/href</a></iframe><h4>Airflow</h4><p>You can use a sql operator such as PostgresOperator in conjunction with:</p><ul><li>a SQL file dags/bi/customer_transaction/transform.sql</li><li>a parameters file dags/bi/customer_transaction/parameters.py</li><li>a file loader helper dags/load_file.py</li><li>a DAG definition file dags/customers.py</li></ul><p>Define the source.sql file with parameters placeholders</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/414696f1d46c5526afb4226bfb7ccd0e/href">https://medium.com/media/414696f1d46c5526afb4226bfb7ccd0e/href</a></iframe><p>Define the parameters.py file with constant values:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/79b1ae848b3714ec666e7f8d7d4fd5bd/href">https://medium.com/media/79b1ae848b3714ec666e7f8d7d4fd5bd/href</a></iframe><p>Add the file loader load_file.py</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ed629287cedcfc099b09d36fd72f4500/href">https://medium.com/media/ed629287cedcfc099b09d36fd72f4500/href</a></iframe><p>You can feed parameters at task invoke the DAG customers.py</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/68f1756e25b36c3b1ede995794a3022b/href">https://medium.com/media/68f1756e25b36c3b1ede995794a3022b/href</a></iframe><p>See <a href="https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/operators/postgres_operator_howto_guide.html#passing-parameters-into-postgresoperator">documentation</a></p><h4>Metabase</h4><p><a href="https://www.metabase.com/">Metabase</a> allows you to use <a href="https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html">variables</a> in queries using {{ }}</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/56fd36c6aaeb0c1773d250a4841fefe4/href">https://medium.com/media/56fd36c6aaeb0c1773d250a4841fefe4/href</a></iframe><p>With the following parameters:</p><ul><li>status as Text</li><li>count_low, count_high and period_days as Number</li></ul><h3>Finding Data</h3><p>Writing and maintaining 🔧⠀complex and efficient SQL queries requires experience and skills. It involves wrapping your mind around growing data assets. You might want to leverage🕵🏽‍♀️⠀the most of your data knowledge.</p><p>I am a co-founder at <a href="https://www.castordoc.com/">Castor</a>, a data discovery platform designed to help anyone find, understand and use data assets across the whole company.</p><p>We developed a SQL query history feature to help data people find and share queries. <a href="https://www.castordoc.com/">Check it out</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=29abd9cf4b2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/castor-app/what-if-you-had-to-use-parameters-in-sql-queries-29abd9cf4b2">What if … you had to use parameters in SQL queries</a> was originally published in <a href="https://medium.com/castor-app">CastorDoc</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>