jeremyyeo · January 27, 2023 01:36 · Jan 27, 2023 · Jan 27, 2023 · Jan 27, 2023 · Jan 27, 2023
diff --git a/README.md b/README.md
@@ -65,22 +65,36 @@ create or replace transient table development.dbt_jyeo.unpack  as
 
 The magic is in:
 
-```
+```jinja
 {%- set column_name = var(key | int, key) %}
 ```
 
 Where we make `key` into an integer - because if not, it is a string and if you look at our vars, there is no variable named `"1"` (the string) but rather there is a variable named `1` (the integer). As you can tell - it's not a super good idea to have integers as variable names. Basically by doing:
 
-```
+```yaml
 vars:
   1: foo
   2: bar
 ```
 
-We've constructed a dictionary:
+We've constructed a `vars` dictionary:
 
 ```Python
 vars = {1: "foo", 2: "bar"}  # NOT THE SAME AS vars = {"1": "foo", "2": "bar"}
 vars[1]   # 'foo'
 vars["1"] # KeyError: '1'
 ```
+
+Knowing this, we could stringify our vars declaration:
+
+```yaml
+vars:
+  "1": foo
+  "2": bar
+```
+
+And then do the following instead:
+
+```jinja
+{%- set column_name = var(key, key) %}
+```
diff --git a/README.md b/README.md
@@ -69,12 +69,18 @@ The magic is in:
 {%- set column_name = var(key | int, key) %}
 ```
 
-Where we make `key` into an integer - because if not, it is a string and if you look at our vars, there is no variable named `"1"` (the string) but rather there is a variable named `1` (the integer). As you can tell - it's not a super good idea to have integers as variable names... just like how you would not write Python like so:
+Where we make `key` into an integer - because if not, it is a string and if you look at our vars, there is no variable named `"1"` (the string) but rather there is a variable named `1` (the integer). As you can tell - it's not a super good idea to have integers as variable names. Basically by doing:
 
-```Python
-1 = 'foo'
-2 = 'bar'
-1 + 2
 ```
+vars:
+  1: foo
+  2: bar
+```
+
+We've constructed a dictionary:
 
-^ Pretty clear this is not advisable :) 
+```Python
+vars = {1: "foo", 2: "bar"}  # NOT THE SAME AS vars = {"1": "foo", "2": "bar"}
+vars[1]   # 'foo'
+vars["1"] # KeyError: '1'
+```
diff --git a/README.md b/README.md
@@ -62,3 +62,19 @@ create or replace transient table development.dbt_jyeo.unpack  as
         );
 01:25:55  SQL status: SUCCESS 1 in 1.32 seconds
 ```
+
+The magic is in:
+
+```
+{%- set column_name = var(key | int, key) %}
+```
+
+Where we make `key` into an integer - because if not, it is a string and if you look at our vars, there is no variable named `"1"` (the string) but rather there is a variable named `1` (the integer). As you can tell - it's not a super good idea to have integers as variable names... just like how you would not write Python like so:
+
+```Python
+1 = 'foo'
+2 = 'bar'
+1 + 2
+```
+
+^ Pretty clear this is not advisable :) 
diff --git a/README.md b/README.md
@@ -0,0 +1,64 @@
+## Extracting Snowflake variant keys and mapping them to dbt vars so we can use them as column names
+
+1. Create a table with variant/json data:
+
+```sql
+create or replace table development.dbt_jyeo.tbl_with_json as (
+  select object_construct('my_key',object_construct('1', 'foo', '2', 'bar', '3', 'baz')) as variant_col
+);
+```
+
+![image](https://user-images.githubusercontent.com/8496748/214987282-ab7011b7-9b53-4534-a0d2-eee7d2e669b7.png)
+
+We will use this table to create a dbt model.
+
+2. Add vars to dbt_project.yml:
+
+```yaml
+# dbt_project.yml
+vars:
+  1: foo
+  2: bar
+```
+
+> Note that it's probably not the best idea to have integers as variable names here.
+
+3. Add our dbt model with all the necessary components:
+
+```sql
+-- models/unpack.sql
+{%- set query_keys -%}
+select k.key 
+  from development.dbt_jyeo.tbl_with_json t, 
+       lateral flatten(input => t.variant_col:my_key) k;
+{%- endset -%}
+
+{%- set results = run_query(query_keys) -%}
+
+{%- if execute -%}
+    {%- set res = results.columns[0].values() -%}
+{%- else -%}
+    {%- set res = [] -%}
+{%- endif -%}
+
+select
+{%- for key in res %}
+    {%- set column_name = var(key | int, key) %}
+    variant_col['my_key']['{{ key }}'] as col_{{ column_name }} {%- if not loop.last -%},{%- endif -%}
+{%- endfor %}
+ from development.dbt_jyeo.tbl_with_json
+```
+
+4. Run the dbt model and inspect the logs (or compile the model):
+
+```
+01:25:54  On model.my_dbt_project.unpack: /* {"app": "dbt", "dbt_version": "1.3.2", "profile_name": "user", "target_name": "dev", "node_id": "model.my_dbt_project.unpack"} */
+create or replace transient table development.dbt_jyeo.unpack  as
+        (select
+    variant_col['my_key']['1'] as col_foo,
+    variant_col['my_key']['2'] as col_bar,
+    variant_col['my_key']['3'] as col_3
+ from development.dbt_jyeo.tbl_with_json
+        );
+01:25:55  SQL status: SUCCESS 1 in 1.32 seconds
+```