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 ;