ETL

For ETL, the data is extracted from a source, transformed, and loaded into tables for end-user usage (can be done with Python, Spark, Scala, or SQL in the data warehouse). ETL is an older method ideal for complex transformations of smaller data sets. This creates a bottleneck because you have engineers who have to transform all the data before moving it to a bigger access point for others.

ELT

For ELT, the data is extracted from a source, then the raw data is loaded into a data warehouse where it is transformed. From there, the warehouse is connected to analysis tools to access the transformed tables. ELT is a newer technology that provides more flexibility to analysts and is perfect for processing both structured and unstructured data. This video does a good job of going through ELT and why to use it.

OLTP

OLTP (online transaction processing) is the processing of the datasets that contain real-time execution of transactions. Also referred to as operational database.

OLAP

OLAP (online analytical processing) is the changing of data within a data warehouse that leads to data analysis being able to be done Also referred to as analytical database.

Data Lake

Data lakes are data stores that hold raw data. This was a popular method to be used because it was a lot cheaper to store all the raw data and then pull out what you needed rather than transforming it before storing. Now as cloud technology improves data lakes are not necessarily needed.

Data Warehouse

Data warehouses are data stores that hold structured (or semi-structured) data. This means that in order for data to be stored in a warehouse it will have to be transformed to be in some sort of structured format, such as connected tables. Now that more companies are going the way of ELT, this means that the raw data is being dumped in the same spot as it is being transformed. This means that some modern data warehouses are both data lakes and warehouses.

Fact Table

Fact tables are at the centre of a schema and they contain general information that create links to more detailed tables. They contain things like measurement, metrics and facts about something business related.

Dimensional Table

Dimensional tables are at the edges of a schema and they contain more descriptive attributes of the attributes from the fact table.

Foreign key

Foreign key is used to describe an attribute on a table that can contain repeating values to link to other tables that might contain more details about the attribute. Some facts about foreign keys are:

• They create a relationship between two tables
• Multiple foreign keys can be used in a table
• Can accept null values

Primary key

Primary key is used to reference the link of attributes in the detailed table to the general fact table. Some facts about primary keys are:

• Uniquely identifies each row
• Single primary key in a table
• Never accepts null values

Star Schema

Star schemas have one fact table (with foreign keys) which is split into dimension tables (linked to fact table by primary keys). This schema is not normalized, meaning there are data redundancies. Here is a graphic of how a star schema would look like:

Snowflake Schema

Snowflake schemas have one fact table and dimensional tables, like the star schema, but it also has lookup tables. Lookup tables are secondary dimensional tables connecting to the initial dimensional table that split the primary dimensional tables until normalized (no redundancy of data). Here is a graphic of how a snowflake schema would look like:

Packages/Libraries

Packages are collections of modules. In order to identify it as a package, it must contain __init__.py file.

Libraries are collections of code that have similar functions. An example would be the Pandas library. This allows us to import the library to our code and then make use of great data analysis functions without having to create the methods ourselves. Remember that you must always first import the library before being about to call on the methods of that library. The language of a library and package can be interchanged.

Modules

Modules are files that have functions and global variables.

You can think of it this way: a module is a subsection of a Package.

Function

When you have a specific task that needs to be done a couple of times throughout your code, the best practice is to make a function for it. A function is a self-contained block of code, defined using the “def” keyword, which carries out a specific task, and is called by name (source). When a function is called, it will usually only operate and return a value.

There are two main kinds of functions:

1. Built-in function: these are the functions that come directly with the Python language.
2. User-defined function: these are the functions that you would create in order to help you execute.

Method

A method in python is a self-contained block of code that carries out a specific task - like a function. However, unlike a function, it is used on an object/class. Because it is called on an object/class it can alter the object/class's data.

How to spot a method: if it has “.” it is a method: .method() because it must be called upon by an object/class.

IDE

IDE stands for integrated development environment. The development (including coding, compiling, debugging, executing, autocomplete, libraries) can be done in a single program with an IDE.

Code editor

Code editor is a text editor in which code can only be edited or modified. This requires more steps to be taken by the programmer, but is less complicated especially when working on smaller-scope projects.

Python

Python is one of the best "readable" codes and is widely used (e.g. data science, machine learning, web/desktop applications, etc.). Uses “pip” - package installer python when installing packages (as the name suggests).

Anaconda

The language of Anaconda is python, however, has a lot more functionality built into it compared to python. Especially when it comes to libraries, Anaconda comes with more data science/machine learning libraries directly installed. It tends to be easier to learn for non-programmers. Uses “conda” when installing packages.

Parameter

A parameter is the variable listed in the function definition.

Argument

An argument is the value in the function when it is called.

Absolute Path

The absolute path is the best to use because there are no limitations to checking where your code is located, and less chance of an error. Easiest to just specify which folder it goes into. You list the whole path starting with /.

Example:
/User/Folder1/Folder2/.../FinalFolder

Relative Path

The relative path you use when you are in a specific folder and doing smaller projects that will not move from the folder. Try to avoid using this as this can lead to errors in code if a file is ever moved.