Netezza Interview Questions

Having data all in one place will improve business processes and enable enterprises to make informed decisions. IBM Netezza is a popular data warehousing system. Whether you are a fresher or an experienced candidate, there are a lot of Netezza opportunities in major companies. In this post, we will overview the Netezza interview questions that are curated by the working professionals. Go through the below frequently asked Netezza architecture interview questions and answers that help you face the interview process very quickly. Let's get started.

1) What is Netezza?

Ans: Netezza is a company that provides data warehouse appliances for data warehousing, predictive analytics, and business intelligence. It was founded in the year 1999 by Foster Hinshaw. IBM acquired Netezza in the year 2010. IBM Netezza is used to store historical data and enables faster processing of data.

2) What are the advantages of Netezza?

Ans: Here are some of the advantages of Netezza,

  • Execution time is very less which highly improves performance
  • We can run complex analytical queries with ease
  • Easy to learn and manage
  • It handles large data sets
  • It uses the FPGA tool to filter unwanted data

3) List the environment variables required to connect to Netezza

Ans: Four environmental variables are required to connect to Netezza.

  • NZ_HOST
  • NZ_DATABASE
  • NZ_USER
  • NZ_PASSWORD

4) What are the states involved in Netezza?

Ans: There are five states to the Netezza server.

  • Online - this is the usual state of the server.
  • Stopped - stops after executing the current queries.
  • Offline - completes the current queries and throws an error for new queries.
  • Paused - occurs on Netezza bootup or startup.
  • Down - occurs due to the Netezza server problem or user initiation.

5) Where is the system catalog information stored?

Ans: Netezza stores system catalog information in the Netezza hosts. Each time a table gets created in Netezza, table definition gets stored in hosts. The actual rows or records of the table gets stored in the Netezza disks.

6) Explain about clustering in Netezza

Ans: Clustering is the process of grouping similar items. Netezza consists of 2 SMP hosts that are part of a cluster - one for active host and the other for the passive host.

7) What are the extent and page sizes in Netezza?

Ans: In Netezza, we can't change the extent and page sizes, they are fixed by default. The extent size is 3 MB, and the page size is 128 KB.

8) What are the constraints in Netezza?

Ans: The following are the list of enforced and non enforced constraints in Netezza.

  • Primary Key (Not enforced)
  • Unique key (Not enforced)
  • Referential Integrity (Not enforced)
  • Not Null (Enforced)
  • Data type verification (Enforced)

9) How can FPGA help improve query performance?

Ans: Netezza uses Field Programmable Gate Array (FPGA) to filter out unwanted data when reading the data from the disk. This removes IO bottlenecks and frees up CPU, memory, and network components.

10) Define snippet

Ans: A snippet is a small unit of database operations that need to be carried out in Netezza. When we run a snippet, it shows all the details of its execution. A snippet can run either on an SPU or host or both.

Netezza Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

 

11) Explain about zone maps

Ans: Netezza maintains zone maps automatically on all the tables. Zone maps offer similar functionality, like partitions. When we want to access particular data, we can rely on the zone map, to get only the range of the data that is required. 

12) How can we get stats on tables in Netezza?

Ans: Netezza gathers stats about the tables in the database through 'generate statics'. The stats include null values, duplicate values, maximum values, minimum values, etc. There are two ways to generate statistics.

  • Generate statistics on a table
  • Generate express statistics on a table

13) Explain about materialized views

Ans: Materialized views reduce the width of data (number of columns). It creates a thin version of the base table, such that it only contains the frequently queried columns. It will have the same distribution key as the base table.

14) What are the best practices for creating materialized views?

Ans: Here are some of the best practices to follow when working with material views.

  • Only create a material view on the frequently queried columns.
  • Refresh the material views often.
  • Stop the material view before performing a large operation on the base table.  Once the operation is complete, refresh the material view so the updates will come into effect

15) What are the limitations of materialized views?

