Table of Contents

About

This Howto will show you how to copy the data definition of all SQL tables from a star/snowflake schema by using the Tabul data create command.

This command does not copy the data. It just creates the structure (ie it performs a SQL CREATE statement). If you want to copy the data, use the tabul data transfer command instead.

Steps

Prerequisites

You should have Tabulify installed on your computer. Learning Tabulify - Step 1 - Installation

Select the fact table

The trick to copy a star/snowflake schema is:

  • to give the fact table (ie the table at the center of the star/snowflake schema) to the Tabul table create command
  • and to ask to create it with its dependencies.

Create the fact table with its dependencies

The below command will create:

  • the table store_sales from the tpcds system. This is the fact table of the tpcds store schema.
  • with all its dependencies (all the foreign tables)
  • into the Sqlite Howto connection with the prefix tpc_ using the logical name of the source
tabul data create --with-dependencies store_sales@tpcds 'tpc_${input_logicalName}@sqlite'
# in bash, you need to escape the $ with \ sign or quote the argument so that it's not seen as a bash variable

Results of the create operation
input                          target
----------------------------   ---------------------------------
date_dim@tpcds                 tpc_date_dim@sqlite
household_demographics@tpcds   tpc_household_demographics@sqlite
item@tpcds                     tpc_item@sqlite
income_band@tpcds              tpc_income_band@sqlite
customer_demographics@tpcds    tpc_customer_demographics@sqlite
customer_address@tpcds         tpc_customer_address@sqlite
store_sales@tpcds              tpc_store_sales@sqlite
customer@tpcds                 tpc_customer@sqlite
promotion@tpcds                tpc_promotion@sqlite
time_dim@tpcds                 tpc_time_dim@sqlite
store@tpcds                    tpc_store@sqlite

List the created tables

With the tabul data list command, we can list the tables created with the tpc prefix. 

tabul data list -a count tpc*@sqlite

path                         media_type   count
--------------------------   ----------   -----
tpc_customer                 table            0
tpc_customer_address         table            0
tpc_customer_demographics    table            0
tpc_date_dim                 table            0
tpc_household_demographics   table            0
tpc_income_band              table            0
tpc_item                     table            0
tpc_promotion                table            0
tpc_store                    table            0
tpc_store_sales              table            0
tpc_time_dim                 table            0

Next

You can fill them with generated data.

How to fill a schema with generated data