Netezza Interview Questions

If you're looking for Netezza Interview Questions for Experienced or Freshers, you are in right place. There are a lot of opportunities from many reputed companies in the world. According to research, Netezza has a market share of about 3.7%. So, You still have the opportunity to move ahead in your career in Netezza Analytics. Mindmajix offers Advanced Netezza Interview Questions 2023 that help you in cracking your interview & acquire a dream career as Netezza Analyst.

Below mentioned are the Top Frequently asked Netezza Interview Questions and Answers 2023 that will help you to prepare for the Netezza interview. Let's have a look at them.

We have categorized Netezza Interview Questions into 3 levels they are:

• Basic
• Scenario-Based
• Advanced

Top Frequently Asked Netezza Interview Questions

1. What are the different states of Netezza?
2. Can you insert duplicate rows in the Netezza table?
3. What is a snippet?
4. How the zone maps are created and updated?
5. What are the limitations of materialized views?
6. What is a collocated join?
7. Which statement is true for database users and groups?
8. Explain FPGA and how is it useful for query performance?
9. What happens to records that are loaded during nzload process, but were not committed?
10. What data type works best for zone maps?

Do you want to Enrich your career with an IBM Netezza certified professional, then visit Mindmajix - A Global online training platform: “IBM Netezza Training” Course.  This course will help you to achieve excellence in this domain.

Basic Netezza Interview Questions and Answers

1) What are the data warehouse appliances you know?

• Netezza
• Greenplum
• Vertica
• Teradata
• Dataupia
• Oracle Exadata.

2) What are the environment variables that are required to connect to Netezza?

The environment variables required are: NZ_HOST, NZ_DATABASE, NZ_USER, NZ_PASSWORD.

3) What are the different states of Netezza?

• Online: Normal or usual state.
• Stopped: Netezza will shut down after completing current queries, no new queries allowed.
• Offline: Waits for completion of current queries, new queries in queue receives an error.
• Paused: Same as above, but no error displayed. Typically caused during Netezza boot up or startup.
• Down: Just plain down, could be due to the Netezza server problem or user-initiated.

4) What are the constraints on a table that are enforced?

The only constraint Netezza supports is Not null. There are no primary key and foreign key constraints in Netezza.

5) Can you insert duplicate rows in the Netezza table?

Yes. As there are no primary key constraints in Netezza you can insert duplicate rows.

6) How the NOT NULL specification on a column improves the Netezza performance?

Specifying Not Null on each column in the table results in better performance. Netezza tracks the NULL values at row header level. Having NULL values results in storing references to NULL values in the header. If all columns are NOT NULL, then there is no record header.

7) How FPGA can be helpful in improving query performance?

While reading data from the disk, the Field Programmable Gate Array (FPGA) on each SPU filters out unwanted data. This process of data elimination removes IO bottlenecks and frees up downstream components such as the CPU, memory, and network from processing extra data.

8) What is a snippet?

A snippet is a small unit of work that is carried out in SPU.

9) What are zone maps?

An extent is the smallest unit of disk allocation on an SPU. Zone maps are internal mapping structures to the extent that take advantage of the internal ordering of data to eliminate extents that do not need to be scanned. Zone maps transparently avoid scanning of unreferenced rows. Zone maps are created for every column in the table and contain the minimum and maximum values for every extent.

10) How the zone maps are created and updated?

Zone maps are created and refreshed for every SPU when you Generate statistics, Nzload operation, Insert, update operations, Nzreclaim operation.

11) What is generate statistics and generate express statistics OR what is the difference between generate statistics and generate express statistics?

• Generate statistics is used to gather statistics about each table column’s proportion of duplicate values, minimum values, maximum values, null values, unique values, and updates the system catalog tables.
• The difference between ‘generate statistics’ and ‘generate express statistics’ is based on how the column uniqueness is calculated. The ‘generate express statistics’ calculates estimated dispersion values based on the sampling of rows in the table. ‘Generate express statistics’ uses approximation in generating the stats whereas ‘generate statistics’ uses all the rows in the table.

12) What is the use of creating materialized views?

A materialized view reduces the width (number of columns) of data being scanned in the base table by creating a thin version (fewer columns) of the base table that contains a small subset of frequently queried columns.

13) What is the distribution of materialized views?

A materialized view has the same distribution key as the base table.

14) What are the limitations of materialized views?

