dbt-core icon indicating copy to clipboard operation
dbt-core copied to clipboard
verified publisher icon dbt-labs

[CT-1145] Max Dictionary Size Limitation

Open GarrickMeyers opened this issue 3 years ago • 1 comments

Is this a new bug in dbt-core?

  • [X] I believe this is a new bug in dbt-core
  • [X] I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

It appears there is a max iterable dictionary size of 21 items. I had made a dictionary of 23 items and was consistently not getting to the last 2 items in the dictionary. I changed the format to be an array of 23 dictionaries (each with 1 item) and got it to work

Before (didn't get to last 2 items):

{%- set metrics = {
"item_1_key" : "item_1_value",
"item_2_key" : "item_2_value",
"item_3_key" : "item_3_value",
"item_4_key" : "item_4_value",
"item_5_key" : "item_5_value",
"item_6_key" : "item_6_value",
"item_7_key" : "item_7_value",
"item_8_key" : "item_8_value",
"item_9_key" : "item_9_value",
"item_10_key" : "item_10_value",
"item_11_key" : "item_11_value",
"item_12_key" : "item_12_value",
"item_13_key" : "item_13_value",
"item_14_key" : "item_14_value",
"item_15_key" : "item_15_value",
"item_16_key" : "item_16_value",
"item_17_key" : "item_17_value",
"item_18_key" : "item_18_value",
"item_19_key" : "item_19_value",
"item_20_key" : "item_20_value",
"item_21_key" : "item_21_value",
"item_22_key" : "item_22_value",
"item_23_key" : "item_23_value",
}
-%}

What I was trying to do:

{% for identifier, alias in metrics.items() %}
    {{identifier}}.{{alias}} as {{alias}},
    sum( {{identifier}}.{{alias}} ) over {{thirty_day_partition_clause}} as rolling_30_day_{{alias}},
    sum( {{identifier}}.{{alias}} ) over {{sixty_day_partition_clause}} as rolling_60_day_{{alias}},
{% endfor -%}

Expected Behavior

After (Got all items):

{%- set metrics = [
{"item_1_key" : "item_1_value"},
{"item_2_key" : "item_2_value"},
{"item_3_key" : "item_3_value"},
{"item_4_key" : "item_4_value"},
{"item_5_key" : "item_5_value"},
{"item_6_key" : "item_6_value"},
{"item_7_key" : "item_7_value"},
{"item_8_key" : "item_8_value"},
{"item_9_key" : "item_9_value"},
{"item_10_key" : "item_10_value"},
{"item_11_key" : "item_11_value"},
{"item_12_key" : "item_12_value"},
{"item_13_key" : "item_13_value"},
{"item_14_key" : "item_14_value"},
{"item_15_key" : "item_15_value"},
{"item_16_key" : "item_16_value"},
{"item_17_key" : "item_17_value"},
{"item_18_key" : "item_18_value"},
{"item_19_key" : "item_19_value"},
{"item_20_key" : "item_20_value"},
{"item_21_key" : "item_21_value"},
{"item_22_key" : "item_22_value"},
{"item_23_key" : "item_23_value"},
]
-%}

{% for metric in metrics -%}
        {% for identifier, alias in metric.items() %}
            {{identifier}}.{{alias}} as {{alias}},
            coalesce( sum( {{identifier}}.{{alias}} ) over {{thirty_day_partition_clause}}, 0) as rolling_30_day_{{alias}},
            coalesce( sum( {{identifier}}.{{alias}} ) over {{sixty_day_partition_clause}}, 0) as rolling_60_day_{{alias}},
            coalesce( sum( {{identifier}}.{{alias}} ) over {{ltd_partition_clause}}, 0) as ltd_{{alias}},
        {% endfor -%}
    {% endfor -%}

Steps To Reproduce

Environment: dbt-core Warehouse: Snowflake

Relevant log output

n/a

Environment

- OS: Mac OS 12.2
- Python: 3.9.13
- dbt: 1.1.1

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

GarrickMeyers avatar Sep 09 '22 14:09 GarrickMeyers

@GarrickMeyers thanks for reaching out!

I tried some simple steps to reproduce, but didn't see the same thing.

Could you try the steps below and see if you get the same results as me or not?

Here's what I did:

  1. Created a file named analysis/dict_items.sql (see below for the content to add to the file)
  2. Ran dbt compile
  3. Checked the output within target/compiled/{my_project_name}/analysis/dict_items.sql
  4. It showed item_23_key, etc and all the previous values too

Content of analysis/dict_items.sql:

{%- set metrics = {
"item_1_key" : "item_1_value",
"item_2_key" : "item_2_value",
"item_3_key" : "item_3_value",
"item_4_key" : "item_4_value",
"item_5_key" : "item_5_value",
"item_6_key" : "item_6_value",
"item_7_key" : "item_7_value",
"item_8_key" : "item_8_value",
"item_9_key" : "item_9_value",
"item_10_key" : "item_10_value",
"item_11_key" : "item_11_value",
"item_12_key" : "item_12_value",
"item_13_key" : "item_13_value",
"item_14_key" : "item_14_value",
"item_15_key" : "item_15_value",
"item_16_key" : "item_16_value",
"item_17_key" : "item_17_value",
"item_18_key" : "item_18_value",
"item_19_key" : "item_19_value",
"item_20_key" : "item_20_value",
"item_21_key" : "item_21_value",
"item_22_key" : "item_22_value",
"item_23_key" : "item_23_value",
}
-%}

{% for identifier, alias in metrics.items() %}
    {{identifier}}.{{alias}} as {{alias}},
    sum( {{identifier}}.{{alias}} ) over {{thirty_day_partition_clause}} as rolling_30_day_{{alias}},
    sum( {{identifier}}.{{alias}} ) over {{sixty_day_partition_clause}} as rolling_60_day_{{alias}},
{% endfor -%}

Output in target/compiled/{my_project_name}/analysis/dict_items.sql:

{'item_1_key': 'item_1_value', 'item_2_key': 'item_2_value', 'item_3_key': 'item_3_value', 'item_4_key': 'item_4_value', 'item_5_key': 'item_5_value', 'item_6_key': 'item_6_value', 'item_7_key': 'item_7_value', 'item_8_key': 'item_8_value', 'item_9_key': 'item_9_value', 'item_10_key': 'item_10_value', 'item_11_key': 'item_11_value', 'item_12_key': 'item_12_value', 'item_13_key': 'item_13_value', 'item_14_key': 'item_14_value', 'item_15_key': 'item_15_value', 'item_16_key': 'item_16_value', 'item_17_key': 'item_17_value', 'item_18_key': 'item_18_value', 'item_19_key': 'item_19_value', 'item_20_key': 'item_20_value', 'item_21_key': 'item_21_value', 'item_22_key': 'item_22_value', 'item_23_key': 'item_23_value'}


    item_1_key.item_1_value as item_1_value,
    sum( item_1_key.item_1_value ) over  as rolling_30_day_item_1_value,
    sum( item_1_key.item_1_value ) over  as rolling_60_day_item_1_value,

    item_2_key.item_2_value as item_2_value,
    sum( item_2_key.item_2_value ) over  as rolling_30_day_item_2_value,
    sum( item_2_key.item_2_value ) over  as rolling_60_day_item_2_value,

    item_3_key.item_3_value as item_3_value,
    sum( item_3_key.item_3_value ) over  as rolling_30_day_item_3_value,
    sum( item_3_key.item_3_value ) over  as rolling_60_day_item_3_value,

    item_4_key.item_4_value as item_4_value,
    sum( item_4_key.item_4_value ) over  as rolling_30_day_item_4_value,
    sum( item_4_key.item_4_value ) over  as rolling_60_day_item_4_value,

    item_5_key.item_5_value as item_5_value,
    sum( item_5_key.item_5_value ) over  as rolling_30_day_item_5_value,
    sum( item_5_key.item_5_value ) over  as rolling_60_day_item_5_value,

    item_6_key.item_6_value as item_6_value,
    sum( item_6_key.item_6_value ) over  as rolling_30_day_item_6_value,
    sum( item_6_key.item_6_value ) over  as rolling_60_day_item_6_value,

    item_7_key.item_7_value as item_7_value,
    sum( item_7_key.item_7_value ) over  as rolling_30_day_item_7_value,
    sum( item_7_key.item_7_value ) over  as rolling_60_day_item_7_value,

    item_8_key.item_8_value as item_8_value,
    sum( item_8_key.item_8_value ) over  as rolling_30_day_item_8_value,
    sum( item_8_key.item_8_value ) over  as rolling_60_day_item_8_value,

    item_9_key.item_9_value as item_9_value,
    sum( item_9_key.item_9_value ) over  as rolling_30_day_item_9_value,
    sum( item_9_key.item_9_value ) over  as rolling_60_day_item_9_value,

    item_10_key.item_10_value as item_10_value,
    sum( item_10_key.item_10_value ) over  as rolling_30_day_item_10_value,
    sum( item_10_key.item_10_value ) over  as rolling_60_day_item_10_value,

    item_11_key.item_11_value as item_11_value,
    sum( item_11_key.item_11_value ) over  as rolling_30_day_item_11_value,
    sum( item_11_key.item_11_value ) over  as rolling_60_day_item_11_value,

    item_12_key.item_12_value as item_12_value,
    sum( item_12_key.item_12_value ) over  as rolling_30_day_item_12_value,
    sum( item_12_key.item_12_value ) over  as rolling_60_day_item_12_value,

    item_13_key.item_13_value as item_13_value,
    sum( item_13_key.item_13_value ) over  as rolling_30_day_item_13_value,
    sum( item_13_key.item_13_value ) over  as rolling_60_day_item_13_value,

    item_14_key.item_14_value as item_14_value,
    sum( item_14_key.item_14_value ) over  as rolling_30_day_item_14_value,
    sum( item_14_key.item_14_value ) over  as rolling_60_day_item_14_value,

    item_15_key.item_15_value as item_15_value,
    sum( item_15_key.item_15_value ) over  as rolling_30_day_item_15_value,
    sum( item_15_key.item_15_value ) over  as rolling_60_day_item_15_value,

    item_16_key.item_16_value as item_16_value,
    sum( item_16_key.item_16_value ) over  as rolling_30_day_item_16_value,
    sum( item_16_key.item_16_value ) over  as rolling_60_day_item_16_value,

    item_17_key.item_17_value as item_17_value,
    sum( item_17_key.item_17_value ) over  as rolling_30_day_item_17_value,
    sum( item_17_key.item_17_value ) over  as rolling_60_day_item_17_value,

    item_18_key.item_18_value as item_18_value,
    sum( item_18_key.item_18_value ) over  as rolling_30_day_item_18_value,
    sum( item_18_key.item_18_value ) over  as rolling_60_day_item_18_value,

    item_19_key.item_19_value as item_19_value,
    sum( item_19_key.item_19_value ) over  as rolling_30_day_item_19_value,
    sum( item_19_key.item_19_value ) over  as rolling_60_day_item_19_value,

    item_20_key.item_20_value as item_20_value,
    sum( item_20_key.item_20_value ) over  as rolling_30_day_item_20_value,
    sum( item_20_key.item_20_value ) over  as rolling_60_day_item_20_value,

    item_21_key.item_21_value as item_21_value,
    sum( item_21_key.item_21_value ) over  as rolling_30_day_item_21_value,
    sum( item_21_key.item_21_value ) over  as rolling_60_day_item_21_value,

    item_22_key.item_22_value as item_22_value,
    sum( item_22_key.item_22_value ) over  as rolling_30_day_item_22_value,
    sum( item_22_key.item_22_value ) over  as rolling_60_day_item_22_value,

    item_23_key.item_23_value as item_23_value,
    sum( item_23_key.item_23_value ) over  as rolling_30_day_item_23_value,
    sum( item_23_key.item_23_value ) over  as rolling_60_day_item_23_value,

dbeatty10 avatar Sep 09 '22 23:09 dbeatty10

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

github-actions[bot] avatar Dec 09 '22 02:12 github-actions[bot]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

github-actions[bot] avatar Dec 17 '22 01:12 github-actions[bot]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

github-actions[bot] avatar Dec 17 '22 01:12 github-actions[bot]