• The course will be explained at the first practical class (recommended attendance)
• There will be one invited lecture instead of a practical class during the semester (compulsory attendance)
• The remaining practical classes are carried out in the form of homework assignments and individual consultations
  • Consultations can be arranged by email and are held by agreement in the S209 office or online via Zoom

• Completing a homework may award you up to 10 points
• There will always be at least 7 days for completing homework assignment
• Particular homework solution can be submitted only once
• One late submission is accepted

• At least 6 points from each homework is required for the course credit to be granted
• Points earned above this limit are transferred as a bonus to the exam
  • I.e., you can earn up to 4 extra points for each homework solution, up to a total of 24 points for the exam

Study Material:

• NDBI046_02_Extract.pdf

Supplementary Material:

• requirements.txt
• urls.txt
• dataset_expenses.json

Solution:

• Example 2.1: Extract dataset 'Production of industrial and municipal waste' (CSV) (file extract_waste_csv.py)
• Exercise 2.2: Extract dataset 'Costs of environmental protection' (JSON) (file extract_expenses_json.py)
• Exercise 2.3: Web scrapping of the Regions dataset (XML) (file extract_regions_xml.py)
• Example 2.4: Extract population dataset from multiple excel sheets (XSLX) (file extract_population_excel.py)

Study Material:

• NDBI046_04_Load.pdf

Supplementary Material:

• prereq.txt
• requirements.txt
• connection.json
• datasets.json

Solution:

• Example 4.1: Load dataset 'Regions' into dim_regions (row by row) (file single_dim_regions.py)
• Example 4.2: Load dataset 'Regions' into dim_regions (bulk loading) (file bulk_with_ic_dim_regions.py)
• Example 4.3: Load dataset 'Regions' into dim_regions (bulk loading & alter table) (file bulk_dim_regions.py)
• Exercise 4.4: Efficient and repeatable bulk loading of all datasets (file bulk_all.py)

Homework Assignment:

• Points: 10 (must score at least 6 points)
• Expected complexity: 15-20 hours
• Deadline: 27th March 2025 20:00 (AoE)
• Assignment:
  • Propose and implement an ETL workflow to upload selected datasets to the data warehouse
    • Extract (at least) 3 datasets
      • Use at least two different sources (e.g. web, web services, data catalog)
      • Utilize no less than two different methods (e.g. web scraping, file download)
      • Data must be in at least two different formats (e.g. JSON, CSV, XML, worksheet)
    • Transform input datasets to allow them to be loaded to the data warehouse
      • Apply at least 7 different transformations in a meaningful way
        • E.g., join multiple datasets, normalize data, encoding values, change date or number formatting, add surrogate key
      • Ensure consistency of datasets
      • Make sure that each dataset contains at least 6 attributes and maximum 2000 rows (filter remaining rows) when transformation is complete
        • Clarification of the assignment (21. 3. 2025): Make sure that each INPUT or CLEARED (up to your choice) dataset contains at least 6 attributes (i.e., non-trivial data) and maximum 2000 rows (i.e., no large data). Or if you understand this assignment to mean that FACT TABLES will have at least 6 attributes (and the original data is more trivial, dimension tables may have fewer attributes), the solution will also be accepted.
      • Graphically illustrate the entire workflow (e.g., as a DAG) and document it
        • In particular, justify for what purpose you are performing the selected transformations
    • Load the datasets to the data warehouse
  • Appropriately design the data warehouse structure with respect to your datasets
    • Consider a star, snowflake or galaxy schema
    • Distinguish appropriately between fact tables, dimension tables, and measurements
    • Document and illustrate the schema (e.g., using UML)
• Submission:
  • Your solution must be uploaded to Gitlab repository
  • Repository must contain:
    • README.md describing:
    • System requirements
    • Installation instructions
    • Description of script files, inputs, and outputs
    • Illustration, description and reasons for each step of the ETL Workflow (especially reasons for selected transformations)
    • Diagram and description of the data warehouse
    • License file (MIT)
    • Python script(s) to extract input datasets from various sources
    • Python script(s) to transform datasets
    • Python script(s) to load data into data warehouse
