There is a best way to pull this off, but it's too lengthy to explain here. So I wrote a book Netezza Underground on Amazon.com (at the risk of shameless promotion)

So the way to handle the Netezza machine like this, and many people do, is to build it out as an asset rather than a one-off, and also to treat it like a chugging processor (input/process/output) when you want to do data processing. Migrate all of your functionality out of the SSIS SQL and into Netezza SQL, and restructure how you solve the problems. You can create intermediate tables on the fly with the create-table-as-select, but I would wave you off of manufacturing new columns on the fly in the final target tables as part of the data processing. Your report users, if they are using a rules-based system, will not like the outcome.

Netezza 6.0 supports using "alter table" to add/drop columns.  Latest info I've heard says 6.0 should be available late this month (September 2010), but I've not seen an official release date annoucement so that is subject to change without notice.

For near real time data movement from SQL Server (or Oracle, DB2, Sybase, Informix) to Netezza, you should take a look at DBMoto.  They offer a 30 day trial license on their website: http://www.hitsw.com/products_services/dbmoto/dbmoto.html

WisdomForce also has a product called DBSync that does near real time replication from an Oracle source to a Netezza target.

You can certainly accomplish data synchronization without using any tools at all, but you should always take the cost of development into account as well... the cost of the time for the developer to write the code and the support costs over time (which can really eat you alive if you do not have a very standard way of doing things).  In a simple case you may save money by doing it yourself, but if you have 100s of tables you will certainly save money using a tool designed for the purpose.

The simplest way to do it is to put a trigger on each table in SQL Server that you want to monitor and insert the primary key value of each record that changes into a staging table.  You can then grab all of the distinct PK values from the staging table and join that back with the source table to get all of the data (rows that no longer exist were deleted... have to use a left join).  Save the data to a file, and use nzload to load it into Netezza.  You could also use triggers plus an ETL tool such as SSIS, Informatica, DataStage, etc.

For small tables the best method is just to copy the entire table from the source to Netezza if you maintain the same table structures on both systems (small is relative, but it takes very little time to copy 100,000 rows for example).  For large tables you would want to use the trigger based mechanism although this can have a substantial effect on the performance of the programs that load data into the database.  This is another reason why a tool such as DBMoto is valuable... it adds very little to the workload on the source database since it is just parsing the transaction logs to get the changes instead of having to use a trigger to capture them in the database itself.

Another option is to add a last update timestamp to each row in the source table which is probably updated using a trigger.  You can then just grab all the records that have been modified since the last time you checked the table.  This method, however, does not capture deletes.

Just to add a tuppins to Shawn's comments. SQLServer provides for a rowversion - it's used for transactional concurrency and will automatically change (upward) whenever the row is changed for any reason. This allows you to track the given table by the rowversion that you last saw as the max, and the rowversion that you now see as the max, and then take the delta. Of course, if the model has not implemented rowversions, then you can't use them. But these are standard fare for most online transactional systems. Deleted records, as Shawn noted, you're on your own.