Including example queries in the contract

[chmduquesne]

Hi,

I am looking for a way to help the consumers by offering example SQL queries to run on the data. Such queries would be seen as "part of the contract", meaning the producer guarantees that the query will return results and that it is a correct intended use of the data.

At first, I was looking at the top-level examples section:

examples:
  type: custom
  description: get the top 10 countries with the most orders
  model: Orders
  data: SELECT COUNT(Id), Country FROM Orders GROUP BY Country ORDER BY COUNT(Id) DESC LIMIT 10;

However, it feels like an abuse of that section: what if I want to make a query to join 2 tables? Should I specify a comma-separated list of tables as the model ?

I would generally be interested in creating data contracts which include examples that one can run, serving both as test and as specification. Ideally, this would not be limited to SQL statements, but could potentially include code samples.

Is this somehow included in the specification (in which case, I missed it and I would like to know how to achieve it)? Or is it not an intended use of data contracts? Or is it something that can be added?

Cheers,
Christophe-Marie

[roykoand]

Also curious about it! I have a similar use case where I want to ensure the data quality not on a single data model but on the interdependencies between a few of them.

@simonharrer @jochenchrist maybe you can provide more details? Thank you!!

[jochenchrist]

We currently discuss to add data quality with SQL statements on model and field level.

Current development branch:

https://github.com/datacontract/datacontract-specification/blob/quality/README.md#sql

Idea is to have something like this:

models:
  my_table:
    quality:
      - name: Maximum duration between two orders
        description: The maximum duration between two orders should be less that 3600 seconds
        sql: |
          SELECT MAX(EXTRACT(EPOCH FROM (order_timestamp - LAG(order_timestamp) OVER (ORDER BY order_timestamp)))) AS max_duration
          FROM orders
        must_be_less_than: 3600

[roykoand]

@jochenchrist I think it's a great idea! Are there any obstacles that prevent us from implementing it?

[jochenchrist]

We want to align it with https://github.com/bitol-io/tsc/blob/main/rfcs/0007-data-quality.md

[roykoand]

@jochenchrist Thanks for the reference, so you're saying we need to make it compatible with https://github.com/bitol-io/tsc/blob/main/rfcs/0007-data-quality.md#sql:

quality:
  - type: sql 
    query: |
      SELECT COUNT(*) FROM ${table} WHERE ${column} IS NOT NULL
    mustBeLessThan: 3600 

so, no blockers?

[jochenchrist]

Well, Bitol RFC-0007 is not yet confirmed, but yes, that is the idea.

[jochenchrist]

What do you think about this proposal?

[chmduquesne]

Hi @jochenchrist ,

I see how this quality feature could be used to describe valid SQL queries, and how such queries could be leveraged by the consumer as examples of how to use the data.

I was more thinking of pieces of code that would be guaranteed to run if you run them in the right environment - possibly a pre-configured notebook - to be interpreted as "here is our recommended way to use the data". Your feature can certainly be used that way in SQL. How would it look like for Kafka topics (one of my main use cases for data contracts)?

Another question I am asking myself: is dbt test still relevant if such quality checks are available?

[chmduquesne]

Hi again,

Given that the solution proposed is a bit far off from what I was personally looking for, I am attempting a counter-proposal. My idea would be to expand the specification to include a section named snippets, with code samples that are contractually guaranteed to run.

In a nutshell, it would look like this:

snippets:
  snippets:
    - description: get the top 10 countries with the most orders
      environment: snowflake
      inline: |
        SELECT COUNT(Id), Country FROM Orders GROUP BY Country ORDER BY COUNT(Id) DESC LIMIT 10;
  environments:
    snowflake:
      command: |
        podman run --mount=type=bind,source=inline.txt,destination=/inline.txt mysnowflakecontainer snow sql -f inline.txt

• Elements of the list snippets.snippets would showcase intended uses of the data. They feature a description, an environment and some inline code.
• Elements of the dictionary snippets.environments are pairs of key-value, where the key is the name of the environment, and the value features a command specifying how to run the inline code, (assuming it the content of inline has been serialized in the file inline.txt). I am assuming that mysnowflakecontainer, is an image which already contains the appropriate credentials and software to run the snippet.
• Tools such as datacontract-cli would run the snippet in the specified environment and check that it returns 0.

I am insisting that the intent is not to provide a data quality check, but rather to hint the consumers as to what are intended ways to use the data. I think quality checks are very valuable, but they do not serve well the users when it comes to teaching them how to write appropriate queries.

What would you think of such a proposal?