Filters as a variable

[tombaeyens]

Let's start with a description of how I understood the request for filters as a variable.

In SodaCL, you already can define filters:

filter CUSTOMERS [daily]:
  where: TIMESTAMP '${ts_start}' <= "ts" AND "ts" < TIMESTAMP '${ts_end}'

Next what you want is to make the whole filter clause available as a variable so that it can be leveraged in custom queries & SQL snippets like this:

checks for dim_product [daily]:
  - product_stock >= 50:
      product_stock query: |
        SELECT COUNT(safety_stock_level - days_to_manufacture)
        FROM dim_product
        WHERE ${ filter('daily') }

Does that cover the use case?

[tombaeyens]

Related to this, there was also the question around passing variable through the command line.

Soda Core has got you covered:

soda scan -d aws_postgres_retail -c configuration.yml -v TODAY=2022-03-31 checks.yml

See https://docs.soda.io/soda-core/scan-core.html#variables

Is this wat you were looking for?

[tombaeyens]

Related idea

filter CUSTOMERS [daily]:
  where: ${CUSTOMERS_FILTER}

checks for dim_product [daily]:
  - product_stock >= 50:
      product_stock query: |
        SELECT COUNT(safety_stock_level - days_to_manufacture)
        FROM dim_product
        WHERE ${CUSTOMERS_FILTER}

soda scan ... -v "CUSTOMERS_FILTER=TIMESTAMP '2022-04-11' <= "ts" AND "ts" < TIMESTAMP '2022-04-11'" ...