• You cannot insert, update, delete, or truncate a materialized view. Any changes on the base tables will reflect materialized views.
• You can specify only one base table in the from clause.
• The base table can’t be an external table, a system table, or a temporary table.
• You cannot use a where clause in the materialized view.
• Expressions are not allowed as columns.

15) What are the best practices of creating materialized views?

• Create materialized views with few columns which are frequently queried.
• Specify order by clause on the most restrictive columns (columns used in where clause).
• Periodically or manually refresh the materialized views.

16) What are the partitioning methods available in Netezza?

There are two partitioning methods available in Netezza:

• Random partitioning: Distributes the data randomly.
• Hash Partitioning: Distributes the data on the specified columns.

17) Up to how many columns you can specify in distributing on clause?

You can specify up to four columns in the distribution clause.

18) If you did not specify any distribution on clause while creating a table, what distribution Netezza uses?

Netezza distributes the data on the first column and it uses Hash partitioning.

19) Can you update the columns used in the distribution clause?

No, the column that is used in the distribution clause cannot be used for updates.

20) What data types are most suited for the columns specified in the distribution clause?

Integer.

Netezza Scenario-Based Interview Questions

21) How do you redistribute a table?

Use Create Table As (CTAS) to redistribute the data in a table. While creating the new table specify the distribution on clause to distribute the data on the new columns.

22) If you did not specify any distribution clause, how the Create Table AS (CTAS) will distribute the rows?

CTAS will get a distribution from the original table.

23) How do you check the rows in a table are equally distributed in all SPU’s or not?

To check the distribution of rows run the following query

SELECT data sliced, COUNT(*)FROM GROUP BY data sliced

24) What is a collocated join?

When you join tables that are distributed on the same key and used these key columns in the join condition, then each SPU in Netezza works 100% independent of the other, as the required data is available in itself. This type of joins is called collocated joins.

25) When Netezza redistributes a table and when it broadcasts a table?

Whenever it is not possible to do a collocated join, Netezza either redistributes the tables or broadcasts the table. When the table is a small one, then Netezza broadcasts the table. Otherwise, Netezza redistributes the table.

26) How do you remove logically deleted records?

Whenever you delete a row in a table, it is not physically deleted. It is logically deleted by flagging the deleted field in the table. NZRECLAIM utility is used to remove the logically deleted records.

27) What is nzload?

Nzload utility is used to load data from a file into a table. It is used to load bulk data quickly and simultaneously rejects erroneous content.

28) What are the ways to load data from a table into a file?

• Create an external table.
• Use NoSQL utility with -o option.

29) What are the different ways to load data from a file into a table?

• Use nzload to load the data from a file into a table
• Create an external table and then load the original table using the external table.

30) How Netezza updates a row in a table?

Netezza logically deletes the original row by flagging the deleted column with the current transaction id and inserts a new row with the updated values.

31) Which two characteristics describe the materialized view?(Choose two.)

A. Materialized views can contain aggregates.

B. Materialized views can contain a HAVING clause.

C. Materialized views can contain a WHERE clause.

D. Materialized views can contain an ORDER BY clause.

E. Materialized views can reference only one base table in the FROM clause.

The answer is D, E.

32) Which CREATE DATABASE attributes are required?

A. The database name.

B. The database name and the redo log file name.

C. The database name and the tablespace name.

D. The database name and the temporary tablespace name.

The answer is A.

33) Which statement is true for database users and groups?

A. All users must belong to the admin group.

B. Creation of users and groups is not allowed.

C. Users and groups are local and tied to a particular database.

D. Users and groups are global and not tied to a particular database.

The answer is D.

34) What is the maximum number of columns you can choose as organizing keys for a clustered base table?

A. 1

B. 2

C. 4

D. 8

The answer is C.

35) What should be considered when you are asked to select the distribution key columns for a very large fact table?

A. As many columns as possible.

B. Columns that contain many nulls.

C. Columns contain few unique values.

D. Columns used to join to other large tables.

The answer is D.

36) What is the command to display/monitor the status of a reclaim?

A. NZ stats -type reclaim

B. nzinventory -type regent asks

C. nzstate -type reclaim

D. nzinventory -type reclaim

The answer is A.

37) Which log file captures SQL submitted to the NPS system?

A. /nz/kit/log/dbos/dbos.log

B. /nz/kit/log/sysmgr/sysmgr.log

C. /nz/kit/log/eventmgr/eventmgr.log

D. /nz/kit/log/postgres/pg.log

The answer is D.

38) Which one of the following pieces of information is maintained by Statistics on a table?

A. Table definition and structure

