Introduction to Data Engineering (NDBI046)

Home Basic Information Formal Requirements Literature Contact

Basic Information Formal Requirements 1: Introduction 2: User Story, Extract 3: Transform 4: Load, Introduction to Visualization 5: Consultation class I 6: Apache Airflow 7: Data Cube 8: Consultation class II 9: Data Provenance 10: Data catalog and metadata 11: Consultation class III 12: Data Integrity 13: Consultation class IV Literature Contact

Basic Information

Annotation (source: SIS)
Lecturer: Petr Škoda (petr.skoda (at) matfyz.cuni.cz), Martin Nečaský (martin.necasky (at) matfyz.cuni.cz)
Tutor: Petr Škoda (petr.skoda (at) matfyz.cuni.cz), Pavel Koupil (pavel.koupil (at) matfyz.cuni.cz)
Lectures
Schedule
- Lectures: Tuesday 9:00 - 10:30 (room S9)
- Practical classes: Monday 10:40 - 12:10 (room SU2)

Formal Requirements

Practical classes:
- Attendance is not compulsory, yet recommended
You will be assigned 6 individual homework during the semester
- Completing a homework may award you up to 10 points
- There will always be at least 13 days for completing homework assignment
- Particular homework solution can be submitted only once
- Only one late submission is accepted
Course credit:
- At least 6 points from each homework is required for the course credit to be granted

19. 2. 2024: Introduction

Supplementary Material:
- Programování 1 (Python) practical class (Czech only)

26. 2. 2024: User Story, Extract

Study Material:
- NDBI046_02_Extract.pdf
Supplementary Material:
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)

4. 3. 2024: Transform

Study Material:
- NDBI046_03_Transform.pdf
Solution:
- Example 3.2: Transform Region Data (CSV) (file transform_regions.py)
- Exercise 3.3: Transform Waste Data (CSV) (file transform_waste.py)
- Exercise 3.4: Transform Population Data (CSV) (file transform_population.py)
- Example 3.5: Join Waste and Population Data (file join_waste_population.py)
- Exercise 3.6: Transform Expenses Data (JSON) (file transform_expenses.py)
- Exercise 3.7: Finalize Data (file transform_finalize.py)

11. 3. 2024: Load, Introduction to Visualization

Study Material:
- NDBI046_04_Load.pdf
- NDBI046_04_Visualization.pdf
Supplementary Material:
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)
- Deadline: 27th March 2024 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
      - 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)
  - Visualization
    - After uploading data to the data warehouse, create two non-trivial visualizations of your data
      - Describe the visualizations (what we can see, no statistical description required)
- Submission:
  - Your solution must be uploaded to Gitlab repository
  - Repository must contain:
    - README.md describing:
    - 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
    - Visualizations as PDF or PNG file (i.e., do not submit Tableau project file)
- Evaluation:
  - Completeness of requirements and ETL workflow functionality - 5 points
  - ETL workflow documentation - 2 points
  - Data warehouse design and documentation - 1 point
  - Visualisation - 2 points
- References:
  - Python 3: https://docs.python.org/3/
  - venv: https://docs.python.org/3/library/venv.html
  - Tableau: https://www.tableau.com
- 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
  - Tableau sample datasets: https://public.tableau.com/app/learn/sample-data?qt-overview_resources=1
  - 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

18. 3. 2024: Consultation class I

25. 3. 2024: Apache Airflow

Study Material:
- NDBI046_06_Apache_Airflow.pdf
Supplementary Material:
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)
- Deadline: 8th April 2024 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:
    - 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

8. 4. 2024: Data Cube

Study Material:
- NDBI046_07_Data_Cube.pdf
Supplementary Material:
- Well-formed data cube validation script (file check-well-formed.py)
Solution:
- Example 7.1: Data structure definition (file cube_structure.py)
- Example 7.2: Dataset (file cube_dataset.py)
- Exercise 7.3: Handling multiple measures (file cube_multiple_measures.py)
- Exercise 7.4: Additional dataset metadata (file cube_dataset_metadata.py)
- Exercise 7.5: Concept schemes and code lists (file cube_resources.py)
- Exercise 7.6: Slices (not provided)
- Exercise 7.7: Well-formed data cube (not provided)
Homework Assignment:
- Points: 10 (must score at least 6 points)
- Deadline: 23rd April 2024 20:00 (AoE)
- Assignment:
  - Create a data cube corresponding to any fact table from the previous homework
    - Use a custom base URL prefix
    - Introduce (reuse) concepts for all dimensions
    - Introduced concepts must have a label (i.e., skos:prefLabel)
    - Apply SDMX concepts to increase interoperability
    - The data cube must contain at least 2 dimensions and 1 measurement
    - Include a core set of DataSet metadata
    - Create a qb:Slice over the selected subset of observations
    - The data cube must be well-formed according to the RDF data cube specification
  - Include in the solution the extraction of the dataset corresponding to the fact table joined with all its dimensions
    - That is, read data from PostgreSQL deployed on the webik.ms.mff.cuni.cz server
- Submission:
  - Your solution must be uploaded to Gitlab repository
  - Repository must contain:
    - README.md describing:
    - License file (MIT)
    - The Python script to create data cube
    - The Python for data cube validation using integrity constraints