Ans: We have some restrictions on material views.

  • We can't insert, update, delete data in a material view. The changes made to the base table will reflect on the material view. 
  • A material view can be created on only one base table
  • The base table cannot be external, a system table, or a temporary table.
  • Where clause cannot be used in material view
  • Expressions are not allowed as columns

16) Explain about partition methods available in Netezza

Ans: Tables in Netezza are split into groups and stored across data slices in SPUs. There are two types of partitioning methods available,

  • Random partitioning: randomly distributes the data
  • Hash Partitioning: distributes data on a specified column

17) Will everything get cached in Netezza?

Ans: No, Netezza does not cache everything. It will only cache the table schema and other database objects. The SPUs hold the actual data of the tables.

18) What are the join types in Netezza?

Ans: Netezza supports the following join types,

  • Hash join
  • Merge Sort join 
  • Expression join
  • Exist join
  • Cross Product Join

19) What are the data loading formats supported by Netezza?

Ans: Netezza supports 3 data loading formats for loading the data from external sources.

  • Text-delimited - every row or record should end with a delimiter.
  • Fixed-length - allows an expressive form of the external table definition.
  • Compressed binary - compresses the data before a backup or restore.

20) Does Netezza support concurrent updates of the same record?

Ans: If the same record is updated concurrently, Netezza will roll back the affected transaction that was performed recently on the same record. Netezza locks the table by using serializable transaction isolation to ensure no dirty reads are being performed.

Subscribe to our youtube channel to get new updates..!

 

21) How to calculate the cumulative sum in Netezza?

Ans: The cumulative sum or running sum can be calculated for queries by using the Netezza analytic functions. 

22) Can we insert duplicate rows in the Netezza table?

Ans: Netezza does not have a primary key enforced. So, we can insert duplicate rows into the Netezza tables. 

23) How can we identify and remove duplicate records in the Netezza table?

Ans: We have multiple ways to identify and delete duplicate rows from a table,

  • Create a temporary table and insert the distinct rows of the base table in it. Truncate the base table and insert the rows from the temporary table.
  • Delete the rows using row id.
  • Use the row_number analytic function to rank the rows for each group and then select only one row from the group.

24) How can we redistribute a table?

Ans: The best way to redistribute a table is to create a new table using CTAS (Create table AS) and load the data at the same time. Alternatively, we can also change the distribution property in the /nz/data/postgresql.conf configuration file and restart the database.

25) Explain about collocated join

Ans: If two tables share the same distribution key, then those tables are called collocated tables. When two collocated tables are joined together, then it is called a collocated join.

26) Explain about the nzload

Ans: The nzload is a utility or command that is used to load bulk data from a file into a table. The nzload can upload data from the localhost or a remote client.

27) What are the different ways to load data?

Ans: There are four ways to upload data into a Netezza table,

  • The nzload command
  • External tables
  • Create table AS 
  • Inserts 
  • The nz_migrate utility

28) How are transactions maintained in Netezza?

Ans: Netezza allows only one transaction to perform on a table at a time. The transactions are maintained through two slots - create xid and delete xid. Each row or record will contain these two slots. 

29) Can we update a distribution column?

Ans: No, we cannot update distribution columns in the Netezza tables. The tables are distributed across several nodes using the distribution column. If you try to update the distribution columns, it will throw an error and lock the entire table.

30) How does the data get stored in Netezza?

Ans: The data loaded to Netezza will be stored in the Snippet Processing Unit (SPU). Each SPU has a dedicated hard drive.

Netezza Training

Weekday / Weekend Batches

 

Submit an interview question

Categories

Request for more information

Mudassir
Mudassir
DevOps ERP and IAM tools
Mudaasir is a programming developer for hkr trainings. He has a well knowledge of today’s technology and I’ve loved technology my entire life. And also been lucky enough to work for the programmer including science and technology. Big thanks to everyone who has followed me on LinkedIn and twitter.

WhatsApp
To Top