Full load every day (no incremental load) ​

Context ​

Let's say you have a fact table that you need to fully load every day (i.e. no incremental load).

During reload operations, data is not available.

Business Requirement ​

The business requires displaying previous data while also loading new data.

How to meet the business requirement ​

Create two versions of a table and use an alias (i.e. a logical pointer).

End-Users only see one fact table while behind the scene you have:

  • created a fact table for each new data (with a different name).
  • and used the alias to redirect queries towards the table you want your end-user to use.

How do you implement it? ​

sql
-- Step1a: Creation of the first table (D1)
DROP TABLE IF NOT EXISTS myschema.fact_D1( .... ) ;
CREATE TABLE myschema.fact_D1( .... ) ;

-- Step1b: Load & commit of Table D1
LOAD DATA ... INTO TABLE myschema.fact_D1 QUERY 'Select ....' ;
commit myschema.fact_D1;

-- Step1c: Set Alias on table D1
ALTER TABLE myschema.fact_D1 SET ALIAS sales;

--Create Table D2 using the same structure as D1 
--(by exporting Table D1 structure)
EXPORT TABLE myschema.fact_D1 TO 's3a://xx1' ONLY_METADATA ; 
IMPORT TABLE myschema.fact_D2 FROM 's3a://xx1/myschema/fact_D1' ;

-- Step2b: Load & commit of Table D2
LOAD DATA ... INTO TABLE myschema.fact_D2 QUERY 'Select ....' ;
commit myschema.fact_D2;

-- Step2c: Set Alias on table D2
ALTER TABLE myschema.fact_D2 SET ALIAS sales; -- No Need to drop the Alias before recreating it

-- Drop "old" table D1
DROP TABLE myschema.fact_D1 ;

References ​