Netezza Interview Questions and Answers

Do you have the technical knowledge and capacity of a Netezza developer? Are you searching for a job where your skills can be applied while you can gain professional experience? If yes, then you will be happy to browse through the ACTE page to get all the required information at one single place. With the growing competitive scenario, the demand for Database administrators, who can give fast access to the consumer data and can help to provide analytical solutions is also increasing. In a Netezza job you can work for business intelligence, advanced analytics, data warehouse applications as well as for business planning of any enterprise. Read the information on ACTE page and go through the Netezza job interview questions and answers to guide yourself to pass the job interview and crack the job hunt.

1. Explain FPGA and how is it useful for query performance.

Ans:

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 with all the data from disk.

2. What is a zone map.

Ans:

Zone map in Netezza is similar (concept wise) to partitions in Oracle. Netezza maintains map for data so that it does relies on 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 date column, zone map helps us to achieve this. Zone map is maintained by Netezza automagically, no user intervention needed. Zone mapping is done at a block (extent) level. Netezza has zone maps for all columns (not just distributed column) and includes information such as minimum, maximum, total number of records.

3. How do you deal with historical data, with respect to zone maps.

Ans:

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

4. What are different ways to load

Ans:

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

5. Does everything gets cached in Netezza (or any other data appliance).

Ans:

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

6. What is the best data appliance.

Ans:

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

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

Largest implementations:

1. 1. Teradata: 72 nodes (two quad-core CPUs, 32GB RAM,104 / 300GB disks per node) and manages 2.4PB.
2. 2. 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)
3. 3. Source: Vertica’s Michael Stonebraker!

7. How is load achieved in Netezza and why is that quick / fast.

Ans:

Loads by pass 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 format and distribution of records calculated very quickly (in one step), fills into ‘set’ structure and writes to 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).

8. When are we likely to receive incorrect (aggregate) results.

Ans:

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

9. Explain how data gets stored in Netezza and how does SPU failover take place.

Ans:

Data is stored based on a selected field(s) which are used for distibution.

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

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

Ans:

For example, for a hypothetical 32 node system system, 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 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 physical IP address of both primary and failover SPU. If there is a failover, this is the only place where Netezza need to update its entries. Same goes for a system that has a new SPU added. It is a little complicated, in principle, this is the concept.

11. what are 4 environment variables that are required. What are different states on Netezza.

Ans:

Environment variables:

1. 1. NZ_HOST
2. 2. NZ_DATABASE
3. 3. NZ_USER
4. 4. NZ_PASSWORD

Following are the different states,

• 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 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.

12. How Netezza updates records. Give an idea of how transactions are maintained and how read consistency is maintaned.

Ans:

Netezza does not update records in place, it marks records with 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 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 roll back and recovery. Once a record is modified, it’s delete xid is given transaction id; this is changed from 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:

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 same logic, how do we know a record is deleted. It has non zero value in delete xid flag.

How do you roll back to transaction. Follow similar to above listing, we can roll back a transaction of our interest.

Note : that transaction id is located in 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.

13. How can we give a global permission to user joe so that he can create table in any database.

Ans:

Login into system database and give that permission to user by saying,

14. Do we need to drop all tables and objects in that database, before dropping a database.

Ans:

No, drop database will take care of it.

15. What feature in Netezza you do not like.

Ans:

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].

16. What data type is most suited for zone maps

Ans:

Zone maps are typically useful for integers, date and time, variations of this data type. Zone maps are useful for ordered that that are usually built into data that is loaded; for example, phone call logs. [Also check more on zonemaps in my original post below].

17. How are materialized views (MV) used in Netezza.

Ans:

Similar to other databases, materialized views are defined against base tables. Just like in views, we can not insert, update or delete from MV. MV is automatically used by optimizer when appropriate. When base table data changes, MV gets automatically updated by Netezza. MV is based on a single base table, thus practically not as useful.

18. What are typical join types in Netezza

Ans:

Mostly hash joins (note that Netezza may do hash join in memory or on the SPUs if memory is not sufficient for doing a hash join); in some cases sort merge or even some cases nested loops  are performed (did not really come across much; In Netezza lingo it is called either function based join or something similar, not sure about the correct term). Of course, cross or product joins are possible like in any databases.

19. What is equivalent of replication of a table in Netezza on all SPUs.

Ans:

Imagine a situation in which a small table (us_states) is required for joining against a large table (creditcard_txn). Netezza may decide to read this table from all SPUs (remember, table data is spread on all SPUs) and “assemble” this table on host. This data is then broadcasted to all SPUs, resulting in a copy on each SPU. Note that an important db parameter “factrel_size_threshold” holds that triggering number; any table beyond these many number of rows is considered as a fact table. That is, if this parameter is set to 1 million, any table holding more than 1 million rows is considered by Netezza as a fact table and will not result in this replication or broadcast.

20. Primary goal of a table design is to distribute data evenly on all tables. Is it a good idea to choose multiple columns in Netezza so that data gets distributed evenly.

Ans:

No, unless all columns are used during a join process. Most likely, this results in a large amount redistribution of data during query execution.

21. From design point of view, do you forsee performance problem using order ID as an integer for one table and Order num as varchar for another table as distribution keys. Assuming that these two tables are often used for join, do you see any performance problem.

