GAB step-by-step

1. Data Product Setup based on templated files

• Lakehouse engine: 1.20.0+
• Copy GAB assets from templated files to your data product:
  • GAB tables:
    • Calendar table - dim_calendar
    • Use case configuration table - lkp_query_builder
    • Unified data table - gab_use_case_results
    • GAB log events table - gab_log_events
  • GAB notebooks:
    • Feed Calendar table - gab_dim_calendar
    • Use case creation - query_builder_helper
    • GAB execution - gab
    • GAB job manager - gab_job_manager

2. Use case setup

Templating SQL Query

The placeholder will always use pre-configured values from the use case (lkp_query_builder).

Available Placeholders:

• Date Aggregation:

  • Reference date (the date column used to compute the cadences and the extended window)

    {% if replace_offset_value == 0 %} {{ project_date_column }}
    {% else %} ({{ project_date_column }} + interval '{{offset_value}}' hour)
    {% endif %}
    

  Note: The replace_offset_value have the responsibility to use directly the date column or shift it to the specified timezone using the offset_value from the configured use case.

  • Last day of the cadence and if snapshots are enabled then it contains the snapshot end day:

    {{ to_date }}
    

• Filter:

  • It expects to have the data partitioned by date (year/month/day) and it replaces the placeholder with a filter like year = **** and month = ** and day = **:

    {{ partition_filter }}
    

  Note: if your table does not have the Year, Month, Day columns you should not add this template

  • Shift the data consumed on your use case to be between the specified offset timezone:

    {{ filter_date_column }} >= ('{{ start_date }}' + interval '{{offset_value}}' hour) AND {{ filter_date_column }} < ('{{ end_date}}' + interval '{{offset_value}}' hour)
    

• The source database to consume the data:

  {{ database }}
  

• Calendar join:

  {{ joins }}
  

  Note: Can be added after any of the table names in the from statement. The framework renders this joins with an internal calendar join and populates the to_date and the project_date_column as per the configured cadences, which means, it will need to be added when using Cadence, Extended Window Calculation, Reconciliation and Snapshot.

Sample combined query making use of the placeholders:

SELECT
    {% if replace_offset_value == 0 %} {{ project_date_column }} {% else %} ({{ project_date_column }} + interval '{{offset_value}}' hour) {% endif %} AS order_date,  # date aggregation: computed cadence start date
    {{ to_date }} AS to_date,  # date aggregation: last day of the cadence or of the snapshot if enabled
    b.category_name,
    COUNT(a.article_id) qty_articles,
    SUM(amount) total_amount
FROM
    {{ database }}.dummy_sales_kpi a  # source database
    {{ joins }}  # calendar table join: used to compute the cadence start and end date
LEFT JOIN
    article_categories b ON a.article_id = b.article_id
WHERE
    {{ partition_filter }}  # filter: partition filter
AND
    TO_DATE({{ filter_date_column }}, 'yyyyMMdd') >= (
        '{{ start_date }}' + interval '{{ offset_value }}' hour
    )  # filter by date column configured in the use case for this file and timezone shift
AND
    TO_DATE({{ filter_date_column }}, 'yyyyMMdd') < (
        '{{ end_date }}' + interval '{{ offset_value }}' hour
    )  # filter by date column configured in the use case for this file and timezone shift
GROUP BY 1,2,3

Note: If there's just one sql file for the use case, the file should start with 1_. When the use case has several different intermediate stages/temp tables, please create them according to the sequence order, as shown in the screenshot, and a final combined script, example:

We suggest using the folder metadata/gab to use as the SQL use case folder but this is a parametrised property which you can override with the property gab_base_path. This property is used in the job manager as well.

Use case configuration using the query_builder_helper

DELETE FROM example_database.lkp_query_builder WHERE QUERY_LABEL = 'f_agg_dummy_sales_kpi';
INSERT INTO example_database.lkp_query_builder VALUES (
  1,
  'f_agg_dummy_sales_kpi',
  'GLOBAL',
  """{
    'vw_f_agg_dummy_sales_kpi': {
      'dimensions': {
        'from_date': 'order_date',
        'to_date': 'to_date',
        'd1': 'category_name'
      },
      'metric': {
        'm1': {
          'metric_name': 'qty_articles',
          'calculated_metric': {},
          'derived_metric': {}
        },
        'm2': {
          'metric_name': 'total_amount',
            'calculated_metric': {
              'last_cadence': [
                {
                  'label': 'total_amount_last_year',
                  'window': '1'
                }
              ],
              'window_function': [
                {
                  'label': 'avg_total_amount_last_2_years',
                  'window': [2, 1],
                  'agg_func': 'avg'
                }
              ]
            },
            'derived_metric': [
              {
                'label': 'discounted_total_amount',
                'formula': 'total_amount*0.56'
              }
            ]
          }
        },
      'filter': {}
    }
  }""",
  """{
    '1': {
        'file_path': 'f_agg_dummy_sales_kpi/1_article_category.sql',
        'table_alias': 'article_categories',
        'storage_level': 'MEMORY_ONLY',
        'project_date_column': '',
        'filter_date_column': '',
        'repartition': {}
    },
    '2': {
        'file_path': 'f_agg_dummy_sales_kpi/2_f_agg_dummy_sales_kpi.sql',
        'table_alias': 'dummy_sales_kpi',
        'storage_level': 'MEMORY_ONLY',
        'project_date_column': 'order_date',
        'filter_date_column': 'order_date',
        'repartition': {}
    }
  }""",
  """{'YEAR': {}}""",
  '0',
  'MONDAY',
  'Y',
  'Low',
  current_timestamp()
);

3. Use case execution

After the initial setup and adding your use case to the lkp_query_builder you can schedule the gab_job_manager to manage the use case execution in any schedule time you want.

4. Consuming the data

The data is available in the view you specified as output from the use-case, so you can normally consume the view as you would consume any other data (directly on power bi or in another pipeline).