• Evaluation:
  • Completeness of requirements and ETL workflow functionality - 6 points, 2 points each dataset
  • ETL workflow documentation - 2 points
  • Data warehouse design and documentation - 2 point
• References:
  • Python 3: https://docs.python.org/3/
  • venv: https://docs.python.org/3/library/venv.html
• Datasets:
  • Czech open data portal: https://data.gov.cz
  • Czech Statistical Office product catalog: https://www.czso.cz/csu/czso/katalog-produktu
  • World Bank Open Data: https://data.worldbank.org
  • European data: https://data.europa.eu/en
  • Eurostat: https://ec.europa.eu/eurostat/data/database
  • Kaggle datasets: https://www.kaggle.com/datasets
• PostgreSQL database server:
  • webik.ms.mff.cuni.cz (port: 5432, database: ndbi046)
  • User credentials were sent in a separate email

Study Material:

• NDBI046_06_Apache_Airflow.pdf

Supplementary Material:

• Dockerfile
• docker-compose.yaml
• requirements.txt

Solution:

• Example 6.1: BashOperator (file dag_bash_operator.py)
• Example 6.2: PythonOperator (file dag_python_operator.py)
• Exercise 6.3: Data Sharing via Airflow Xcoms (file dag_xcoms.py)
• Example 6.4: Connection to PostgreSQL and PostgreOperator (file dag_postgre_operator.py)
• Exercise 6.5: TaskFlow API (file dag_taskflow.py)
• Exercise 6.6: Complete ETL Workflow in Apache Airflow (file dag_etl.py)
• Exercise 6.7: Scheduling using the cron expression

Homework Assignment:

• Points: 10 (must score at least 6 points)
• Expected complexity: 6 hours
• Deadline: 10th April 2025 20:00 (AoE)
• Assignment:
  • Create an Apache Airflow workflow that implements the ETL proposed in the previous homework assignment
    • Decompose the workflow into at least eight tasks
      • Implemented as, e.g., PythonOperator, PostgreOperator, custom operator(s), or using TaskFlow API
    • Use XComs and share data between tasks appropriately
    • Use at least one Sensor appropriately
    • Use the Cron expression to schedule the execution
    • Decompose the workflow into task groups for better readability
    • Set postgres_conn_id equal to postgres_webik
• Submission:
  • Your solution must be uploaded to Gitlab repository
  • Repository must contain:
    • README.md describing:
    • System requirements
    • Installation instructions
    • Description of script file
    • License file (MIT)
    • The only Python script implementing the Apache Airflow DAG
• Evaluation:
  • Correct implementation of the ETL workflow - 3 points
  • The solution performs a meaningful data update instead of deleting and reloading all tables - 2 points
  • Appropriate use of XComs and data sharing - 1 point
  • Tasks decomposition - 1 point
  • Implementation of a custom operator - 1 point
  • Use of Sensor - 1 point
  • Use of Cron - 1 point
• References:
  • Python 3: https://docs.python.org/3/
  • Docker: https://www.docker.com
  • Apache Airflow: https://airflow.apache.org
  • Cron and Time Intervals: https://airflow.apache.org/docs/apache-airflow/stable/authoring-and-scheduling/cron.html
• PostgreSQL database server:
  • webik.ms.mff.cuni.cz (port: 5432, database: ndbi046)
  • User credentials were sent in a separate email

Study Material:

• NDBI046_09_Data_Provenance.pdf

Solution:

• Example 9.1: Simple provenance description - ETL Regions (file prov-o-regions.pdf)
• Example 9.2: Simple provenance description using Python (file prov-starting-regions.py)
• Exercise 9.3: Utilizing expanded terms (file prov-expanded-etl.py)
• Exercise 9.4: Extending provenance document utilizing qualified terms (not provided)
• Exercise 9.5: Extending provenance document utilizing qualified terms II (not provided)

Homework Assignment:

