Make Batch Loads easy and fast with Snowflake’s SWAP Command

  1. Create a TEMP table with same structure as the final table that is loaded by the ETL process.
  2. You can do this by either using Snowflake’s Clone command; apart from creating the cloned table this will also copy the data without using any Snowflake’s computation power or use command create table <tmp_table> as select * from <main_table> where 1=0 , this will duplicate table structure but without data from the main table.
  3. Use the TEMP table as a target in the ETL process, load it with processed data by running ETL jobs.
  4. If you wish to run some quality checks , do that on the TEMP table after it is loaded in step 3. Set limit for your data quality checks , if the checks do not meet the data quality criteria stop/fail the ETL at this stage, correct the data and rerun the ETL as said in step 3.
  5. If quality checks are fine, then at the last step swap this TEMP table with the actual main_table using Snowflake’s SWAP WITH command.This SWAP with command works on the metadata of these two tables and in a nutshell renames them.This command is very fast, gets executed with milliseconds.After the SWAP main_table which is used by the business or your reporting tools is refreshed with latest data within a single transactions and in milliseconds.

--

--

--

Data Engineer, BI, Data Analytics,DWH

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

GA COVID-19 Report October 6, 2021

That kids go back to physical school and that children might also be

Surveys and The Slaughtered Ox Problem

Auto data scrapping with no code 🕷

Visualizing the Deposits Multiplier with Python

Angel Burford cashed in on pandemic-era apartment concessions in Seattle — but good luck finding a…

Data-Driven — My key takeaways.

Turtle conservation helps grow Africa’s data science skills

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
ruchi khanuja

ruchi khanuja

Data Engineer, BI, Data Analytics,DWH

More from Medium

Building Data Products in the Snowflake Data Cloud with Jyoti Pathak from TD SYNNEX

Incremental MySQL loads to using Matillion

Is Google BigQuery a right solution for Datawarehouse migration?

Does my organization need a Data Warehouse? (part 1)