-- set @pbi_date:='7/13/2021'
-- set @db_date_time:='2021-07-13 00:00:00'
-- START CREATION OF SEQUENCE NUMBERS FOR EACH DATASET

-- assign orders to datasets 
insert into `bim_dwh_orders_in_dataset`
(order_id, dataset_id, inserted_at)
select zero_order.order_id, datasets.dataset_id,
       @db_date_time as inserted_at
from
    (select
         0 as order_id) as zero_order
        join (select distinct dataset_id from bim_stg_dataset_scope) as datasets
        left join bim_dwh_orders_in_dataset oid
                  on oid.order_id = zero_order.order_id
                      and datasets.dataset_id = oid.dataset_id
where oid.dataset_id is NULL
UNION
select
    o.order_id, ds.dataset_id as dataset_id,
    @db_date_time as inserted_at
from
    bim_stg_order o
        join bim_stg_dataset_scope ds
             on ds.order_id = 0 AND ds.store_id = o.store_id
        left join `bim_dwh_orders_in_dataset` tmp
                  on tmp.dataset_id = ds.dataset_id
                      and tmp.order_id = o.order_id
WHERE
        CAST(DATE_FORMAT(DATE_SUB((DATE_SUB(STR_TO_DATE(@pbi_date, '%m/%d/%Y'), INTERVAL ds.amount_of_months MONTH)), INTERVAL 1 DAY), '%Y%m%d') AS SIGNED) <=
        floor(o.created_at_datetime/1000000)
  AND tmp.dataset_id is NULL
UNION
select
    ds.order_id, ds.dataset_id as dataset_id,
    @db_date_time as inserted_at
from bim_stg_dataset_scope ds
         left join `bim_dwh_orders_in_dataset` tmp
                   on tmp.dataset_id = ds.dataset_id
                       and tmp.order_id = ds.order_id
WHERE ds.order_id <> 0
  AND tmp.dataset_id is NULL;

-- {{IF_ANALYZE}}bim_dwh_orders_in_dataset{{/IF_ANALYZE}}

-- disable keys
-- ALTER TABLE`sn_dim_shipping_method`DISABLE KEYS;
-- ALTER TABLE`sn_dim_salesrule`DISABLE KEYS;
-- ALTER TABLE`sn_map_salesrule`DISABLE KEYS;
-- ALTER TABLE`sn_dim_product`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order_item`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order_item_parent`DISABLE KEYS;
-- ALTER TABLE`sn_fact_shipment_item`DISABLE KEYS;
-- ALTER TABLE`sn_fact_shipment_item_parent`DISABLE KEYS;
-- ALTER TABLE`sn_fact_invoice_item`DISABLE KEYS;
-- ALTER TABLE`sn_fact_invoice_item_parent`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order_cancel_item`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order_cancel_item_parent`DISABLE KEYS;
-- ALTER TABLE`sn_dim_customer_mini`DISABLE KEYS;
-- ALTER TABLE`sn_dim_customer`DISABLE KEYS;
-- ALTER TABLE`sn_dim_default_billing_address`DISABLE KEYS;
-- ALTER TABLE`sn_dim_default_shipping_address`DISABLE KEYS;
-- ALTER TABLE`sn_dim_store`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order_status_updates`DISABLE KEYS;
-- ALTER TABLE`sn_fact_comment`DISABLE KEYS;
-- ALTER TABLE`sn_fact_credit_memo_item`DISABLE KEYS;
-- ALTER TABLE`sn_fact_credit_memo_item_parent`DISABLE KEYS;
-- ALTER TABLE`sn_dim_shipping_address`DISABLE KEYS;
-- ALTER TABLE`sn_fact_shipment`DISABLE KEYS;
-- ALTER TABLE`sn_dim_billing_address`DISABLE KEYS;
-- ALTER TABLE`sn_fact_invoice`DISABLE KEYS;
-- ALTER TABLE`sn_fact_payment`DISABLE KEYS;
-- ALTER TABLE`sn_dim_payment_method`DISABLE KEYS;
-- ALTER TABLE`sn_fact_credit_memo`DISABLE KEYS;
-- ALTER TABLE`sn_fact_order_cancel`DISABLE KEYS;
-- ALTER TABLE`sn_dim_category`DISABLE KEYS;
-- ALTER TABLE`sn_map_product_category`DISABLE KEYS;
-- ALTER TABLE`sn_dim_attribute_option`DISABLE KEYS;
-- ALTER TABLE`sn_dim_address_details`DISABLE KEYS;
-- ALTER TABLE`sn_map_product_attribute`DISABLE KEYS;
-- ALTER TABLE`sn_dim_order_date`DISABLE KEYS;
-- ALTER TABLE`sn_dim_invoice_date`DISABLE KEYS;
-- ALTER TABLE`sn_dim_order_time`DISABLE KEYS;
-- ALTER TABLE`sn_dim_age`DISABLE KEYS;
-- ALTER TABLE`sn_fact_metadata`DISABLE KEYS;
-- ALTER TABLE`sn_map_order_state`DISABLE KEYS;
-- ALTER TABLE`sn_dim_order_state`DISABLE KEYS;
-- ALTER TABLE`sn_dim_tax_rate`DISABLE KEYS;
-- ALTER TABLE`sn_map_order_tax_rate`DISABLE KEYS;
-- ALTER TABLE`sn_map_order_item_tax_rate`DISABLE KEYS;

-- POPULATE `sn_dim_product`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_product`;
select @max_sn_pk:=coalesce(max(pk_dim_product), -10) from sn_dim_product;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from sn_dim_product;
insert into sn_dim_product
(pk_dim_product, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_product,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_product,
             fin.dataset_id,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset
         from (
                  select
                      ch.pk_dim_product,
                      ch.dataset_id,
                      max(ch.fk_orders_in_dataset) as fk_orders_in_dataset
                  from (
                           select
                               dim.pk_dim_product,
                               d.dataset_id,
                               max(d.pk_orders_in_dataset) as fk_orders_in_dataset
                           from dim_product dim
                                    join fact_order_item oi
                                         on dim.pk_dim_product = oi.fk_dim_product
                                    join bim_dwh_orders_in_dataset d
                                         on oi.order_id = d.order_id
                                    left join sn_dim_product sn
                                              on sn.pk_dim_product = dim.pk_dim_product
                                                  and sn.dataset_id = d.dataset_id
                           WHERE dim.pk_dim_product > @max_sn_pk
                             AND sn.pk_dim_product is NULL
                           GROUP BY dim.pk_dim_product,
                                    d.dataset_id
                           UNION
                           select
                               dim.pk_dim_product,
                               d.dataset_id,
                               max(d.pk_orders_in_dataset) as fk_orders_in_dataset
                           from dim_product dim
                                    join fact_order_item oi
                                         on dim.pk_dim_product = oi.fk_dim_product
                                    join bim_dwh_orders_in_dataset d
                                         on oi.order_id = d.order_id
                                    left join sn_dim_product sn
                                              on sn.pk_dim_product = dim.pk_dim_product
                                                  and sn.dataset_id = d.dataset_id
                           WHERE
                             -- run this part only during initial load
                                   @max_sn_pk <> -10 AND d.pk_orders_in_dataset > @max_sn_order_pk
                             AND sn.pk_dim_product is NULL
                           GROUP BY dim.pk_dim_product,
                                    d.dataset_id
                       ) ch
                  GROUP BY ch.pk_dim_product,
                           ch.dataset_id
                  UNION
                  select
                      par.pk_dim_product,
                      par.dataset_id,
                      max(par.fk_orders_in_dataset) as fk_orders_in_dataset
                  from (
                           select
                               dim.pk_dim_product,
                               dp.dataset_id,
                               max(dp.pk_orders_in_dataset) as fk_orders_in_dataset
                           from dim_product dim
                                    join fact_order_item_parent oip
                                         on dim.pk_dim_product = oip.fk_dim_complex_product
                                    join bim_dwh_orders_in_dataset dp
                                         on oip.order_id = dp.order_id
                                    left join sn_dim_product sn
                                              on sn.pk_dim_product = dim.pk_dim_product
                                                  and sn.dataset_id = dp.dataset_id
                           WHERE dim.pk_dim_product > @max_sn_pk
                             AND sn.pk_dim_product is NULL
                           GROUP BY dim.pk_dim_product,
                                    dp.dataset_id
                           UNION
                           select
                               dim.pk_dim_product,
                               dp.dataset_id,
                               max(dp.pk_orders_in_dataset) as fk_orders_in_dataset
                           from dim_product dim
                                    join fact_order_item_parent oip
                                         on dim.pk_dim_product = oip.fk_dim_complex_product
                                    join bim_dwh_orders_in_dataset dp
                                         on oip.order_id = dp.order_id
                                    left join sn_dim_product sn
                                              on sn.pk_dim_product = dim.pk_dim_product
                                                  and sn.dataset_id = dp.dataset_id
                           WHERE @max_sn_pk <> -10 AND dp.pk_orders_in_dataset > @max_sn_order_pk
                             AND sn.pk_dim_product is NULL
                           GROUP BY dim.pk_dim_product,
                                    dp.dataset_id
                       ) par
                  GROUP BY
                      par.pk_dim_product,
                      par.dataset_id
              ) as fin
         GROUP BY
             fin.pk_dim_product,
             fin.dataset_id
     ) s
