Most frequently asked Redshift Interview Questions

What is Amazon Redshift?
Explain the process of Amazon Redshift?
What is the purpose of using AWS Redshift database ?
What are the features of Amazon Redshift?
What is AQUA for Amazon Redshift?
Which node types support AQUA?
Explain the architecture of ELT and ETL patterna in AWS Redshift?
What are the limitations of Amazon Redshift?
How can we send a CSV File from S3 into Redshift with an AWS Lambda Function?
How can we find size of database, schema, table in redshift?
How to create an Index in Amazon Redshift?
How to unload a table on RedShift to a single CSV file?
How can Redshift load CSV File using Copy and Example?
How can we measure table space on disk in RedShift?

What is Amazon Redshift?

Amazon Redshift is used for analyzing all our data using standard SQL and our Existing Business Intelligence tools and also used for allowing us in running complex analytic queries that using structured and semi structured can lead against terabytes to petabytes, sophisticated query optimization, columnar storage on high-performance storage, and massively parallel query execution.Redshift is used for transformation and can open data formats which includes Avro, CSV, Grok, Amazon Ion, JSON, ORC, Parquet, RCFile, RegexSerDe, Sequence, Text, Hudi, Delta and TSV.It gives us fast querying capabilities over structured data using familiar SQL based clients and business intelligence tools by using standard ODBC and JDBC connections.

Explain the process of Amazon Redshift?

What are the purpose of using AWS Redshift database ?

Amazon Redshift is known as a managed petabyte scale data warehouse service which is used in cloud and can be started just a few gigabytes of data and scale to a petabyte or more.It is used for enabling us for using our data for aquiring new insights for our business and customers.

What are the features of Amazon Redshift?

Supports VPC - redshift allows the users to launch within VPC and also in controlling access to the cluster through the virtual networking environment.
Encryption - all the data stored in redshift can be encrypted and configure while creating tables.
SSL - redshift allows connections between clients and Redshift for encryption.
Scalable - it allows us in scaling over storage capacity without any loss in performance.
Cost-effective - redshift is a cost-effective alternative to traditional data warehousing practices with no up-front costs, no long-term commitments and on-demand pricing structure.

What is AQUA for Amazon Redshift?

Advanced Query Accelerator(AQUA) is a hardware-accelerated cache which enables redshift for running up to 10x faster than any other enterprise cloud data warehouse.All the data in warehousing architecture with centralized storage requiring data be moved to compute clusters for processing.AQUA is used in bringing the compute to storage by doing a substantial share of data processing in-place on the innovative cache.

Which node types support AQUA?

Nodes supported by AQUA are :
RA3 .16XL
RA3 .4XL

Explain the architecture of ELT and ETL patterna in AWS Redshift?

What is Redshift Spectrum?

Redshift Spectrum is used for enabling us for running queries against exabytes of unstructured data in Amazon S3, with no loading or ETL required.It generates and optimizes a query plan.Spectrum also scales out to thousands of instances if needed, so queries run quickly regardless of data size.We can also use the same SQL for Amazon S3 data as you do for our Amazon Redshift queries and and also connects to the same Amazon Redshift endpoint using your same BI tools.

What are the limitations of Amazon Redshift?

Redshift limitations are on the number of tables that we can create in a cluster by node type.
An Amazon Redshift table can't have more than 1600 columns.

How can we send a CSV File from S3 into Redshift with an AWS Lambda Function?

How can we find size of database, schema, table in redshift?

Amazon Redshift systems tables provides information about the user defined table .The tables are only visible to the super users, and its coloumn is the table's name.
Here is the command provided for making tables:

SELECT "table", size, tbl_rows FROM SVV_TABLE_INFO

How to create an Index in Amazon Redshift?

create index on session_log(UserId);

How to unload a table on RedShift to a single CSV file?

unload ('select * from venue')
to 's3://mybucket/tickit/unload/venue_' credentials
'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
parallel off;

How can Redshift load CSV File using Copy and Example?

How can we measure table space on disk in RedShift?

select
    trim(pgdb.datname) as Database,
    trim(pgn.nspname) as Schema,
    trim(a.name) as Table,
    b.mbytes,
    a.rows
from (
    select name, sum(rows) as rows
    from stv_tbl_perm a
    group by db_id, id, name
) as a
pgclass as pgc on pgc.oid = a.id
pgnamespace as pgn on pgn.oid = pgc.relname space
pgdatabase as pgdb on pgdb.oid = a.db_id
join (
    select tbl, count(*) as mbytes
    from stv_blocklist
    group by tbl
) b on a.id = b.tbl
order by mbytes desc, a.db_id, a.name;