Ans:

Yes, we will encounter performance problem as Netezza redistributes integer to varchar type and recreates the first table. This can be avoided using both tables distributed on order number as either integer or varchar.

22. What is a snippet

Ans:

Snippet is a small block of database operation, typically three to four operations, that are carried out on all SPUs where data is location. If a query results in these snippets: Snippet A, Snippet B, Snippet C, ….Snippet X; they are carried out in a sequential manner.

23. List which options are prioritized when join operation is required.

Ans:

Netezza evaluates joins in this order of preference:

1. 1.Colocated joins: All data for joins are located on the same SPU.
2. 2. Redistribute: All required data is not located in the same SPU, send data to corresponding SPU where driving table data is located.
3. 3. Broadcast: Mentioned as replication above; Send all data from SPU to host which collates all that data, sends it to all SPUs. Each SPU has entire table data. That is, if Netezza machine has 32 SPU, there will be 32 physical tables, one one each SPU.

Coming to joins, typically Netezza prefers in this order:

• Hash join in memory
• Hash join on disk
• Sort Merge join
• Nested loops
• Cross join

Oracle preference closely resembles the same or similar order.

24. How can I look into some system parameters

Ans:

nzsystem showRegistry: Command for looking into system specific information

/nz/data/postgresql.conf: To check NZ db parameters; we can change this file OR use set command at SQL.

25. Why integer data type is preferred in Netezza.

Ans:

A couple of reasons:

1. 1. Better joins, thus effecient.
2. 2. Netezza compress works only for integer type of data, not with varchar or date.
3. 3. Zonemaps are based on integer data types.

26. How can we find log (SQL) activity for a day.

Ans:

We can find this under /nz/kit.x.y/log/postgres/pg.log file. Older files are named as pg.log.N (Where N starts from 1, after pg.log file this is the latest file). Assuming that we are looking for a week day within pg.log, we may run

If this produces no data, look for corresponding log file based on the last update timestamp (ls -ltr sorts them in reverse time stemp order).

27. What are the ways to get data into Netezza. What happens if inserts are interrupted, how Netezza handles commits.

Ans:

Please see my previous posting first. Here is a short list: load using nzload, SQL inserts (very slow), create table as command or inserts from other tables, and external tables. When insert is interrupted, all rows that are inserted are already committed unless we use transaction command.

28. How do you nzsystem command

Ans:

Most Netezza commands come with a variety of sub commands. For nzsystem, we can list a number of parameters using “nzsystem showregistry”. To find quickly if Netezza is up and running, use

However, using these commands require you to set “NZ_USER” and “NZ_PASSWORD” at unix level. You can do this with,

and then run this command. If not, you can specify login and password combo at command level. I do not prefer this way, as any users can do “ps -ef” to check what commands you are using, which also gives login and password you entered at shell command level.

29. Where are Netezza binaries stored

Ans:

Two important bin locations for Netezza are:

1. 1. /nz/kit.x.y/sbin
2. 2. /nz/kit.x.y/bin

Some configurations are located in /nz/data directory.

30. How can we remove formatting with NZSQL

Ans:

NZSQL by default shows text formatted. In cases in which we do not need white spaces, use “nzsql -A” option. For example,

allows us to run SQL command direct without logging into nzsql interactively.

31. Is there a way to stop NZSQL command, if one of the SQL commands fail.

Ans:

Yes. A similar feature exists in other databases too. In this case, ON_ERROR_STOP=true on nzsql command so that other commands do not get executed. For instance, we like to create a new table (CTAS) and later drop old table. If a new table creation fails, we certainly do not want to drop old table. In this case, this option is very useful.

32. Can we access data in other databases with the same NZSQL.

Ans:

This depends on the version. Version 5 and upwards support selects against database.owner.table, provided that user has same login and password access. Inserts are allowed in the current database with data from other databases, not the other way (meaning, we cannot insert into another database from the database where we logged in).

33. How can we plan and corresponding CPP files.

Ans:

We can just to ‘explain’ on a query to see how plan looks. At run time, plans are created under /nz/data/plans we will see corresponding plans generated during run time. Corresponding CPP code is located under /nz/data/cache/

34. Does Netezza support concurrent update of the same record

Ans:

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

35. What happens to records that are loaded during nzload process, but were not committed.

Ans:

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

36. Can a group become a member of another group in Netezza user administration. Can we use same group name for databases.

Ans:

In Netezza, public group is created automatically and every one is a memeber of this group by default. We can create as many groups and any user can be 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.

37. What permission will you give to connect to a database.

Ans:

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

38. What constraints on a table are enforced.

Ans:

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

39. Why NOT NULL specification is better in Netezza.

Ans:

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

40. Create Table AS (CTAS), does it distribute data randomly or based on table on which it received data.

Ans:

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

41. Why do you prefer truncate instead of drop table command.

Ans:

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

42.When no distribution clause is used while creating a table, what distribution is used by Netezza.

Ans:

First column (same as in Teradata).

43. Can we update all columns in a Netezza table.

Ans:

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

44. What is dataslice and SPU.

Ans:

For me, they are the same! Of course, this answer is not accurate reply in your interview(s).

45. What data type works best for zone maps.

Ans:

Zone maps work best for integer data types.