- Evaluation:
  - Data cube structure (i.e., minimal requirement for dimensions and measurements) - 1 point
  - (Reuse) concepts for all dimensions - 2 points
  - Use of SDMX concepts - 1 point
  - Data Cube metadata - 1 point
  - Well-formed data cube - 2 points
  - Slice over a selected subset of observations - 3 points
- References:
  - The RDF Data Cube Vocabulary: https://www.w3.org/TR/vocab-data-cube/
  - Python 3: https://docs.python.org/3/
  - rdflib: https://rdflib.readthedocs.io/en/stable/
  - SPARQL querying: https://rdflib.readthedocs.io/en/stable/intro_to_sparql.html
- PostgreSQL database server:
  - webik.ms.mff.cuni.cz (port: 5432, database: ndbi046)
  - User credentials were sent in a separate email

15. 4. 2024: Consultation class II

22. 4. 2024: Data Provenance

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)
- Deadline: 7th May 2024 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:
    - 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/

29. 4. 2024: Data catalog and metadata

Study Material:
- NDBI046_10_Data_Catalog.pdf
Solution:
- Example 10.1: Basic structure of data catalog (file catalog.py)
- Example 10.2: Technical metadata (file catalog_tech.py)
- Exercise 10.3: Provenance metadata (file catalog_prov.py)
- Example 10.4: Find a suitable theme in Eurovoc (link http://eurovoc.europa.eu/1158)
- Exercise 10.5: Domain metadata (file catalog_dom.py)
- Exercise 10.6: Business metadata (not provided)
- Exercise 10.7: Query data descriptions (not provided)
Homework Assignment:
- Points: 10 (must score at least 6 points)
- Deadline: 14th May 2024 20:00 (AoE)
- Assignment:
  - Create a Python script that produces a DCAT description of data catalog (dcat:Catalog) that contains a single dataset entry (dcat:Dataset) along with two distributions (dcat:Distribution)
    - The dataset corresponds to the data you used to create the data cube in the third homework assignment
    - Distributions correspond to the original data (i.e., fact table exported to csv) and the data cube in RDF turtle format
    - Provide the following metadata:
      - Minimum of 5 technical metadata, e.g., dcat:accessService, dcat:accessURL, dcat:byteSize, dcat:downloadUrl, dcat:mediaType, dcterms:format, dcat:endpointDescription, dcat:endpointURL
      - Minimum of 5 provenance metadata, e.g., dcat:contactPoint, dcterms:creator, dcterms:issued, dcterms:modified, dcterms:published, prov:qualifiedAttribution, prov:wasGeneratedBy
      - Minimum of 6 domain metadata , e.g., dcat:keywords, dcat:theme, dcterms:description, dcterms:title, dcat:spatialResolutionInMeters, dcat:termporalResolution, dcterms:spatial, dcterms:termporal
      - Minimum of 3 business metadata, e.g., dcterms:accrualPeriodicity, dcterms:accessRights, dcterms:license, dcterms:rights, odrl:hasPolicy
    - The dataset must be described using at least 3 keywords and 3 themes
    - Follow the DCAT documentation and use the appropriate value for each metadata property
    - Remember to employ your prefix (base URL)
    - Employ resources from authority tables, e.g.:
    - Employ themes:
      - Eurovoc: https://op.europa.eu/en/web/eu-vocabularies/dataset/-/resource?uri=http://publications.europa.eu/resource/dataset/eurovoc
    - Save the description file as turtle
    - Make sure the file is valid
  - Create a Python script that executes 2 meaningful SPARQL queries over the created description of your data catalog
    - The query must be different from the Exercise 10.7 in the practical class
    - In the comments of the submitted Python script, explain the meaning of the queries
- Submission:
  - Your solution must be uploaded to Gitlab repository
  - Repository must contain:
    - README.md describing:
    - License file (MIT)
    - The Python script to create dcat description document
    - The Python script to execute SPARQL queries
- Evaluation:
  - Appropriate use of technical metadata (e.g., correct range) - 2 points
  - Appropriate use of provenance metadata - 2 points
  - Appropriate use of domain metadata including - 3 points
  - Appropriate use of business metadata including - 1 points
  - Meaningful SPARQL queries - 2 points
- References:
  - Data Catalog Vocabulary (DCAT) - Version 3: https://www.w3.org/TR/vocab-dcat-3/
  - Eurovoc: https://op.europa.eu/en/web/eu-vocabularies/dataset/-/resource?uri=http://publications.europa.eu/resource/dataset/eurovoc
  - SPARQL Query Language for RDF: https://www.w3.org/TR/prov-o/
  - Python 3: https://docs.python.org/3/
  - rdflib: https://rdflib.readthedocs.io/en/stable/

6. 5. 2024: Consultation class III

13. 5. 2024: Data Integrity

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)
- Deadline: 27th May 2024 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:
    - 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

20. 5. 2024: Consultation class IV

Recommended Literature

Contact Me

     Department of Software Engineering
         Faculty of Mathematics and Physics
         Charles University
         Malostranské náměstí 25
         118 00 Prague
         Czech Republic

pavel.koupil@matfyz.cuni.cz

     Project ELI Beamlines
         Institute of Physics
         Czech Academy of Sciences
         Za Radnicí 835
         252 41 Dolní Břežany
         Czech Republic

pavel.koupil@eli-beams.eu