DMT Pro 2 Jeopardy

SQL/Indexes

100

What is the difference between “group by” and “order by” clauses?

• “Order by” alters the order in which items are returned
• “Group by” aggregates records by the specified columns, allowing the use of functions on non-grouped columns

200

What are the advantages and downsides of using an index?

• Advantage: used to retrieve data very fast, speed up SQL queries
• Disadvantage: Need to be updated with every insert/update/delete, takes up physical space

300

DAILY DOUBLE

What are the five main types of SQL joins, and what are they each used for (for double the points)?

• Inner: returns records that match in both tables
• Left: returns all records on the "left" side, along with matching records from the right
• Right: returns all records on the "right" side, along with matching records from the left
• Full: returns all records between both tables
• Self: join with itself

400

What is the difference between a clustered and non-clustered index?

• Clustered: typically sorted on the primary key, directly references the data on the table itself, can only be one per table – fast to read, slow to write
• Non-clustered: uses pointers where the actual data is stored, can have multiple per table – slow to read, fast to write

500

What is the correct processing order for the following SQL clauses?

• Select
• Distinct
• Limit (ex: Top 100)
• From, Joins
• Where
• Group By
• Order By
• Having

1. From/Joins
2. Where
3. Group By
4. Having
5. Select
6. Distinct
7. Order By
8. Limit

Transactions

100

What is a transaction?

Unit of work performed against the database, used when inserting/updating/deleting data

200

What are the three main "steps" or "actions" of a transaction, and what are they all used for?

• Commit: finishes the transaction and commits to database, not able to be easily taken back after this
• Rollback: reverse the transaction if something is wrong
• Dispose: only needed if done in try-catch, removes transaction from memory

300

What are the four principles of ACID?

• Atomic: happens all at once
• Consistent: only happens if it puts the system in a valid state
• Isolated: no one sees the transaction until its completed
• Durable: once committed, the change sticks

400

What are the advantages of using a transaction within a “using” block versus a “try-catch” block?

• Transaction is automatically disposed of after the using block
• Try-catch makes it easier to handle errors, displaying the ISE to users, etc.

500

What factors should be considered when determining how many transactions should be batched together?

• Time it takes for each transaction to run (1-second rule)
• Number of possible/expected transactions
• If the transactions themselves can create other transactions (ex: PostSave)

C#

100

What are the four "tentpoles", or main principles, of object-oriented programming?

• Polymorphism: program flow can change at runtime
• Inheritance: one class shares structure/behavior defined in another class
• Abstraction: hiding all but the relevant data about an object in order to reduce complexity, increase efficiency
• Encapsulation: bundling data, along with methods that operate on that data, into a single unit (class)

200

What are the four main accessibility levels in C#?

• Public: no restrictions
• Protected: limited to containing class, types derived from containing class
• Internal: limited to current assembly/project/namespace
• Private: limited to containing type

300

If you want to declare a class or method that you intend to be fully defined by a subclass, what are some potential ways to go about doing this?

Abstract class/method, virtual class/method, interface

DAILY DOUBLE BONUS POINTS: What is a "sealed" class/method?

400

If a function call includes the "await" keyword, what is this telling the CPU to do upon calling this method?

Wait for the method to fully complete before continuing execution

500

What is the difference between “passing by value” and “passing by reference”?

• Value: argument and parameter have two independent variables with the same value, anything done inside the method to that variable will not affect the variable outside the method
• Reference: argument and parameter use the same variable, anything done inside the method to that variable will affect the variable outside the method

DMT

100

What is a migration?

A series of runs that each contain individual processes (loaders, update processes, utilities)

200

What classes are included in a standard PaC class library, and what are their functions?

• PaC loader/map helper
• Raw object
• Dependency manager
• Destination loader/BB object (with Ibb interface)
• Cache manager
• Strategy manager

300

Say you're writing a DMT loader, and you've got a ton of fields coming from SMS that can't be easily mapped directly to a DMT loader.  What are two ways that this scenario can be worked around?

• Temporary data (CKR data, DMT data, Temp data, etc.)
• Create an intermediate data object and transfer the values over manually

400

What are each of the following types of temporary data storage used for: CKR data, DMT data, Temp data, Reporting fields?

• CKR data: written to CKR/CKD tables, “permanently” linked to DataObject
• DMT data: data that is linked to the DataObject and has the same lifespan as the DataObject
• Temp data: Data that is linked to an object created by a mapping, accessible in transforms, mappings
• Reporting field: information that is displayed when an error is generated for that record

500

When a DMT loader processes a record, is it immediately available in the corresponding primary cache? What about PaC loaders?

• DMT loader: no, because the call to e.Process() is not awaited, another thread is concurrently taking care of that task
• PaC loader: yes, because the call to ProcessRawObjectAsync() is awaited, and the current thread is waiting for that task to be completed before continuing

Debugging

100

What’s the difference between a service call (SC) and internal question (IQ)?

• SC: typically sent to us by a customer
• IQ: typically a question from another team

200

Say you’ve got an issue with a DMT loader, and absolutely zero records are coming over.  What are four potential causes for this problem?

• No records in SMS, or the query is returning 0 records
• No ConfigureMapping call
• ConfigureMapping not pointed at correct ProcessMap
• No e.Process() call

300

Say you’ve got an issue with a DMT loader, and the loader is using DefaultHandler, but it’s still taking forever.  What are three places you can look for potential issues?

• SMS query itself
• Transform methods within the DMT
• Q logic (PreSave, etc.)

400

What’s the name of the tool present in both SSMS and the DMT that displays exactly how a particular SQL query was executed?

Execution plan

500

Say your coworker, Bill, investigates an SC that comes into your team.  Upon looking at the raw SMS data, he notices that the data does not match up with the screenshots provided by the customer, and thus, he sends the customer a request for new data.  How could Bill have handled this better?

Use a test database to create an example mimicking the customer’s, and test using that.