QSDA common mistakes
QSDA · Understand the traps and how to avoid them.
Joining two fact tables (or a fact and a lookup with different grain) in the load script to "simplify" the model, then reporting revenue inflated by the row multiplication.
Joining feels like SQL habit and removes a visible island of tables, so the duplication is invisible in the sheet; candidates validate row counts before the join but not sums after it.
What to do instead
Keep facts at their native grain and relate them through conformed keys or a link table; after any join, compare a control aggregate (e.g. Sum(Sales)) against the pre-join total and assert it is unchanged.
Leaving autogenerated synthetic keys (Synthetic Key, $Syn 1) in the model because the app still opens and the sheet 'looks right'.
Qlik resolves the association silently, so the model appears connected; candidates see associations in the preview and assume correctness without checking the key names.
What to do instead
Rename the shared field to one common key before loading, or drop the redundant field and create an explicit composite key (e.g. KeyField = Field1 & '|' & Field2) in both tables; then verify the table viewer shows no $Syn tables.
Using a LEFT JOIN instead of LEFT KEEP (or applying the wrong join direction) when trimming a dimension, resulting in extra columns and duplicated fact rows rather than a filtered fact.
The syntaxes are one keyword apart and both 'work' without error; candidates optimise for fewer tables rather than for preserving grain.
What to do instead
Use LEFT KEEP when the goal is to filter the fact to matching dimension keys and LEFT JOIN only when genuinely adding attributes; state the intent in a comment and check that the fact row count either stays the same (KEEP-filtered) or is unchanged per key.
Relying on preceding/resident loads whose field order or WHERE clause silently changes after an upstream edit, so the final table keeps stale or missing rows without an error.
Resident loads reference the previous table by name and position; when a field is inserted upstream, an unqualified SELECT * or a positional reference picks up the wrong data instead of failing.
What to do instead
Always list fields explicitly in resident loads, load the source table immediately before its resident consumers, and add a row-count / null-count check (e.g. Compare count of keys after each stage) so drift surfaces at reload time.
Dropping source tables and interim resident tables too early, or failing to drop them at all, leaving either a broken later reference or a bloated model with orphan tables.
Table lifetime is not visually obvious in a long script; candidates copy patterns without tracking which table a later resident load depends on.
What to do instead
Adopt a convention of load-transform-drop in sequence, drop tables only after their last consumer, and confirm in the table viewer that only final fact/dimension tables remain.
Building incremental loads on a timestamp without handling late-arriving or updated records, so corrected historical rows never reach the QVD/app.
The obvious WHERE ModifiedDate > last run works for inserts but silently misses backdated edits and reruns after failures; candidates test only the happy path.
What to do instead
Use a lookback window (or a delete-and-reinsert of affected keys) rather than a strict greater-than, persist the high-water mark only after a successful store, and reconcile record counts and a control total against source after each run.
Treating nulls and empty strings as equivalent, or letting a numeric field load as mixed text/numeric so that Sum() returns 0 or drops values.
Qlik's dual data type and the NullAsNull/NullAsValue behaviour differ from SQL expectations, and a clean-looking table doesn't reveal that a column is text.
What to do instead
Check field types and null counts explicitly (Num(), text between string and number, IsNull, Len(Trim()) tests), decide and document the null policy per field, and validate that Sum/Count on the field matches a source-side query.
Associating tables on keys that differ in cleanliness or type (trailing spaces, case, leading zeros, numeric vs text), producing a many-to-many or unmatched-key situation the candidate never investigates.
Qlik matches on the exact dual value, so 'ABC ' and 'ABC' are different keys; candidates assume the database already enforces consistency and skip key profiling.
What to do instead
Standardise keys at load time with Trim(), Upper()/Lower(), and consistent Num#()/Text() formatting on both sides, and validate key coverage by counting distinct keys and orphan keys on each side of the relationship.
Applying Section Access without testing it as a reduced user, or leaving the reduction fields in the fact table so a user can infer restricted values from unrelated aggregates.
The developer's own session runs as ADMIN and ignores restrictions, so the app looks fine; the link between the reduction field and the data model is easy to over-share.
What to do instead
Maintain a small test-user set, reload and open the app under each identity to confirm row counts, and place the reduction field only where it is needed (and consider OMIT on sensitive fields) rather than exposing it across all tables.
Skipping the layered QVD architecture and reloading straight from source into the published app on every schedule, or overwriting the transformed QVD before its dependent apps have reloaded.
A single-stage script is fastest to build and works in development with small data; ordering and ownership problems only appear once multiple apps share the same QVD.
What to do instead
Separate extract, transform, and presentation layers, store each layer to its own QVD, define and document the reload chain so each QVD refreshes before its consumers, and version or timestamp QVDs so a failed stage is recoverable.