SQL Transforms

​

SQL Transforms

SQL transforms allow you to write SQL queries to modify and shape data from multiple sources within the pipeline. This is ideal for operations that need to be performed within the data pipeline itself, such as filtering, aggregating, or joining datasets.

Depending on how you choose to source your data, you might find that you run into 1 of 2 challenges:

1. You only care about a few contracts

   Rather than fill up your database with a ton of extra data, you’d rather filter down your data to a smaller set.

2. The data is still a bit raw

   Maybe you’d rather track gwei rounded to the nearest whole number instead of wei. You’re looking to map data to a different format so you don’t have to run this calculation over and over again.

​

The SQL Solution

You can use SQL-based transforms to solve both of these challenges that normally would have you writing your own indexer or data pipeline. Instead, Goldsky can automatically run these for you using just 3 pieces of info:

• name: A shortname for this transform

  You can refer to this from sinks via from or treat it as a table in SQL from other transforms.

• sql: The actual SQL

  To filter your data, use a WHERE clause, e.g. WHERE liquidity > 1000.

  To map your data, use an AS clause combined with SELECT, e.g. SELECT wei / 1000000000 AS gwei.

• primary_key: A unique ID

  This should be unique, but you can also use this to intentionally de-duplicate data - the latest row with the same ID will replace all the others.

Combine them together into your config:

transforms:
  negative_fpmm_scaled_liquidity_parameter:
    sql: SELECT id FROM polymarket.fixed_product_market_maker WHERE scaled_liquidity_parameter < 0
    primary_key: id

transforms:
  negative_fpmm_scaled_liquidity_parameter:
    sql: SELECT id FROM polymarket.fixed_product_market_maker WHERE scaled_liquidity_parameter < 0
    primary_key: id

transforms:
  - referenceName: negative_fpmm_scaled_liquidity_parameter
    type: sql
    sql: SELECT id FROM polygon.fixed_product_market_maker WHERE scaled_liquidity_parameter < 0
    primaryKey: id

That’s it. You can now filter and map data to exactly what you need.