Implement table sampling mechanisms to help limit query volume and testing

[nryberg]

Using Malloy's shorthand, I'd like to be able to randomly select n records from a source table for the following reasons.

• Quoting BigQuery, "Sampling returns a variety of records while avoiding the costs associated with scanning and processing an entire table."
• And DuckDB, "Samples are useful for exploring a dataset faster, as often you might not be interested in the exact answers to queries, but only in rough indications of what the data looks like and what is in the data. Samples allow you to get approximate answers to queries faster, as they reduce the amount of data that needs to pass through the query engine."

BigQuery Documentation

DuckDB Documentation

[lloydtabb]

So I've looked at this a bit. Currently BigQuery limits a sampled table to appearing only once in a query which creates all kinds of problems. The other problem is joining two sampled tables (because you will end up with missing records).

Our stance, generally, until we have the right underlying architecture to support this kind of thing is to give you access through SQL.

Here is how you might do it.

sql: airports_sql is {
  select: """
    select * from 'airports.parquet' USING SAMPLE 5
  """
}

source: foo is from_sql(airports_sql) {
  measure: c is count()
}

query: foo-> {aggregate: c}