B. Min and Max value of columns

C. Table Object id

D. Access information

The answer is B.

39) Which one of the following pieces of information is maintained in the zone map table to assist the performance of a query?

A. Table definition and structure for each column

B. Table object id stored in the catalog

C. Min and Max value of a column to an extent.

D. Number for rows in a table

The answer is C.

40) Which system table could be used to find an object id for any table/view or object in a database?

A. _t_spu

B. _t_object

C. _t_attribute

D. _t_names

The answer is B.

Advanced Netezza Interview Questions

41) Explain FPGA and how is it useful for query performance?

FPGA: Field Programmable Gate Array (FPGA) is located on each SPU. Netezza is different from other architectures. Netezza can do a “hardware upgrade” through software by using FPGA. Hardware is reconfigured during install.

While reading data from disk, FPGA on each SPU also helps in ‘filtering’ unnecessary data before getting loaded into memory on each SPU. This way, FPGA does not overwhelm all the data from the disk.

42) What is a zone map?

The zone map in Netezza is similar (concept-wise) to partitions in Oracle. Netezza maintains a map for data so that it does rely on a zone map to pull only the range it is interested in.

For example, if we need to pull out data from Jan 2009 till June 2009 from a table that is distributed on the date column, the zone map helps us to achieve this. The zone map is maintained by Netezza automatically, no user intervention needed. Zone mapping is done at a block (extent) level. Netezza has zone maps for all columns (not just distributed columns) and includes information such as minimum, the maximum, total number of records.

43) How do you deal with historical data, with respect to zone maps?

Sort data first, based on historical data (for example, date), and load this in using nzload.

/../ibm-netezza" target="_blank" rel="noopener">Check Out Netezza Tutorials

44) What are different ways to load?

• nzload
• External tables
• Create table AS (aka, CTAS).
• Inserts (Eeeewee!!).

45) Does everything get cached in Netezza (or any other data appliance)?

Typically only schema and other database objects are cached in appliances. Data is not cached, in general. In most cases, data is not saved anywhere (in any cache or on the host computer) and is streamed directly from SPU to client software.

46) What is the best data appliance?

Obviously, it all depends. This is my (limited) view:

• From features respect, Green Plum.
• Popularity with a bit of hype, Netezza.
• Matured and well respected, Teradata.
• With existing database integration, Dataupia.

Largest implementations:

• Teradata: 72 nodes (two quad-core CPUs, 32GB RAM,104 / 300GB disks per node) and manages 2.4PB.
• Greenplum: Fox Interactive Media using a 40-node, Sun X4500 with two dual-core CPUs, 48 / 500GB disks, and 16 GB RAM (1PB total disk space)
  Source: Vertica’s Michael Stonebraker!

47) How is load achieved in Netezza and why is that quick/fast?

Loads bypass a few steps that typically a query would go through (a query goes through plan generation, optimization, and transaction management). Loads are done in terms of “sets” and this set is based on underlying table structure (thus loads for two different tables are different as their sets are based on table structures). Data is processed to check the format and distribution of records calculated very quickly (in one step), fills into the ‘set’ structure, and writes to the storage structure. Storage also performs space availability and other admin tasks, all these operations go pretty quick (think of them as UNIX named pipes that streams data, and SPU stores these records).

48) When are we likely to receive incorrect (aggregate) results?

Very rarely a driver may return aggregated results that are still getting processed back to the client. In this case, the client may assume that the calculation is complete, instead of updating with the latest or final results. Obviously, the driver has to wait for Netezza to complete operation on the host computer, before delivery.

49) Explain how data gets stored in Netezza and how does SPU failover takes place?

Data is stored based on a selected field(s) that are used for distribution.

==Data (A)==> Hash Function (B) ==> Logical SPU identifier list (C) ==> Physical SPU list (D) ==> Storage (E)

When data arrives, it is hashed based on the field(s) and a hash function (B) is used for this purpose.

For example, for a hypothetical 32 node system, the logical SPU identifier list has 32 unique entries. If there are 1000 hashed data items from (B), there are 1000 entries in (C), all having only 32 SPU entries (a number of data items go to the same SPU, thus multiple (B) entries map to the same (C)). For instance, (C) has values [3,19,30,7,20,25,11,3,22,19….]. This way, 1000 data entries are mapped. (D) has a physical IP address of both primary and failover SPU. If there is a failover, this is the only place where Netezza needs to update its entries. The same goes for a system that has a new SPU added. It is a little complicated, in principle, this is the concept.

