Flashcards
Data Engineering Lecture 5: Data Integration & Data Quality
Cards in this deck (42)
- 1The need for distributed data storage:- Scalability
- Availability (e.g., latency) (assuming reliability)
- Transparency (e.g., access and location transparency)
- Reliability / fault tolerance (related to scalability) - 2Distributed database system- A distributed database system consists of loosely coupled sites that share no physical component
- Database systems that run on each site are independent of each other
- Transactions may access data at one or more sites - 3Distributed database system model
- 4Homogeneous distributed database- All sites have identical software
- Are aware of each other and agree to cooperate in processing user requests
- Each site surrenders part of its autonomy in terms of right to change schemas or software
- Appears to user as a single system - 5Homogeneous distributed database model
- 6Heterogeneous distributed database- Different sites may use different schemas and software
- Sites may not be aware of each other and may provide only limited facilities for cooperation in transaction processing - 7Heterogeneous distributed database model
- 8Distributed database design strategies- Replication
- Fragmentation
- Combined (replication & fragmentation) - 9Replication - context▪ System maintains multiple copies of data, stored in different sites, for faster retrieval and fault tolerance. Replicating data --> push it as closely as possible to data processing location.
▪ A relation or fragment of a relation is replicated if it is stored redundantly in two or more sites.
▪ Full replication of a relation is the case where the relation is stored at all sites.
▪ Fully redundant databases are those in which every site contains a copy of the entire database. - 10Replication - pros & cons- Advantages of Replication
▪ Availability: failure of site containing relation r does not result in unavailability of r is replicas exist.
▪ Parallelism: queries on r may be processed by several nodes in parallel.
▪ Reduced data transfer: relation r is available locally at each site containing a replica of r.
- Disadvantages of Replication
▪ Increased cost of updates: each replica of relation r must be updated.
▪ Increased complexity of concurrency control: concurrent updates to distinct replicas may lead to inconsistent data unless special concurrency control mechanisms are implemented. - 11Fragmentation - context▪ Relation is partitioned into several fragments stored in distinct sites
▪ Division of relation r into fragments r1, r2, …, rn which contain sufficient information to reconstruct relation r.
▪ Horizontal fragmentation
▪ Vertical fragmentation - 12Horizontal fragmentationeach tuple of r is assigned to one or more fragments by slicing. Mostly, the data is sorted first, on for instance location. You split up attributes like Tilburg VS Eindhoven that first belonged together but are now in two separate tables (relational database).
- 13Vertical fragmentationthe schema for relation r is split into several smaller schemas
▪ All schemas must contain a common candidate key (or super key) to ensure lossless join property.
▪ A special attribute, the tuple-id attribute may be added to each schema to serve as a candidate key.
▪ In other words, you need a new key since you split up columns into two tables. You need a joining key. - 14Advantages of Horizontal Fragmentation▪ allows parallel processing on fragments of a relation
▪ allows a relation to be split so that tuples are located where they are most frequently accessed - 15Advantages of Vertical Fragmentation▪ allows tuples to be split so that each part of the tuple is stored where it is most frequently accessed
▪ tuple-id attribute allows efficient joining of vertical fragments
▪ allows parallel processing on a relation
▪ Vertical and horizontal fragmentation can be mixed.
▪ Fragments may be successively fragmented to an arbitrary depth. - 16- Replication and fragmentation can be combinedRelation is partitioned into several fragments: system maintains several identical replicas of each such fragment.
- 17Data integration - Motivation▪ Many databases and sources of data that need to be integrated to work together
▪ Almost all applications have many sources of data - 18Data Integration - contextIt is the process of integrating data from multiple sources and probably have a single view over all these sources?
▪ And answering queries using the combined information - 19Integration can be physical or virtual▪ Physical: Coping the data to warehouse or data lakes
▪ Virtual: Keep the data only at the sources. Virtual thin layer of veneer used to give the feeling to end user that there is a big database. It is hidden to the client (i.e., transparent = invisible). - 20Data federationsone to one mapping in between data sources
- 21Data mediatorGlobal database schema which maps all the local database schema in one holistic one.
- 22Examples▪ Police data
▪ Open data (Facebook)
▪ Darknet data - 23Intra-enterprise data integration- Data integration is also valid within a single organization
▪ Integrating data from different departments or sectors
- 24Heterogeneity problems- Source Type Heterogeneity
- Communication Heterogeneity
- Schema Heterogeneity
- Data Type Heterogeneity
- Value Heterogeneity
- Semantic Heterogeneity - 25Source Type HeterogeneitySystems storing the data can be different
- 26Communication Heterogeneity▪ Some systems have web interface others do not
▪ Some systems allow direct query language others offer APIs - 27Schema HeterogeneityThe structure of the tables storing the data can be different (even if storing the same data)
- 28Data Type Heterogeneity▪ Storing the same data (and values) but with different data types
▪ E.g., Storing the phone number as String or as Number
▪ E.g., Storing the name as fixed length or variable length - 29Value Heterogeneity▪ Same logical values stored in different ways
▪ E.g., ‘Prof’, ‘Prof.’, ‘Professor’ --- ‘Right’, ‘R’, ‘1’ --- ‘Left’, ‘L’, ‘-1’ - 30Semantic Heterogeneity▪ Same values in different sources can mean different things
▪ E.g., Column ‘Title’ in one database means ‘Job Title’ while in another database it means ‘Person Title’ - 31Data integration: architectural models- Federated databases
- Data warehouse
- Data lake
- Mediation - 32Federated databases- Simplest architecture
- Every pair of sources can build their own mapping and transformation
- Source X needs to communicate with source Y
▪ Build a mapping between X and Y
- Does not have to be between all sources (on demand)
- Advantages
▪ If many sources and only very few are communicating
- Disadvantages
▪ If most sources are communicating (n2 mappings)
▪ If sources are dynamic (need to change many mappings)
- 33Data Warehouse- Very common approach
- Data from multiple sources are copied and stored in a warehouse
1. Data is materialized in the warehouse
- Users can then query the warehouse database only
- A simple query goes into the enterprise database, this database comprises several tables like customers, orders, vendors, etc. and these are all copied, organized, and summarized in the data warehouse. From this data warehouse, using data mining, complex and OLAP queries flow.
- Synchronizing data between sources and data warehouse (two approaches in which the warehouse is always not up to date): - 34Complete rebuild – big bang – complete reboot▪ Periodically re-build the warehouse from the sources (e.g., every night or every week)
▪ (+) The procedure is easy
▪ (-) Expensive and time consuming - 35Incremental update – chicken little (what a strange name for warehouse update :/)▪ Periodically update the warehouse based on the changes in the sources
▪ (+) Less expensive and efficient
▪ (-) More complex to perform incremental update
▪ (-) Requires sources to keep track of their updates - 36ETL is outdated but still widely used
- 37Data lake- Store all types of structured and unstructured data as-is, without having to first structure the data.
- Incoming data goes into the lake in its raw form; we select and organize data for each need
- Situations exist where we have both a warehouse and a lake for the data infrastructure.
- Does NOT assume relational schemas. Data warehouses do. - 38Data Lake Architecture
- 39Mediation- Mediator is a virtual view over the data (it does not store any data)
▪ Data is stored only at the sources
- Mediator has a virtual schema that combines all schemas from the sources
- The mapping takes place at query time
▪ This is unlike warehousing where mapping takes place at upload time
- If you ‘glue’ several API’s together, you basically get a mediator - 40Data mapping▪ A user query is mapped to multiple other queries
▪ Each query (or set of queries) is sent to the sources
▪ Sources evaluate the queries and return the results
▪ Results are merged (combined) together and passed to the end-user - 41Wrappers▪ Usually, wrappers are the components that perform the mapping of queries
▪ One approach is to use templates with parameters
▪ If the mediator query matches a template, then replace the parameters and execute the query
▪ If no template is found, return empty results
▪ Designing these templates is a complex process because they need to be flexible and represent many queries - 42Mediator Model
Information
- Description
- Engineering Flashcards on Data Engineering Lecture 5: Data Integration & Data Quality, created by Tom van Eijk on 20/12/2021.
- Created by
- Tom van Eijk
- Views
- 0
- Rating