The following macros are intended to be used to create and maintain various models. These macros can be accessed via version >=v1.5.0 of the fsc-evm package. Please see the fsc-evm installation instructions in the README for more details on how to access and utilize this macro effectively.

The External Table Views Macros are used to create Bronze level Views and enable more efficient access to External Tables by selecting from snowflake information schemas with a standardized table structure.

---

streamline_external_table_query

Usage

This macro constructs a SQL query to retrieve data from an external table, utilizing the specified model and partition function, and only includes the last 3 days of data by referencing the information_schema.external_table_file_registration_history table. The query joins the external table with metadata to filter and select relevant records based on partition keys and error conditions. It includes a balances parameter, which must be set to true when using this macro to build Bronze views for native or token balances.

fsc_evm.streamline_external_table_query

Parameters

Note: Parameters with default values must be ordered after non-default parameters.

Parameter	Type	Default	Description
model	String	-	Specifies the model name for the external table query.
partition_function	String	-	A SQL expression used to define how the data is partitioned.
balances	Boolean	false	Indicates whether to include balance-related joins and columns in the query.
block_number	Boolean	true	Adds a block_number column to the bronze view.

Example:

{{ fsc_evm.streamline_external_table_query(
    model = 'transactions',
    partition_function = 'CAST(block_number as STRING)',
    balances = false,
    block_number = true
) }}

---

streamline_external_table_fr_query

Usage

This macro constructs a SQL query to retrieve data from an external table with a focus on full refresh scenarios by referencing the information_schema.external_table_files table with no lookback. The query filters records based on the specified partition key and error conditions. It allows for specifying a custom join key for partitions and includes a balances parameter, which must be set to true when using this macro to build Bronze views for native or token balances.

fsc_evm.streamline_external_table_fr_query

Parameters

Note: Parameters with default values must be ordered after non-default parameters.

Parameter	Type	Default	Description
model	String	-	Specifies the model name for the external table query.
partition_function	String	-	A SQL expression used to define how the data is partitioned.
partition_join_key	String	"partition_key"	The key used for joining partitions. Usage is typically required on Streamline 1.0 External Tables. Example: _partition_by_block_id.
balances	Boolean	false	Indicates whether to include balance-related joins and columns in the query.
block_number	Boolean	true	Adds a block_number column to the bronze view.

Example:

{{ fsc_evm.streamline_external_table_fr_query(
    model= 'blocks',
    partition_function = 'CAST(block_number as STRING)',
    partition_join_key = '_partition_by_block_id',
    balances = false,
    block_number = true
) }}

streamline_external_table_fr_union_query

---

Usage

This macro constructs a view that performs a union of data from two different versions of external tables based on the specified model. It retrieves relevant fields such as partition_key, file_name and _inserted_timestamp. The union combines results from the latest version of the external table with the previous version, ensuring that all relevant data is captured for full-refresh scenarios.

fsc_evm.streamline_external_table_fr_union_query

Parameters

Note: Parameters with default values must be ordered after non-default parameters.

Parameter	Type	Default	Description
model	String	-	Specifies the model name for which the union query is being constructed. Acceptable values are: 'blocks', 'confirmed_blocks', 'transactions', 'receipts', 'traces'

Example:

{{ fsc_evm.streamline_external_table_fr_union_query(
    model='transactions'
) }}