How AtScale Handles Incremental Aggregate Builds

This document describes how AtScale handles incremental builds for different scenarios. Not all aggregates are created equal.

First Scenario

This will show different table scenarios with 1 fact + 2 dimensions. The fact table will have incremental indicators based on time dimension with a 0 grace period.

There are 4 system-defined aggregates. 2 incremental and 2 do not have incremental.

ATSCALE.as_agg_44af312b_pttp	2021-08-12 14:02:21.388 -0700
ATSCALE.as_agg_7cc7277c_pttp	2021-08-12 12:13:41.434 -0700

CREATE VIEW PETPLAN.ATSCALE."as_agg_37908021_inc" AS

SELECT

   "table_union"."key_c1" AS "key_c1",

   "table_union"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

   "table_union"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

   "table_union"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

FROM

(

      SELECT

         "as_agg_bf5dfdb9_none"."key_c1" AS "key_c1",

         "as_agg_bf5dfdb9_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_bf5dfdb9_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_bf5dfdb9_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_bf5dfdb9_none"

   UNION ALL

      SELECT

         "as_agg_607bfeab_none"."key_c1" AS "key_c1",

         "as_agg_607bfeab_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_607bfeab_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_607bfeab_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_607bfeab_none"

) AS "table_union";

as_agg_bf5dfdb9_none	2021-08-12 12:38:46.266 -0700
as_agg_607bfeab_none	2021-08-12 12:38:46.284 -0700

CREATE VIEW PETPLAN.ATSCALE."as_agg_3f3e422a_inc" AS

SELECT

   "table_union"."key_c1" AS "key_c1",

   "table_union"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

   "table_union"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

   "table_union"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

FROM

(

      SELECT

         "as_agg_be22d33c_none"."key_c1" AS "key_c1",

         "as_agg_be22d33c_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_be22d33c_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_be22d33c_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_be22d33c_none"

   UNION ALL

      SELECT

         "as_agg_90af2c31_none"."key_c1" AS "key_c1",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_90af2c31_none"

) AS "table_union";

as_agg_be22d33c_none	2021-08-12 12:20:48.538 -0700
as_agg_90af2c31_none	2021-08-12 12:20:48.532 -0700

After an aggregate standard build, AtScale will review the stored aggregate max value of the incremental indicator.

SELECT

   MAX(CAST("t_13"."date_sk_c12" AS DECIMAL(38,0))) AS "c_16"

FROM

(

   SELECT

      "fact_inc_t11"."DATE_SK" AS "date_sk_c12"

   FROM

      "PETPLAN"."FACT"."FACT_INC" AS "fact_inc_t11"

   WHERE

      CAST("fact_inc_t11"."DATE_SK" AS DECIMAL(38,0)) >= 20180614

   GROUP BY

      1

) AS "t_13"

WHERE

   true

Then, it will do an insert based on the last update date.

SELECT

   "t_31"."rawpolicyid_c30" AS "key_c1",

   SUM("t_31"."inforceamt_c28") AS "m_inforceamt_sum_c2",

   MIN("t_31"."inforceamt_c28") AS "m_inforceamt_sum_c3",

   MAX("t_31"."inforceamt_c28") AS "m_inforceamt_sum_c4"

FROM

(

   SELECT

      "fact_inc_t27"."DATE_SK" AS "date_sk_c29",

      "fact_inc_t27"."INFORCEAMT" AS "inforceamt_c28",

      "fact_inc_t27"."RAWPOLICYID" AS "rawpolicyid_c30"

   FROM

      "PETPLAN"."FACT"."FACT_INC" AS "fact_inc_t27"

   WHERE

      "fact_inc_t27"."DATE_SK" <= 20180614

   AND

      "fact_inc_t27"."DATE_SK" >= 20180614

) AS "t_31"

WHERE

   true

GROUP BY

   1

The final results after the standard build an incremental update.

The two base table has been rebuild with new aggregate table

as_agg_828ee4ef_pttp	2021-08-12 14:23:16.335 -0700
as_agg_ad1e2a65_pttp	2021-08-12 14:23:07.916 -0700

Value

20180306 Dog Dog 26569587 0 2122

20180306 Cat Cat 3651746 0 913

Value

Dog Dog 55197350 0 2131

Cat Cat 7534455 0 913

The incremental table, however is different.

CREATE VIEW PETPLAN.ATSCALE."as_agg_7b591a1b_inc" AS

SELECT

   "table_union"."key_c1" AS "key_c1",

   "table_union"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

   "table_union"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

   "table_union"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

FROM

(

      SELECT

         "as_agg_d9015942_none"."key_c1" AS "key_c1",

         "as_agg_d9015942_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_d9015942_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_d9015942_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_d9015942_none"

   UNION ALL

      SELECT

         "as_agg_607bfeab_none"."key_c1" AS "key_c1",

         "as_agg_607bfeab_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_607bfeab_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_607bfeab_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_607bfeab_none"

) AS "table_union";

as_agg_d9015942_none	2021-08-12 14:23:15.811 -0700
as_agg_607bfeab_none	2021-08-12 12:38:46.284 -0700

value

20180614 32510472 0 2131

CREATE VIEW PETPLAN.ATSCALE."as_agg_c1f91aaa_inc" AS

