SET join_algorithm = 'full_sorting_merge';
SET optimize_read_in_order = 1;
SET optimize_sorting_by_input_stream_properties = 1;

{{
    config(
        materialized = "incremental",
        engine = "ReplicatedMergeTree",
        unique_key = "transaction_id",
        order_by = '(txn_time_q, txn_result, txn_type, shop_id, transaction_id)',
        partition_by = 'txn_time_year',
        incremental_strategy = 'delete+insert',
        settings = {'allow_nullable_key': 1,},
       query_settings = {"join_algorithm": "full_sorting_merge",  "optimize_read_in_order": 1,
                          "optimize_sorting_by_input_stream_properties": 1,},
    )
}}

insert into warehouse_dev.transactions_test
        ("transaction_id", "last_mod", "shop_id", "transaction_added", "txn_time_year", "txn_time_q", "txn_result", "txn_type", "email")


WITH transactions AS (
    SELECT
        transaction_id,
        shop_id,
        last_mod,
        transaction_added,
        txn_result,
        txn_type
    FROM warehouse_dev.stg_transactions
        WHERE
            last_mod > (
                SELECT MAX(last_mod) FROM warehouse_dev.transactions
                WHERE
                    txn_time_year
                    >= date_trunc(
                        'year', now('America/Mexico_City') - INTERVAL '365 day'
                    )
            )
),

event AS (
    SELECT
        transaction_id,
        email
    FROM warehouse_dev.stg_transaction_event_card
        WHERE
        	last_mod > (
                SELECT MAX(last_mod) FROM warehouse_dev.transactions
                WHERE
                    txn_time_year
                    >= date_trunc(
                        'year', now('America/Mexico_City') - INTERVAL '365 day'
                    )
            )
),

shops AS (
    SELECT
        shop_id,
        company
    FROM warehouse_dev.stg_shops
),

transaction_join AS (
    SELECT
        t.transaction_id AS transaction_id,
        t.last_mod AS last_mod,
        t.shop_id AS shop_id,
        t.transaction_added AS transaction_added,
        e.email AS email,
        s.company AS company,
        t.txn_result AS txn_result,
        t.txn_type AS txn_type
    FROM transactions AS t
    LEFT JOIN event AS e ON e.transaction_id = t.transaction_id
    LEFT JOIN SHOP AS s ON s.shop_id = t.shop_id
)

SELECT
    t1.transaction_id AS transaction_id,
    t1.last_mod AS last_mod,
    t1.shop_id AS shop_id,
    t1.transaction_added AS transaction_added,
    date_trunc('year', t1.transaction_added) AS txn_time_year,
    date_trunc('quarter', t1.transaction_added) AS txn_time_q,
    t1.email AS email,
    t1.company AS company,
    t1.txn_result AS txn_result,
    t1.txn_type AS txn_type
FROM transaction_join AS t1

-- limit 1000

SETTINGS  join_algorithm=full_sorting_merge,  optimize_read_in_order=1,  optimize_sorting_by_input_stream_properties=1

Code: 62. DB::Exception: Syntax error: failed at position M (',') (line N, col N1): ,  optimize_read_in_order=1,  optimize_sorting_by_input_stream_properties=1. Expected one of: token, Dot, OpeningRoundBracket, end of query. (SYNTAX_ERROR) (version 23.11.2.11 (official build))