Teradata SAMPLE in Snowflake, Databricks and BigQuery
Teradata SAMPLE takes a row count or a fraction. Snowflake SAMPLE and Databricks TABLESAMPLE accept both rows and percentages; BigQuery TABLESAMPLE only samples a percentage of storage blocks.
Teradata syntax: SELECT ... SAMPLE { n | fraction } [, ...] · Teradata docs
Equivalents
| Target | Support | Syntax | Notes | Source |
|---|---|---|---|---|
| Snowflake | Rewrite | SAMPLE (n ROWS) / SAMPLE (percent) | The number without ROWS is a percentage, not a fraction. | Snowflake docs |
| Databricks | Rewrite | TABLESAMPLE (n ROWS) / TABLESAMPLE (percent PERCENT) | REPEATABLE (seed) makes the sample repeatable. | Databricks docs |
| BigQuery | Rewrite | TABLESAMPLE SYSTEM (percent PERCENT) | Samples blocks, not rows. For an exact row count use ORDER BY RAND() LIMIT n. | BigQuery docs |
Behavior differences to check
- Teradata SAMPLE 0.25 means 25 percent. Snowflake SAMPLE (0.25) means 0.25 percent, a hundred times smaller.
- Multiple samples with SAMPLEID and stratified sampling (WHEN ... THEN) have no direct equivalent; build them with a window function over RANDOM().
Examples
Pick 1,000 random customers
SELECT * FROM customers SAMPLE 1000;SELECT * FROM customers SAMPLE (1000 ROWS);SELECT * FROM customers TABLESAMPLE (1000 ROWS);SELECT * FROM customers ORDER BY RAND() LIMIT 1000;