SELECT

   "table_union"."key_c1" AS "key_c1",

   "table_union"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

   "table_union"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

   "table_union"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

FROM

(

      SELECT

         "as_agg_6597e160_none"."key_c1" AS "key_c1",

         "as_agg_6597e160_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_6597e160_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_6597e160_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_6597e160_none"

   UNION ALL

      SELECT

         "as_agg_90af2c31_none"."key_c1" AS "key_c1",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_90af2c31_none"

) AS "table_union";

as_agg_6597e160_none	2021-08-12 14:23:14.361 -0700
as_agg_90af2c31_none	2021-08-12 12:20:48.532 -0700

value

1068444 0 0 0

49380 137 137 137

It retained the hold historical aggregate + new last build data.

Alternate Scenario

Dimension values have been updated, and new dimensions have been added.

As predicted, the data that contains dimensions will be a full build, but the aggregate that is using keypair value still utilizes the incremental process.

CREATE VIEW PETPLAN.ATSCALE."as_agg_5ae949c8_inc" AS

SELECT

   "table_union"."key_c1" AS "key_c1",

   "table_union"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

   "table_union"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

   "table_union"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

FROM

(

      SELECT

         "as_agg_cb6d2da6_none"."key_c1" AS "key_c1",

         "as_agg_cb6d2da6_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_cb6d2da6_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_cb6d2da6_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_cb6d2da6_none"

   UNION ALL

      SELECT

         "as_agg_90af2c31_none"."key_c1" AS "key_c1",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c2" AS "m_inforceamt_sum_c2",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c3" AS "m_inforceamt_sum_c3",

         "as_agg_90af2c31_none"."m_inforceamt_sum_c4" AS "m_inforceamt_sum_c4"

      FROM

         "PETPLAN"."ATSCALE"."as_agg_90af2c31_none"

) AS "table_union";

as_agg_90af2c31_none	2021-08-12 12:20:48.532 -0700
as_agg_cb6d2da6_none	2021-08-12 15:53:39.565 -0700

Keypair value data can be incremental because during runtime, atscale will do a join between fact to a dimension table based on keypair value.

SELECT

  "t_19"."policy_gbakc2" AS "policy",

  "t_19"."sum_m_inforceamt_s_gbakc3" AS "sum_m_inforceamt_sum_ok"

FROM

  (

    SELECT

      "t_17"."policydisplaynumbe_c16" AS "policy_gbakc2",

      SUM("as_agg_3f3e422a_in_t13"."m_inforceamt_sum_c2") AS "sum_m_inforceamt_s_gbakc3",

      "t_17"."rawpolicyid_c15" AS "policy_gbakc1"

    FROM

      "PETPLAN"."ATSCALE"."as_agg_3f3e422a_inc" AS "as_agg_3f3e422a_in_t13"

      JOIN (

        SELECT

          "policy_inc_t14"."POLICYDISPLAYNUMBER" AS "policydisplaynumbe_c16",

          "policy_inc_t14"."RAWPOLICYID" AS "rawpolicyid_c15"

        FROM

          "PETPLAN"."DIM"."POLICY_INC" AS "policy_inc_t14"

        WHERE

          true

        GROUP BY

          1,

          2

      ) AS "t_17" ON "as_agg_3f3e422a_in_t13"."key_c1" = "t_17"."rawpolicyid_c15"

    WHERE

      true

    GROUP BY

      1,

      3

  ) AS "t_19"

Summary

In summary, when the system defines aggregate, it identifies that the aggregate table is using key join between fact to dimension.

The aggregate will store the join key in the aggregate table.

But if the aggregate is creating a denormalized table (storing a string value in the aggregate)

The value in the aggregate is stored as the literal string value of the dimension.

AtScale would indicate that this aggregate is a non-incremental table.

Only when you enable then, every aggregate table in AtScale will be incremental. But this could cause a problem when you have a different dataset in the aggregate table.

Using standard build

20180907 BAD_DOG BAD_DOG 29486801 0 2131

20180907 NEW_CAT NEW_CAT 3962455 0 958

20180614 NEW_CAT NEW_CAT 3882709 0 913

20180614 BAD_DOG BAD_DOG 28627763 0 2131

20180306 NEW_CAT NEW_CAT 3651746 0 913

20180309 NEW_CAT NEW_CAT 3687558 0 913

20180309 NEW_DOG NEW_DOG 26842945 0 2122

20180307 NEW_DOG NEW_DOG 26657533 0 2122

20180306 NEW_DOG NEW_DOG 26569587 0 2122

20180307 NEW_CAT NEW_CAT 3666808 0 913

Full Build

20180907 BAD_DOG BAD_DOG 29486801 0 2131

20180614 NEW_CAT NEW_CAT 3882709 0 913

20180306 NEW_CAT NEW_CAT 3651746 0 913

20180309 NEW_CAT NEW_CAT 3687558 0 913

20180309 BAD_DOG BAD_DOG 26842945 0 2122

20180614 BAD_DOG BAD_DOG 28627763 0 2131

20180306 BAD_DOG BAD_DOG 26569587 0 2122

20180907 NEW_CAT NEW_CAT 3962455 0 958

20180307 BAD_DOG BAD_DOG 26657533 0 2122

20180307 NEW_CAT NEW_CAT 3666808 0 913

We hope this explains how AtScale handles incremental aggregate builds.