Databases Flashcards
1
Q
Do NoSQL databases perform aggregations such as SUM() or AVG()
A
No
2
Q
Do NoSQL databases scale horizontally?
A
Yes
3
Q
Is DynamoDB Highly Available across Multiple AZs out of the box?
A
Yes
4
Q
Does Dynamo DB integrate with IAM?
A
Yes
5
Q
DynamoDB is made of ____________?
A
Tables
6
Q
DynamoDB tables must have a _________?
A
Partition key
7
Q
Each DynamoDB item has________?
A
Attributes
8
Q
What is the maximum size of an item in dynamodb?
A
400kb
9
Q
What is a partition key in DynamoDB?
A
It acts as a primary key and must be unique for each item. It also must be diverse enough to data is distributed.
10
Q
What two primary key options are available in DynamoDB?
A
Partition Key
Partition Key + Sort Key
11
Q
When using a partition key and sort key in DynamoDB, what is the limitation?
A
The partition and sort key combination must be unique.
12
Q
What are the two table classes in DynamoDB?
A
DynamoDB standard
DynamodDB standard-IA
13
Q
What does the sort key really do in the UI?
A
Allows you to sort on a column.
14
Q
What are the capacity modes in DynamoDB?
A
Provisioned Mode
On-Demand Mode
15
Q
How often can you switch between capacity modes in DynamoDB?
A
Every 24 hours
16
Q
If you exceed your RCU or WCU in DynamodDB, what error is returned?
A
ProvisionedThroughputExceededException
17
Q
How can you deal with ProvisionedThroughputExceededException without increasing WCU or RCU?
A
Exponential Backoff
18
Q
What is the measurement for one WCU?
A
One item per second for an item up to 1kb in size.
19
Q
If you have an item that is 4.5kb in size, how many WCU is required?
A
5 .. Rounding up is required.
20
Q
What are the two kinds of reads in Dynamodb?
A
Strongly Consistent
Eventually Consistent
21
Q
What is the problem with Eventually consistent reads?
A
Old data may be returned if there is replication latency.
22
Q
What does a consistent read in DynamoDB do?
A
It reads the data after a write, but ensures that it is the most recent data.
23
Q
How much more RCU do ConsistentReads use?
A
Twice as much
24
Q
A single RCU is how much for a strongly consistent read?
A
One read per second up to 4kb in size
25
A single RCU is how much for an eventually consistent read?
Two read per second up to 4kb in size
26
Are WCU and RCU spread evenly amongst partitions?
Yes.. That means if I have 5 partitions and 5 WCU, each partition will get one.
27
In DynamoDB on-Demand mode, do you need to provision RCU/WCU?
No. This scales up and down automatically.
28
If you want to change an item's attributes in DynamoDB, what API call is used?
UpdateItem
29
If you want to create an item in DynamoDB, what API call is used?
PutItem
30
If you want to fully replace an item in DynamoDB, what API call is used?
PutItem
31
If you want to read an item in DynamoDB, what API call is used?
GetItem
32
What does a ProjectedExpression in DynamoDB do?
It can be specified to retrieve only certain attributes from an item.
33
What is a FilterExpression in DynamodDB?
Additional filtering after the query operation has been completed. Only works with non-key attributes and does not support hash or range.
34
How much data will scan return in DynamodDB?
1MB, you must use pagination to keep on reading.
35
Can you use Scan to filter out data?
Yes, but you are still loading all attributes. This is inefficient.
36
What can be done in DynamoDB for more performance?
Use Parallel Scan.
37
How many PutItem API calls can be included in a BatchWriteItem call in Dynamo DB?
25 PutItem and/or DeleteItem in one call.
16MB od data written and up to 400K per item.
38
Can BatchWriteItems in DynamoDB be used with UpdateItem?
No
39
How many GetItem API calls can be included in a BatchGetItem call in Dynamo DB?
100 items, up to 16MB of data
40
In DynamoDB, are items being retrieved from a BatchGetItem API call retrieved in parallel?
Yes
41
What is PartiQL?
A SQL like query language for Dynamo DB
42
What are the two types of indexes in DynamoDB?
LSI and GSI
43
Does an LSI use the same partition key of the base table?
Yes
44
What data types can be used for a sort key in an LSI?
String, Number, Binary
45
How many LSI can you have per table?
5
46
When are LSIs defined?
Creation of the table
47
Can LSI only include certain attributes?
Yes, using attribute projections.
48
Does a GSI use the same partition key of the base table?
No
49
Do you have to provision WCU and RCU for a GSI?
Yes
50
Can a GSI be added or modified after a table is created?
Yes
51
When a GSI is throttled, what happens to the main table?
It is also throttled.
52
Where do LSIs get their WCU and RCU?
From the main table
53
What problem does DynamoDB Accelerator (DAX) solve?
The problem of too many reads.
54
What is the default TTL for DynamoDB DAX caches?
5 minutes
55
How many nodes can be in a DAX Cluster?
10
56
What are DynamoDB Streams?
They write item level modifications in a table.
57
Where can DynamoDB Streams stream to?
Kinesis Data Streams
Lambda
KCL
58
How long is data in a DynamoDB Streams retained?
24 hours
59
What do KEYS_ONLY show in DynamoDB Streams?
Only the key attributes of the modified items.
60
What does NEW_IMAGE show in DynamoDB Streams?
The entire item as it appears after it was modified
61
What does OLD_IMAGE show in DynamoDB Streams?
The entire item as it appears before it was modified
62
What does NEW_AND_OLD_IMAGES show in DynamoDB Streams?
Both the new and old images of the item.
63
Can DynamoDB streams be used retroactively?
No
64
If you want Lambda to Poll DynamoDB Streams, how would you configure it?
Using Triggers. Use the DynamoDB trigger.
65
What does TTL do in DynamoDB?
It allows you to automatically delete items after an expiry timestamp.
66
Does TTL use WCU?
No
67
What is the max length for expired items to be deleted?
48 hours
68
What data type should the TTL use?
Number with Unix Epoch timestamp
69
What is the best way to store images in DynamoDB?
Use S3 for the object and store the key into dynamoDB.
70
What are Global Tables in DynamoDB?
Multi-Region, Multi-Active, Fully replicated tables.
71
Does DynamodDB support DMS?
Yes
72
Do all RDS databases support ACID transactions?
Yes
73
In ACID transactions, what happens to a transactions that partially fails?
The entire transaction fails
74
How many read replicas can you have in Aurora?
15
75
Does Aurora have continuous backup to S3?
Yes
76
What are the two types of locks?
Shared
Exclusive
77
What does a shared lock do?
Allows reads, but prevents writes
78
What does an exclusive lock do?
Prevents all reads and writes to a resource. Only one transaction can hold the lock.
79
Do relational databases manage locks automatically?
Yes
80
What is Document DB?
A NoSQL database based on MongoDB
81
What is MemoryDB for Redis?
A redis compatible in-memory database service.
82
What is Amazon Keyspaces?
Managed Apache Cassandra NoSQL database
83
How do you query Amazon Keyspaces?
Cassandra Query Language (CQL)
84
What is Amazon Neptune?
A fully managed graph database.
85
What languages does Amazon Neptune support?
Gremlin, OpenCypher, and SPARQL
86
What is Amazon Timestream?
A managed timestream database
87
What is Amazon Redshift?
A petabyte scale data warehouse
88
Is Redshift OLAP or OLTP?
OLAP
89
What nodes are in a Redshift cluster?
A leader node and one or more compute nodes.
90
What does the leader node do?
It communicates with the client and also develops an execution plan.
91
What do the compute nodes do?
They execute the execution plan generated by the leader node.
92
What does each compute node have in regards to resources?
CPUs, Memory, and attached disk storage.
93
What are node slices in Redshift?
They process a portion of the workload that is assigned to that node.
94
Where do the resources from node slices come from?
They come from the resources assigned to the compute node.
95
What compression does RedShift Spectrum support?
GZIP and Snappy
96
What block size does RedShift use?
1MB
97
What happens when you turn the backup retention period to zero?
It disables automatic backup.
98
What cluster types must be used for Multi-AZ Redshift?
RA3
99
Does Redshift scale horizontally or vertically?
Both
100
What is the process for Redshift Scalling on the backend?
Your old cluster remains available for reads
A new cluster is created
The cname is flipped to a new clister
Data moved in parallel to new compute nodes
101
What are the Redshift distribution Styles?
Auto
Even
Key
All
102
What does the Redshift distribution style Auto do?
The default. It bases distribution on what it thinks is best.
103
What does the Redshift distribution style Even do?
It distributes data in a round robin fashion.
104
What does the Redshift distribution style Key do?
It distributes data based on the value in a column.
105
What does the Redshift distribution style All do?
It distributes the entire dataset to every node.
106
How do you get data into Redshift?
Using the COPY Command?
107
Where can I use the COPY command from?
S3, EMR, DynamoDB, and remote hosts
108
When using the COPY command from S3, what is required?
A manifest file and an IAM role.
109
How do you get data out of RedShift?
Use the UNLOAD command
110
What does Enhanced VPC routing do?
Ensures routing happens through the AWS backbone and not the internet.
111
What does Auto-Copy in S3 do?
It automatically loads data from S3
112
What does Amazon Aurora Zero ETL do?
It replicates data from Aurora to RedShift
113
What does Redshift Streaming ingestion do?
It loads data from Kinesis data streams or Managed Service for Kafka
114
Is the COPY command for Redsgift meant for internal or external use?
external
115
Can the COPY command decrypt data as it is loaded from S3?
Yes
116
What is a narrow table?
A table with lots of rows, but few columns
117
What is the best way to load a narrow table into RedShift?
Using a single COPY command.
118
What is the process for copying a KMS encrypted snapshot to another region?
In the destination region you will create a KMS key
Create a copy grant
Specify the KMS key ID for which you are creating the copy grant
In the source destination you will enable copying to the copy grant you created.
119
What does DBLink in RedShift do?
It allows you to connect to a PostgreSQL database.
120
What does Redshift Workload Management (WLM) do?
It prioritizes short fast queries over long slow ones.
121
How do you create different query queues in RedShift?
Using Workload Management (WLM)
122
What is RedShift concurrency scaling?
It automatically adds cluster capacity to handle and increase in concurrent read queries.
123
How many queues can you have with automatic workload management?
8
124
What are query monitoring rules in Workload Management (WLM)?
They monitor queries and when the defined threshold is exceeded, an action is performed. e.g., when a query hits 60 seconds, it aborts and is moved to a long running query queue.
125
How many queues can you have with manual workload management?
5
126
What does Short Query Acceleration (SQA) do?
It prioritizes short running queries over loner running ones.
127
Where are queries run when using Short Query Acceleration (SQA) ?
In a dedicated space so they don't sit behind long queries.
128
Does SQA work with CTAS?
Yes
129
Are SQA and WLM similar?
Yes
130
When to use SQA over WLM?
When all you need to do is prioritize short running queries.
131
What does VACUUM do in RedShift?
It recovers space from deleted rows and restore sort order.
132
What are the four types of vacuum commands
VACUUM FULL
VACUUM DELETE ONLY
VACUUM SORT ONLY
VACUUM REINDEX
133
How does VACUUM FULL work?
Default
It resorts the rows and reclaims space from deleted rows.
134
How does VACUUM DELETE ONLY work?
It only reclaims deleted row space
135
How does VACUUM SORT ONLY work?
It resorts the rows, but does not reclaim disk space.
136
How does VACUUM Reindex work?
It is used with Interleaved sort keys
137
How does Elastic Resize work in RedShift?
You can add or remove nodes of the same type.
Cluster is down for a few minutes.
138
How does Classic Resize work in RedShift?
You can change node types and number of nodes.
Could be read-only for hours or days.
139
How do you keep your redshift cluster available while performing a classic resize?
Snapshot, Restore, Resize and then cut over to new cluster when copy is complete.
140
What Redshift node type allows independent scaling of compute and storage?
RA3
141
What does Redshift DataLake Export do?
Allows you to Unload Redshift Query to S3 in Parquet format.
142
Does RedShift support spatial datatypes?
Yes
143
Can you share live data across redshift clusters between regions?
Yes using cross-region data sharing.
144
What is the requirement to use Redshift cross-region data sharing?
RA3 node types
145
If you want to use your HSM witH RedShift, what certificates are required?
Both client and server
146
Does Redshift come in serverless?
Yes
147
What is a good use case for Redshift serverless?
Test environments
Ad hoc business analysis
148
What needs to be set up manually to use Redshift Serverless?
An IAM role with an action redshift-serverless:*
149
How is RedShift Serverless Billed?
Redshift Processing Units RPUs by the hours
150
Can you adjust the base capacity of RedShift Serverless RPU?
Yes
151
Can you set the maximum RPU in Redshift Serverless?
Yes
152
What can't Redshift Serverless do that Redshift can?
Parameter Groups
Workload Management
Maintenance Windows / Version tracks
153
Is Redshift Serverless externally available?
No. It must be called from within the VPC.
154
What are some Redshift views for Monitoring performance?
SYS_QUERY_HISTORY
SYS_LOAD_HISTORY
SYS_SERVERLESS_USAGE
155
What is a materialized view in Redshift?
It is precomputing the query and storing the results..
A traditional view just stores the query.
156
What is materialized views good for?
Performance Optimization
157
How do you keep materialized views in synch automatically?
Set AUTO REFRESH
158
What does Redshift data sharing do?
It shares the data with other clusters for read purposes.
159
What are the encryption requirements for Redshift Data Sharing?
Both clusters must be encrypted.
160
What is a Redshift Lambda UDF?
It allows you to call a Lambda function in your SQL queries.
161
How do you register a Redshift External Function?
CREATE EXTERNAL FUNCTION
162
What are Redshift federated queries?
Allows you to access live data in Aurora and RDS using RedShift.
163
How do you connect to an Aurora / RDS instance for federated queries?
CREATE EXTERNAL SCHEMA
164
In Redshift, what are SYS views for?
To monitor query and workload usage.
165
In Redshift, what are STV views for?
They monitor Transient data containing snapshots of current system data.
166
In Redshift, what are SVV views for?
Metadata about DB objects that reference STV tables.
167
In Redshift, what are STL views for?
Generated from logs persisted to disk.
168
In Redshift, what are SVCS views for?
Details about queries on main and concurrency scaling clusters.
169
In Redshift, what are SVL views for?
Details about queries on main clusters
170
What is the Redshift Data API?
Secure HTTP endpoint for SQL Statements to Redshift clusters.
171
Is the Redshift Data API asynchronous?
Yes
172
173
174
