192 Data Engineer Interview Questions & Answers

Use Always Encrypted when the threat includes a malicious DBA. The trusted client encrypts and decrypts selected values while keys stay outside SQL Server. TDE, Dynamic Data Masking, and Row-Level Security solve other security problems, but a sufficiently privileged DBA can still access or bypass their protections.

This question asks which protection can keep sensitive information hidden even from the person who has full control over the database. That person may be able to read stored information, run powerful searches, change settings, and remove normal access rules. The key decision is where readable information and the means to unlock it are kept. One option keeps them outside that administrator's control. The other options protect stored files or limit what ordinary users see, but a powerful administrator can still get around them. The design therefore depends on keeping key ownership separate from database administration.

1. Should I assume the malicious DBA has sysadmin-level SQL Server privileges but no access to the external key store?
2. Are we protecting selected sensitive columns from database administrators, or mainly protecting database files and backups if storage is stolen?
3. Should I assume standard Always Encrypted with an enabled client driver and no secure enclave in the Database Engine?

The practical answer is Always Encrypted when the threat model includes a malicious SQL Server DBA. For the design shown in the diagram, assume standard Always Encrypted without a secure enclave. A trusted application uses an Always Encrypted-capable client driver. The application encrypts sensitive values before they reach SQL Server and decrypts returned encrypted values on the client side.

Always Encrypted uses a column encryption key, or CEK, to encrypt column values. The CEK is itself protected by a column master key, or CMK. The CMK is held in a trusted external key store such as a hardware security module or key vault. SQL Server stores ciphertext, CMK metadata, and encrypted CEK values, but not plaintext CMKs or CEKs. The client driver uses the external key store to perform the key operation needed to unwrap the CEK. Microsoft specifically recommends role separation when the goal is preventing DBAs from reading sensitive data. ([learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted?view=sql-server-ver17))

That separation of duties is the core defense. A security administrator owns the external key store and keys. The DBA manages SQL Server and the key metadata stored in the database, but must not have access to the actual key material or the credentials that provide key-store access. The malicious DBA can therefore query an encrypted column, but without key access the database returns ciphertext rather than plaintext. ([learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted?view=sql-server-ver17))

