Created
January 17, 2026 07:11
-
-
Save davidclarance/77085c97b42adda582cf9d313e42eb94 to your computer and use it in GitHub Desktop.
Materialization to a/b test optimizations
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {%- materialization table_ab, default -%} | |
| {%- set target = this %} | |
| {%- set base = this.incorporate(path={'identifier': this.identifier ~ '_base'}) %} | |
| {%- set opt = this.incorporate(path={'identifier': this.identifier ~ '_opt'}) %} | |
| {% if execute %} | |
| -- check to see if a table already exists (snowflake doesn't allow overwriting it to a view) | |
| {% set existing = adapter.get_relation( | |
| database = target.database, | |
| schema = target.schema, | |
| identifier = target.identifier | |
| ) %} | |
| {% if existing is not none and existing.is_table %} | |
| {% set backup_rel = this.incorporate( | |
| path={'identifier': this.identifier ~ '_backup' } | |
| ) %} | |
| {% call statement('backup_rename') %} | |
| alter table {{ existing }} rename to {{ backup_rel }} | |
| {% endcall %} | |
| {% else %} | |
| {{ log('No existing table found for ' ~ target, info=True) }} | |
| {% endif %} | |
| {{ log('Creating base table: ' ~ base, info=True) }} | |
| -- base table | |
| {% call statement('create_base') %} | |
| create or replace table {{ base }} as | |
| {{ sql }} | |
| {% endcall %} | |
| {{ log('Base table created: ' ~ base, info=True) }} | |
| -- optimized version, you can add differet optimizations here | |
| {{ log('Cloning base table to optimized table: ' ~ opt, info=True) }} | |
| {% call statement('clone_opt') %} | |
| create or replace table {{ opt }} clone {{ base }} | |
| {% endcall %} | |
| {{ log('Optimized table created: ' ~ opt, info=True) }} | |
| {% set cluster_cols = config.get('cluster_by', []) %} | |
| {% if cluster_cols %} | |
| {{ log('Clustering optimized table ' ~ opt ~ ' by columns: ' ~ cluster_cols | join(', '), info=True) }} | |
| {% call statement('cluster_opt') %} | |
| alter table {{ opt }} cluster by ({{ cluster_cols | join(', ') }}) | |
| {% endcall %} | |
| {{ log('Clustering complete for ' ~ opt, info=True) }} | |
| {% endif %} | |
| {{ adapter.commit() }} | |
| -- create the view that does the randomization | |
| {% set split = config.get('split', 0.5) %} | |
| {{ log('Creating randomized view ' ~ target ~ ' with split: ' ~ split, info=True) }} | |
| {% call statement('main') %} | |
| create or replace view {{ target }} as | |
| -- random seed is based on the second, so we can retrieve the assignment later | |
| -- this is a bit of a hack, but it works as then its equal to the start time of the query | |
| with r as (select mod(abs(hash(to_char(date_trunc('second', current_timestamp()::timestamp_ntz),'YYYYMMDDHH24MISS'))), 100) / 100.0 as rand) | |
| select base.* | |
| from {{ base }} as base cross join r | |
| where rand < {{ split }} | |
| union all | |
| select opt.* | |
| from {{ opt }} as opt cross join r | |
| where rand >= {{ split }} | |
| {% endcall %} | |
| {{ log('Randomized view created: ' ~ target, info=True) }} | |
| {% endif %} | |
| {{ log('Materialization complete. Relations: ' ~ [target, base, opt] | join(', '), info=True) }} | |
| {{ return({'relations': [target, base, opt]}) }} | |
| {%- endmaterialization -%} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment