Custom source queries

❗️

This feature is disabled by default. Please contact support to enable this feature before attempting to use it!

Prequel has the optional ability to write your own source_query with access to some of the important Prequel variables that will be templated in at runtime.

For example, in the Prequel configuration, you could write:

{
  ...,
  source_query: "SELECT * FROM (
  	SELECT *,  ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rank_order
		FROM some_table
			WHERE tenant_id = {{.IdInProviderSystem}} AND updated_at >= from_unixtime({{.LastModifiedAtEpoch}})
		)
		WHERE rank_order = 1",
	...
}

Prequel will still run a query on top of the source_query (to handle column selecting and renaming), so the columns section of the config will not change. This change simply allows you to be more expressive around how Prequel reads data, and provides the option to specifically leverage some of Prequel's parameters (like the epoch timestamp) in your query, enabling efficient partition pruning, etc.

Using custom source queries with parameterized views

Custom queries can also be used to support parameterized views in sources that support the functionality (e.g., ClickHouse).

{
  ...,
  source_query: "SELECT * FROM some_parameterized_view(tenant_id = {{.IdInProviderSystem}})",
	...
}

Available Prequel variables