Data Warehouse User Roles
The following roles can be assigned to Data Warehouse users.
Data Admin
This role should be assigned to any Data Warehouse user who needs to use the instance for loading and processing data.
- Read all tables or views.
- Import data into Data Warehouse tables.
- Create, drop, or purge Data Warehouse tables.
- Create other objects such as functions and views in the database.
The Data Admin role is sufficient for basic use of the Data Warehouse instance.
Admin
This role should be reserved for the user or users who need to have control over the other users in the Data Warehouse instance.
- Read all tables or views.
- Import data into Data Warehouse tables.
- Create, drop, or purge Data Warehouse tables.
- Create other objects such as functions and views in the database.
- Add user.
- Remove user. The user cannot be the Owner of the instance.
- Change a user's role. The user cannot be the Owner of the instance and cannot be changed to the Owner role.
- Edit the name or description of a Data Warehouse instance.
Read-only user
This role should be reserved for the user or users who need to only review or test data in the Data Warehouse instance without permissions to make any changes.
- Access the Data Warehouse instance in read-only mode.
- Read all tables or views.
- Call the functions that do not modify the data.
Read-only users cannot:
- Create any object (for example, tables, views, sequences).
- Insert, update, merge, delete or drop any objects or entities.
- Call any Vertica function that requires access higher than read-only.
Data Warehouse instance owner
The user who created the Data Warehouse instance is automatically assigned ownership of the instance. Ownership is not a formal role in the instance.
The Owner is also automatically assigned the admin role. The Owner has all of the permissions of the admin role, and the permission to delete the Data Warehouse instance.
Data Movement
What is data movement?
Data movement is the ability to move data from one place in your organization to another through technologies that include extract, transformation, load (ETL), extract, load, transform (ELT), data replication and change data capture (CDC), primarily for the purposes of data migration and data warehousing.
Your organization's IT infrastructure and application landscape are continuously changing, and your applications need data from a range of databases. This means you need efficient and secure data movement solutions to shift data across your systems without impacting the performance of your sources.
Data movement, data synchronization and data replication are complementary methods of data integration. Together, they enable you to deliver fresh data to keep your databases, data warehouse, big data and cloud systems current.
What does data movement do?
The first difference between the different types of data movement products is with respect to transformation. Replication is a process by which data is copied, often for availability and disaster recovery purposes, without requiring any transformation. It is commonly used in distributed database environments and may be provided through either synchronous or asynchronous means.
Change data capture is similar, and may be used to support replication, but is essentially about supporting real-time updates to data, where that data is stored in multiple places and you need to propagate changes from an originating source.
Stream processing platforms typically have some processing capabilities, so it is possible to do perform simple transformations using these products. However, this is not their main raison d’être, which is to allow the movement of large volumes of data – typically such things as sensor data, stock ticks, web clicks or log data – where there are low latency performance requirements. Unlike any of the technologies discussed here they are not usually used for moving data from one database to another but, rather, from sources that are more disparate and less structured.
Finally, data integration tools and their extension into data warehouse automation, are used when it is necessary to combine and use disparate data that is in different formats and you need that data in a consistent format for processing purposes. The classical use case for this was in moving, and transforming, data from production systems to data warehouses. However, there are many other use cases, including B2B exchange and in support of data preparation tools within data lakes.
Methods of data movement
Replication
Offers a dependable, low-impact method of creating an accurate and up-to-date copy of your single- or multiple-source data, which can be deployed to any person who needs access to it, from wherever and whenever they work. You can stay in control of replicated databases with flexible configurations of archiving and retention rules, data relocation and storage. Your data remains accessible through the original application or through any analytics tools you have in place.
Synchronization
Enables you to keep your replicated data fresh, so your users and applications are working from the best information you can give them. You can update replicated databases with either a batch-oriented (pull) or real-time (push) configuration. For many relational databases, you can synchronize new data instantly with a capability called change data capture.
Why move data?
Comprehensive data movement and transformation capabilities are essential to modernize and extend your IT portfolio. For example, you may need data movement tools to move data from your legacy systems and platforms to cloud databases. ELT is a data integration process that combines data from multiple sources in a single data store. This capability is valuable to meet hybrid integration requirements, such as connecting and transforming legacy data sources to a data warehouse environment, or moving data from transactional databases to a big data or data lake environment.
Use cases include:
- Database replication Replicate a database for disaster recovery, faster analytics at multiple locations, and more efficient use of distributed resources.
- Cloud data warehousing Gain confidence that your data warehouse contains the most up-to-date information from across your organization, including from legacy databases and platforms, while minimizing the impact on the uptime and performance of the underlying systems. Move data to cloud databases, such as MySQL to Microsoft® Azure® SQL Database, Oracle to Amazon® AWS RDS, or SQL Server® to Amazon REDSHIFT®. Extend ETL capabilities to connect and transform legacy data sources to a data warehouse environment, such as OpenVMS® Rdb to Teradata®.
- Hybrid data movement Master data movement between on-premises systems and cloud services to balance innovation, performance and business continuity. Move on-premises data to the cloud to generate insights and benefit from on-demand elastic services. Move data from cloud applications to the mainframe for accurate, complete data in your system of record.
- Cloud data lakes Use a data movement solution to shift data from transactional databases, such as Adabas, VSAM or IMS, to a big data or data lake environment, such as Hadoop® , Snowflake®, AWS and Azure®.
- Data archiving Schedule archives of your core data to proactively manage the growth of your databases and keep your systems running at peak performance. Boost compliance with regulatory guidelines for data capture to enable traceability and future audits.
Also Read - Data Warehouse Security: Risks, Policies & Controls