order by s.dataset_id, s.pk_dim_product;
-- {{IF_ANALYZE}}sn_dim_product{{/IF_ANALYZE}}
-- Note: do not split these 2 queries, next query depends on previous one, it reuses @rank variable
-- add data for full dimension data extraction mode
insert into sn_dim_product
(pk_dim_product, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    d.pk_dim_product,
    0 as dataset_id,
    0 as fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from dim_product d
         left join sn_dim_product sn on sn.dataset_id = 0 and sn.pk_dim_product = d.pk_dim_product
where sn.pk_dim_product is null;
-- {{IF_ANALYZE}}sn_dim_product{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order_item`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_order_item`;
select @max_sn_pk:=coalesce(max(pk_fact_order_item), 0) from `sn_fact_order_item`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_order_item`;
insert into sn_fact_order_item
(pk_fact_order_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_order_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_order_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      oi.pk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_order_item oi
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_order_item sn
                                     on sn.pk_fact_order_item = oi.pk_fact_order_item
                                         and sn.dataset_id = d.dataset_id
                  WHERE oi.pk_fact_order_item > @max_sn_pk
                    AND sn.pk_fact_order_item is NULL
                  GROUP BY oi.pk_fact_order_item,
                           d.dataset_id
                  UNION
                  select
                      oi.pk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_order_item oi
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_order_item sn
                                     on sn.pk_fact_order_item = oi.pk_fact_order_item
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_order_item is NULL
                  GROUP BY oi.pk_fact_order_item,
                           d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_order_item,
                  fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_order_item;
-- {{IF_ANALYZE}}sn_fact_order_item{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order_item_parent`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_order_item_parent`;
select @max_sn_pk:=coalesce(max(pk_fact_order_item_parent), 0) from `sn_fact_order_item_parent`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_order_item_parent`;
insert into sn_fact_order_item_parent
(pk_fact_order_item_parent, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_order_item_parent,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_order_item_parent,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      oi.pk_fact_order_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_order_item_parent oi
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_order_item_parent sn
                                     on sn.pk_fact_order_item_parent = oi.pk_fact_order_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE oi.pk_fact_order_item_parent > @max_sn_pk
                    AND sn.pk_fact_order_item_parent is NULL
                  GROUP BY oi.pk_fact_order_item_parent, d.dataset_id
                  UNION
                  select
                      oi.pk_fact_order_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_order_item_parent oi
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_order_item_parent sn
                                     on sn.pk_fact_order_item_parent = oi.pk_fact_order_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_order_item_parent is NULL
                  GROUP BY oi.pk_fact_order_item_parent, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_order_item_parent, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_order_item_parent;
-- {{IF_ANALYZE}}sn_fact_order_item_parent{{/IF_ANALYZE}}

-- POPULATE `sn_fact_shipment_item`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_shipment_item`;
select @max_sn_pk:=coalesce(max(pk_fact_shipment_item), 0) from `sn_fact_shipment_item`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_shipment_item`;
insert into sn_fact_shipment_item
(pk_fact_shipment_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_shipment_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_shipment_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (
                 select
                     f.pk_fact_shipment_item,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_shipment_item f
                          join fact_order_item oi
                               on oi.order_item_id = f.order_item_id
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_fact_shipment_item sn
                                    on sn.pk_fact_shipment_item = f.pk_fact_shipment_item
                                        and sn.dataset_id = d.dataset_id
                 WHERE f.pk_fact_shipment_item > @max_sn_pk
                   AND sn.pk_fact_shipment_item is NULL
                 GROUP BY f.pk_fact_shipment_item, d.dataset_id
                 UNION
                 select
                     f.pk_fact_shipment_item,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_shipment_item f
                          join fact_order_item oi
                               on oi.order_item_id = f.order_item_id
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_fact_shipment_item sn
                                    on sn.pk_fact_shipment_item = f.pk_fact_shipment_item
                                        and sn.dataset_id = d.dataset_id
                 WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                   AND sn.pk_fact_shipment_item is NULL
                 GROUP BY f.pk_fact_shipment_item, d.dataset_id
             ) fin
         GROUP BY fin.pk_fact_shipment_item, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_shipment_item;
-- {{IF_ANALYZE}}sn_fact_shipment_item{{/IF_ANALYZE}}

-- POPULATE `sn_fact_shipment_item_parent`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_shipment_item_parent`;
select @max_sn_pk:=coalesce(max(pk_fact_shipment_item_parent), 0) from `sn_fact_shipment_item_parent`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_shipment_item_parent`;
insert into sn_fact_shipment_item_parent
(pk_fact_shipment_item_parent, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_shipment_item_parent,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_shipment_item_parent,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_shipment_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_shipment_item_parent f
                           join fact_order_item_parent oip
                                on oip.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oip.order_id = d.order_id
                           left join sn_fact_shipment_item_parent sn
                                     on sn.pk_fact_shipment_item_parent = f.pk_fact_shipment_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_shipment_item_parent > @max_sn_pk
                    AND sn.pk_fact_shipment_item_parent is NULL
                  GROUP BY f.pk_fact_shipment_item_parent, d.dataset_id
                  UNION
                  select
                      f.pk_fact_shipment_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_shipment_item_parent f
                           join fact_order_item_parent oip
                                on oip.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oip.order_id = d.order_id
                           left join sn_fact_shipment_item_parent sn
                                     on sn.pk_fact_shipment_item_parent = f.pk_fact_shipment_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_shipment_item_parent is NULL
                  GROUP BY f.pk_fact_shipment_item_parent, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_shipment_item_parent, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_shipment_item_parent;
-- {{IF_ANALYZE}}sn_fact_shipment_item_parent{{/IF_ANALYZE}}

-- POPULATE `sn_fact_invoice_item`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_invoice_item`;
select @max_sn_pk:=coalesce(max(pk_fact_invoice_item), 0) from `sn_fact_invoice_item`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_invoice_item`;
insert into sn_fact_invoice_item
(pk_fact_invoice_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_invoice_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_invoice_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (
                 select
                     f.pk_fact_invoice_item,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_invoice_item f
                          join fact_order_item oi
                               on oi.order_item_id = f.order_item_id
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_fact_invoice_item sn
                                    on sn.pk_fact_invoice_item = f.pk_fact_invoice_item
                                        and sn.dataset_id = d.dataset_id
                 WHERE f.pk_fact_invoice_item > @max_sn_pk
                   AND sn.pk_fact_invoice_item is NULL
                 GROUP BY f.pk_fact_invoice_item, d.dataset_id
                 UNION
                 select
                     f.pk_fact_invoice_item,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_invoice_item f
                          join fact_order_item oi
                               on oi.order_item_id = f.order_item_id
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_fact_invoice_item sn
                                    on sn.pk_fact_invoice_item = f.pk_fact_invoice_item
                                        and sn.dataset_id = d.dataset_id
                 WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                   AND sn.pk_fact_invoice_item is NULL
                 GROUP BY f.pk_fact_invoice_item, d.dataset_id
             ) fin
         GROUP BY fin.pk_fact_invoice_item, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_invoice_item;
-- {{IF_ANALYZE}}sn_fact_invoice_item{{/IF_ANALYZE}}

-- POPULATE `sn_fact_invoice_item_parent`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_invoice_item_parent`;
select @max_sn_pk:=coalesce(max(pk_fact_invoice_item_parent), 0) from `sn_fact_invoice_item_parent`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_invoice_item_parent`;
insert into sn_fact_invoice_item_parent
(pk_fact_invoice_item_parent, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_invoice_item_parent,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_invoice_item_parent,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_invoice_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_invoice_item_parent f
                           join fact_order_item_parent oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_invoice_item_parent sn
                                     on sn.pk_fact_invoice_item_parent = f.pk_fact_invoice_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_invoice_item_parent > @max_sn_pk
                    AND sn.pk_fact_invoice_item_parent is NULL
                  GROUP BY f.pk_fact_invoice_item_parent, d.dataset_id
                  UNION
                  select
                      f.pk_fact_invoice_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_invoice_item_parent f
                           join fact_order_item_parent oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_invoice_item_parent sn
                                     on sn.pk_fact_invoice_item_parent = f.pk_fact_invoice_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_invoice_item_parent is NULL
                  GROUP BY f.pk_fact_invoice_item_parent, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_invoice_item_parent, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_invoice_item_parent;
-- {{IF_ANALYZE}}sn_fact_invoice_item_parent{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order_cancel_item`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_order_cancel_item`;
select @max_sn_pk:=coalesce(max(pk_fact_order_cancel_item), 0) from `sn_fact_order_cancel_item`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_order_cancel_item`;
insert into sn_fact_order_cancel_item
(pk_fact_order_cancel_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_order_cancel_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_order_cancel_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_order_cancel_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_order_cancel_item f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_order_cancel_item sn
                                     on sn.pk_fact_order_cancel_item = f.pk_fact_order_cancel_item
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_order_cancel_item > @max_sn_pk
                    AND sn.pk_fact_order_cancel_item is NULL
                  GROUP BY f.pk_fact_order_cancel_item, d.dataset_id
                  UNION
                  select
                      f.pk_fact_order_cancel_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_order_cancel_item f
                           join fact_order_item oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_order_cancel_item sn
                                     on sn.pk_fact_order_cancel_item = f.pk_fact_order_cancel_item
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_order_cancel_item is NULL
                  GROUP BY f.pk_fact_order_cancel_item, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_order_cancel_item, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_order_cancel_item;
-- {{IF_ANALYZE}}sn_fact_order_cancel_item{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order_cancel_item_parent`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_order_cancel_item_parent`;
select @max_sn_pk:=coalesce(max(pk_fact_order_cancel_item_parent), 0) from `sn_fact_order_cancel_item_parent`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_order_cancel_item_parent`;
insert into sn_fact_order_cancel_item_parent
(pk_fact_order_cancel_item_parent, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_order_cancel_item_parent,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_order_cancel_item_parent,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (
                 select
                     f.pk_fact_order_cancel_item_parent,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_order_cancel_item_parent f
                          join fact_order_item_parent oi
                               on oi.order_item_id = f.order_item_id
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_fact_order_cancel_item_parent sn
                                    on sn.pk_fact_order_cancel_item_parent = f.pk_fact_order_cancel_item_parent
                                        and sn.dataset_id = d.dataset_id
                 WHERE f.pk_fact_order_cancel_item_parent > @max_sn_pk
                   AND sn.pk_fact_order_cancel_item_parent is NULL
                 GROUP BY f.pk_fact_order_cancel_item_parent, d.dataset_id
                 UNION
                 select
                     f.pk_fact_order_cancel_item_parent,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_order_cancel_item_parent f
                          join fact_order_item_parent oi
                               on oi.order_item_id = f.order_item_id
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_fact_order_cancel_item_parent sn
                                    on sn.pk_fact_order_cancel_item_parent = f.pk_fact_order_cancel_item_parent
                                        and sn.dataset_id = d.dataset_id
                 WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                   AND sn.pk_fact_order_cancel_item_parent is NULL
                 GROUP BY f.pk_fact_order_cancel_item_parent, d.dataset_id
             ) fin
         GROUP BY fin.pk_fact_order_cancel_item_parent, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_order_cancel_item_parent;
-- {{IF_ANALYZE}}sn_fact_order_cancel_item_parent{{/IF_ANALYZE}}

-- POPULATE `sn_dim_customer_mini`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_customer_mini`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_customer_mini`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_customer_mini`;
insert into sn_dim_customer_mini
(pk_dim_customer_mini, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_customer_mini,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_customer_mini as pk_dim_customer_mini,
             max(fin.fk_fact_order) as fk_fact_order,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      o.fk_dim_customer_mini as pk_dim_customer_mini,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_dim_customer_mini sn
                                     on sn.pk_dim_customer_mini = o.fk_dim_customer_mini
                                         and sn.dataset_id = d.dataset_id
                  WHERE
                    -- get records where with orders not yet inserted into sn_dim_customer_mini
                          o.pk_fact_order > @max_sn_pk
                    -- filter out records that already exist in sn_dim_customer_mini
                    AND sn.pk_dim_customer_mini is NULL
                  GROUP BY o.fk_dim_customer_mini, d.dataset_id
                  UNION
                  select
                      o.fk_dim_customer_mini as pk_dim_customer_mini,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_dim_customer_mini sn
                                     on sn.pk_dim_customer_mini = o.fk_dim_customer_mini
                                         and sn.dataset_id = d.dataset_id
                  WHERE
                    -- provide result only if some records already exist in sn_dim_customer_mini
                          @max_sn_pk <> 0 AND
                    -- get records for for new records in bim_dwh_orders_in_dataset
                          d.pk_orders_in_dataset > @max_sn_order_pk
                    -- filter out records that already exist in sn_dim_customer_mini
                    AND sn.pk_dim_customer_mini is NULL
                  GROUP BY o.fk_dim_customer_mini, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_customer_mini, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_customer_mini;
-- {{IF_ANALYZE}}sn_dim_customer_mini{{/IF_ANALYZE}}

-- POPULATE `sn_dim_customer`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_customer`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_customer`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_customer`;
insert into sn_dim_customer
(pk_dim_customer, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_customer,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_customer as pk_dim_customer,
             max(fin.fk_fact_order) as fk_fact_order,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      o.customer_id as pk_dim_customer,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_dim_customer sn
                                     on sn.pk_dim_customer = o.customer_id
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order > @max_sn_pk
                    AND sn.pk_dim_customer is NULL
                  GROUP BY o.customer_id, d.dataset_id
                  UNION
                  select
                      o.customer_id as pk_dim_customer,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_dim_customer sn
                                     on sn.pk_dim_customer = o.customer_id
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_customer is NULL
                  GROUP BY o.customer_id, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_customer, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_customer;
-- {{IF_ANALYZE}}sn_dim_customer{{/IF_ANALYZE}}
-- Note: do not split these 2 queries, next query depends on previous one, it reuses @rank variable
-- add data for full dimension data extraction mode
insert into sn_dim_customer
(pk_dim_customer, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    d.pk_dim_customer,
    0 as fk_fact_order,
    0 as dataset_id,
    0 as fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from dim_customer d
         left join sn_dim_customer sn on sn.dataset_id = 0 and sn.pk_dim_customer = d.pk_dim_customer
where sn.pk_dim_customer is null;
-- {{IF_ANALYZE}}sn_dim_customer{{/IF_ANALYZE}}

-- POPULATE `sn_dim_default_billing_address`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_default_billing_address`;
select @max_sn_pk:=coalesce(max(pk_dim_default_billing_address), 0) from `sn_dim_default_billing_address`;
insert into sn_dim_default_billing_address
(pk_dim_default_billing_address, dataset_id, sequence_number, inserted_at)
select
    s.pk_dim_default_billing_address,
    s.dataset_id,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             dt.pk_dim_default_billing_address,
             ds.dataset_id
         from dim_default_billing_address dt
                  join (select DISTINCT dataset_id from bim_dwh_orders_in_dataset) ds
                  left join sn_dim_default_billing_address sn
                            on sn.pk_dim_default_billing_address = dt.pk_dim_default_billing_address
                                and sn.dataset_id = ds.dataset_id
         where sn.pk_dim_default_billing_address is NULL
         order by ds.dataset_id, dt.pk_dim_default_billing_address
     ) as s;
-- {{IF_ANALYZE}}sn_dim_default_billing_address{{/IF_ANALYZE}}

-- POPULATE `sn_dim_default_shipping_address`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_default_shipping_address`;
select @max_sn_pk:=coalesce(max(pk_dim_default_shipping_address), 0) from `sn_dim_default_shipping_address`;
insert into sn_dim_default_shipping_address
(pk_dim_default_shipping_address, dataset_id, sequence_number, inserted_at)
select
    s.pk_dim_default_shipping_address,
    s.dataset_id,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             dt.pk_dim_default_shipping_address,
             ds.dataset_id
         from dim_default_shipping_address dt
                  join (select DISTINCT dataset_id from bim_dwh_orders_in_dataset) ds
                  left join sn_dim_default_shipping_address sn
                            on sn.pk_dim_default_shipping_address = dt.pk_dim_default_shipping_address
                                and sn.dataset_id = ds.dataset_id
         WHERE sn.pk_dim_default_shipping_address is NULL
         order by ds.dataset_id, dt.pk_dim_default_shipping_address
     ) as s;
-- {{IF_ANALYZE}}sn_dim_default_shipping_address{{/IF_ANALYZE}}

-- POPULATE `sn_dim_store`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_store`;
insert into sn_dim_store
(pk_dim_store, dataset_id, sequence_number, inserted_at)
select
    s.pk_dim_store,
    s.dataset_id,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             o.fk_dim_store as pk_dim_store,
             max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
             d.dataset_id
         from bim_dwh_orders_in_dataset d
                  join fact_order o
                       on o.order_id = d.order_id
                  left join sn_dim_store sn
                            on sn.pk_dim_store = o.fk_dim_store
                                and sn.dataset_id = d.dataset_id
         WHERE sn.pk_dim_store is NULL
         GROUP BY o.fk_dim_store,
                  d.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_store;
-- {{IF_ANALYZE}}sn_dim_store{{/IF_ANALYZE}}

select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_payment_method`;
-- pk_fact_payment is used to identify the records not yet included into sn
select @max_sn_pk:=coalesce(max(fk_fact_payment), 0) from `sn_dim_payment_method`;
-- this variable helps to find out what new orders needs to be processed
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_payment_method`;
insert into sn_dim_payment_method
(pk_dim_payment_method, fk_fact_payment, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_payment_method,
    s.fk_fact_payment,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_payment_method as pk_dim_payment_method,
             max(fin.fk_fact_payment) as fk_fact_payment,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  -- first query is faster for initial load because it does not involve reading of bim_dwh_orders_in_dataset table
                  -- it uses the primary keys and indexes only
                  select
                      p.fk_payment_method as pk_dim_payment_method,
                      max(p.pk_fact_payment) as fk_fact_payment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_payment p
                                on p.order_id = d.order_id
                           left join sn_dim_payment_method sn
                                     on sn.pk_dim_payment_method = p.fk_payment_method
                                         and sn.dataset_id = d.dataset_id
                       -- query is efficient for initial load of data
                       -- also it will take payment methods from new payment orders created for orders that were already processed
                  WHERE p.pk_fact_payment > @max_sn_pk
                    AND sn.pk_dim_payment_method is NULL
                  GROUP BY p.fk_payment_method, d.dataset_id
                  UNION
                  select
                      p.fk_payment_method as pk_dim_payment_method,
                      max(p.pk_fact_payment) as fk_fact_payment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_payment p
                                on p.order_id = d.order_id
                           left join sn_dim_payment_method sn
                                     on sn.pk_dim_payment_method = p.fk_payment_method
                                         and sn.dataset_id = d.dataset_id
                       -- query not for initial load of data. disabling of this part of the logic helps to reduce the query time for initial run
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_payment_method is NULL
                  GROUP BY p.fk_payment_method, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_payment_method, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_payment_method;
-- {{IF_ANALYZE}}sn_dim_payment_method{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_order`;
select @max_sn_pk:=coalesce(max(pk_fact_order), 0) from `sn_fact_order`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_order`;
insert into sn_fact_order
(pk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_order,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      o.pk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_fact_order sn
                                     on sn.pk_fact_order = o.pk_fact_order
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order > @max_sn_pk
                    AND sn.pk_fact_order is NULL
                  GROUP BY o.pk_fact_order, d.dataset_id
                  UNION
                  select
                      o.pk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_fact_order sn
                                     on sn.pk_fact_order = o.pk_fact_order
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_order is NULL
                  GROUP BY o.pk_fact_order, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_order, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_order;
-- {{IF_ANALYZE}}sn_fact_order{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order_status_updates`;
SELECT @rank:=coalesce(max(sequence_number), 0)*10000 FROM `sn_fact_order_status_updates`;
SELECT @max_sn_pk:=coalesce(max(pk_fact_order_status_updates), 0) FROM `sn_fact_order_status_updates`;
SELECT @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) FROM `sn_fact_order_status_updates`;
INSERT INTO sn_fact_order_status_updates
(pk_fact_order_status_updates, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
SELECT
    s.pk_fact_order_status_updates,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_fact_order_status_updates,
             fin.dataset_id,
             MAX(fin.fk_orders_in_dataset) as fk_orders_in_dataset
         FROM (
                  SELECT
                      o.pk_fact_order_status_updates,
                      d.dataset_id,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset
                  FROM bim_dwh_orders_in_dataset d
                           INNER JOIN fact_order_status_updates o ON o.order_id = d.order_id
                           LEFT JOIN sn_fact_order_status_updates sn ON sn.pk_fact_order_status_updates = o.pk_fact_order_status_updates AND sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order_status_updates > @max_sn_pk AND sn.pk_fact_order_status_updates is NULL
                  GROUP BY o.pk_fact_order_status_updates, d.dataset_id
                  UNION
                  SELECT
                      o.pk_fact_order_status_updates,
                      d.dataset_id,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset
                  FROM bim_dwh_orders_in_dataset d
                           INNER JOIN fact_order_status_updates o ON o.order_id = d.order_id
                           LEFT JOIN sn_fact_order_status_updates sn ON sn.pk_fact_order_status_updates = o.pk_fact_order_status_updates AND sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk AND sn.pk_fact_order_status_updates is NULL
                  GROUP BY o.pk_fact_order_status_updates, d.dataset_id
              ) AS fin
         GROUP BY fin.pk_fact_order_status_updates, fin.dataset_id
     ) AS s
ORDER BY s.dataset_id, s.pk_fact_order_status_updates;
-- {{IF_ANALYZE}}sn_fact_order_status_updates{{/IF_ANALYZE}}

-- POPULATE `sn_fact_comment`;
SELECT @rank:=coalesce(max(sequence_number), 0)*10000 FROM `sn_fact_comment`;
SELECT @max_sn_pk:=coalesce(max(pk_fact_comment), 0) FROM `sn_fact_comment`;
SELECT @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) FROM `sn_fact_comment`;
INSERT INTO sn_fact_comment
(pk_fact_comment, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
SELECT
    s.pk_fact_comment,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_fact_comment,
             fin.dataset_id,
             MAX(fin.fk_orders_in_dataset) as fk_orders_in_dataset
         FROM (
                  SELECT
                      o.pk_fact_comment,
                      d.dataset_id,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset
                  FROM bim_dwh_orders_in_dataset d
                           INNER JOIN fact_comment o ON o.order_id = d.order_id
                           LEFT JOIN sn_fact_comment sn ON sn.pk_fact_comment = o.pk_fact_comment AND sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_comment > @max_sn_pk AND sn.pk_fact_comment is NULL
                  GROUP BY o.pk_fact_comment, d.dataset_id
                  UNION
                  SELECT
                      o.pk_fact_comment,
                      d.dataset_id,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset
                  FROM bim_dwh_orders_in_dataset d
                           INNER JOIN fact_comment o ON o.order_id = d.order_id
                           LEFT JOIN sn_fact_comment sn ON sn.pk_fact_comment = o.pk_fact_comment AND sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk AND sn.pk_fact_comment is NULL
                  GROUP BY o.pk_fact_comment, d.dataset_id
              ) AS fin
         GROUP BY fin.pk_fact_comment, fin.dataset_id
     ) AS s
ORDER BY s.dataset_id, s.pk_fact_comment;
-- {{IF_ANALYZE}}sn_fact_comment{{/IF_ANALYZE}}

-- POPULATE `sn_fact_credit_memo_item`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_credit_memo_item`;
select @max_sn_pk:=coalesce(max(pk_fact_credit_memo_item), 0) from `sn_fact_credit_memo_item`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_credit_memo_item`;
insert into sn_fact_credit_memo_item
(pk_fact_credit_memo_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_credit_memo_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_credit_memo_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_credit_memo_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_credit_memo_item f
                           join fact_order_item oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_credit_memo_item sn
                                     on sn.pk_fact_credit_memo_item = f.pk_fact_credit_memo_item
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_credit_memo_item > @max_sn_pk
                    AND sn.pk_fact_credit_memo_item is NULL
                  GROUP BY f.pk_fact_credit_memo_item, d.dataset_id
                  UNION
                  select
                      f.pk_fact_credit_memo_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_credit_memo_item f
                           join fact_order_item oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_credit_memo_item sn
                                     on sn.pk_fact_credit_memo_item = f.pk_fact_credit_memo_item
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_credit_memo_item is NULL
                  GROUP BY f.pk_fact_credit_memo_item, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_credit_memo_item, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_credit_memo_item;
-- {{IF_ANALYZE}}sn_fact_credit_memo_item{{/IF_ANALYZE}}

-- POPULATE `sn_fact_credit_memo_item_parent`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_credit_memo_item_parent`;
select @max_sn_pk:=coalesce(max(pk_fact_credit_memo_item_parent), 0) from `sn_fact_credit_memo_item_parent`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_credit_memo_item_parent`;
insert into sn_fact_credit_memo_item_parent
(pk_fact_credit_memo_item_parent, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_credit_memo_item_parent,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_credit_memo_item_parent,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_credit_memo_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_credit_memo_item_parent f
                           join fact_order_item_parent oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_credit_memo_item_parent sn
                                     on sn.pk_fact_credit_memo_item_parent = f.pk_fact_credit_memo_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_credit_memo_item_parent > @max_sn_pk
                    AND sn.pk_fact_credit_memo_item_parent is NULL
                  GROUP BY f.pk_fact_credit_memo_item_parent, d.dataset_id
                  UNION
                  select
                      f.pk_fact_credit_memo_item_parent,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_credit_memo_item_parent f
                           join fact_order_item_parent oi
                                on oi.order_item_id = f.order_item_id
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_fact_credit_memo_item_parent sn
                                     on sn.pk_fact_credit_memo_item_parent = f.pk_fact_credit_memo_item_parent
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_credit_memo_item_parent is NULL
                  GROUP BY f.pk_fact_credit_memo_item_parent, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_credit_memo_item_parent, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_credit_memo_item_parent;
-- {{IF_ANALYZE}}sn_fact_credit_memo_item_parent{{/IF_ANALYZE}}

-- POPULATE `sn_dim_shipping_address`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_shipping_address`;
select @max_sn_pk:=coalesce(max(fk_fact_shipment), 0) from `sn_dim_shipping_address`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_shipping_address`;
insert into sn_dim_shipping_address
(pk_dim_shipping_address, fk_fact_shipment, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_shipping_address,
    s.fk_fact_shipment,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_shipping_address as pk_dim_shipping_address,
             max(fin.fk_fact_shipment) as fk_fact_shipment,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      o.fk_shipping_address as pk_dim_shipping_address,
                      max(o.pk_fact_shipment) as fk_fact_shipment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_shipment o
                                on o.order_id = d.order_id
                           left join sn_dim_shipping_address sn
                                     on sn.pk_dim_shipping_address = o.fk_shipping_address
                                         and sn.dataset_id = d.dataset_id
                  where o.pk_fact_shipment > @max_sn_pk
                    and sn.pk_dim_shipping_address is NULL
                  group by o.fk_shipping_address, d.dataset_id
                  UNION
                  select
                      o.fk_shipping_address as pk_dim_shipping_address,
                      max(o.pk_fact_shipment) as fk_fact_shipment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_shipment o
                                on o.order_id = d.order_id
                           left join sn_dim_shipping_address sn
                                     on sn.pk_dim_shipping_address = o.fk_shipping_address
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_shipping_address is NULL
                  GROUP BY o.fk_shipping_address, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_shipping_address, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_shipping_address;
-- {{IF_ANALYZE}}sn_dim_shipping_address{{/IF_ANALYZE}}

-- POPULATE `sn_fact_shipment`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_shipment`;
select @max_sn_pk:=coalesce(max(pk_fact_shipment), 0) from `sn_fact_shipment`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_shipment`;
insert into sn_fact_shipment
(pk_fact_shipment, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_shipment,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_shipment,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_shipment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_shipment f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_shipment sn
                                     on sn.pk_fact_shipment = f.pk_fact_shipment
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_shipment > @max_sn_pk
                    AND sn.pk_fact_shipment is NULL
                  GROUP BY f.pk_fact_shipment, d.dataset_id
                  UNION
                  select
                      f.pk_fact_shipment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_shipment f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_shipment sn
                                     on sn.pk_fact_shipment = f.pk_fact_shipment
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_shipment is NULL
                  GROUP BY f.pk_fact_shipment, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_shipment, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_shipment;
-- {{IF_ANALYZE}}sn_fact_shipment{{/IF_ANALYZE}}

-- POPULATE `sn_dim_billing_address`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_billing_address`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_billing_address`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_billing_address`;
insert into sn_dim_billing_address
(pk_dim_billing_address, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_billing_address,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_billing_address as pk_dim_billing_address,
             max(fin.fk_fact_order) as fk_fact_order,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      o.fk_billing_address        as pk_dim_billing_address,
                      max(o.pk_fact_order)      as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_dim_billing_address sn
                                     on sn.pk_dim_billing_address = o.fk_billing_address
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order > @max_sn_pk
                    AND sn.pk_dim_billing_address is NULL
                  GROUP BY o.fk_billing_address, d.dataset_id
                  UNION
                  select
                      o.fk_billing_address        as pk_dim_billing_address,
                      max(o.pk_fact_order)      as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order o
                                on o.order_id = d.order_id
                           left join sn_dim_billing_address sn
                                     on sn.pk_dim_billing_address = o.fk_billing_address
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_billing_address is NULL
                  GROUP BY o.fk_billing_address, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_billing_address, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_billing_address;
-- {{IF_ANALYZE}}sn_dim_billing_address{{/IF_ANALYZE}}

-- POPULATE `sn_fact_invoice`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_invoice`;
select @max_sn_pk:=coalesce(max(pk_fact_invoice), 0) from `sn_fact_invoice`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_invoice`;
insert into sn_fact_invoice
(pk_fact_invoice, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_invoice,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_invoice,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_invoice,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_invoice f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_invoice sn
                                     on sn.pk_fact_invoice = f.pk_fact_invoice
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_invoice > @max_sn_pk
                    AND sn.pk_fact_invoice is NULL
                  GROUP BY f.pk_fact_invoice, d.dataset_id
                  UNION
                  select
                      f.pk_fact_invoice,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_invoice f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_invoice sn
                                     on sn.pk_fact_invoice = f.pk_fact_invoice
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_invoice is NULL
                  GROUP BY f.pk_fact_invoice, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_invoice, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_invoice;
-- {{IF_ANALYZE}}sn_fact_invoice{{/IF_ANALYZE}}

-- POPULATE `sn_fact_payment`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_payment`;
select @max_sn_pk:=coalesce(max(pk_fact_payment), 0) from `sn_fact_payment`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_payment`;
insert into sn_fact_payment
(pk_fact_payment, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_payment,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_payment,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_payment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_payment f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_payment sn
                                     on sn.pk_fact_payment = f.pk_fact_payment
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_payment > @max_sn_pk
                    AND sn.pk_fact_payment is NULL
                  GROUP BY f.pk_fact_payment, d.dataset_id
                  UNION
                  select
                      f.pk_fact_payment,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_payment f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_payment sn
                                     on sn.pk_fact_payment = f.pk_fact_payment
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_payment is NULL
                  GROUP BY f.pk_fact_payment, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_payment, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_payment;
-- {{IF_ANALYZE}}sn_fact_payment{{/IF_ANALYZE}}

-- POPULATE `sn_fact_credit_memo`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_credit_memo`;
select @max_sn_pk:=coalesce(max(pk_fact_credit_memo), 0) from `sn_fact_credit_memo`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_credit_memo`;
insert into sn_fact_credit_memo
(pk_fact_credit_memo, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_credit_memo,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_credit_memo,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      f.pk_fact_credit_memo,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_credit_memo f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_credit_memo sn
                                     on sn.pk_fact_credit_memo = f.pk_fact_credit_memo
                                         and sn.dataset_id = d.dataset_id
                  WHERE f.pk_fact_credit_memo > @max_sn_pk
                    AND sn.pk_fact_credit_memo is NULL
                  GROUP BY f.pk_fact_credit_memo, d.dataset_id
                  UNION
                  select
                      f.pk_fact_credit_memo,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from fact_credit_memo f
                           join bim_dwh_orders_in_dataset d
                                on f.order_id = d.order_id
                           left join sn_fact_credit_memo sn
                                     on sn.pk_fact_credit_memo = f.pk_fact_credit_memo
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_fact_credit_memo is NULL
                  GROUP BY f.pk_fact_credit_memo, d.dataset_id
              ) fin
         GROUP BY fin.pk_fact_credit_memo, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_credit_memo;
-- {{IF_ANALYZE}}sn_fact_credit_memo{{/IF_ANALYZE}}

-- POPULATE `sn_fact_order_cancel`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_order_cancel`;
select @max_sn_pk:=coalesce(max(pk_fact_order_cancel), 0) from `sn_fact_order_cancel`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_fact_order_cancel`;
insert into sn_fact_order_cancel
(pk_fact_order_cancel, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_fact_order_cancel,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_fact_order_cancel,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (
                 select
                     f.pk_fact_order_cancel,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_order_cancel f
                          join bim_dwh_orders_in_dataset d
                               on f.order_id = d.order_id
                          left join sn_fact_order_cancel sn
                                    on sn.pk_fact_order_cancel = f.pk_fact_order_cancel
                                        and sn.dataset_id = d.dataset_id
                 WHERE f.pk_fact_order_cancel > @max_sn_pk
                   AND sn.pk_fact_order_cancel is NULL
                 GROUP BY f.pk_fact_order_cancel, d.dataset_id
                 UNION
                 select
                     f.pk_fact_order_cancel,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from fact_order_cancel f
                          join bim_dwh_orders_in_dataset d
                               on f.order_id = d.order_id
                          left join sn_fact_order_cancel sn
                                    on sn.pk_fact_order_cancel = f.pk_fact_order_cancel
                                        and sn.dataset_id = d.dataset_id
                 WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                   AND sn.pk_fact_order_cancel is NULL
                 GROUP BY f.pk_fact_order_cancel, d.dataset_id
             ) fin
         GROUP BY fin.pk_fact_order_cancel, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_fact_order_cancel;
-- {{IF_ANALYZE}}sn_fact_order_cancel{{/IF_ANALYZE}}

-- POPULATE `sn_dim_category`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_category`;
select @max_sn_pk:=coalesce(max(fk_fact_order_item), 0) from `sn_dim_category`;
select @max_sn_map_pk:=coalesce(max(pk_map_product_category), 0) from `sn_map_product_category`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_category`;
insert into sn_dim_category
(pk_dim_category, fk_fact_order_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_category,
    s.fk_fact_order_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_category as pk_dim_category,
             max(fin.fk_fact_order_item) as fk_fact_order_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      map.fk_dim_category         as pk_dim_category,
                      max(o.pk_fact_order_item)   as fk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order_item o
                                on o.order_id = d.order_id
                           join map_product_category map
                                on map.fk_dim_product = o.fk_dim_product
                           left join sn_dim_category sn
                                     on sn.pk_dim_category = map.fk_dim_category
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order_item > @max_sn_pk
                    AND sn.pk_dim_category is NULL
                  GROUP BY map.fk_dim_category, d.dataset_id
                  UNION
                  select
                      map.fk_dim_category         as pk_dim_category,
                      max(o.pk_fact_order_item)   as fk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order_item o
                                on o.order_id = d.order_id
                           join map_product_category map
                                on map.fk_dim_product = o.fk_dim_product
                           left join sn_dim_category sn
                                     on sn.pk_dim_category = map.fk_dim_category
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_category is NULL
                  GROUP BY map.fk_dim_category, d.dataset_id
                  UNION
                  -- handle the case when category was added to existing order
                  select
                      map.fk_dim_category         as pk_dim_category,
                      max(o.pk_fact_order_item)   as fk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from bim_dwh_orders_in_dataset d
                           join fact_order_item o
                                on o.order_id = d.order_id
                           join map_product_category map
                                on map.fk_dim_product = o.fk_dim_product
                           left join sn_dim_category sn
                                     on sn.pk_dim_category = map.fk_dim_category
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND map.pk_map_product_category > @max_sn_map_pk
                    AND sn.pk_dim_category is NULL
                  GROUP BY map.fk_dim_category, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_category, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_category;
-- {{IF_ANALYZE}}sn_dim_category{{/IF_ANALYZE}}
-- Note: do not split these 2 queries, next query depends on previous one, it reuses @rank variable
-- add data for full dimension data extraction mode
insert into sn_dim_category
(pk_dim_category, fk_fact_order_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    d.pk_dim_category,
    0 as fk_fact_order_item,
    0 as dataset_id,
    0 as fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from dim_category d
         left join sn_dim_category sn on sn.dataset_id = 0 and sn.pk_dim_category = d.pk_dim_category
where sn.pk_dim_category is null;
-- {{IF_ANALYZE}}sn_dim_category{{/IF_ANALYZE}}

-- POPULATE `sn_map_product_category`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_map_product_category`;
select @max_sn_pk:=coalesce(max(pk_map_product_category), 0) from `sn_map_product_category`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_map_product_category`;
insert into sn_map_product_category
(pk_map_product_category, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_map_product_category,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_map_product_category as pk_map_product_category,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (
                 select
                     map.pk_map_product_category as pk_map_product_category,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from bim_dwh_orders_in_dataset d
                          join fact_order_item o
                               on o.order_id = d.order_id
                          join map_product_category map
                               on map.fk_dim_product = o.fk_dim_product
                          left join sn_map_product_category sn
                                    on sn.pk_map_product_category = map.pk_map_product_category
                                        and sn.dataset_id = d.dataset_id
                 WHERE map.pk_map_product_category > @max_sn_pk
                   AND sn.pk_map_product_category is NULL
                 GROUP BY map.pk_map_product_category, d.dataset_id
                 UNION
                 select
                     map.pk_map_product_category as pk_map_product_category,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from bim_dwh_orders_in_dataset d
                          join fact_order_item o
                               on o.order_id = d.order_id
                          join map_product_category map
                               on map.fk_dim_product = o.fk_dim_product
                          left join sn_map_product_category sn
                                    on sn.pk_map_product_category = map.pk_map_product_category
                                        and sn.dataset_id = d.dataset_id
                 WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                   AND sn.pk_map_product_category is NULL
                 GROUP BY map.pk_map_product_category, d.dataset_id
             ) fin
         GROUP BY fin.pk_map_product_category, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_map_product_category;
-- {{IF_ANALYZE}}sn_map_product_category{{/IF_ANALYZE}}
-- Note: do not split these 2 queries, next query depends on previous one, it reuses @rank variable
-- add data for full dimension data extraction mode
insert into sn_map_product_category
(pk_map_product_category, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    d.pk_map_product_category,
    0 as dataset_id,
    0 as fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from map_product_category d
         left join sn_map_product_category sn on sn.dataset_id = 0 and sn.pk_map_product_category = d.pk_map_product_category
where sn.pk_map_product_category is null;
-- {{IF_ANALYZE}}sn_map_product_category{{/IF_ANALYZE}}

-- POPULATE `sn_dim_attribute_option`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_attribute_option`;
select @max_sn_pk:=coalesce(max(fk_fact_order_item), 0) from `sn_dim_attribute_option`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_attribute_option`;
insert into sn_dim_attribute_option
(pk_dim_attribute_option, fk_fact_order_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_attribute_option,
    s.fk_fact_order_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_attribute_option as pk_dim_attribute_option,
             max(fin.fk_fact_order_item) as fk_fact_order_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      map.fk_dim_attribute_option as pk_dim_attribute_option,
                      max(oi.pk_fact_order_item) as fk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from map_product_attribute map
                           join fact_order_item oi
                                on map.fk_dim_product = oi.fk_dim_product
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_dim_attribute_option sn
                                     on sn.pk_dim_attribute_option = map.fk_dim_attribute_option
                                         and sn.dataset_id = d.dataset_id
                  WHERE oi.pk_fact_order_item > @max_sn_pk
                    AND sn.pk_dim_attribute_option is NULL
                  GROUP BY map.fk_dim_attribute_option, d.dataset_id
                  UNION
                  select
                      map.fk_dim_attribute_option as pk_dim_attribute_option,
                      max(oi.pk_fact_order_item) as fk_fact_order_item,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from map_product_attribute map
                           join fact_order_item oi
                                on map.fk_dim_product = oi.fk_dim_product
                           join bim_dwh_orders_in_dataset d
                                on oi.order_id = d.order_id
                           left join sn_dim_attribute_option sn
                                     on sn.pk_dim_attribute_option = map.fk_dim_attribute_option
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_attribute_option is NULL
                  GROUP BY map.fk_dim_attribute_option, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_attribute_option, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_attribute_option;
-- {{IF_ANALYZE}}sn_dim_attribute_option{{/IF_ANALYZE}}
-- Note: do not split these 2 queries, next query depends on previous one, it reuses @rank variable
-- add data for full dimension data extraction mode
insert into sn_dim_attribute_option
(pk_dim_attribute_option, fk_fact_order_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    d.pk_dim_attribute_option,
    0 as fk_fact_order_item,
    0 as dataset_id,
    0 as fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from dim_attribute_option d
         left join sn_dim_attribute_option sn on sn.dataset_id = 0 and sn.pk_dim_attribute_option = d.pk_dim_attribute_option
where sn.pk_dim_attribute_option is null;
-- {{IF_ANALYZE}}sn_dim_attribute_option{{/IF_ANALYZE}}

-- POPULATE `sn_map_product_attribute`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_map_product_attribute`;
select @max_sn_pk:=coalesce(max(pk_map_product_attribute), 0) from `sn_map_product_attribute`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_map_product_attribute`;
insert into sn_map_product_attribute
(pk_map_product_attribute, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_map_product_attribute,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_map_product_attribute,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (
                 select
                     map.pk_map_product_attribute,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from map_product_attribute map
                          join fact_order_item oi
                               on map.fk_dim_product = oi.fk_dim_product
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_map_product_attribute sn
                                    on sn.pk_map_product_attribute = map.pk_map_product_attribute
                                        and sn.dataset_id = d.dataset_id
                 WHERE map.pk_map_product_attribute > @max_sn_pk
                   AND sn.pk_map_product_attribute is NULL
                 GROUP BY map.pk_map_product_attribute, d.dataset_id
                 UNION
                 select
                     map.pk_map_product_attribute,
                     max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                     d.dataset_id
                 from map_product_attribute map
                          join fact_order_item oi
                               on map.fk_dim_product = oi.fk_dim_product
                          join bim_dwh_orders_in_dataset d
                               on oi.order_id = d.order_id
                          left join sn_map_product_attribute sn
                                    on sn.pk_map_product_attribute = map.pk_map_product_attribute
                                        and sn.dataset_id = d.dataset_id
                 WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                   AND sn.pk_map_product_attribute is NULL
                 GROUP BY map.pk_map_product_attribute, d.dataset_id
             ) fin
         GROUP BY fin.pk_map_product_attribute, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_map_product_attribute;
-- {{IF_ANALYZE}}sn_map_product_attribute{{/IF_ANALYZE}}
-- Note: do not split these 2 queries, next query depends on previous one, it reuses @rank variable
-- add data for full dimension data extraction mode
insert into sn_map_product_attribute
(pk_map_product_attribute, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    d.pk_map_product_attribute,
    0 as dataset_id,
    0 as fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from map_product_attribute d
         left join sn_map_product_attribute sn on sn.dataset_id = 0 and sn.pk_map_product_attribute = d.pk_map_product_attribute
where sn.pk_map_product_attribute is null;
-- {{IF_ANALYZE}}sn_map_product_attribute{{/IF_ANALYZE}}

-- POPULATE `sn_dim_age`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_age`;
select @max_sn_pk:=coalesce(max(pk_dim_age), 0) from `sn_dim_age`;
insert into sn_dim_age
(pk_dim_age, dataset_id, sequence_number, inserted_at)
select
    s.pk_dim_age,
    s.dataset_id,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             dt.pk_dim_age,
             ds.dataset_id
         from dim_age dt
                  join (select DISTINCT dataset_id from bim_dwh_orders_in_dataset) ds
                  left join sn_dim_age sn
                            on sn.pk_dim_age = dt.pk_dim_age
                                and sn.dataset_id = ds.dataset_id
         WHERE sn.pk_dim_age is NULL
         order by ds.dataset_id, dt.pk_dim_age
     ) as s;
-- {{IF_ANALYZE}}sn_dim_age{{/IF_ANALYZE}}

-- POPULATE `sn_dim_order_date`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_order_date`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_order_date`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_order_date`;
insert into sn_dim_order_date
(pk_dim_order_date, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_order_date,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_order_date,
             max(fin.fk_fact_order) as fk_fact_order,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  select
                      dim.pk_dim_order_date,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_order_date dim
                           join fact_order o
                                on o.fk_order_date = dim.pk_dim_order_date
                           join bim_dwh_orders_in_dataset d
                                on o.order_id = d.order_id
                           left join sn_dim_order_date sn
                                     on sn.pk_dim_order_date = dim.pk_dim_order_date
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order > @max_sn_pk
                    AND sn.pk_dim_order_date is NULL
                  GROUP BY dim.pk_dim_order_date, d.dataset_id
                  UNION
                  select
                      dim.pk_dim_order_date,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_order_date dim
                           join fact_order o
                                on o.fk_order_date = dim.pk_dim_order_date
                           join bim_dwh_orders_in_dataset d
                                on o.order_id = d.order_id
                           left join sn_dim_order_date sn
                                     on sn.pk_dim_order_date = dim.pk_dim_order_date
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_order_date is NULL
                  GROUP BY dim.pk_dim_order_date, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_order_date, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_order_date;
-- {{IF_ANALYZE}}sn_dim_order_date{{/IF_ANALYZE}}

-- POPULATE `sn_dim_invoice_date`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_invoice_date`;
select @max_sn_pk:=coalesce(max(fk_fact_invoice), 0) from `sn_dim_invoice_date`;
select @max_sn_invoice_pk:=coalesce(max(fk_invoices_in_dataset), 0) from `sn_dim_invoice_date`;
insert into sn_dim_invoice_date
(pk_dim_invoice_date, fk_fact_invoice, dataset_id, fk_invoices_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_invoice_date,
    s.fk_fact_invoice,
    s.dataset_id,
    s.fk_invoices_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_invoice_date as pk_dim_invoice_date,
             max(fin.fk_fact_invoice) as fk_fact_invoice,
             max(fin.fk_invoices_in_dataset) as fk_invoices_in_dataset,
             fin.dataset_id
         from (
                  select
                      dim.pk_dim_invoice_date as pk_dim_invoice_date,
                      max(o.pk_fact_invoice) as fk_fact_invoice,
                      max(d.pk_orders_in_dataset) as fk_invoices_in_dataset,
                      d.dataset_id
                  from dim_invoice_date dim
                           join fact_invoice o
                                on o.fk_invoice_date = dim.pk_dim_invoice_date
                           join bim_dwh_orders_in_dataset d
                                on o.order_id = d.order_id
                           left join sn_dim_invoice_date sn
                                     on sn.pk_dim_invoice_date = dim.pk_dim_invoice_date
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_invoice > @max_sn_pk
                    AND sn.pk_dim_invoice_date is NULL
                  GROUP BY dim.pk_dim_invoice_date, d.dataset_id
                  UNION
                  select
                      dim.pk_dim_invoice_date,
                      max(o.pk_fact_invoice) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_invoice_date dim
                           join fact_invoice o
                                on o.fk_invoice_date = dim.pk_dim_invoice_date
                           join bim_dwh_orders_in_dataset d
                                on o.order_id = d.order_id
                           left join sn_dim_invoice_date sn
                                     on sn.pk_dim_invoice_date = dim.pk_dim_invoice_date
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_invoice_pk
                    AND sn.pk_dim_invoice_date is NULL
                  GROUP BY dim.pk_dim_invoice_date, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_invoice_date, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_invoice_date;
-- {{IF_ANALYZE}}sn_dim_invoice_date{{/IF_ANALYZE}}

-- POPULATE `sn_dim_order_time`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_order_time`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_order_time`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_order_time`;
insert into sn_dim_order_time
(pk_dim_order_time, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_order_time,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_dim_order_time,
             max(fin.fk_fact_order) as fk_fact_order,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         FROM (
                  select
                      dim.pk_dim_order_time,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_order_time dim
                           join fact_order o
                                on o.fk_order_time = dim.pk_dim_order_time
                           join bim_dwh_orders_in_dataset d
                                on o.order_id = d.order_id
                           left join sn_dim_order_time sn
                                     on sn.pk_dim_order_time = dim.pk_dim_order_time
                                         and sn.dataset_id = d.dataset_id
                  WHERE o.pk_fact_order > @max_sn_pk
                    AND sn.pk_dim_order_time is NULL
                  GROUP BY dim.pk_dim_order_time,
                           d.dataset_id
                  UNION
                  select
                      dim.pk_dim_order_time,
                      max(o.pk_fact_order) as fk_fact_order,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_order_time dim
                           join fact_order o
                                on o.fk_order_time = dim.pk_dim_order_time
                           join bim_dwh_orders_in_dataset d
                                on o.order_id = d.order_id
                           left join sn_dim_order_time sn
                                     on sn.pk_dim_order_time = dim.pk_dim_order_time
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_order_time is NULL
                  GROUP BY dim.pk_dim_order_time,
                           d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_order_time, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_order_time;
-- {{IF_ANALYZE}}sn_dim_order_time{{/IF_ANALYZE}}

-- POPULATE `sn_dim_salesrule`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_salesrule`;
select @max_sn_pk:=coalesce(max(fk_fact_order_item), 0) from `sn_dim_salesrule`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_salesrule`;
insert into sn_dim_salesrule
(pk_dim_salesrule, fk_fact_order_item, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_salesrule,
    s.fk_fact_order_item,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_dim_salesrule as pk_dim_salesrule,
             max(fin.fk_fact_order_item) as fk_fact_order_item,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         FROM (
                  SELECT
                      dim.pk_dim_salesrule as pk_dim_salesrule,
                      0 as fk_fact_order_item,
                      0 as fk_orders_in_dataset,
                      ds.dataset_id
                  FROM dim_salesrule dim
                           join (select distinct dataset_id from bim_dwh_orders_in_dataset) ds
                           left JOIN sn_dim_salesrule sn
                                     on sn.pk_dim_salesrule = dim.pk_dim_salesrule
                                         and sn.dataset_id = ds.dataset_id
                  WHERE dim.pk_dim_salesrule IN(-1, -2)
                    AND sn.pk_dim_salesrule is NULL
                  GROUP BY dim.pk_dim_salesrule, ds.dataset_id
                  UNION
                  SELECT
                      m.fk_dim_salesrule as pk_dim_salesrule,
                      max(oi.pk_fact_order_item) as fk_fact_order_item,
                      max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                      ds.dataset_id
                  FROM map_salesrule m
                           join fact_order_item oi
                                on oi.order_item_id = m.order_item_id
                           join bim_dwh_orders_in_dataset ds
                                on oi.order_id = ds.order_id
                           left JOIN sn_dim_salesrule sn
                                     on sn.pk_dim_salesrule = m.fk_dim_salesrule
                                         and sn.dataset_id = ds.dataset_id
                  WHERE oi.pk_fact_order_item > @max_sn_pk
                    AND sn.pk_dim_salesrule is NULL
                  GROUP BY m.fk_dim_salesrule, ds.dataset_id
                  UNION
                  SELECT
                      m.fk_dim_salesrule as pk_dim_salesrule,
                      max(oi.pk_fact_order_item) as fk_fact_order_item,
                      max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                      ds.dataset_id
                  FROM map_salesrule m
                           join fact_order_item oi
                                on oi.order_item_id = m.order_item_id
                           join bim_dwh_orders_in_dataset ds
                                on oi.order_id = ds.order_id
                           left JOIN sn_dim_salesrule sn
                                     on sn.pk_dim_salesrule = m.fk_dim_salesrule
                                         and sn.dataset_id = ds.dataset_id
                  WHERE @max_sn_pk <> 0 AND ds.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_salesrule is NULL
                  GROUP BY m.fk_dim_salesrule, ds.dataset_id
              ) fin
         GROUP BY fin.pk_dim_salesrule, fin.dataset_id
     ) s
order by s.dataset_id, s.pk_dim_salesrule;
-- {{IF_ANALYZE}}sn_dim_salesrule{{/IF_ANALYZE}}

-- POPULATE `sn_map_salesrule`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_map_salesrule`;
select @max_sn_pk:=coalesce(max(pk_map_salesrule), 0) from `sn_map_salesrule`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_map_salesrule`;
insert into sn_map_salesrule
(pk_map_salesrule, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_map_salesrule,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_map_salesrule,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         FROM
             ( SELECT
                   m.pk_map_salesrule,
                   max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                   ds.dataset_id
               FROM map_salesrule m
                        join fact_order_item oi
                             on oi.order_item_id = m.order_item_id
                        join bim_dwh_orders_in_dataset ds
                             on oi.order_id = ds.order_id
                        left JOIN sn_map_salesrule sn
                                  on sn.pk_map_salesrule = m.pk_map_salesrule
                                      and sn.dataset_id = ds.dataset_id
               WHERE m.pk_map_salesrule > @max_sn_pk
                 AND sn.pk_map_salesrule is NULL
               GROUP BY m.pk_map_salesrule, ds.dataset_id
               UNION
               SELECT
                   m.pk_map_salesrule,
                   max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                   ds.dataset_id
               FROM map_salesrule m
                        join fact_order_item oi
                             on oi.order_item_id = m.order_item_id
                        join bim_dwh_orders_in_dataset ds
                             on oi.order_id = ds.order_id
                        left JOIN sn_map_salesrule sn
                                  on sn.pk_map_salesrule = m.pk_map_salesrule
                                      and sn.dataset_id = ds.dataset_id
               WHERE @max_sn_pk <> 0 AND ds.pk_orders_in_dataset > @max_sn_order_pk
                 AND sn.pk_map_salesrule is NULL
               GROUP BY m.pk_map_salesrule, ds.dataset_id
             ) fin
         GROUP BY fin.pk_map_salesrule, fin.dataset_id
     ) s
order by s.dataset_id, s.pk_map_salesrule;
-- {{IF_ANALYZE}}sn_map_salesrule{{/IF_ANALYZE}}

-- POPULATE `sn_dim_tax_rate`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_tax_rate`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_tax_rate`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_tax_rate`;
insert into sn_dim_tax_rate
(pk_dim_tax_rate, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_tax_rate,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_dim_tax_rate            as pk_dim_tax_rate,
             max(fin.fk_fact_order)         as fk_fact_order,
             max(fin.fk_orders_in_dataset)  as fk_orders_in_dataset,
             fin.dataset_id
         FROM
             (SELECT
                  m.fk_dim_tax_rate            as pk_dim_tax_rate,
                  max(o.pk_fact_order)         as fk_fact_order,
                  max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                  ds.dataset_id
              FROM map_order_tax_rate m
                       join bim_dwh_orders_in_dataset ds
                            on ds.order_id = m.order_id
                       join fact_order o
                            on m.order_id = o.order_id
                       left JOIN sn_dim_tax_rate sn
                                 on sn.pk_dim_tax_rate = m.fk_dim_tax_rate
                                     and sn.dataset_id = ds.dataset_id
              WHERE o.pk_fact_order > @max_sn_pk
                AND sn.pk_dim_tax_rate is NULL
              GROUP BY m.fk_dim_tax_rate, ds.dataset_id
              UNION
              SELECT
                  m.fk_dim_tax_rate            as pk_dim_tax_rate,
                  max(o.pk_fact_order)         as fk_fact_order,
                  max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                  ds.dataset_id
              FROM map_order_tax_rate m
                       join bim_dwh_orders_in_dataset ds
                            on ds.order_id = m.order_id
                       join fact_order o
                            on m.order_id = o.order_id
                       left JOIN sn_dim_tax_rate sn
                                 on sn.pk_dim_tax_rate = m.fk_dim_tax_rate
                                     and sn.dataset_id = ds.dataset_id
              WHERE @max_sn_pk <> 0 AND ds.pk_orders_in_dataset > @max_sn_order_pk
                AND sn.pk_dim_tax_rate is NULL
              GROUP BY m.fk_dim_tax_rate, ds.dataset_id
             ) fin
         GROUP BY fin.pk_dim_tax_rate, fin.dataset_id
     ) s
order by s.dataset_id, s.pk_dim_tax_rate;
-- {{IF_ANALYZE}}sn_dim_tax_rate{{/IF_ANALYZE}}

-- POPULATE `sn_map_order_tax_rate`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_map_order_tax_rate`;
select @max_sn_pk:=coalesce(max(pk_map_order_tax_rate), 0) from `sn_map_order_tax_rate`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_map_order_tax_rate`;
insert into sn_map_order_tax_rate
(pk_map_order_tax_rate, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_map_order_tax_rate,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_map_order_tax_rate as pk_map_order_tax_rate,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         FROM
             (SELECT
                  m.pk_map_order_tax_rate as pk_map_order_tax_rate,
                  max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                  ds.dataset_id
              FROM map_order_tax_rate m
                       join bim_dwh_orders_in_dataset ds
                            on ds.order_id = m.order_id
                       left JOIN sn_map_order_tax_rate sn
                                 on sn.pk_map_order_tax_rate = m.pk_map_order_tax_rate
                                     and sn.dataset_id = ds.dataset_id
              WHERE m.pk_map_order_tax_rate > @max_sn_pk
                AND sn.pk_map_order_tax_rate is NULL
              GROUP BY m.pk_map_order_tax_rate, ds.dataset_id
              UNION
              SELECT
                  m.pk_map_order_tax_rate as pk_map_order_tax_rate,
                  max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                  ds.dataset_id
              FROM map_order_tax_rate m
                       join bim_dwh_orders_in_dataset ds
                            on ds.order_id = m.order_id
                       left JOIN sn_map_order_tax_rate sn
                                 on sn.pk_map_order_tax_rate = m.pk_map_order_tax_rate
                                     and sn.dataset_id = ds.dataset_id
              WHERE @max_sn_pk <> 0 AND ds.pk_orders_in_dataset > @max_sn_order_pk
                AND sn.pk_map_order_tax_rate is NULL
              GROUP BY m.pk_map_order_tax_rate, ds.dataset_id
             ) fin
         GROUP BY fin.pk_map_order_tax_rate, fin.dataset_id
     ) s
order by s.dataset_id, s.pk_map_order_tax_rate;
-- {{IF_ANALYZE}}sn_map_order_tax_rate{{/IF_ANALYZE}}

-- POPULATE `sn_map_order_item_tax_rate`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_map_order_item_tax_rate`;
select @max_sn_pk:=coalesce(max(pk_map_order_item_tax_rate), 0) from `sn_map_order_item_tax_rate`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_map_order_item_tax_rate`;
insert into sn_map_order_item_tax_rate
(pk_map_order_item_tax_rate, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_map_order_item_tax_rate,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             fin.pk_map_order_item_tax_rate as pk_map_order_item_tax_rate,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         FROM (
                  SELECT
                      m.pk_map_order_item_tax_rate as pk_map_order_item_tax_rate,
                      max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                      ds.dataset_id
                  FROM map_order_item_tax_rate m
                           join fact_order_item oi
                                on oi.order_item_id = m.order_item_id
                           join bim_dwh_orders_in_dataset ds
                                on ds.order_id = oi.order_id
                           left JOIN sn_map_order_item_tax_rate sn
                                     on sn.pk_map_order_item_tax_rate = m.pk_map_order_item_tax_rate
                                         and sn.dataset_id = ds.dataset_id
                  WHERE m.pk_map_order_item_tax_rate > @max_sn_pk
                    AND sn.pk_map_order_item_tax_rate is NULL
                  GROUP BY m.pk_map_order_item_tax_rate, ds.dataset_id
                  UNION
                  SELECT
                      m.pk_map_order_item_tax_rate as pk_map_order_item_tax_rate,
                      max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
                      ds.dataset_id
                  FROM map_order_item_tax_rate m
                           join fact_order_item oi
                                on oi.order_item_id = m.order_item_id
                           join bim_dwh_orders_in_dataset ds
                                on ds.order_id = oi.order_id
                           left JOIN sn_map_order_item_tax_rate sn
                                     on sn.pk_map_order_item_tax_rate = m.pk_map_order_item_tax_rate
                                         and sn.dataset_id = ds.dataset_id
                  WHERE @max_sn_pk <> 0 AND ds.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_map_order_item_tax_rate is NULL
                  GROUP BY m.pk_map_order_item_tax_rate, ds.dataset_id
              ) fin
         GROUP BY fin.pk_map_order_item_tax_rate, fin.dataset_id
     ) s
order by s.dataset_id, s.pk_map_order_item_tax_rate;
-- {{IF_ANALYZE}}sn_map_order_item_tax_rate{{/IF_ANALYZE}}

-- POPULATE `sn_dim_shipping_method`
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_shipping_method`;
select @max_sn_pk:=coalesce(max(fk_fact_order), 0) from `sn_dim_shipping_method`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_shipping_method`;
insert into sn_dim_shipping_method
(pk_dim_shipping_method, fk_fact_order, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_shipping_method,
    s.fk_fact_order,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             o.fk_dim_shipping_method as pk_dim_shipping_method,
             max(o.pk_fact_order) as fk_fact_order,
             max(ds.pk_orders_in_dataset) as fk_orders_in_dataset,
             ds.dataset_id
         from bim_dwh_orders_in_dataset ds
                  join fact_order o
                       on o.order_id = ds.order_id
                  left join sn_dim_shipping_method sn
                            on sn.pk_dim_shipping_method = o.fk_dim_shipping_method
                                and sn.dataset_id = ds.dataset_id
         WHERE (o.pk_fact_order > @max_sn_pk OR @max_sn_pk <> 0 AND ds.pk_orders_in_dataset > @max_sn_order_pk)
           AND sn.pk_dim_shipping_method is NULL
         GROUP BY ds.dataset_id, o.fk_dim_shipping_method
     ) as s
order by s.dataset_id, s.pk_dim_shipping_method;
-- {{IF_ANALYZE}}sn_dim_shipping_method{{/IF_ANALYZE}}

-- POPULATE `sn_dim_order_state`
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_order_state`;
select @max_sn_pk:=coalesce(max(pk_dim_order_state), 0) from `sn_dim_order_state`;
insert into sn_dim_order_state
(pk_dim_order_state, dataset_id, sequence_number, inserted_at)
select
    s.pk_dim_order_state,
    s.dataset_id,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             dt.pk_dim_order_state,
             ds.dataset_id
         from dim_order_state dt
                  join (select DISTINCT dataset_id from bim_dwh_orders_in_dataset) ds
                  left join sn_dim_order_state sn
                            on sn.pk_dim_order_state = dt.pk_dim_order_state
                                and sn.dataset_id = ds.dataset_id
         WHERE sn.pk_dim_order_state is NULL
     ) as s
order by s.dataset_id, s.pk_dim_order_state;
-- {{IF_ANALYZE}}sn_dim_order_state{{/IF_ANALYZE}}

-- POPULATE `sn_map_order_state`
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_map_order_state`;
select @max_sn_pk:=coalesce(max(pk_map_order_state), 0) from `sn_map_order_state`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_map_order_state`;
insert INTO sn_map_order_state
(pk_map_order_state, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_map_order_state,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_map_order_state,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from
             (select
                  m.pk_map_order_state,
                  max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                  d.dataset_id
              from map_order_state m
                       join bim_dwh_orders_in_dataset d
                            on m.order_id = d.order_id
                       left join sn_map_order_state sn
                                 on sn.pk_map_order_state = m.pk_map_order_state
                                     and sn.dataset_id = d.dataset_id
              WHERE m.pk_map_order_state > @max_sn_pk
                AND sn.pk_map_order_state is NULL
              GROUP BY m.pk_map_order_state,
                       d.dataset_id
              UNION
              select
                  m.pk_map_order_state,
                  max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                  d.dataset_id
              from map_order_state m
                       join bim_dwh_orders_in_dataset d
                            on m.order_id = d.order_id
                       left join sn_map_order_state sn
                                 on sn.pk_map_order_state = m.pk_map_order_state
                                     and sn.dataset_id = d.dataset_id
              WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                AND sn.pk_map_order_state is NULL
              GROUP BY m.pk_map_order_state,
                       d.dataset_id
             ) fin
         GROUP BY fin.pk_map_order_state,
                  fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_map_order_state;
-- {{IF_ANALYZE}}sn_map_order_state{{/IF_ANALYZE}}

-- POPULATE `sn_dim_address_details`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_dim_address_details`;
select @max_sn_pk:=coalesce(max(pk_dim_address_details), 0) from `sn_dim_address_details`;
select @max_sn_order_pk:=coalesce(max(fk_orders_in_dataset), 0) from `sn_dim_address_details`;
insert into sn_dim_address_details
(pk_dim_address_details, dataset_id, fk_orders_in_dataset, sequence_number, inserted_at)
select
    s.pk_dim_address_details,
    s.dataset_id,
    s.fk_orders_in_dataset,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         select
             fin.pk_dim_address_details,
             max(fin.fk_orders_in_dataset) as fk_orders_in_dataset,
             fin.dataset_id
         from (
                  -- address data from shipments for new dim_address_details records
                  select
                      dim.pk_dim_address_details,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_address_details dim
                           join fact_shipment s
                                on dim.pk_dim_address_details = s.fk_dim_address_details
                           join bim_dwh_orders_in_dataset d
                                on s.order_id = d.order_id
                           left join sn_dim_address_details sn
                                     on sn.pk_dim_address_details = dim.pk_dim_address_details
                                         and sn.dataset_id = d.dataset_id
                  WHERE dim.pk_dim_address_details > @max_sn_pk
                    AND sn.pk_dim_address_details is NULL
                  GROUP BY dim.pk_dim_address_details, d.dataset_id
                  UNION
                  -- address data from invoices for new dim_address_details records
                  select
                      dim.pk_dim_address_details,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_address_details dim
                           join fact_invoice i
                                on dim.pk_dim_address_details = i.fk_dim_address_details
                           join bim_dwh_orders_in_dataset d
                                on i.order_id = d.order_id
                           left join sn_dim_address_details sn
                                     on sn.pk_dim_address_details = dim.pk_dim_address_details
                                         and sn.dataset_id = d.dataset_id
                  WHERE dim.pk_dim_address_details > @max_sn_pk
                    AND sn.pk_dim_address_details is NULL
                  GROUP BY dim.pk_dim_address_details, d.dataset_id
                  UNION
                  -- address data from shipments for new orders
                  select
                      dim.pk_dim_address_details,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_address_details dim
                           join fact_shipment s
                                on dim.pk_dim_address_details = s.fk_dim_address_details
                           join bim_dwh_orders_in_dataset d
                                on s.order_id = d.order_id
                           left join sn_dim_address_details sn
                                     on sn.pk_dim_address_details = dim.pk_dim_address_details
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_address_details is NULL
                  GROUP BY dim.pk_dim_address_details, d.dataset_id
                  UNION
                  -- address data from invoices for new orders
                  select
                      dim.pk_dim_address_details,
                      max(d.pk_orders_in_dataset) as fk_orders_in_dataset,
                      d.dataset_id
                  from dim_address_details dim
                           join fact_invoice i
                                on dim.pk_dim_address_details = i.fk_dim_address_details
                           join bim_dwh_orders_in_dataset d
                                on i.order_id = d.order_id
                           left join sn_dim_address_details sn
                                     on sn.pk_dim_address_details = dim.pk_dim_address_details
                                         and sn.dataset_id = d.dataset_id
                  WHERE @max_sn_pk <> 0 AND d.pk_orders_in_dataset > @max_sn_order_pk
                    AND sn.pk_dim_address_details is NULL
                  GROUP BY dim.pk_dim_address_details, d.dataset_id
              ) fin
         GROUP BY fin.pk_dim_address_details, fin.dataset_id
     ) as s
order by s.dataset_id, s.pk_dim_address_details;
-- {{IF_ANALYZE}}sn_dim_address_details{{/IF_ANALYZE}}

-- POPULATE `sn_fact_metadata`;
select @rank:=coalesce(max(sequence_number), 0)*10000 from `sn_fact_metadata`;
select @max_sn_pk:=coalesce(max(pk_fact_metadata), 0) from `sn_fact_metadata`;
insert into sn_fact_metadata
(pk_fact_metadata, dataset_id, sequence_number, inserted_at)
select
    s.pk_fact_metadata,
    s.dataset_id,
    CEIL((@rank:=@rank+1)/10000) as sequence_number,
    @db_date_time as inserted_at
from (
         SELECT
             f.pk_fact_metadata,
             ds.dataset_id
         FROM fact_metadata f
                  JOIN (SELECT DISTINCT dataset_id
                        FROM bim_stg_dataset_scope) ds
                  left JOIN sn_fact_metadata sn
                            on sn.pk_fact_metadata = f.pk_fact_metadata
         WHERE sn.pk_fact_metadata is NULL
     ) s
order by s.dataset_id, s.pk_fact_metadata;
-- {{IF_ANALYZE}}sn_fact_metadata{{/IF_ANALYZE}}

-- enable keys
-- ALTER TABLE`sn_dim_shipping_method`ENABLE KEYS;
-- ALTER TABLE`sn_dim_salesrule`ENABLE KEYS;
-- ALTER TABLE`sn_map_salesrule`ENABLE KEYS;
-- ALTER TABLE`sn_dim_product`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order_item`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order_item_parent`ENABLE KEYS;
-- ALTER TABLE`sn_fact_shipment_item`ENABLE KEYS;
-- ALTER TABLE`sn_fact_shipment_item_parent`ENABLE KEYS;
-- ALTER TABLE`sn_fact_invoice_item`ENABLE KEYS;
-- ALTER TABLE`sn_fact_invoice_item_parent`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order_cancel_item`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order_cancel_item_parent`ENABLE KEYS;
-- ALTER TABLE`sn_dim_customer_mini`ENABLE KEYS;
-- ALTER TABLE`sn_dim_customer`ENABLE KEYS;
-- ALTER TABLE`sn_dim_default_billing_address`ENABLE KEYS;
-- ALTER TABLE`sn_dim_default_shipping_address`ENABLE KEYS;
-- ALTER TABLE`sn_dim_store`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order_status_updates`ENABLE KEYS;
-- ALTER TABLE`sn_fact_comment`ENABLE KEYS;
-- ALTER TABLE`sn_fact_credit_memo_item`ENABLE KEYS;
-- ALTER TABLE`sn_fact_credit_memo_item_parent`ENABLE KEYS;
-- ALTER TABLE`sn_dim_shipping_address`ENABLE KEYS;
-- ALTER TABLE`sn_fact_shipment`ENABLE KEYS;
-- ALTER TABLE`sn_dim_billing_address`ENABLE KEYS;
-- ALTER TABLE`sn_fact_invoice`ENABLE KEYS;
-- ALTER TABLE`sn_fact_payment`ENABLE KEYS;
-- ALTER TABLE`sn_dim_payment_method`ENABLE KEYS;
-- ALTER TABLE`sn_fact_credit_memo`ENABLE KEYS;
-- ALTER TABLE`sn_fact_order_cancel`ENABLE KEYS;
-- ALTER TABLE`sn_dim_category`ENABLE KEYS;
-- ALTER TABLE`sn_map_product_category`ENABLE KEYS;
-- ALTER TABLE`sn_dim_attribute_option`ENABLE KEYS;
-- ALTER TABLE`sn_dim_address_details`ENABLE KEYS;
-- ALTER TABLE`sn_map_product_attribute`ENABLE KEYS;
-- ALTER TABLE`sn_dim_order_date`ENABLE KEYS;
-- ALTER TABLE`sn_dim_order_time`ENABLE KEYS;
-- ALTER TABLE`sn_dim_age`ENABLE KEYS;
-- ALTER TABLE`sn_fact_metadata`ENABLE KEYS;
-- ALTER TABLE`sn_map_order_state`ENABLE KEYS;
-- ALTER TABLE`sn_dim_order_state`ENABLE KEYS;
-- ALTER TABLE`sn_dim_tax_rate`ENABLE KEYS;
-- ALTER TABLE`sn_map_order_tax_rate`ENABLE KEYS;
-- ALTER TABLE`sn_map_order_item_tax_rate`ENABLE KEYS;
