Custom source queries
Using your own 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
Available Variable | Data Type | Description |
---|---|---|
{{.LastModifiedAtEpoch}} | integer | The greatest (most recent) epoch Prequel has transferred so far. Sample value: 1681841760 |
{{.IdInProviderSystem}} | string | Tenant ID (available in "multi-tenant table" mode) |
{{.Schema}} | string | Schema name (available in "schema-tenanted database" mode) |
{{.PrimaryKeyColumnName}} | string | |
{{.LastModifiedColumnName}} | string | |
{{.OrganizationColumnName}} | string |
Updated 4 months ago