50) What are 4 environment variables that are required. What are the different states on Netezza?

Environment variables: NZ_HOST, NZ_DATABASE, NZ_USER, and NZ_PASSWORD

• Online: Normal or usual state.
• Stopped: Netezza will shutdown after completing current queries, no new queries allowed.
• Offline: Waits for completion of current queries, new or queries in queue receive an error.
• Paused: Same as above, but no error displayed. Typically caused during Netezza bootup or startup.
• Down: Just plain down, could be due to Netezza server problem or user-initiated.

51) Does Netezza support the concurrent update of the same record?

In case of conflict in which the same record is set for modification, Netezza rolls back a recent transaction that is attempted on the same record, in fact, the same table. This is generally acceptable in DW environments. Netezza does support serialization transactions and does not permit dirty reads.

52) How Netezza updates records. Give an idea of how transactions are maintained and how to read consistency is maintained?

Netezza does not update records in place, it marks records with a delete flag. In fact, each record contains two slots, one for create Xid another for delete xid. Delete xid allows us to mark a record with a current transaction for deletion, up to 31 transactions are allowed in Netezza for all tables. As noted earlier, only one update at a time allowed on the same table though. Here update refers to transactions that are not committed yet. Coming back to delete xid, this is how Netezza maintains transaction rollback and recovery. Once a record is modified, its delete xid is given transaction id; this is changed from the previous value of 0, all records when loaded will contain 0 for delete xid. Note that FPGA uses its intelligence to scan data before delivering them to host or applications.

Sample data:

[ROW id][Create xid][Delete xid]
[R1][T1][0]                           // First time a record is loaded, record R1
// After some time, updating the same record
[R1][T1][T33]                      // Record R1 is updated; note T33
[R33][T33][0]                     // New update record R33; similar to a new record this has zero for Delete Xid.

If the record is deleted, simply deletion xid will contain that transaction id.

• Based on the above, how do you know a record is the latest. It has zero in delete xid flag.
• Extending the same logic, how do we know a record is deleted. It has a non-zero value in the delete xid flag.
• How do you roll back to the transaction? Follow similar to the above listing, we can roll back a transaction of our interest.
• Note that the transaction id is located in the create xid flag and that is our point of interest in this case. From what I know, row id and create id is never modified by Netezza.

53) What happens to records that are loaded during nzload process, but were not committed?

They are logically deleted and the administrator can run nzreclaim, we may also truncate the table.

54) Can a group become a member of another group in Netezza user administration. Can we use the same group name for databases?

In Netezza, a public group is created automatically and everyone is a member of this group by default. We can create as many groups and any user can be a member of any group(s). Group can not be a member of another group. Group names, user names, and database names are unique. That is, we can not have a database called sales and a group also called sales.

55) How can we give global permission to user joe so that he can create a table in any database?

Login into the system database and give that permission to the user by saying “grant create a table to joe;”

56) What permission will you give to connect to a database?

List. Grant list, select on the table to the public (if logged into sales database, this allows all users to query tables in sales database).

57) Do we need to drop all tables and objects in that database, before dropping a database?

No, the drop database will take care of it.

58) What constraints on a table are enforced?

Not null and default. Netezza does not apply to PK and FK.

59) Why NOT NULL specification is better in Netezza?

Specifying not null results in better performance as NULL values are tracked at row header level. Having NULL values results in storing references to NULL values in the header. If all columns are NOT NULL, then there is no record header.

60) Create Table AS (CTAS), does it distribute data randomly or based on the table on which it received data?

Response: Newly created table from CTAS gets distribution from the original table.

61) Why do you prefer to truncate instead of drop table command?

Just empties data from the table, keeping table structure, and permission intact.

62) When no distribution clause is used while creating a table, what distribution is used by Netezza?

The first column (same as in Teradata).

63) Can we update all columns in a Netezza table?

No, the column that is used in the distribution clause cannot be used for updates. Remember, up to four columns can be used for the distribution of data on SPU. From a practical sense, updating distribution columns result in the redistribution of data; the single most performance hit when a large table is involved. This restriction makes sense.

64) What data type works best for zone maps?

Zone maps work best for integer data types.

65) What feature in Netezza you do not like?

Of course, a large list, especially when compared to Oracle. PK and FK enforcement is a big drawback though this is typically enforced at ETL or ELT process [ELT: Extract, Transform, and Load. Note that ‘Transform’ and ‘Load’ can happen within Netezza].