Transparent Data Encryption, or TDE, protects a different threat. It encrypts SQL Server data and log files at rest, and backups of a TDE-protected database are also encrypted. Database pages are decrypted when SQL Server reads them into memory. Because the running Database Engine can read the plaintext, TDE does not stop a malicious administrator who can query SQL Server or inspect the running system. It is valuable against stolen disks, copied files, and exposed backups, but it is not the malicious-DBA boundary shown in the diagram. ([learn.microsoft.com](https://learn.microsoft.com/ga-ie/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15))

Dynamic Data Masking, or DDM, changes how selected values appear in query results for users who do not have permission to see the original value. The stored value is unchanged. SQL Server administrative roles such as sysadmin and db_owner can view unmasked data because they have the required control permissions. DDM therefore reduces accidental or routine exposure for less-privileged users, but it does not defend against the malicious DBA in this threat model. ([learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver17))

Row-Level Security, or RLS, applies security predicates that determine which rows a user can access. It is an authorization mechanism rather than encryption. Its policies apply even to dbo users, but sufficiently privileged administrators can alter or drop those policies. A malicious DBA who controls those policies can therefore remove the restriction and expose rows that ordinary users could not see. ([learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver17))

The residual risk is also important. Always Encrypted does not help if the same malicious DBA gains control of the external key store, obtains the key-store credentials, or compromises the trusted client environment that can decrypt the values. The security boundary therefore depends on real separation between database administration and key administration.

The interview takeaway is simple: Always Encrypted can defend selected column plaintext against a malicious DBA because the Database Engine does not receive the plaintext keys. TDE protects files at rest. Dynamic Data Masking limits what less-privileged users see in query results. Row-Level Security controls which rows users can access. Those controls can complement one another, but TDE, DDM, and RLS do not replace Always Encrypted for the malicious-DBA threat shown in the approved diagram.

1. Define the adversary as a highly privileged SQL Server DBA who can run queries and change database configuration or security policies.
2. Draw the trust boundary around SQL Server and assume the DBA does not control the trusted application or external key store.
3. Evaluate Always Encrypted: the client encrypts selected values, SQL Server stores ciphertext and key metadata, and the client performs the external key operation needed to unwrap the CEK. Treat this as the protection against the malicious DBA only when key ownership is separated.
4. Evaluate TDE: it encrypts database storage at rest, but SQL Server decrypts pages for processing, so a privileged DBA can still obtain plaintext through the running database.
5. Evaluate Dynamic Data Masking: it masks query results for less-privileged users, but administrative users can retrieve unmasked values.
6. Evaluate Row-Level Security: it filters rows using an authorization policy, but a sufficiently privileged DBA can alter or drop that policy.
7. State the residual risk: if the DBA also gains the external keys, key-store credentials, or control of the trusted client, the Always Encrypted protection is lost.

Always Encrypted has the highest application and operational cost of these four controls because the client must support encryption-aware drivers, encrypted columns can limit some query operations, and key administration must be separated from DBA duties. It also adds external key-store management and key rotation work. TDE is largely transparent to applications and is simpler when the goal is protecting files at rest. Dynamic Data Masking and Row-Level Security are relatively convenient for limiting normal user exposure, but their policies require ongoing permission and policy maintenance. None of those lower-cost controls should be presented as a substitute for Always Encrypted when the adversary is the DBA.

The first mistake is saying TDE protects against the DBA simply because the database is encrypted. TDE protects stored files, while the running Database Engine can still read the data. The second mistake is treating Dynamic Data Masking as encryption; it only changes result presentation for users without unmasking privileges. The third mistake is treating Row-Level Security as an unbreakable security boundary; privileged administrators can alter or remove its policies. The fourth mistake is saying Always Encrypted automatically defeats every DBA. It only provides the intended protection when the DBA cannot access the external keys, key-store credentials, or trusted client that can decrypt the protected values.

Organize the answer by security boundary. Start with Always Encrypted because plaintext keys stay outside SQL Server, then contrast each alternative by the threat it actually addresses: TDE for data at rest, Dynamic Data Masking for result exposure, and Row-Level Security for row authorization. Finish with the separation-of-duties requirement and the residual risk if the DBA gains key-store access.

A data test asserts something about dataset values or relationships, such as non-nullness, uniqueness, accepted values, referential integrity, reconciliation, or freshness. Unit tests validate isolated code, integration tests validate components working together, and data tests validate source, intermediate, or output datasets.

This question asks how we check three different kinds of correctness. One check asks whether a small piece of a program behaves as expected. Another asks whether separate parts can work together correctly. The third asks whether the information moving through a data process is trustworthy. For example, we may want to know that important fields are present, identifiers are not unexpectedly repeated, allowed choices are respected, related records exist, totals agree with a source, and information arrives on time. The key idea is that working software and trustworthy data are related but different goals.

1. Should I explain only the conceptual differences, or also where each test type normally runs during development and pipeline execution?
2. Would you like examples of common data assertions such as non-nullness, uniqueness, accepted values, referential integrity, reconciliation, and freshness?

A data test is an assertion about the values or relationships in a dataset. It checks whether the data satisfies a defined rule. Examples include checking that order_id is not null, that order_id is unique, that status contains only accepted values, that each customer_id has a corresponding customer record, that source and target counts or totals reconcile, and that the data is fresh enough for its intended use.

A unit test has a different target. It checks a small piece of code in isolation, such as a transformation function or component. The inputs are controlled, the expected output is known, and external infrastructure is normally excluded. Unit tests are commonly run during development and in continuous integration so code defects can be caught early.

An integration test checks whether two or more components work together correctly. In data engineering, this can mean confirming that a pipeline component can correctly interact with a database, file system, API, queue, or another dependency. Integration tests therefore focus on system boundaries and real dependencies rather than one isolated function. They commonly run in a test environment, in continuous integration, or during pre-release testing.

A data test focuses on the dataset itself rather than only the code that produced it. Data tests can run against source, intermediate, or output datasets during pipeline execution, and they may also run during development. They are often placed before publication so invalid data does not reach consumers, but they are not limited to the final output stage.

A simple way to remember the difference is: unit tests ask, "Does this isolated code work?" Integration tests ask, "Do these components work together?" Data tests ask, "Does this dataset satisfy the required rules?"

All three complement one another. A transformation can pass its unit tests and successfully write to a database in an integration test while the actual dataset still contains duplicate identifiers, unexpected nulls, invalid values, missing relationships, mismatched totals, or stale records. Data tests provide the dataset-level protection that code-focused tests alone cannot provide.

A data test passes when its defined data assertion is satisfied. The exact implementation depends on the testing system, but the vendor-neutral concept is the same: the observed dataset must satisfy the rule being checked.

1. Identify the thing being validated: isolated code, interaction between components, or dataset values and relationships.
2. Use a unit test for isolated transformation or function logic.
3. Use an integration test for boundaries such as databases, files, APIs, queues, or other dependencies.
4. Use data tests on source, intermediate, or output datasets for rules such as non-nullness, uniqueness, accepted values, referential integrity, reconciliation, and freshness.
5. Run unit tests during development and CI, integration tests in environments where required dependencies are available, and data tests during development or pipeline execution at the datasets where the assertions matter.
6. Before publishing a data product, require the appropriate checks for that stage to pass.

Unit tests are usually the fastest and cheapest because they check small pieces of code without real external systems. Integration tests need more setup and time because multiple components or dependencies must be available. Data-test cost depends on the dataset size and the assertion. For example, uniqueness, reconciliation, or referential-integrity checks over large datasets can require substantial scanning and compute. There is also maintenance cost because tests must be updated when valid schemas, business rules, or data contracts change.

A common mistake is saying that data tests and unit tests are the same because both use assertions. Their targets are different: unit tests primarily validate isolated code behavior, while data tests validate dataset properties. Another mistake is calling every database-related test an integration test. A check that order_id is unique is a data test, while a test that verifies a pipeline component can correctly read from and write to a database is an integration test. It is also incorrect to say that data tests run only after the final dataset is produced; they can check source, intermediate, or output datasets throughout pipeline execution.

Start with one clear sentence: unit tests check isolated code, integration tests check components working together, and data tests check the data itself. Then give one concrete example of each. Finish by explaining that a reliable data pipeline usually needs all three because correct code and successful integrations do not automatically guarantee correct data.

A source-to-target mapping document becomes the ETL test specification. Field mappings define equality checks, derivations define calculated expected values, defaults define expected replacements for missing inputs, and filters define the expected row set. Tests compare actual target output with those documented expectations.

Think of the document as a written agreement that describes what the finished information should look like. Before checking the finished result, the tester uses each written rule to decide what should happen. A copied value should stay the same. A value built from other values should follow the stated calculation. A missing value may receive a stated replacement. Some records should be included while others should be left out. The finished information is then compared with these expected results so mistakes can be found in a consistent and repeatable way.

1. Should I treat the approved source-to-target mapping document as the source of expected results?
2. Does the mapping include both field-level transformation rules and row-level filter rules?
3. Would you like me to walk through examples for direct mappings, derivations, defaults, and filters?

I would treat the source-to-target mapping document as the ETL test specification, or test oracle. A test oracle is the trusted definition of what the correct result should be.

The mapping document first describes how source fields become target fields. I convert each rule into an expected-result check before comparing it with the ETL output.

For a direct field mapping, the expected target value should equal the mapped source value. In the diagram, cust_id maps to customer_id. If the source cust_id is 123, the expected target customer_id is also 123.

For a derivation, I independently calculate the expected value from the documented expression. In the diagram, full_name = first_name || ' ' || last_name. With source values John and Doe, the expected full_name is John Doe. I then compare that expected value with the actual target value.

For a default rule, I test the condition that activates the default. The diagram defines status_code as COALESCE(status, 'ACTIVE'). If the source status is NULL, the expected target value is ACTIVE. I would also test a non-NULL status to confirm that an existing source value is retained rather than replaced by the default.

For a filter rule, I derive the expected qualifying row set. The diagram says to include only rows where order_date >= '2024-01-01'. Therefore, a source row with order_date = '2023-12-31' should be excluded. I test both sides of the boundary: qualifying rows should appear in the target, and non-qualifying rows should not appear.

The complete flow is: Source System -> ETL Pipeline -> Target System for the actual data path. Separately, the source-to-target mapping document defines Field Mapping Checks, Derivation Checks, Default Value Checks, and Filter Rule Checks. Those checks produce Expected Results containing expected field values and the expected qualifying row set. The actual Target System output is then compared with those Expected Results. The Test Results should confirm mapped values, derived values, defaults, and filter behavior.

The important testing principle is independence. I should derive the expected results from the approved mapping document instead of copying the ETL implementation into the test. Otherwise, the same mistake could exist in both the pipeline and the test and still appear to pass.

I would assume the mapping document is the approved specification. If the pipeline output and the mapping disagree, I would record the mismatch and determine whether the ETL implementation is wrong or the mapping document is outdated. I would not silently change the expected result to match the current implementation.

The main tradeoff is maintenance. Mapping-driven tests are clear, traceable, and easy to connect to requirements, but approved rule changes require the mapping document and its corresponding tests to be updated together.

1. Select representative source records for each documented rule, including normal values, NULL values, and filter-boundary cases.
2. Read the source-to-target mapping document and derive the expected target field values and expected qualifying row set.
3. For direct mappings, expect the mapped source value in the target.
4. For derivations, independently calculate the documented expression.
5. For defaults, test both the condition that activates the default and a case where the source already contains a value.
6. For filters, test rows that should be included and rows that should be excluded.
7. Run the ETL pipeline so the source data reaches the target system.
8. Compare actual target output with the expected results derived from the mapping document.
9. Report any mismatch against the exact mapping rule that produced the expectation.

The amount of testing work grows with the number of records and mapping rules being checked. Direct mappings are simple comparisons. Derivations require recalculating values. Default tests need both missing-value and normal-value cases. Filter tests require checking both the rows that should exist and the rows that should be absent. Large data sets can be checked in smaller batches or representative samples when appropriate. The main maintenance cost is keeping tests synchronized with approved mapping changes.

Common mistakes include checking only whether the ETL job completed, validating only row counts, testing direct mappings but ignoring derivations, defaults, or filters, copying the ETL implementation into the test instead of independently deriving expectations, failing to test NULL values, testing only included rows without confirming that excluded rows are absent, missing filter-boundary cases, and changing expected results merely because the current implementation behaves differently from the approved mapping document.

Describe the mapping document as the test oracle, then walk through the four rule types in the same order as the diagram: field mapping, derivation, default, and filter. For each one, explain how the rule produces an expected result and finish by saying that the actual target output is compared with those expectations.

Generic dbt tests are reusable and parameterized, usually referenced from YAML. Singular tests are one-off SQL assertions for specific rules. Both return failing records: by default, zero returned rows passes, while one or more returned rows fails the test.

This question asks you to explain two ways of checking whether information is correct. One kind is a reusable rule that can be applied in many places by changing a few inputs. The other kind is a special rule written for one specific need. You should also explain how the result tells you whether the check passed or failed. The main idea is simple: reusable rules reduce repeated work, while one-off rules are useful when a check is unique to a particular situation or business requirement.

1. Should I focus on the basic difference between generic and singular dbt data tests, or also discuss custom generic tests?
2. Would you like a short example of each test type?

A generic dbt data test is reusable and parameterized. The reusable logic is defined once in a test block, and you reference that test by name with arguments, commonly in YAML. The same generic test can be applied to many models, columns, sources, snapshots, or seeds. For example, a column such as order_id can use data_tests: with not_null. Common repeatable checks include not_null, unique, and accepted_values.

A singular test is a one-off SQL assertion. It is normally written as a standalone .sql file in the tests directory for a specific business rule. For example, a singular test can select orders where total_amount < 0. The query returns the records that violate the rule. Because the SQL is written for that specific condition, it is not reusable in the same parameterized way as a generic test.

Both test types use the same basic result model when they run with dbt test. dbt executes each test as a query that identifies failing records. With the default rule shown in the diagram, zero failing rows means PASS. One or more failing rows means FAIL by default.

The practical choice is simple. Use generic tests for common, repeatable checks because they reduce duplicated logic and can be parameterized. Use singular tests for custom, project-specific business rules where a direct SQL assertion is clearer. Generic tests usually have lower maintenance cost when the same rule is used repeatedly. Singular tests give more freedom for unique SQL conditions, but many similar singular tests can create duplicated logic.

1. Decide whether the data-quality rule is common and reusable or specific to one business condition.
2. Use a generic test when the same check can be defined once and applied with different arguments.
3. Reference the generic test from YAML, such as not_null under data_tests: for a column.
4. Use a singular test when the rule needs one-off SQL for a specific business condition.
5. Write the singular SQL so it returns only records that violate the rule.
6. Run the tests with dbt test.
7. Interpret zero failing rows as PASS and one or more failing rows as FAIL by default.

The main runtime cost comes from the SQL query that each test executes. A simple generic check such as not_null may scan one column, while a singular test can cost more if its SQL uses joins or complex conditions. Memory and processing cost depend on the query and data size, not simply on whether the test is generic or singular. Generic tests usually reduce maintenance work because one reusable definition can be applied many times. Singular tests are simple for unique rules, but many similar singular files can duplicate logic.

Common mistakes are saying generic and singular tests use different pass/fail mechanics, writing a singular test that returns valid rows instead of failing rows, treating a singular test as reusable and parameterized like a generic test, or writing many one-off SQL files for checks that should be reusable generic tests. Another mistake is forgetting that the diagram shows the default result rule: zero failing rows passes, while one or more failing rows fails by default.

Start with the reuse difference: generic tests are reusable and parameterized, while singular tests are one-off SQL assertions. Then explain the shared result behavior: both identify failing records, so zero rows passes and one or more rows fails by default. Finish with one short example of each.

Parameterize the reusable rule, configure error_if and warn_if, narrow expensive checks with where, use limit to cap failure output, and persist failing rows with store_failures_as. Then investigate those rows, correct the data or test logic, and rerun the test to verify the fix.

This question asks how to make one reusable check practical and safe for everyday work. The same check should work in many places without being rewritten each time. It should allow small problems to be treated differently from large problems. It should avoid looking through more information than needed when a smaller slice is enough. It should also keep the bad records so someone can study what went wrong. After the cause is corrected, the same check should run again to prove the result is now acceptable.

1. Should a small number of failing rows produce a warning while a larger number should fail the run?
2. Can routine checks be restricted to recent or otherwise relevant rows instead of scanning all history?
3. Should failing rows be persisted for investigation, and are there retention or sensitive-data restrictions on them?
4. Will the test be used mainly in CI, scheduled production runs, or both?

I would organize the reusable test around four operational controls: parameterization, failure thresholds, cost control, and retained failure evidence.

First, I would parameterize the reusable test logic. In the diagram, the custom generic test accepts model and column_name and returns rows where the selected column is null. The same SQL/Jinja definition can therefore be reused for different models and columns. The test arguments describe what is being checked, while operational behavior such as severity, filtering, failure limits, and persistence belongs in the test config.

Second, I would make failure behavior explicit. The diagram uses severity: error, error_if: '> 100', and warn_if: '> 0'. With severity: error, dbt checks error_if first. More than 100 failures therefore return an error. If that condition is false, dbt checks warn_if; 1 through 100 failures produce a warning, and 0 failures pass. If severity is instead set to warn, dbt skips error_if and evaluates warn_if. This lets the team distinguish tolerated small problems from failures that should stop the quality gate.

Third, I would restrict costly checks with config.where. The diagram uses where: 'order_date = current_date', which filters the resource before the test condition is evaluated. That lets routine checks focus on recent or relevant rows and can reduce work when the underlying data platform can prune unrelated data. This is different from limit: 1000. The limit configuration caps the number of failing rows returned by the test query, which is useful when failures are persisted, but it does not by itself guarantee a smaller underlying scan.

Fourth, I would retain failing rows for investigation. The diagram sets store_failures_as: table, so the failing records are stored as a queryable database relation. An engineer can query that relation to determine whether the problem comes from the source data, an upstream transformation, or the test logic itself. The diagram shows the default audit schema as derived from the active profile schema and ending in _dbt_test__audit. Because failure records may contain sensitive values, access, retention, and database permissions should be considered before persistence is enabled.

Finally, I would close the loop by using the persisted evidence to diagnose the problem, correcting the data or the test logic, and rerunning the same test. The rerun provides verification that the correction worked. Used consistently, these controls turn a custom test into a reusable quality gate for CI and production.

The main tradeoff is coverage versus operating cost. Testing all historical rows gives broader coverage but may be expensive. A targeted where filter lowers routine work but can miss older defects, so a team may combine frequent scoped checks with less frequent wider validation. Persisting failures improves diagnosis but adds storage, permission, retention, and sensitive-data considerations.

1. Define a generic test whose query returns only failing rows.
2. Parameterize reusable inputs such as model and column_name.
3. Keep operational controls in the test config.
4. Configure severity, error_if, and warn_if so pass, warning, and error behavior is explicit.
5. Add where when the routine test should cover only recent or relevant data.
6. Use limit to cap the number of failure rows returned or persisted, not as a substitute for reducing the source data scanned.
7. Set store_failures_as: table when investigators need durable failing rows.
8. Query those failures, identify whether the defect is in the data or test logic, make the appropriate correction, and rerun the test.
9. Treat the successful rerun as verification before considering the issue resolved.

The largest operating cost is usually how much data the test reads. A test over all history can be expensive. A selective where filter may reduce that work when the data platform can avoid reading unrelated rows or partitions. limit controls how many failures are returned or stored, but it does not necessarily reduce the amount of source data read. Persisting failures adds storage and permission requirements. Parameterization lowers maintenance cost because one test definition can be reused instead of copying similar SQL for every model or column.

Common mistakes include hard-coding model-specific values into the reusable test, mixing test arguments with operational configuration, treating every nonzero failure count as the same severity, misunderstanding the order in which error_if and warn_if are evaluated, scanning full history on every run when a justified where scope is enough, and assuming limit guarantees a cheaper scan. Another mistake is discarding failure evidence or persisting it without considering permissions, retention, storage growth, and sensitive-data exposure.

Structure the answer around four controls: parameterization, thresholds, cost scope, and retained failures. Explicitly distinguish where from limit: where restricts the data being tested, while limit caps failure output. Finish with the investigation, correction, and rerun loop shown in the diagram.

Verification asks, "Did the ETL follow the mapping specification correctly?" Validation asks, "Does the output meet the real business requirement?" A transformation can therefore pass verification but fail validation when the documented mapping is incomplete or does not represent the business rule.

This question asks whether you can tell the difference between checking that work follows written instructions and checking that the final result is actually useful to the people who need it. A result can exactly follow the written rule and still be wrong for its real purpose. In the example, the written rule calculates money from price and quantity, so the result follows the instructions. But the people using the result need discounts and refunds included too. The correct response is to correct the written rule and test the result again.

1. What is the documented mapping rule for the revenue field?
2. What does the business define as the expected revenue value?
3. Should both discounts and refunds reduce revenue?
4. Are there approved expected values that can be used for validation testing?

Verification checks the ETL transformation against its mapping specification. It answers: "Did we implement the documented transformation correctly?"

In the diagram, the original mapping specification says: revenue = unit_price × quantity

For order 1001, unit_price is 10 and quantity is 2. The ETL produces revenue = 20. That exactly matches the documented mapping, so verification passes. The same rule produces 20 for order 1002 and 45 for order 1003.

Validation checks the resulting data against the real business requirement. It answers: "Does this output represent what the business actually needs?"

The business requirement says revenue should represent net revenue after discounts and refunds. For order 1001, the expected value is (10 × 2) − 2 − 0 = 18, but the ETL produces 20. For order 1002, the original ETL produces 20, while the business rule expects (20 × 1) − 0 − 5 = 15. The transformation therefore implements the original mapping correctly but still fails the business requirement. Verification passes, while validation fails.

This indicates that the original mapping specification is incomplete for the stated business requirement, rather than showing that the original transformation was implemented incorrectly. After confirming the business definition, update the approved mapping to: revenue = unit_price × quantity − discount − refund

With that updated mapping, the diagram's expected outputs are 18 for order 1001, 15 for order 1002, and 45 for order 1003. Verification should confirm that the ETL follows the updated mapping specification. Validation should independently confirm that those results satisfy the business definition of net revenue. Both checks then pass.

The key distinction is that verification checks conformance to the documented specification, while validation checks whether the output fulfills the intended business use. Passing verification does not guarantee passing validation because a specification can itself be incomplete or incorrect. I would not silently change working ETL logic just to make a validation test pass. I would confirm the business requirement, update the controlled mapping and expected test cases, implement the approved change, and rerun both checks.

1. Read the documented mapping specification and identify the exact transformation rule.
2. Create or review expected outputs based on that mapping.
3. Run verification tests and compare the ETL output with those specification-based expectations.
4. Separately identify the real business requirement and its expected results.
5. Run validation tests against the business expectations.
6. If verification passes but validation fails, compare the mapping specification with the business requirement instead of immediately treating the ETL implementation as defective.
7. Confirm the intended business rule with the appropriate owner.
8. Update the approved mapping specification and expected test cases.
9. Implement the approved transformation change.
10. Rerun verification against the updated mapping and validation against the business requirement.

The row-level calculation is simple and adds very little processing or memory cost because each row can be evaluated independently. The more important cost is maintenance: the business requirement, mapping specification, ETL logic, and test expectations must stay synchronized. Keeping separate verification and validation checks requires extra test design and review, but it catches a valuable failure mode where code correctly implements a specification that does not represent the business need.

A common mistake is treating verification and validation as the same test. Another is assuming that passing mapping-based tests proves the business result is correct. Engineers may also change correctly implemented ETL code when the real defect is an incomplete mapping specification. Another mistake is validating only against the documented formula instead of independently checking the business expectation. In the diagram, ignoring discount and refund makes the original output conform to the original mapping while producing the wrong net revenue for orders 1001 and 1002.

Use the simple distinction: verification means "built according to the specification," while validation means "meets the business need." Then use the diagram's concrete example: revenue = unit_price × quantity passes verification, but validation fails because net revenue must subtract discounts and refunds. Finish by explaining that the approved mapping and its tests should be updated before rerunning both checks.

I would create edge-case source records, run the ETL mapping, and compare target types and values with explicit expected results. I would test decimal rounding and overflow, date parsing and representation, unexpected string truncation, nulls, invalid values, and identifiers whose leading zeros must remain intact.

The goal is to prove that information still has the expected meaning after the ETL mapping converts it. I would prepare examples that are easy to mishandle: precise numbers, boundary or invalid dates, long text, null values, and identifiers that start with zero. Then I would run those records through the mapping and compare each result with a known expected result. A test should fail when a value is unexpectedly rounded, shortened, rejected, interpreted differently, or stripped of meaningful zeros. These checks should also run automatically after mapping changes.

1. What source and target types are defined for each field?
2. What rounding and scale behavior is expected for decimal values?
3. Which date representations are accepted, and what target representation is expected?
4. Is string shortening ever intentional, or should overlength values be rejected?
5. Which identifiers must remain textual so leading zeros survive?
6. How should null, invalid, ambiguous, and out-of-range values be handled?

I would treat every type conversion as a contract between a source value and an expected target value and type.

First, I would prepare targeted test data. For decimals, I would include high-precision values, values near the target precision and scale boundaries, nulls, and values that may exceed the target range. I would assert the exact expected rounding or scale behavior. Expected rounding can be correct behavior; unexpected precision loss or overflow is a failure.

For dates, I would test the expected input representation, any alternate representations that the mapping intentionally accepts, boundary dates, and ambiguous or invalid inputs. I would compare the parsed target value and representation with the expected mapping instead of assuming the original source text must remain unchanged.

For strings, I would test values below, at, and above the expected target length. If shortening is not part of the mapping contract, truncation is a failure. If shortening is intentional, I would assert the exact expected shortened result so silent data loss is not mistaken for success.

For identifiers, I would use values such as "0012345678" and compare the exact target value. When leading zeros are meaningful, the identifier should remain in a textual representation rather than being treated as a number. A result such as "12345678" would fail because the identifier changed.

Next, I would run the ETL mapping and validate both the converted value and its resulting type or representation. This matches the core flow: prepare targeted test data, run the mapping, validate results, and then either pass or investigate. A passing case means the tested conversion behaves as specified. A mismatch means I inspect the mapping rule, target type, or format setting, correct the issue, and rerun the same test case.

Finally, I would automate these assertions in the ETL test suite. The main tradeoff is strictness versus intentional conversion behavior. Rounding, alternate date parsing, or string shortening can be valid only when the mapping explicitly requires them and the expected behavior is encoded in the tests.

1. List each source-to-target conversion and define the expected target type, value, and allowed representation changes.
2. Create targeted test records for normal values, nulls, boundaries, and invalid inputs.
3. For decimals, test expected rounding and scale, plus overflow and unexpected precision loss.
4. For dates, test expected, alternate, boundary, ambiguous, and invalid representations according to the mapping contract.
5. For strings, test values below, at, and above the target length and distinguish intentional shortening from unexpected truncation.
6. For identifiers, assert exact textual equality so leading zeros survive.
7. Run the ETL mapping.
8. Compare actual target values and types with the expected results.
9. If a mismatch occurs, inspect the mapping rule, target type, or representation setting.
10. Correct the mapping and rerun the same cases.
11. Add the checks to the automated regression test suite.

These tests usually use a small number of carefully chosen records, so runtime and memory cost are low. The main cost is maintaining expected results as approved mapping rules change. Adding more boundary and invalid cases slightly increases test time, but it reduces the risk of silent data corruption. Automated checks also add a small amount of pipeline or CI runtime, which is usually much cheaper than finding precision loss, truncation, bad dates, or damaged identifiers after data has been published.

Common mistakes are checking only whether the ETL job completes, testing only ordinary values, ignoring target data types, assuming all decimal rounding is an error, accepting silent string truncation, converting meaningful identifiers to numeric types, testing only one date representation, ignoring ambiguous or invalid dates, skipping null and boundary cases, and failing to automate the tests. Another mistake is treating every representation change as a failure even when that change is explicitly required by the mapping contract.

Structure the answer around four risks: decimal precision, date representation, string length, and leading-zero identifiers. For each one, state the test input, expected target result, and failure condition. Then explain that both target values and target types or representations should be validated automatically.

I would send known accented text, non-Latin characters, and emoji through the ETL pipeline, decode the destination with its intended encoding, and compare the resulting Unicode text exactly with the source. I would fail or quarantine data if characters are lost, replaced, or garbled.

I would start with a small set of known words and symbols that are easy to recognize. The set should include accented letters, writing systems from different languages, and emoji. I would send those values through the same path as normal data and then read them back from the final location. The returned text must be exactly the same as the original text. I would also look for missing symbols, changed letters, replacement characters, or strange-looking output. If a value cannot be stored safely, I would keep it separate instead of silently changing it.

1. What encoding is declared by the source?
2. What encoding or character set is expected at the destination?
3. Are source and destination expected to use the same encoding, or is transcoding allowed?
4. Does the pipeline process files, database text columns, messages, or several of these?
5. Should values that cannot be represented by the destination encoding fail the load or be quarantined?

I would begin with controlled ground-truth rows such as café for accented Latin text, 東京 for non-Latin text, and 😀 for emoji. The source is the last known correct boundary because I know exactly which Unicode characters should be present before the pipeline runs.

First, I would decode the source bytes using the declared source encoding, such as UTF-8. After decoding, the pipeline should operate on Unicode text. Transformations such as trimming or cleaning must not unintentionally change the test characters.

Next, I would write the text using the intended destination encoding. For example, if the destination uses UTF-8, all three test values can be represented. If a destination encoding cannot represent a required character, such as Latin-1 for 東京 or 😀, the safe behavior is to fail or quarantine the affected record rather than silently replace the character.

Then I would read the destination back using its declared encoding and compare the complete decoded Unicode value with the source ground truth. I would compare the full string or its Unicode code-point sequence. The pass condition is an exact decoded-text match for every test row.

I would not require raw source and destination bytes to match when intentional transcoding occurs. Different encodings can use different byte sequences for the same text. In that case, decoded Unicode equality is the correct test.

I would also verify that no character has been replaced, dropped, or garbled. I would look for unexpected ?, the Unicode replacement character U+FFFD, missing characters, and mojibake, which is garbled text caused by decoding bytes with the wrong character encoding. An expected literal question mark in the source would not be treated as an error because the final value is compared against the known source string.

If a mismatch occurs, I would inspect the value at each pipeline boundary to find the first incorrect boundary while keeping the original source as the last known correct ground truth. If corruption appears immediately after the read step, I would investigate source decoding. If the text stays correct through transformation but changes after the destination write or read, I would investigate destination encoding or transcoding.

The safe response is to quarantine affected records, correct the smallest encoding or conversion defect, and rerun only the affected test scope. I would not publish corrected data until the destination values have been read back and reconciled exactly with the source.

1. Create ground-truth rows containing accented text such as café, non-Latin text such as 東京, and emoji such as 😀.
2. Record the source encoding and verify that the source bytes decode correctly.
3. Run the test rows through the normal ETL read, transformation, and write path.
4. Verify that transformations operate on decoded text and do not unintentionally change characters.
5. Encode the output using the intended destination encoding.
6. If a required character cannot be represented by that encoding, fail or quarantine the record instead of silently replacing it.
7. Read the destination back using the destination encoding.
8. Compare the complete decoded Unicode string or code-point sequence with the source ground truth.
9. Check for unexpected ?, U+FFFD, missing characters, mojibake, or other substitutions.
10. If a mismatch occurs, inspect each boundary to identify the first incorrect boundary and the last known correct boundary.
11. Correct the smallest encoding defect, rerun the affected scope, and require exact source-to-destination text reconciliation before considering the test successful.

For n characters, comparing the source and destination text takes about O(n) time because each character must be checked. Memory can be O(n) when both strings are held at once, although records can also be checked one at a time. Operational cost is low for a small integration test. The main maintenance cost is keeping representative characters and expected encodings up to date as sources and destinations change.

Common mistakes are testing only ASCII, assuming a successful ETL job means text was preserved, relying on implicit encodings, comparing raw bytes even when intentional transcoding is allowed, and checking only whether the destination value is non-null. Another mistake is silently accepting replacement characters, question-mark substitutions, dropped text, or mojibake. The correct test uses known multilingual and emoji values and compares the decoded destination text exactly with the source ground truth.

Explain the test as one end-to-end flow: known Unicode ground truth → explicit source decoding → transformations → explicit destination encoding → read back → exact Unicode comparison. Mention that bytes may differ across valid encodings, and emphasize failing or quarantining unrepresentable characters rather than silently replacing them.

Keep the production manifest as CI state, give every pull request its own schema, select state:modified+, and run dbt build with --defer. Changed nodes and downstreams build in the PR schema, eligible unchanged upstream refs use production relations, and merge happens only after the build and tests pass.

The goal is to check only the parts of the project that a proposed change can affect instead of rebuilding everything each time. I would keep a trusted description of the last successful live version, give every proposed change its own temporary workspace, compare the new version with the trusted one, and process only what changed plus anything that depends on it. Unchanged earlier results can be reused. The change is accepted only after its work and checks succeed. The temporary workspace is then removed when the change is accepted or abandoned.

1. Where is the manifest from the last successful production run stored, and how does CI retrieve it?
2. Does each pull request already receive a unique warehouse schema, or should the CI workflow create one?
3. Should state:modified+ be the default selector for all pull requests, or are there models or tests that must always run?
4. Is the isolated PR schema guaranteed to start empty, or should CI use --favor-state when production state must take precedence over existing development relations?

I would start with the last successful production dbt run. That run produces manifest.json, which describes the dbt project graph and node definitions. I would retain that manifest in dedicated state storage so CI has a trusted production baseline for later comparisons.

When a pull request starts, CI creates or targets a unique schema such as pr_123. That schema is the execution target for the pull request. It prevents two pull requests from writing into the same namespace and prevents CI from writing selected models into the production schema.

Next, CI compares the pull-request project with the production manifest using dbt state selection. The selector state:modified+ starts with nodes that dbt considers modified relative to the supplied state and expands the selection to their downstream descendants. The trailing plus matters because an unchanged downstream model can still be affected by a changed upstream model.

Then CI runs dbt build with --defer and the same production state. In a fresh isolated PR schema, eligible unselected upstream refs can resolve to the corresponding production relations represented by that state instead of being rebuilt. This is the selective part of the design. Ephemeral models are not deferred because they do not exist as persistent warehouse relations; their SQL is compiled into dependent nodes.

The production manifest and the PR schema serve different purposes. The manifest is the comparison and deferral state. The PR schema is the write target for selected CI work. Keeping those boundaries separate avoids accidentally using CI output as the trusted production baseline.

The main tradeoff is speed versus complete environmental independence. Deferral reduces warehouse work because unchanged upstream relations can be reused from production. However, the pull-request build is therefore not a full isolated rebuild of the entire project. If production data cannot safely be referenced, or if a particular integration test requires complete isolation, that part of the graph should be built in a separate test environment instead of deferred.

Finally, dbt build executes the selected resources and applicable tests. A nonzero dbt exit status fails CI and blocks the merge. If CI passes, the pull request can merge. When the pull request is merged or closed, the temporary PR schema is removed. After the next successful production run, its new manifest becomes the trusted state baseline for future pull requests.

1. Run the normal production dbt job and retain manifest.json from the last successful production run.
2. Store that artifact in dedicated production-state storage, separate from pull-request targets.
3. When a pull request starts, create or select a unique schema such as pr_123.
4. Retrieve the production manifest into a local state directory available to the CI runner.
5. Run dbt with state:modified+ so modified nodes and their downstream descendants are selected.
6. Enable --defer and point --state at the production manifest directory.
7. Build selected resources in the isolated PR schema while eligible unselected upstream refs resolve to production relations.
8. Let dbt build execute the applicable tests for the selected graph.
9. Treat any nonzero dbt exit status as a failed CI check and block the merge.
10. Allow the merge only after CI succeeds.
11. Drop the temporary PR schema after the pull request is merged or closed.
12. Publish the manifest from the next successful production run as the new CI baseline.

Common mistakes are comparing against a manifest produced by the current PR instead of the last successful production manifest; putting production state and the PR target in the same directory or namespace; using state:modified without considering affected downstream descendants; rebuilding the whole upstream graph instead of using deferral; assuming ephemeral models can be deferred; reusing one schema for multiple simultaneous pull requests; configuring the PR target to write into production; assuming --defer always prefers production even when relations already exist in the current target; treating a successful model build as sufficient while ignoring tests; and leaving disposable PR schemas behind after pull requests are closed.

Present the design as four boundaries: production manifest for state, state:modified+ for selection, --defer for eligible unchanged upstream refs, and one disposable schema per pull request. Then state the safety rule clearly: selected CI work writes only to the PR schema, and merge is blocked unless dbt build and its applicable tests succeed.

SQL is a declarative language for defining, querying, and changing relational data. I use it to work with tables, rows, columns, and keys. SQL lets me select and filter records, join related tables, aggregate data, use window functions, and make controlled changes with transactions. In data engineering, I use SQL to transform, validate, and model warehouse data inside pipelines. There is no single time or space complexity because performance depends on the query, data, indexes, execution plan, and database engine.

SQL gives us a simple way to describe what data we want or what change we want to make. Relational data is stored in tables. Rows represent records, columns represent fields, and keys identify or connect records. Data engineers use SQL to read, filter, combine, summarize, transform, validate, and model data. They also use it inside pipelines that move data from source systems into warehouses and then to downstream users. The exact syntax and execution behavior can differ between SQL platforms.

1. Would you like a conceptual explanation, or should I also show small SQL examples?
2. Should I discuss a particular SQL database or warehouse dialect?

A relational system organizes data into tables. A row represents one record. A column represents one field. In the diagram, the customers table contains customer_id, name, email, and country. The customer_id column is the primary key, so it uniquely identifies each customer. The orders table contains order_id, customer_id, order_date, and amount. Its customer_id is a foreign key that links each order to a customer.

SQL is declarative. I describe the result I want instead of writing every low-level processing step. For example, SELECT customer_id, name FROM customers; asks the database for two columns. A statement such as CREATE TABLE can define relational structures, while statements such as SELECT query data and statements such as UPDATE change data. The database engine decides how to execute the request.

SELECT reads data. WHERE filters rows. The diagram shows SELECT * FROM orders WHERE amount > 100;, which keeps only orders above 100. A JOIN combines related tables. The diagram joins orders and customers using o.customer_id = c.customer_id. GROUP BY creates groups so aggregate functions such as COUNT and SUM can summarize each customer's orders.

A window function calculates across related rows while keeping the individual rows in the result. The diagram uses ROW_NUMBER() with PARTITION BY customer_id and ORDER BY order_date to number each customer's orders by date. SQL also supports set-based operations. The diagram uses UNION to combine the results of two queries. Transactions group related changes into one logical unit. The diagram starts a transaction with BEGIN, updates order 101, and finishes with COMMIT.

The diagram shows data moving from source systems to ingestion, SQL transformation, a data warehouse, SQL validation, and then consumers. Data engineers use SQL to clean and standardize data, join multiple sources, aggregate metrics, build warehouse models, and run data-quality checks. Warehouse models can include fact and dimension tables. SQL work can also be part of repeatable pipelines run with data-transformation or orchestration tools. The final trusted data can serve analytics, business intelligence, machine learning, and other downstream uses.

SQL has common concepts, but SQL dialects and database engines are not identical. PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, and other systems can differ in syntax, functions, supported features, transaction behavior, and query optimization. A data engineer should understand the general SQL ideas and also check the rules and documentation for the specific platform being used.

This is a conceptual SQL question, so there is no single algorithm or data structure to select. The central idea is relational and set-based processing. Tables contain rows and columns. Primary keys uniquely identify rows, and foreign keys connect related tables. SQL statements describe the desired result or data change. The database engine chooses the physical execution plan. Data engineers combine filtering, joins, grouping, window functions, set operations, and transactions to transform and validate data. The key invariant is that each operation must preserve the intended meaning and grain of the data while using the correct keys and relationships.

There is no single time complexity or auxiliary space complexity for SQL as a language. Performance depends on the exact query, number of rows, indexes, join strategy, grouping or sorting work, available memory, data distribution, and the execution plan chosen by the engine. For example, a filter that can use a suitable index may behave very differently from a full table scan. Joins can also use different execution strategies. In practice, a data engineer checks the query plan and engine behavior when query performance matters.

A common mistake is describing SQL only as a language for reading data and forgetting that it can also define and change relational data. Another mistake is confusing rows with columns or primary keys with foreign keys. Candidates may join tables without using the correct relationship key. They may also use GROUP BY without realizing that aggregation changes the result grain. Another common mistake is treating a window function like a normal aggregation even though a window function can keep individual rows. Finally, candidates should not assume every SQL dialect has identical syntax, functions, transaction behavior, or execution characteristics.

Explain SQL from a data engineer's point of view. Start with tables and keys, then describe SELECT, filtering, joins, grouping, window functions, set operations, and transactions. Finish by connecting those operations to transformation, validation, warehouse modeling, and repeatable pipeline work.