Introduction to Data Warehouses

• Explain how OLAP may support certain business users better than OLTP.

• Implement ETL for OLAP Transformations with SQL.

• Describe Data Warehouse Architecture.

• Describe OLAP cube from facts and dimensions to slice, dice, roll-up, and drill down operations.

• Implement OLAP cubes from facts and dimensions to slice, dice, roll-up, and drill down.

• Compare columnar vs. row-oriented approaches.

• Implement columnar vs. row-oriented approaches.

ELT and Data Warehouse Technology in the Cloud

• Explain the differences between ETL and ELT.

• Differentiate scenarios where ELT is preferred over ETL.

• Implement ETL for OLAP Transformations with SQL.

• Select appropriate cloud data storage solutions.

• Select appropriate cloud pipeline solutions.

• Select appropriate cloud data warehouse solutions.

AWS Data Technologies

• Describe AWS data warehouse services and technologies.

• Create and configure AWS Storage Resources.

• Create and configure Amazon Redshift resources.

• Implement infrastructure as code for Redshift on AWS.

Implementing Data Warehouses on AWS

• Describe Redshift data warehouse architecture.

• Run ETL process to extract data from AWS S3 into Redshift.

• Design optimized tables by selecting appropriate distribution styles and sorting keys.

Big Data Ecosystem, Data Lakes, & Spark

• Identify what constitutes the big data ecosystem for data engineering.

• Explain the purpose and evolution of data lakes in the big data ecosystem.

• Compare the Spark framework with Hadoop framework.

• Identify when to use Spark and when not to use it.

• Describe the features of lakehouse architecture.

Spark Essentials

• Wrangle data with Spark and functional programming to scale across distributed systems.

• Process data with Spark DataFrames and Spark SQL.

• Process data in common formats such as CSV and JSON.

• Use the Spark RDDs API to wrangle data.

• Transform and filter data with Spark.

Using Spark & Data Lakes in the AWS Cloud

• Use distributed data storage with Amazon S3.

• Identify properties of AWS S3 data lakes.

• Identify service options for using Spark in AWS.

• Configure AWS Glue.

• Create and run Spark Jobs with AWS Glue.

Ingesting & organizing data in lakehouse architecture on AWS

• Use Spark with AWS Glue to run ELT processes on data of diverse sources, structures, and vintages in lakehouse architecture.

• Create a Glue Data Catalog and Glue Tables.

• Use AWS Athena for ad-hoc queries in a lakehouse.

• Leverage Glue for SQL AWS S3 queries and ELT.

• Ingest data into lakehouse zones.

• Transform and filter data into curated lakehouse zones with Spark and AWS Glue.

• Join and process data into lakehouse zones with Spark and AWS Glue.

Automate Data Pipelines

Data Pipelines

• Define and describe a data pipeline and its usage.

• Explain the relationship between DAGs, S3, and Redshift within a given example.

• Employ tasks as instantiated operators.

• Organize task dependencies based on logic flow.

• Apply templating in codebase with kwargs parameter to set runtime variables.

Airflow & AWS

• Create Airflow Connection to AWS using AWS credentials.

• Create Postgres/Redshift Airflow Connections.

• Leverage hooks to use Connections in DAGs.

• Connect S3 to a Redshift DAG programmatically.

Data Quality

• Utilize the logic flow of task dependencies to investigate potential errors within data lineage.

• Leverage Airflow catchup to backfill data.

• Extract data from a specific time range by employing the kwargs parameters.

• Create a task to ensure data quality within select tables.

Production Data Pipelines

• Consolidate repeated code into operator plugins.

• Refactor a complex task into multiple tasks with separate SQL statements.

• Convert an Airflow 1 DAG into an Airflow 2 DAG.

• Construct a DAG and custom operator end-to-end.