• Points: 10 (must score at least 6 points)
• Expected complexity: 3-4 hours
• Deadline: 24th April 2025 20:00 (AoE)
• Assignment:
  • Create a Python script that produces a provenance document describing ETL workflow, data cube creation, and optionally also visualizations using the terms from PROV-O
    • Employ the resources from all previous homework assignments
    • Include prov:Entities for each dataset/visualization/digital artifact
    • Include at least three instances of prov:Agent (e.g., persons, organizations, software used)
    • Include at least two instances of prov:Activity
    • Use at least one class and one property from Qualified terms
      • For example, prov:qualifiedUsage, prov:qualifiedAssociation
  • Save the provenance as RDF TriG file
  • Make sure the file is valid
• Submission:
  • Your solution must be uploaded to Gitlab repository
  • Repository must contain:
    • README.md describing:
    • System requirements
    • Installation instructions
    • Description of script files, inputs, outputs
    • License file (MIT)
    • The Python script to create provenance document
• Evaluation:
  • Content of the provenance document (i.e., minimal requirement ETL workflow and data cube creation) - 4 points
  • Appropriate use of expanded terms - 3 points
  • Use of qualified terms - 3 points
• References:
  • PROV-O: https://www.w3.org/TR/prov-o/
  • Python 3: https://docs.python.org/3/

Study Material:

• NDBI046_12_Data_Integrity.pdf

Solution:

• Exercise 12.1: Assurance of integrity and authenticity of distributions (file checksum.py)
• Example 12.2: Create a certificate signing request (file csr.sh)
• Example 12.3: Sign the data catalog file (file sign_cat.sh)
• Exercise 12.4: Publish data (not provided)

Homework Assignment:

• Points: 10 (must score at least 6 points)
• Expected complexity: 2 hours
• Deadline: 8th May 2025 20:00 (AoE)
• Assignment:
  • Extend the data catalog description from homework 5 in order to secure distribution integrity using spdx:checksum
  • Employ openssl to create a certificate sign request (CSR) from your RSA private key
    • Provide the following CRS information: countryName (C), organizationName (O), and commonName (CN)
    • Send me the request, the .csr file, for signing
    • Alternatively, use a self-signed certificate (however, this solution is not recommended in practice)
  • Use the signed certificate (i.e., file certificate.crt) to sign data catalog file
    • Use sha256 algorithm for hashing and encode binary content using base64 format
    • Save digital signature info file data-catalog.sha256.sign
  • Publish your data fragments (i.e., csv dataset distribution and data cube distribution in RDF Turtle format) on the server webik.ms.mff.cuni.cz
    • That is, upload the necessary files to the server
    • Add an index.html page with links to the following files:
      • (Signed) certificate certificate.crt
      • Data catalog description file data-catalog.ttl
      • Digital signature data-catalog.sha256.sign
      • CSV dataset distribution file (i.e., the exported fact table that was used to create the data cube in homework 3)
      • Data cube distribution file (i.e., the data cube in RDF Turtle format from homework 3)
    • Do not publish your private key!
• Submission:
  • Your solution must be uploaded to Gitlab repository and publishes on server webik.ms.mff.cuni.cz
  • Repository must contain:
    • README.md describing:
    • Description of script files, inputs, outputs
    • License file (MIT)
    • The Shell script to execute openssl commands
    • The Python script to extend data catalog
    • index.html
    • Certificate signing request
    • Signed certificate certificate.crt
    • Data catalog description file data-catalog.ttl
    • Digital signature data-catalog.sha256.sign
    • CSV dataset distribution file
    • Data cube distribution file
• Evaluation:
  • Sending certificate signing request - 4 points
  • Appropriate use of checksum, certificates for signing the data catalog, and publishing all data fragments - 6 points
  • Private key publication leads to homework failure
  • Disclosure of any credentials leads to homework failure
• References:
  • Data Catalog Vocabulary (DCAT) - Version 3: https://www.w3.org/TR/vocab-dcat-3/
  • OpenSSL: https://www.openssl.org/
  • Python 3: https://docs.python.org/3/
  • hashlib: https://docs.python.org/3/library/hashlib.html
  • W3C HTML tutorial: https://www.w3schools.com/html/
• Webik server:
  • webik.ms.mff.cuni.cz
  • User credentials were sent in a separate email